# 仪表板报告 - 无响应主题
**URL:** https://meta.discourse.org/t/dashboard-report-topics-with-no-response/294002
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [2024 年2 月 2 日 22:55 UTC](https://meta.discourse.org/t/dashboard-report-topics-with-no-response/294002 "2024-02-02T22:55:48Z")
**Posts on this page:** 9
**Page:** 1
### 作者：  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### 发布日期： [2024 年2 月 2 日 22:55 UTC](https://meta.discourse.org/t/dashboard-report-topics-with-no-response/294002/1 "2024-02-02T22:55:48Z")
这是“无回复主题”仪表板报告的 SQL 版本。
仪表板报告旨在计算在指定日期范围内创建但未收到其他用户任何回复的主题数量。此报告可按特定类别进行筛选，并可选择包含子类别。
```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](https://meta.discourse.org/u/SStrong)
#### 发布日期： [2024 年7 月 2 日 15:43 UTC](https://meta.discourse.org/t/dashboard-report-topics-with-no-response/294002/2 "2024-07-02T15:43:24Z")
> [@SaraDev](#):
>
> ```plaintext
> -- [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](https://meta.discourse.org/u/SaraDev)
#### 发布日期： [2024 年7 月 8 日 22:06 UTC](https://meta.discourse.org/t/dashboard-report-topics-with-no-response/294002/3 "2024-07-08T22:06:36Z")
> [@SStrong](#):
>
> 你能做一个不包含参数的版本吗？
是的，这是查询的更新版本，它不使用参数即可追溯到 7 天前。 🙂
此版本不包含按类别或子类别进行的任何筛选。
```sql
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
```
如果你想调整查询回溯的长度，只需更改查询中的这一行：
```sql
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND
```
---
### 作者：  [@SStrong](https://meta.discourse.org/u/SStrong)
#### 发布日期： [2024 年7 月 23 日 07:24 UTC](https://meta.discourse.org/t/dashboard-report-topics-with-no-response/294002/4 "2024-07-23T07:24:36Z")
谢谢您的回复，下次需要时我会再回来处理，因为现在重点已转移到其他事情上，所以没有时间回来处理。
---
### 作者：  [@SStrong](https://meta.discourse.org/u/SStrong)
#### 发布日期： [2024 年9 月 3 日 14:23 UTC](https://meta.discourse.org/t/dashboard-report-topics-with-no-response/294002/5 "2024-09-03T14:23:34Z")
你好，
我很难将此内容从按日期驱动改为按年月驱动。
我已经尝试了多种方法来使其正常工作，但它一直告诉我该列不存在（实际上它存在，因为我刚刚在一个 with 语句中创建了它，然后又引用了它）。
有人能修改这段代码，使其不再按天查看无响应的主题，而是可以按年、按月等查看吗？
谢谢。
---
### 作者：  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### 发布日期： [2024 年9 月 3 日 19:38 UTC](https://meta.discourse.org/t/dashboard-report-topics-with-no-response/294002/6 "2024-09-03T19:38:00Z")
你好 Sophie，
> [@SStrong](#):
>
> 如何修改此代码，使其不再按天查看没有回复的主题，而是能够按年、按月等查看？
要修改查询，使其能够按年、月或其他时间间隔聚合没有回复的主题，您可以在 `date_trunc` 函数中添加一个参数来指定所需的时间间隔。
例如：
```sql
-- [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
```
如果您想删除参数，也可以使用类似以下的查询：
```sql
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](https://meta.discourse.org/u/SStrong)
#### 发布日期： [2024 年9 月 4 日 09:20 UTC](https://meta.discourse.org/t/dashboard-report-topics-with-no-response/294002/7 "2024-09-04T09:20:30Z")
谢谢，这解决了如何获取年份的问题。
我现在又卡住了，因为 Postgre 中的日期似乎表现不同
`to_char(t.created_at, 'MM-YY')` 作为 `Yearmonth`，
这给了我 10-22，代表“10 月-22 日”。
我该如何将 10-22 更改为 Oct-22？我尝试在 discourse 中查找指导，但找不到，除非我不知道该去哪里找？
谢谢。
---
### 作者：  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### 发布日期： [2024 年9 月 4 日 20:23 UTC](https://meta.discourse.org/t/dashboard-report-topics-with-no-response/294002/8 "2024-09-04T20:23:34Z")
> [@SStrong](#):
>
> 我如何能将 10-22 更改为 Oct-22？
要在 PostgreSQL 中将日期格式从 `10-22` 更改为 `Oct-22`，您可以使用 `TO_CHAR` 函数。此函数允许您以各种方式格式化日期，例如：
```sql
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` 参数的另一个示例：
```sql
-- [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](https://meta.discourse.org/u/SStrong)
#### 发布日期： [2024 年9 月 5 日 13:44 UTC](https://meta.discourse.org/t/dashboard-report-topics-with-no-response/294002/9 "2024-09-05T13:44:53Z")
谢谢！