الأسئلة المحلولة والمواضيع المخصصة حاليًا لكل مستخدم شهريًا

مجتمع البيانات والتقارير
,
1

:discourse: يتطلب هذا التقرير تفعيل إضافة Discourse Solved.

يوفر تقرير Data Explorer هذا نظرة عامة على الأنشطة التي قام بها أعضاء مجموعة محددة خلال إطار زمني معين. ويركز تحديدًا على نشاطين رئيسيين: الأسئلة المحلولة والمواضيع المُسندة.

صُمم هذا التقرير لمساعدة المسؤولين على فهم مساهمات وأعباء عمل أعضاء المجموعة، مما يسهل تخصيص الموارد بشكل أفضل والاعتراف بالمساهمين النشطين.

--[params]
-- string :group_name_filter = staff
-- string :date_trunc = month
-- null user_list :user_list
-- boolean :userlist_filter = false
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01

WITH group_users_filtered AS (
    SELECT 
        gu.user_id
    FROM group_users gu
    JOIN groups g ON g.id = gu.group_id
    WHERE g.name = :group_name_filter
),
user_groups AS (
    SELECT 
        gu.user_id, 
        STRING_AGG(g.name, ', ') AS group_names
    FROM group_users gu
    JOIN groups g ON g.id = gu.group_id
    GROUP BY gu.user_id
),
questions_solved AS (
    SELECT 
        p.user_id, 
        DATE_TRUNC(:date_trunc, dsta.created_at) AS month,
        COUNT(*) AS total_solved
    FROM discourse_solved_solved_topics dsst
    JOIN discourse_solved_topic_answer dsta on dsta.solved_id = dsst.id
    JOIN posts p ON p.id = dsta.answer_post_id
    JOIN group_users_filtered guf ON guf.user_id = p.user_id
    WHERE dsta.created_at BETWEEN :start_date AND :end_date
    GROUP BY p.user_id, DATE_TRUNC(:date_trunc, dsta.created_at)
),
assigns_per_user AS (
    SELECT 
        a.assigned_to_id AS user_id, 
        DATE_TRUNC(:date_trunc, a.created_at) AS month,
        COUNT(a.topic_id) AS total_assigned
    FROM assignments a
    JOIN topics t ON t.id = a.topic_id
    JOIN group_users_filtered guf ON guf.user_id = a.assigned_to_id
    WHERE a.assigned_to_type = 'User'
      AND t.deleted_at IS NULL
      AND a.created_at BETWEEN :start_date AND :end_date
    GROUP BY a.assigned_to_id, DATE_TRUNC(:date_trunc, a.created_at)
)
SELECT 
    COALESCE(qs.month, apu.month)::DATE AS date,
    :date_trunc as date_range,
    COALESCE(qs.user_id, apu.user_id, ug.user_id) AS user_id, 
    COALESCE(qs.total_solved, 0) AS total_solved_questions,
    COALESCE(apu.total_assigned, 0) AS total_assigned_topics,
    COALESCE(ug.group_names, '') AS group_names
FROM questions_solved qs
FULL OUTER JOIN assigns_per_user apu ON qs.user_id = apu.user_id AND qs.month = apu.month
LEFT JOIN user_groups ug ON ug.user_id = COALESCE(qs.user_id, apu.user_id)
WHERE (qs.user_id IN(:user_list) OR :userlist_filter = FALSE)
ORDER BY date DESC

شرح استعلام SQL

يتم إنشاء التقرير من خلال سلسلة من التعابير الجدولية الشائعة (CTEs) التي تقوم بتصفية وتجميع البيانات بناءً على المعايير المحددة. وفيما يلي تفصيل لكل CTE ودوره في التقرير:

شرح CTEs

  1. group_users_filtered: تحدد هذه CTE المستخدمين الذين هم أعضاء في المجموعة المحددة (:group_name_filter). وهي تقوم بتصفية المستخدمين بناءً على عضويتهم في المجموعة محل الاهتمام.
  2. user_groups: تقوم هذه CTE بتجميع جميع المجموعات التي ينتمي إليها المستخدم في سلسلة نصية واحدة. وهذا يساعد في تحديد جميع المجموعات المرتبطة بالمستخدم، مما يوفر سياقًا لأنشطتهم.
  3. questions_solved: تحسب هذه CTE العدد الإجمالي للأسئلة التي حلها كل مستخدم ضمن النطاق الزمني المحدد (:start_date إلى :end_date).
  4. assigns_per_user: تشبه questions_solved، حيث تقوم هذه CTE بعد المهام المخصصة لكل مستخدم في النطاق الزمني المحدد. وهي تضمن عد التعيينات الموجهة للمستخدمين فقط (وليس المجموعات أو الكيانات الأخرى) وتستبعد المواضيع المحذوفة.

