이 Data Explorer 보고서는 특정 날짜 범위 내 사이트의 해결된 및 미해결된 주제에 대한 포괄적인 분석을 제공하며, 필요에 따라 특정 태그로 필터링할 수 있습니다.
이 보고서를 사용하려면 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, -- 각 주제에 대한 태그 집계
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 쿼리 설명
이 보고서는 데이터 효율적으로 조직화하고 처리하기 위해 공통 테이블 표현식(CTE)을 활용하는 복잡한 SQL 쿼리를 통해 생성됩니다. 쿼리는 다음과 같이 구성됩니다:
- 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 |
