تقرير لوحة المعلومات - نمو مستوى الثقة

البيانات والتقارير
, ,
1

هذا هو إصدار SQL لتقرير لوحة المعلومات لنمو مستوى الثقة.

يحتسب تقرير لوحة المعلومات هذا عدد المستخدمين الذين يصلون إلى مستويات الثقة 1 و 2 و 3 و 4 كل يوم ضمن النطاق الزمني المحدد، ويساعد المسؤولين على تتبع تفاعل المستخدمين وتقدمهم عبر مستويات الثقة في مجتمع Discourse.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01

SELECT
  date(created_at) AS date,
  COUNT(*) FILTER (WHERE previous_value::integer < 1 AND new_value = '1') AS tl1_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 2 AND new_value = '2') AS tl2_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 3 AND new_value = '3') AS tl3_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 4 AND new_value = '4') AS tl4_reached
FROM user_histories
WHERE created_at >= :start_date
  AND created_at <= :end_date
  AND (action = 2 OR action = 15)
GROUP BY date(created_at)
ORDER BY date(created_at)

شرح استعلام SQL

يعمل الاستعلام عن طريق تحديد السجلات من جدول user_histories، الذي يسجل التغييرات في حالات المستخدم، بما في ذلك مستويات الثقة الخاصة بهم.

إليك تفصيل للاستعلام:

  • معلمات التاريخ: يقبل الاستعلام معلمتين، :start_date و :end_date، اللتين تحددان النطاق الزمني للتقرير. تقبل كلتا معلمتي التاريخ تنسيق التاريخ YYYY-MM-DD.
  • date(created_at) AS date: هذا الجزء من عبارة SELECT يستخرج التاريخ من الطابع الزمني created_at ويسميه ‘date’ لكل سجل. سيتم استخدام هذا لتجميع النتائج حسب اليوم.
  • COUNT(*) FILTER (WHERE previous_value::integer < X AND new_value = 'Y') AS tlY_reached: يقوم هذا القسم بحساب عدد المستخدمين الذين تقدموا إلى مستوى ثقة معين (Y) من مستوى ثقة أقل (أقل من X). يتم استخدام عبارة FILTER لتضمين الصفوف التي تطابق الشرط المحدد فقط. يتم استخدام التحويل ::integer لتحويل previous_value إلى عدد صحيح للمقارنة. هناك أربعة من أعمدة العد هذه، واحد لكل مستوى ثقة من 1 إلى 4.
  • FROM user_histories: يحدد هذا الجدول الذي سيتم استرداد البيانات منه.
  • WHERE created_at >= :start_date AND created_at <= :end_date: يقوم هذا بتصفية السجلات لتضمين تلك الموجودة ضمن النطاق الزمني المحدد فقط.
  • AND (action = 2 OR action = 15): يقوم هذا بتصفية السجلات لتضمين تلك التي يشير فيها الإجراء إلى تغيير في مستوى الثقة فقط. الإجراءات المحددة التي تتوافق مع تغييرات مستوى الثقة ممثلة بالأرقام 2 و 15.
  • GROUP BY date(created_at): يقوم هذا بتجميع النتائج حسب التاريخ، بحيث تحصل على عدد تغييرات مستوى الثقة لكل يوم ضمن النطاق المحدد.
  • ORDER BY date(created_at): يقوم هذا بترتيب النتائج بترتيب زمني حسب التاريخ.

نتائج مثال

date tl1_reached tl2_reached tl3_reached tl4_reached
2024-01-01 1 3 1 0
2024-01-02 5 1 0 1
2024-01-03 8 2 3 0
… … … … …
4 إعجابات