تقرير لوحة المعلومات - وقت الاستجابة الأول

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

هذا هو إصدار SQL لتقرير لوحة المعلومات الخاص بوقت الاستجابة الأول.

يوفر تقرير لوحة المعلومات هذا رؤى حول متوسط وقت الاستجابة للمواضيع ضمن نطاق تاريخ محدد. يحسب التقرير الوقت المستغرق للنشر الأول رد من شخص آخر غير منشئ الموضوع.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-12-31
-- null category_id :category_id
-- boolean :include_subcategories = false
-- null user_id :user_ids 

-- الوقت للاستجابة الأولى حسب التاريخ
SELECT 
  t.created_at AS "date",
  CAST(AVG(t.hours)::numeric(10,2) AS float) AS "response_time_hours"
FROM (
  SELECT 
    t.id, 
    t.created_at::date AS created_at, 
    EXTRACT(EPOCH FROM MIN(p.created_at) - t.created_at)::float / 3600.0 AS "hours"
  FROM topics t
  JOIN posts p ON p.topic_id = t.id
  WHERE 
    t.created_at >= :start_date
    AND t.created_at < :end_date
    AND t.archetype <> 'private_message'
    AND t.deleted_at IS NULL
    AND p.deleted_at IS NULL
    AND p.post_number > 1
    AND p.user_id != t.user_id
    AND p.post_type = 1  -- نوع المنشور العادي
    AND EXTRACT(EPOCH FROM p.created_at - t.created_at) > 0
    AND (:category_id IS NULL OR 
         CASE WHEN :include_subcategories THEN 
           t.category_id IN (
             WITH RECURSIVE subcategories AS (
               SELECT id FROM categories WHERE id = :category_id
               UNION
               SELECT c.id FROM categories c
               JOIN subcategories sc ON sc.id = c.parent_category_id
             )
             SELECT id FROM subcategories
           )
         ELSE 
           t.category_id = :category_id
         END
        )
    AND (:user_ids IS NULL OR p.user_id IN (SELECT unnest(string_to_array(:user_ids, ','))::int))
  GROUP BY t.id
) t
GROUP BY t.created_at
ORDER BY t.created_at

شرح استعلام SQL

يقيس هذا التقرير مدى سرعة تلقي المواضيع لأول رد ذي معنى من خلال:

  • حساب وقت الاستجابة: لكل موضوع، يحدد أول منشور يقوم بما يلي:
    • ليس من منشئ الموضوع الأصلي
    • له رقم منشور > 1 (ليس المنشور الأولي)
    • هو منشور عادي (post_type = 1)
    • تم إنشاؤه بعد الموضوع (فرق زمني إيجابي)
    • لم يتم حذفه
  • التجميع حسب التاريخ: يجمع أوقات الاستجابة هذه حسب تاريخ إنشاء الموضوع.
  • النتائج تظهر:
    • “date”: تاريخ إنشاء المواضيع.
    • “hours”: متوسط الوقت (بالساعات) حتى أول رد للمواضيع التي تم إنشاؤها في ذلك التاريخ.
  • الاستثناءات:
    • يتم استبعاد الرسائل الخاصة.
    • يتم استبعاد المواضيع والمنشورات المحذوفة.
    • يتم استبعاد الردود الذاتية.

المعلمات

  • :start_date (date) - بتنسيق YYYY-MM-DD
    • تاريخ البدء لفترة التقرير.
    • سيتم تضمين المواضيع التي تم إنشاؤها في هذا التاريخ أو بعده فقط.
  • :end_date (date) - بتنسيق YYYY-MM-DD
    • تاريخ الانتهاء لفترة التقرير.
    • سيتم تضمين المواضيع التي تم إنشاؤها قبل هذا التاريخ فقط.
  • :category_id (category_id, nullable)
    • عند توفيره، يقوم بتصفية النتائج لفئة معينة.
    • عندما يكون فارغًا (null)، يتضمن المواضيع من جميع الفئات.
  • :include_subcategories (boolean) - الافتراضي: false
    • عندما يكون صحيحًا ويتم توفير category_id، يتضمن المواضيع من جميع الفئات الفرعية.
    • عندما يكون خطأ، يتضمن المواضيع من الفئة المحددة بالضبط فقط.
  • :user_ids (user_id, nullable)
    • عند توفيره، يتضمن الردود من مستخدمين محددين فقط.
    • عندما يكون فارغًا (null)، يتضمن الردود من جميع المستخدمين.
    • يمكن أن يقبل معرفات مستخدمين متعددة كقائمة مفصولة بفواصل.

نتائج مثال

date response_time_hours
2023-11-12 29.87
2023-11-13 81.52
2023-11-14 5.17
2023-11-15 6.51
2023-11-16 7.75
… …
3 إعجابات