Этот отчет 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-запросу
Отчёт генерируется с помощью сложного SQL-запроса, использующего общие табличные выражения (CTE) для эффективной организации и обработки данных. Запрос структурирован следующим образом:
- valid_topics: Это CTE фильтрует темы по указанному диапазону дат и архетипу (‘regular’), исключая удалённые темы. Также оно агрегирует теги, связанные с каждой темой, для последующей фильтрации по имени тега, если оно указано.
- solved_topics: Определяет темы, помеченные как решённые.
- last_reply: Определяет пользователя, сделавшего последний ответ в каждой теме, находя максимальный ID сообщения (указывающий на самое последнее сообщение), которое не удалено и имеет тип сообщения 1 (обычное сообщение).
- first_reply: Аналогично last_reply, но определяет первого пользователя, ответившего на тему после исходного сообщения.
Основной запрос объединяет эти CTE для формирования детального отчёта по каждой теме, включая статус (решена или нет), имена тегов, название категории, идентификаторы темы и пользователя, адреса электронной почты, количество просмотров, количество ответов и временные метки первого ответа и решения.
Параметры
- start_date: Начало диапазона дат для генерации отчёта.
- end_date: Конец диапазона дат для генерации отчёта.
- tag_name: Конкретный тег для фильтрации тем. Используйте ‘all’, чтобы включить темы с любым тегом.
Результаты
Отчёт предоставляет следующую информацию по каждой теме в рамках указанных параметров:
- 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/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 | Как сбросить пароль? | 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 | Проблема с активацией аккаунта | 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 | Не могу загрузить фото профиля | 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 | Ошибка при публикации | 50 | 8 | user8@example.com | 2022-04-20 | 0 | 0 | 0 | 0 | 2022-04-20 | 0 | 373 |
