仪表板报告 - 无响应主题

[SaraDev (Sara Devlaeminck)]

这是“无回复主题”仪表板报告的 SQL 版本。
仪表板报告旨在计算在指定日期范围内创建但未收到其他用户任何回复的主题数量。此报告可按特定类别进行筛选，并可选择包含子类别。

-- [params]
-- date :start_date = 2024-01-01    
-- date :end_date = 2024-02-01
-- null int :category_id
-- boolean :include_subcategories = false

WITH no_response_total AS (
SELECT *
    FROM (
      SELECT t.id, t.created_at, MIN(p.post_number) first_reply
      FROM topics t
      LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
      WHERE t.archetype <> 'private_message'
      AND t.deleted_at ISNULL
      AND (
        :category_id IS NULL
        OR t.category_id = :category_id
        OR (
          :include_subcategories
          AND t.category_id IN (
            SELECT id FROM categories WHERE parent_category_id = :category_id
          )
        )
      )
      GROUP BY t.id
    ) tt
    WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)

SELECT 
  DATE(nrt.created_at) AS date, 
  COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY date
ORDER BY date ASC

参数

• 日期参数：
  • 该查询接受两个参数 :start_date 和 :end_date，它们定义了报告的日期范围。两个日期参数都接受 YYYY-MM-DD 格式的日期。
• 类别参数：
  • :category_id：一个整数参数，可以设置为特定类别的 ID，以将分析范围缩小到该类别内的帖子。如果设置为 null 或未提供，则考虑所有类别的帖子。
  • :include_subcategories：一个布尔参数，用于控制是否包含指定 :category_id 的子类别中的帖子。如果设置为 true，报告将包含指定类别及其子类别中的帖子链接；如果设置为 false，则仅考虑指定类别。

SQL 查询说明

查询以一个名为 no_response_total 的公共表表达式 (CTE) 开始。此 CTE 执行以下步骤：

• 选择主题：它从 topics 表中选择所有主题 (t.id) 及其创建日期 (t.created_at)。
• 与帖子进行左连接：它与 posts 表进行左连接，以查找每个主题的第一个回复。连接条件确保帖子不是由主题创建者创建的 (p.user_id != t.user_id)，帖子未被删除 (p.deleted_at IS NULL)，并且帖子类型为 1，这通常表示标准回复。
• 筛选主题：查询会筛选掉私信主题 (t.archetype <> 'private_message') 和已删除的主题 (t.deleted_at ISNULL)。
• 类别筛选：如果提供了 :category_id，查询将筛选主题，仅包含指定类别中的主题。如果 :include_subcategories 为 true，它还将包含指定类别子类别中的主题。
• 分组和最小帖子编号：按主题 ID 对主题进行分组，并计算最小帖子编号 (MIN(p.post_number)) 以查找第一个回复。
• 筛选无回复：子查询 tt 筛选掉第一个回复的帖子编号大于或等于 2 的主题，只留下没有回复的主题 (tt.first_reply IS NULL) 或只有原始帖子的主题 (tt.first_reply < 2)。

在定义 no_response_total CTE 后，主查询执行以下操作：

• 按日期范围筛选：它根据提供的开始和结束日期 (:start_date 和 :end_date) 筛选 CTE 中的主题。
• 计算无回复主题的数量：它计算指定范围内每天无回复的主题数量。
• 按日期分组：结果按主题创建日期 (DATE(nrt.created_at)) 进行分组。
• 排序：结果按日期升序排序。

示例结果

date	topics_without_response
2024-01-02	4
2024-01-03	8
2024-01-04	4
2024-01-05	3
2024-01-06	3

[SStrong (Sophie Strong)]

@SaraDev

-- [params]
-- date :start_date = 2024-01-01    
-- date :end_date = 2024-02-01
-- null int :category_id
-- boolean :include_subcategories = false

WITH no_response_total AS (
SELECT *
    FROM (
      SELECT t.id, t.created_at, MIN(p.post_number) first_reply
      FROM topics t
      LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
      WHERE t.archetype <> 'private_message'
      AND t.deleted_at ISNULL
      AND (
        :category_id IS NULL
        OR t.category_id = :category_id
        OR (
          :include_subcategories
          AND t.category_id IN (
            SELECT id FROM categories WHERE parent_category_id = :category_id
          )
        )
      )
      GROUP BY t.id
    ) tt
    WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)

SELECT 
  DATE(nrt.created_at) AS date, 
  COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY date
ORDER BY date ASC

你能做一个不包含参数的版本吗？我想创建一个回顾过去7天的版本，然后通过电子邮件发送给人们，但由于其中设置了参数，我很难使用此代码。

谢谢。

[SaraDev (Sara Devlaeminck)]

@SStrong

你能做一个不包含参数的版本吗？

是的，这是查询的更新版本，它不使用参数即可追溯到 7 天前。

此版本不包含按类别或子类别进行的任何筛选。

