仪表板报告 - 用户到用户

数据与报告
, ,
1

这是用户对用户（含回复）和用户对用户（不含回复）的仪表板报告的 SQL 版本。

本报告计算指定日期范围内用户每天发送的私人消息数量，从而深入了解用户在私人通信中的参与活动。

--[params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- boolean :include_replies = false

SELECT
    p.created_at::date as day,
    COUNT(p.user_id) as personal_messages
FROM posts p
INNER JOIN topics t ON (p.topic_id = t.id)
WHERE archetype = 'private_message'
    AND p.user_id > 0
    AND t.subtype = 'user_to_user'
    AND p.deleted_at ISNULL
    AND t.deleted_at ISNULL
    AND p.created_at::date BETWEEN :start_date AND :end_date
    AND (:include_replies = true OR p.post_number = 1)
GROUP BY p.created_at::date
ORDER BY p.created_at::date

SQL 查询说明

此 SQL 查询执行以下操作：

  • 参数：
    • :start_date 和 :end_date，用于在指定日期范围内筛选数据。两个日期参数均接受 YYYY-MM-DD 格式。
    • :include_replies 用于确定是否在计数中包含对初始消息的回复。默认设置为 false，以反映用户对用户（不含回复）报告的行为。将此参数设置为 true 将反映用户对用户（含回复）报告的行为。
  • 数据选择：
    • 查询从 posts 表中选择 created_at 日期，该日期表示每条消息创建的日期。
  • 连接操作：
    • 查询在 posts 和 topics 表之间通过它们的 id 字段执行 INNER JOIN。此连接至关重要，因为它将每个帖子与其对应的帖子主题关联起来，使我们能够仅筛选出属于私人消息对话的帖子。
  • 筛选条件：查询应用了多个筛选条件：
    • 它仅考虑属于 archetype 为 ‘private_message’ 的主题的帖子，确保仅计算私人消息。
    • 它通过确保 user_id 大于 0 来筛选出未与用户关联的帖子。
    • 它将结果进一步缩小到 subtype 为 ‘user_to_user’ 的主题，这表示用户之间的私人消息。
    • 它通过检查两个表的 deleted_at 均为 NULL 来排除已删除的帖子或主题。
    • 它应用日期范围筛选，仅包括在 :start_date 和 :end_date 之间创建的帖子。
    • 如果 :include_replies 为 false，则仅计算每个主题的第一个帖子（p.post_number = 1），不包括回复。
  • 聚合：查询按帖子创建的日期对结果进行分组，从而可以计算每天发送的消息数量。
  • 排序：最后，查询按日期升序对结果进行排序，提供用户对用户私人消息活动的按时间顺序排列的序列。

示例结果

day personal_messages
2023-11-11 92
2023-11-12 57
2023-11-13 345
2023-11-14 124
2023-11-15 56
4 个赞