仪表板报告 - 帖子

数据与报告
, ,
1

这是帖子仪表板报告的 SQL 版本。

本报告提供指定日期范围内创建的帖子的每日计数。它旨在跟踪常规主题中的活动，不包括来自私人消息和其他特殊原型的帖子。

--[params]
-- date :start_date
-- date :end_date

SELECT 
    p.created_at::date AS "Day",
    COUNT(p.id) AS "Count"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at ISNULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
GROUP BY p.created_at::date
ORDER BY 1

SQL 查询说明

  • 参数:
    • 查询接受两个参数 :start_date 和 :end_date，它们定义了报告的日期范围。两个日期参数都接受 YYYY-MM-DD 的日期格式。

SQL 查询执行以下操作：

  • 数据选择:
    • 它选择每个帖子创建的日期 (created_at::date)，并将其转换为日期格式以忽略时间部分。
    • 它还计算每天创建的帖子数量 (COUNT(p.id))。
  • 连接:
    • 查询使用 INNER JOIN 将 posts 表与 topics 表连接起来。此连接确保仅考虑与现有主题关联的帖子。
    • 它会过滤掉任何已删除的主题 (t.deleted_at ISNULL)。
  • 筛选:
    • 它筛选帖子，仅包括指定日期范围内的帖子 (p.created_at::date BETWEEN :start_date AND :end_date)。
    • 它排除已删除的帖子 (p.deleted_at ISNULL)。
    • 它将结果限制为来自常规主题的帖子 (t.archetype = 'regular')。
    • 它仅考虑 p.post_type = 1 的帖子，排除版主操作、耳语和 small_action 帖子。
  • 分组和排序:
    • 结果按帖子创建日期分组 (GROUP BY p.created_at::date)。
    • 最终输出按日期升序排序 (ORDER BY 1)，其中 1 指的是 SELECT 语句中的第一列，即日期。

示例结果

Day Count
2023-11-12 25
2023-11-13 35
2023-11-14 38
2023-11-15 47
2023-11-16 36
2023-11-17 79
… …
2 个赞
2

这太棒了。
能否在运行时按日期定义类别/子类别？
另外，能否按用户列出结果，或定义用户？

我想了解在我的支持工单区域，我的支持人员在（某个时间段内）发布了多少帖子。

3

是的，您可以使用以下查询：

--[params]
-- date :start_date
-- date :end_date
-- null category_id :category_id 
-- null user_id :user_id
-- boolean :include_subcategories = false

SELECT 
    u.username AS "用户",
    p.created_at::date AS "日期",
    COUNT(p.id) AS "计数"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at IS NULL
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON t.category_id = c.id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at IS NULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
    AND (
        :category_id IS NULL 
        OR t.category_id = :category_id
        OR (:include_subcategories AND c.parent_category_id = :category_id)
    )
    AND (:user_id IS NULL OR p.user_id = :user_id)
GROUP BY u.username, p.created_at::date
ORDER BY p.created_at::date ASC, u.username

参数：

  • :start_date & :end_date: 定义报告时间范围（必需）
  • :category_id: 过滤特定类别的可选参数
  • :user_id: 过滤特定用户的可选参数
  • :include_subcategories: 包含所选类别子类别的选项

此查询显示：

  • 用户: 帖子作者的用户名
  • 日期: 帖子创建的日历日期
  • 计数: 该用户在该日期创建的帖子数量

示例数据：

用户 日期 计数
user 1 2023-01-01 3
user 2 2023-01-01 2
user 3 2023-01-01 1
user 1 2023-01-02 2
user 2 2023-01-02 3
user 1 2023-01-03 1
… … …
2 个赞
4

谢谢，这对我帮助很大！

2 个赞