# 已解决/未解决话题的统计，包括“解决时间”

**URL:** https://meta.discourse.org/t/topics-stats-of-solved-unsolved-topics-inc-time-to-solution/275393
**Category:** Data & reporting
**Tags:** solved, sql-query
**Created:** [2023 年4 月 22 日 12:36 UTC](https://meta.discourse.org/t/topics-stats-of-solved-unsolved-topics-inc-time-to-solution/275393 "2023-04-22T12:36:53Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [2023 年4 月 22 日 12:36 UTC](https://meta.discourse.org/t/topics-stats-of-solved-unsolved-topics-inc-time-to-solution/275393/1 "2023-04-22T12:36:53Z")

</div>

⚠ DRAFT

此查询将提供已解决/未解决、第一次回复 | 首次回复者 | 回复时间（小时），以及解决方案帖子 | 解决者 | 解决方案时间（小时）（附带类别和“主题创建自”参数）。

如果有人想进行健全性检查，将不胜感激。 🙂 👍

```sql

-- [params]
-- int :category_id = 6
-- date :since = 2013-01-01

WITH solved_topics AS (

SELECT ua.target_topic_id AS topic_id,
       ua.user_id,
       ua.target_post_id AS post_id,
       p.created_at
FROM user_actions ua
JOIN posts p on p.id = ua.target_post_id
WHERE action_type = 15

),

first_reply AS (
    SELECT p.topic_id,
           MIN(id) AS post_id
    FROM posts p
    WHERE p.deleted_at ISNULL
    AND p.post_type = 1
    AND p.post_number > 1
    GROUP BY p.topic_id
)

SELECT CASE WHEN st.topic_id IS NOT NULL THEN 'Solved' ELSE 'Unsolved' END AS "Solved?",
       t.id as topic_id,
       t.user_id AS question_user_id,
       t.created_at::date AS "Topic Posted On:",
       t.views,
       p.user_id AS first_reply_user_id,
       COALESCE(fr.post_id, 0) AS first_reply_post_id,
       COALESCE(CEIL(extract(epoch FROM (p.created_at - t.created_at))/3600.00),0) AS "Time to First Reply (hrs)",
       st.user_id AS solved_by_user_id,
       COALESCE(st.post_id,0) AS solution_post_id,
       COALESCE(CEIL(extract(epoch FROM (st.created_at - t.created_at))/3600.00),0) AS "Time to Solution (hrs)"
FROM topics t
LEFT JOIN solved_topics st ON t.id = st.topic_id
LEFT JOIN first_reply fr ON fr.topic_id = t.id
LEFT JOIN posts p ON p.id = fr.post_id
WHERE t.category_id = :category_id
AND t.created_at >= :since
AND t.deleted_at ISNULL
AND t.visible = TRUE
ORDER BY t.created_at

```