仪表盘报告：通知用户

数据与报告
, ,
1

这是 Notify User 的仪表板报告的 SQL 版本。

此仪表板报告提供了用户因帖子上的标记而被私下通知的次数的每日计数。

-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16

SELECT
  DATE(created_at) AS day,
  COUNT(1) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'notify_user'
  AND created_at BETWEEN :start_date AND :end_date
  AND deleted_at IS NULL
  AND user_id > 0
GROUP BY DATE(created_at)
ORDER BY day

SQL 查询说明

该查询通过从 topics 表中提取数据来工作——特别是那些符合在给定时间范围内向用户发送的私信的条目，子类型为 notify_user。让我们分解一下：

  • 日期参数：
    • 查询接受两个参数 :start_date 和 :end_date，它们定义了报告的日期范围。两个日期参数都接受 YYYY-MM-DD 格式的日期。
  • 选择和计数：
    • DATE(created_at) 将 created_at 时间戳转换为日期格式，并将其标记为 day。这允许按创建消息的日期对消息进行分组和计数。
    • COUNT(1) 用于计算每天的条目数，并将此计数标记为 notifications_count。
  • 过滤条件：
    • 查询仅过滤 archetype 为 'private_message' 的条目，确保我们只查看消息，而不查看其他类型的帖子。
    • subtype 限制为 'notify_user'，隔离了用户收到有关标记帖子通知的情况。
    • 它检查消息的 created_at 时间戳是否在指定的 :start_date 和 :end_date 之间。
    • 已删除的消息（deleted_at IS NULL）不计入计数，以确保报告仅反映活动通信。
    • 最后，它通过确认 user_id > 0 来确保消息与有效用户相关联。
  • 分组和排序：
    • 查询使用 GROUP BY DATE(created_at) 按指定日期范围内的每一天聚合结果。
    • 然后，它使用 ORDER BY day 按时间顺序对结果进行排序，使报告易于阅读和理解。

示例结果

day notifications_count
2023-12-17 1
2023-12-18 1
2023-12-21 1
2023-12-23 1
2023-12-26 1
1 个赞