Dieser Data Explorer-Bericht bietet eine umfassende Analyse von gelösten und ungelösten Themen auf einer Website innerhalb eines bestimmten Datumsbereichs und optional gefiltert nach einem bestimmten Tag.
Dieser Bericht erfordert, dass das Plugin Discourse Solved aktiviert ist.
Dieser Bericht erfordert, dass das Plugin Dieser Bericht ist besonders nützlich für Administratoren und Moderatoren, die die Reaktionsfähigkeit der Community verstehen und Verbesserungsmöglichkeiten in der Benutzerunterstützung und im Engagement identifizieren möchten.
Statistiken zu gelösten und ungelösten Themen mit Datums- und Tag-Parametern
--[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, -- Tags für jedes Thema aggregieren
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-Abfrageerklärung
Die Berichterstellung erfolgt über eine komplexe SQL-Abfrage, die Common Table Expressions (CTEs) verwendet, um die Daten effizient zu organisieren und zu verarbeiten. Die Abfrage ist wie folgt strukturiert:
- valid_topics: Diese CTE filtert Themen nach dem angegebenen Datumsbereich und Archetyp (‘regular’) und schließt gelöschte Themen aus. Sie aggregiert auch die mit jedem Thema verbundenen Tags für eine spätere Filterung nach Tag-Namen, falls angegeben.
- solved_topics: Identifiziert Themen, die als gelöst markiert wurden.
- last_reply: Ermittelt den Benutzer, der die letzte Antwort in jedem Thema gegeben hat, indem die maximale Post-ID (die die aktuellste Antwort angibt) ermittelt wird, die nicht gelöscht ist und vom Post-Typ 1 (eine reguläre Antwort) ist.
- first_reply: Ähnlich wie last_reply, identifiziert jedoch den ersten Benutzer, der auf das Thema nach dem ursprünglichen Beitrag geantwortet hat.
Die Hauptabfrage kombiniert dann diese CTEs, um einen detaillierten Bericht für jedes Thema zu erstellen, einschließlich, ob es gelöst oder ungelöst ist, Tag-Namen, Kategorie-Namen, Themen- und Benutzer-IDs, E-Mails, Aufrufe, Antwortanzahlen und Zeitangaben für die erste Antwort und die Lösung.
Parameter
- start_date: Der Beginn des Datumsbereichs, für den der Bericht generiert werden soll.
- end_date: Das Ende des Datumsbereichs, für den der Bericht generiert werden soll.
- tag_name: Der spezifische Tag, nach dem Themen gefiltert werden sollen. Verwenden Sie ‘all’, um Themen mit beliebigen Tags einzuschließen.
Ergebnisse
Der Bericht liefert die folgenden Informationen für jedes Thema innerhalb der angegebenen Parameter:
- status: Gibt an, ob das Thema gelöst wurde oder ungelöst bleibt.
- tag_names: Zeigt die mit dem Thema verbundenen Tags an.
- category_name: Zeigt die mit dem Thema verbundene Kategorie an.
- topic_id: Die eindeutige Kennung für das Thema.
- topic_user_id: Die ID des Benutzers, der das Thema erstellt hat.
- user_email: Die E-Mail-Adresse des Themaerstellers.
- title: Der Titel des Themas.
- views: Die Anzahl der Aufrufe, die das Thema erhalten hat.
- last_reply_user_id: Die ID des Benutzers, der die letzte Antwort auf das Thema gegeben hat.
- last_reply_user_email: Die E-Mail-Adresse des Benutzers, der die letzte Antwort gegeben hat.
- topic_create: Das Datum, an dem das Thema erstellt wurde.
- first_reply_create: Das Datum der ersten Antwort auf das Thema.
- solution_create: Das Datum, an dem eine Lösung für das Thema markiert wurde (falls zutreffend).
- time_first_reply(days/hours): Die Zeit, die benötigt wurde, um die erste Antwort zu erhalten, in Tagen und Stunden.
- time_solution(days/hours): Die Zeit, die benötigt wurde, um das Thema zu lösen, in Tagen und Stunden.
- created_at: Das Erstellungsdatum des Themas.
- number_of_replies: Die Gesamtzahl der Antworten auf das Thema.
- total_days_without_solution: Die Gesamtzahl der Tage, an denen das Thema ohne Lösung aktiv war.
Beispielergebnisse
| 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 |
