تقرير لوحة المعلومات - بين المستخدمين

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

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

يقوم هذا التقرير بحساب عدد الرسائل الخاصة التي يرسلها المستخدمون كل يوم، ضمن نطاق تاريخ محدد، مما يوفر نظرة ثاقبة لنشاط تفاعل المستخدم في الاتصالات الخاصة.

--[params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- boolean :include_replies = false

SELECT
    p.created_at::date as day,
    COUNT(p.user_id) as personal_messages
FROM posts p
INNER JOIN topics t ON (p.topic_id = t.id)
WHERE archetype = 'private_message'
    AND p.user_id > 0
    AND t.subtype = 'user_to_user'
    AND p.deleted_at ISNULL
    AND t.deleted_at ISNULL
    AND p.created_at::date BETWEEN :start_date AND :end_date
    AND (:include_replies = true OR p.post_number = 1)
GROUP BY p.created_at::date
ORDER BY p.created_at::date

شرح استعلام SQL

يقوم استعلام SQL هذا بتنفيذ الإجراءات التالية:

  • المعلمات:
    • :start_date و :end_date، والتي تُستخدم لتصفية البيانات ضمن النطاق الزمني المحدد. تقبل كلتا معلمتي التاريخ تنسيق YYYY-MM-DD.
    • :include_replies لتحديد ما إذا كان يجب تضمين الردود على الرسائل الأولية في العد. تم تعيينها على false افتراضيًا لتعكس سلوك تقرير “المستخدم إلى المستخدم (باستثناء الردود)”. سيؤدي تعيين هذه المعلمة إلى true إلى عكس سلوك تقرير “المستخدم إلى المستخدم (مع الردود)”.
  • اختيار البيانات:
    • يختار الاستعلام تاريخ created_at من جدول posts، والذي يمثل التاريخ الذي تم فيه إنشاء كل رسالة.
  • عملية الربط:
    • يجري الاستعلام ربطًا داخليًا (INNER JOIN) بين جدولي posts و topics على حقول id الخاصة بهما. هذا الربط ضروري لأنه يربط كل منشور بموضوعه المقابل، مما يسمح لنا بتصفية المنشورات التي تعد جزءًا من محادثة رسائل خاصة فقط.
  • معايير التصفية: يطبق الاستعلام عدة عوامل تصفية:
    • إنه يأخذ في الاعتبار فقط المنشورات التي تعد جزءًا من موضوع ذي archetype بقيمة ‘private_message’، مما يضمن عد الرسائل الخاصة فقط.
    • يقوم بتصفية أي منشورات غير مرتبطة بمستخدم عن طريق التأكد من أن user_id أكبر من 0.
    • يضيق النتائج بشكل أكبر للمواضيع ذات subtype بقيمة ‘user_to_user’، مما يشير إلى رسالة خاصة بين مستخدمين فرديين.
    • يستبعد أي منشورات أو مواضيع تم حذفها عن طريق التحقق من أن deleted_at هو NULL لكلا الجدولين.
    • يطبق عامل تصفية النطاق الزمني لتضمين فقط المنشورات التي تم إنشاؤها بين :start_date و :end_date.
    • إذا كان :include_replies خاطئًا، فسيتم عد المنشور الأول فقط في كل موضوع (p.post_number = 1)، مع استبعاد الردود.
  • التجميع: يجمع الاستعلام النتائج حسب التاريخ الذي تم فيه إنشاء المنشورات، مما يسمح بحساب عدد الرسائل المرسلة في كل تاريخ.
  • الفرز: أخيرًا، يقوم الاستعلام بفرز النتائج حسب التاريخ بترتيب تصاعدي، مما يوفر تسلسلًا زمنيًا لنشاط الرسائل الخاصة بين المستخدمين.

نتائج مثال

day personal_messages
2023-11-11 92
2023-11-12 57
2023-11-13 345
2023-11-14 124
2023-11-15 56
4 إعجابات