يجمع الاستعلام النهائي هذه CTEs لإنتاج تقرير يتضمن التاريخ (أو النطاق الزمني)، ومعرف المستخدم، وإجمالي الأسئلة المحلولة، وإجمالي المواضيع المخصصة، وأسماء المجموعات التي ينتمي إليها المستخدم. ويسمح بالتصفية حسب قائمة محددة من المستخدمين إذا تم توفيرها (:user_list)، ويمكنه ضبط دقة التاريخ بناءً على معلمة :date_trunc (مثل: شهر، سنة).

المعايير

  • group_name_filter (نص): اسم المجموعة المراد تصفية المستخدمين حسبها، ويتم تعيينه افتراضيًا إلى «staff». تُستخدم هذه المعلمة لاختيار المستخدمين الذين هم أعضاء في مجموعة محددة.
  • date_trunc (نص): يحدد دقة تجميع الوقت للتقارير (مثل: «month»، «year»، «week»، «day»). وهذا يؤثر على كيفية تجميع البيانات حسب الوقت في المخرجات.
  • user_list (null user_list): قائمة اختيارية بمعرفات المستخدمين لتصفية النتائج بشكل أكبر. إذا تم توفيرها، فإن الاستعلام سيتضمن بيانات هؤلاء المستخدمين فقط.
  • userlist_filter (boolean): علم للإشارة إلى ما إذا كان يجب تطبيق مرشح user_list. إذا تم تعيينه إلى false، يتم تجاهل معلمة user_list، ويتم تضمين بيانات جميع المستخدمين في المجموعة المحددة.
  • start_date (تاريخ): تاريخ بداية الفترة المراد الإبلاغ عن أنشطة خلالها.
  • end_date (تاريخ): تاريخ نهاية الفترة المراد الإبلاغ عن أنشطة خلالها.

النتائج

يوفر التقرير الأعمدة التالية:

  • date: التاريخ أو بداية النطاق الزمني الذي تم تجميع البيانات فيه.
  • date_range: دقة النطاق الزمني (مثل: شهر، سنة).
  • user_id: معرف المستخدم.
  • total_solved_questions: العدد الإجمالي للأسئلة التي حلها المستخدم.
  • total_assigned_topics: العدد الإجمالي للمهام المخصصة للمستخدم.
  • group_names: سلسلة نصية تحتوي على أسماء جميع المجموعات التي ينتمي إليها المستخدم.

:person_tipping_hand: تجدر الإشارة إلى أنه نظرًا لطريقة عمل جدول assignments في Discourse، فإن هذا التقرير سيكون قادرًا فقط على تتبع المواضيع المخصصة حاليًا، وسيظهر المواضيع المخصصة حاليًا حسب التاريخ الذي تم تعيينها فيه (أو إعادة تعيينها).

أمثلة على النتائج

date date_range user total_solved_questions total_assigned_topics group_names
2023-12-01 month user1 5 3 admins, customer-projects-team, trust_level_3, trust_level_4, trust_level_0, trust_level_1, staff, trust_level_2
2023-11-01 month user2 8 1 admins, moderators, trust_level_3, trust_level_4, trust_level_0, trust_level_1, staff, support, trust_level_2
2023-11-01 month user3 3 4 admins, moderators, trust_level_3, trust_level_4, trust_level_0, trust_level_1, staff, trust_level_2
… … … … … …
2

لم يكن من الممكن أن يأتي هذا في وقت أفضل! كنت أحتاج فقط إلى القيام بشيء كهذا و ها هو! يظهر! :tada:

شكرا!

من المؤسف أننا لا نستطيع الحصول على إحصائيات حول الواجبات في الماضي (للمواضيع المغلقة). لا يمكن الحصول على كل شيء على ما أعتقد.