يوفر تقرير مستكشف البيانات هذا تحليلاً شاملاً للمواضيع التي تم حلها والتي لم يتم حلها على موقع، ضمن نطاق زمني محدد، ويتم تصفيتها اختياريًا حسب علامة معينة.
يتطلب هذا التقرير تمكين المكون الإضافي 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 معقد يستخدم تعبيرات الجدول الشائعة (CTEs) لتنظيم ومعالجة البيانات بكفاءة. يتم تنظيم الاستعلام على النحو التالي:
- valid_topics: يقوم هذا CTE بتصفية المواضيع حسب النطاق الزمني المحدد والنموذج الأولي (‘regular’)، مع استبعاد المواضيع المحذوفة. كما أنه يجمع العلامات المرتبطة بكل موضوع للتصفية لاحقًا حسب اسم العلامة إذا تم تحديده.
- solved_topics: يحدد المواضيع التي تم تمييزها على أنها محلولة.
- last_reply: يحدد المستخدم الذي قام بالرد الأخير على كل موضوع عن طريق العثور على معرف المنشور الأقصى (مما يشير إلى أحدث منشور) الذي لم يتم حذفه وهو من نوع المنشور 1 (مما يشير إلى منشور عادي).
- first_reply: على غرار last_reply، ولكنه يحدد أول مستخدم يرد على الموضوع بعد المنشور الأصلي.
ثم يجمع الاستعلام الرئيسي بين هذه CTEs لتجميع تقرير مفصل حول كل موضوع، بما في ذلك ما إذا كان قد تم حله أو لم يتم حله، وأسماء العلامات، واسم الفئة، ومعرفات المواضيع والمستخدمين، ورسائل البريد الإلكتروني، والمشاهدات، وعدد الردود، والتوقيتات للرد الأول والحل.
المعلمات
- 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 | 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 |
