仪表板报告 - 系统

数据与报告
, ,
1

这是系统仪表板报告的 SQL 版本。

此仪表板报告提供系统自动发送的个人消息数量的每日计数。

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

SELECT
  DATE(created_at) AS day,
  COUNT(*) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  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 表中提取数据来工作——特别是那些在给定时间范围内符合系统消息子类型的私人消息。我们来分解一下：

  • 日期参数：
    • 查询接受两个参数 :start_date 和 :end_date，它们定义了报告的日期范围。两个日期参数都接受 YYYY-MM-DD 格式的日期。
  • SELECT：查询选择两个字段：
    • DATE(created_at) AS day：这会提取 created_at 时间戳的日期部分，有效地按创建日期的记录进行分组。
    • COUNT(*) AS notifications_count：这会计算每天系统生成的 PM 的总数。
  • FROM：将 topics 表指定为数据源，其中包含所有主题的记录，包括私人消息。
  • WHERE：包含多个过滤器以缩小数据集范围：
    • archetype = 'private_message'：仅包括作为私人消息的条目。
    • subtype = 'system_message'：进一步将选择范围缩小到仅系统生成的邮件。
    • created_at BETWEEN :start_date AND :end_date：将 PM 筛选到在参数指定的范围内创建的那些。
    • deleted_at IS NULL：排除已删除的消息。
    • user_id > 0：确保消息与真实用户帐户相关联，而不是系统或匿名帐户。
  • GROUP BY：根据创建日期对结果进行分组。
  • ORDER BY：按升序日期对最终结果集进行排序，确保每日计数的按时间顺序排列。

示例结果

day notifications_count
2024-01-01 5
2024-01-02 7
2024-01-03 11
2024-01-04 14
2024-01-05 8
3 个赞
2

这可以与特定的 PM 相关联，例如欢迎 PM 吗？

我想知道每天有多少个发送出去。

有什么想法？

1 个赞
3

是的，最好的方法是在查询的 WHERE 语句中添加一个部分，通过主题 title 进行过滤。

例如：

WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  AND created_at BETWEEN :start_date AND :end_date
  AND title = 'Greetings!'

这将查找所有 Greetings! 消息。

请注意，一些系统消息不包含真实用户，因此使用此类查询时，可能需要删除 AND user_id > 0 行。

您可能还想删除 AND deleted_at IS NULL，以便仍然计算用户可能删除的欢迎消息。

您可以使用正则表达式来匹配具有相似标题的主题。

要在 PostgreSQL 中使用正则表达式（regex）按标题匹配主题，您可以使用 ~ 运算符，它将正则表达式与字符串进行匹配。查询结构如下所示：

SELECT *
FROM topics
WHERE title ~ 'YourRegexPatternHere'

将 'YourRegexPatternHere' 替换为您要与 title 字段匹配的实际正则表达式模式。

例如，如果您要查找标题包含“Welcome”（不区分大小写）的主题，可以使用：

SELECT *
FROM topics
WHERE title ~* 'Welcome'

~* 运算符用于不区分大小写的匹配。

3 个赞
4

太棒了。非常感谢！我会试试的！

1 个赞