WITH no_response_total AS (
  SELECT *
  FROM (
    SELECT 
      t.id, 
      t.created_at, 
      MIN(p.post_number) AS first_reply
    FROM 
      topics t
    LEFT JOIN 
      posts p 
    ON 
      p.topic_id = t.id 
      AND p.user_id != t.user_id 
      AND p.deleted_at IS NULL 
      AND p.post_type = 1
    WHERE 
      t.archetype <> 'private_message'
      AND t.deleted_at IS NULL
      AND (
        t.category_id = :category_id
        OR t.category_id IN (
          SELECT id FROM categories WHERE parent_category_id = :category_id
        )
      )
    GROUP BY 
      t.id
  ) tt
  WHERE 
    tt.first_reply IS NULL 
    OR tt.first_reply < 2
)

SELECT 
  DATE(nrt.created_at) AS date, 
  COUNT(nrt.id) AS topics_without_response
FROM 
  no_response_total nrt
WHERE 
  nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND CURRENT_DATE
GROUP BY 
  date
ORDER BY 
  date ASC

如果你想调整查询回溯的长度，只需更改查询中的这一行：

nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND 

[SStrong (Sophie Strong)]

谢谢您的回复，下次需要时我会再回来处理，因为现在重点已转移到其他事情上，所以没有时间回来处理。

[SStrong (Sophie Strong)]

你好，

我很难将此内容从按日期驱动改为按年月驱动。

我已经尝试了多种方法来使其正常工作，但它一直告诉我该列不存在（实际上它存在，因为我刚刚在一个 with 语句中创建了它，然后又引用了它）。

有人能修改这段代码，使其不再按天查看无响应的主题，而是可以按年、按月等查看吗？

谢谢。

[SaraDev (Sara Devlaeminck)]

你好 Sophie，

@SStrong

如何修改此代码，使其不再按天查看没有回复的主题，而是能够按年、按月等查看？

要修改查询，使其能够按年、月或其他时间间隔聚合没有回复的主题，您可以在 date_trunc 函数中添加一个参数来指定所需的时间间隔。

例如：

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = day -- Options: day, week, month, year

WITH no_response_total AS (
  SELECT *
  FROM (
    SELECT t.id, t.created_at, MIN(p.post_number) first_reply
    FROM topics t
    LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
    WHERE t.archetype <> 'private_message'
      AND t.deleted_at ISNULL
      AND (
        :category_id IS NULL
        OR t.category_id = :category_id
        OR (
          :include_subcategories
          AND t.category_id IN (
            SELECT id FROM categories WHERE parent_category_id = :category_id
          )
        )
      )
    GROUP BY t.id
  ) tt
  WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)

SELECT
  date_trunc(:interval, nrt.created_at)::date AS period,
  COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period
ORDER BY period ASC

如果您想删除参数，也可以使用类似以下的查询：

WITH no_response_total AS (
  SELECT *
  FROM (
    SELECT t.id, t.created_at, MIN(p.post_number) first_reply
    FROM topics t
    LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
    WHERE t.archetype <> 'private_message'
      AND t.deleted_at ISNULL
    GROUP BY t.id
  ) tt
  WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)

SELECT
  date_trunc('year', nrt.created_at)::date AS period,
  COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN '2024-01-01' AND '2025-01-01'
GROUP BY period
ORDER BY period ASC

[SStrong (Sophie Strong)]

谢谢，这解决了如何获取年份的问题。

我现在又卡住了，因为 Postgre 中的日期似乎表现不同

to_char(t.created_at, 'MM-YY') 作为 Yearmonth，

这给了我 10-22，代表“10 月-22 日”。

我该如何将 10-22 更改为 Oct-22？我尝试在 discourse 中查找指导，但找不到，除非我不知道该去哪里找？

谢谢。

[SaraDev (Sara Devlaeminck)]

@SStrong

我如何能将 10-22 更改为 Oct-22？

要在 PostgreSQL 中将日期格式从 10-22 更改为 Oct-22，您可以使用 TO_CHAR 函数。此函数允许您以各种方式格式化日期，例如：

SELECT
TO_CHAR(TO_DATE('10-22', 'MM-YY'), 'Mon-YY') AS formatted_date

在此 SQL 语句中：

• TO_DATE('10-22', 'MM-YY') 使用 MM-YY 格式将字符串 10-22 转换为日期类型。
• TO_CHAR(..., 'Mon-YY') 然后将此日期格式化为显示缩写的月份名称后跟年份，结果为 Oct-22。

以下是基于上面共享的 Topics with No Response 查询的 interval 参数的另一个示例：

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = month

WITH no_response_total AS (
  SELECT *
  FROM (
    SELECT t.id, t.created_at, MIN(p.post_number) first_reply
    FROM topics t
    LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
    WHERE t.archetype <> 'private_message'
      AND t.deleted_at ISNULL
      AND (
        :category_id IS NULL
        OR t.category_id = :category_id
        OR (
          :include_subcategories
          AND t.category_id IN (
            SELECT id FROM categories WHERE parent_category_id = :category_id
          )
        )
      )
    GROUP BY t.id
  ) tt
  WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)

SELECT
  TO_CHAR(date_trunc(:interval, nrt.created_at)::date, 'Mon-YY') AS period,
  COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period, date_trunc(:interval, nrt.created_at)::date
ORDER BY date_trunc(:interval, nrt.created_at)::date ASC

供参考，此查询的结果将如下所示：

period	topics_without_response
Dec-23	123
Jan-24	455
Feb-24	789
…	…

[SStrong (Sophie Strong)]

谢谢！