仪表盘报告 - 用户访问

[SaraDev (Sara Devlaeminck)]

这是用户访问和用户访问（移动设备）的仪表板报告的 SQL 版本。

此仪表板报告通过跟踪每日用户访问来提供用户参与度方面的见解。管理员可以根据日期范围、组成员资格和设备类型（移动设备或非移动设备）筛选数据。

了解用户访问模式可以帮助管理员做出明智的决策，以增强用户参与度和社区健康状况。

-- [params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- null text :group_name
-- boolean :mobile_views = false

SELECT uv.visited_at AS date,
       COUNT(DISTINCT uv.user_id) AS visits_count
FROM user_visits uv
LEFT JOIN group_users gu ON gu.user_id = uv.user_id
LEFT JOIN groups g ON g.id = gu.group_id AND (:group_name IS NULL OR g.name = :group_name)
WHERE uv.visited_at BETWEEN :start_date AND :end_date
AND (:group_name IS NULL OR g.name IS NOT NULL)
AND (:mobile_views = false OR uv.mobile = true)
GROUP BY uv.visited_at
ORDER BY uv.visited_at ASC

SQL 查询说明

SQL 查询通过从 user_visits 表中选择数据来运行，该表记录了用户对平台的每次访问。查询的主要组成部分解释如下：

• 参数
  • :start_date 和 :end_date 定义报告的日期范围。两个日期参数都接受 YYYY-MM-DD 格式的日期。
  • :group_name 允许按特定用户组名称进行筛选。如果未指定，则包含所有组的数据。
  • :mobile_views 确定是仅包含移动设备访问（true）还是所有访问（false）。
• 数据选择和连接操作：
  • 查询选择访问日期（visited_at）并计算不同的用户 ID（user_id）以计算每天的唯一访问次数。
  • 它与 group_users 表执行 LEFT JOIN 以将用户访问与其各自的组关联，然后与 groups 表执行另一个 LEFT JOIN 以按指定的组名（如果存在）进行筛选。
• 筛选：
  • WHERE 子句根据输入参数应用筛选器：
    • 它确保仅包含指定日期范围内的访问。
    • 如果指定了组名，则按组名进行筛选，并确保计算属于该组的用户的访问次数。
    • 如果 :mobile_views 参数设置为 true，则仅包括移动访问。
• 分组和排序：
  • 查询按访问日期（visited_at）对结果进行分组，以汇总每天的访问次数。
  • 它按日期升序对结果进行排序，提供用户访问的按时间顺序的视图。

示例结果

date	visits_count
2024-01-06	67
2024-01-07	71
2024-01-08	88
2024-01-09	79
2024-01-10	78
…	…