此数据探索器报告提供了在指定日期范围内，对网站上已解决和未解决主题的全面分析，并可选择按特定标签进行筛选。
此报告需要启用 Discourse Solved 插件。
此报告需要启用 此报告对于希望了解社区响应能力并识别用户支持和参与度改进领域的管理员和版主特别有用。
带日期和标签参数的已解决和未解决主题统计信息
--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all
WITH valid_topics AS (
SELECT
t.id,
t.user_id,
t.title,
t.views,
(SELECT COUNT(*) FROM posts WHERE topic_id = t.id AND deleted_at IS NULL AND post_type = 1) - 1 AS "posts_count",
t.created_at,
(CURRENT_DATE::date - t.created_at::date) AS "total_days",
STRING_AGG(tags.name, ', ') AS tag_names, -- Aggregate tags for each topic
c.name AS category_name
FROM topics t
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
LEFT JOIN tags ON tags.id = tt.tag_id
LEFT JOIN categories c ON c.id = t.category_id
WHERE t.deleted_at IS NULL
AND t.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
GROUP BY t.id, c.name
),
solved_topics AS (
SELECT
vt.id,
dsst.created_at
FROM discourse_solved_solved_topics dsst
INNER JOIN valid_topics vt ON vt.id = dsst.topic_id
),
last_reply AS (
SELECT p.topic_id, p.user_id FROM posts p
INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p
WHERE deleted_at IS NULL
AND post_type = 1
GROUP BY topic_id) x ON x.post = p.id
),
first_reply AS (
SELECT p.topic_id, p.user_id, p.created_at FROM posts p
INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p
WHERE deleted_at IS NULL
AND post_type = 1
AND post_number > 1
GROUP BY topic_id) x ON x.post = p.id
)
SELECT
CASE
WHEN st.id IS NOT NULL THEN 'solved'
ELSE 'unsolved'
END AS status,
vt.tag_names,
vt.category_name,
vt.id AS topic_id,
vt.user_id AS topic_user_id,
ue.email,
vt.title,
vt.views,
lr.user_id AS last_reply_user_id,
ue2.email AS last_reply_user_email,
vt.created_at::date AS topic_create,
COALESCE(TO_CHAR(fr.created_at, 'YYYY-MM-DD'), '') AS first_reply_create,
COALESCE(TO_CHAR(st.created_at, 'YYYY-MM-DD'), '') AS solution_create,
COALESCE(fr.created_at::date - vt.created_at::date, 0) AS "time_first_reply(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (fr.created_at - vt.created_at)) / 3600.00), 0) AS "time_first_reply(hours)",
COALESCE(st.created_at::date - vt.created_at::date, 0) AS "time_solution(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (st.created_at - vt.created_at)) / 3600.00), 0) AS "time_solution(hours)",
vt.created_at::date,
vt.posts_count AS number_of_replies,
vt.total_days AS total_days_without_solution
FROM valid_topics vt
LEFT JOIN last_reply lr ON lr.topic_id = vt.id
LEFT JOIN first_reply fr ON fr.topic_id = vt.id
LEFT JOIN solved_topics st ON st.id = vt.id
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true
WHERE (:tag_name = 'all' OR vt.tag_names ILIKE '%' || :tag_name || '%')
GROUP BY st.id, vt.tag_names, vt.category_name, vt.id, vt.user_id, ue.email, vt.title, vt.views, lr.user_id, ue2.email, vt.created_at, fr.created_at, st.created_at, vt.posts_count, vt.total_days
ORDER BY topic_create, vt.total_days DESC
SQL 查询说明
报告是通过一个复杂的 SQL 查询生成的，该查询利用公共表表达式 (CTE) 来有效地组织和处理数据。查询结构如下：
- valid_topics: 此 CTE 按指定的日期范围和类型（“regular”）筛选主题，排除已删除的主题。它还聚合与每个主题关联的标签，以便稍后按标签名称进行筛选（如果指定）。
- solved_topics: 标识已标记为已解决的主题。
- last_reply: 通过查找未删除且类型为 1（表示常规帖子）的最大帖子 ID（表示最新帖子）来确定每个主题上最后回复的用户。
- first_reply: 与 last_reply 类似，但用于识别原始帖子后第一个回复该主题的用户。
主查询然后合并这些 CTE，以编译每个主题的详细报告，包括它是已解决还是未解决、标签名称、类别名称、主题和用户 ID、电子邮件、浏览量、回复计数以及第一次回复和解决方案的时间。
参数
- start_date: 生成报告的日期范围的开始日期。
- end_date: 生成报告的日期范围的结束日期。
- tag_name: 用于筛选主题的特定标签。使用“all”包含具有任何标签的主题。
结果
报告为每个主题提供以下信息（在指定参数范围内）：
- status: 指示主题是已解决还是仍未解决。
- tag_names: 显示与主题关联的标签。
- category_name: 显示与主题关联的类别。
- topic_id: 主题的唯一标识符。
- topic_user_id: 创建主题的用户的 ID。
- user_email: 主题创建者的电子邮件地址。
- title: 主题的标题。
- views: 主题收到的浏览量。
- last_reply_user_id: 在主题上进行最后回复的用户的 ID。
- last_reply_user_email: 进行最后回复的用户的电子邮件地址。
- topic_create: 创建主题的日期。
- first_reply_create: 主题第一次回复的日期。
- solution_create: 为主题标记解决方案的日期（如果适用）。
- time_first_reply(days/hours): 收到第一次回复所需的时间，以天和小时为单位。
- time_solution(days/hours): 解决主题所需的时间，以天和小时为单位。
- created_at: 主题的创建日期。
- number_of_replies: 主题的总回复数。
- total_days_without_solution: 主题在没有解决方案的情况下处于活动状态的总天数。
示例结果
| status | tag_names | category_name | topic_id | topic_user_id | user_email | title | views | last_reply_user_id | last_reply_user_email | topic_create | first_reply_create | solution_create | time_first_reply(days) | time_first_reply(hours) | time_solution(days) | time_solution(hours) | created_at | number_of_replies | total_days_without_solution |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| solved | support, password | category1 | 101 | 1 | user1@example.com | How to reset my password? | 150 | 3 | user3@example.com | 2022-01-05 | 2022-01-06 | 2022-01-07 | 1 | 24 | 2 | 48 | 2022-01-05 | 5 | 2 |
| unsolved | support, account | category2 | 102 | 2 | user2@example.com | Issue with account activation | 75 | 4 | user4@example.com | 2022-02-10 | 2022-02-12 | 2 | 48 | 0 | 0 | 2022-02-10 | 3 | 412 | |
| solved | support | category3 | 103 | 5 | user5@example.com | Can’t upload profile picture | 200 | 6 | user6@example.com | 2022-03-15 | 2022-03-16 | 2022-03-18 | 1 | 24 | 3 | 72 | 2022-03-15 | 8 | 3 |
| unsolved | NULL | category4 | 104 | 7 | user7@example.com | Error when posting | 50 | 8 | user8@example.com | 2022-04-20 | 0 | 0 | 0 | 0 | 2022-04-20 | 0 | 373 |
