تقرير لوحة المعلومات - الإشارات المرجعية

[SaraDev (Sara Devlaeminck)]

هذا هو إصدار SQL لتقرير لوحة المعلومات للإشارات المرجعية.

يوفر تقرير لوحة المعلومات هذا عددًا يوميًا للإشارات المرجعية التي تم إنشاؤها ضمن نطاق تاريخ محدد.

-- [params]
-- date :start_date
-- date :end_date

WITH bookmark_counts AS (
  SELECT
    DATE(bookmarks.created_at) AS bookmark_date,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Topic' THEN bookmarks.id END) AS topic_bookmarks,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Post' THEN bookmarks.id END) AS post_bookmarks
  FROM bookmarks
  WHERE bookmarks.created_at::date BETWEEN :start_date AND :end_date
  GROUP BY bookmark_date
)
SELECT
  bookmark_date,
  topic_bookmarks + post_bookmarks AS total_bookmarks,
  topic_bookmarks,
  post_bookmarks
FROM bookmark_counts
ORDER BY bookmark_date

شرح استعلام SQL

المعلمات

لإنشاء التقرير، تحتاج إلى توفير معلمتين: :start_date و :end_date. تحدد هاتان المعلمتان نطاق التاريخ الذي تريد تحليل نشاط الإشارات المرجعية له. تقبل كلتا معلمتي التاريخ تنسيق التاريخ YYYY-MM-DD.

Bookmark_Counts (CTE)

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

• تصفية التاريخ: يقوم بتصفية الإشارات المرجعية حسب تاريخ إنشائها لتضمين تلك التي تم إنشاؤها فقط ضمن التواريخ المحددة (البداية والنهاية) (:start_date و :end_date).
• تجميع التاريخ: يقوم بتجميع النتائج حسب تاريخ إنشاء الإشارات المرجعية، مما يضمن تجميع الأعداد على أساس يومي.
• عد الإشارات المرجعية: يقوم بعد عدد الإشارات المرجعية للمواضيع والمشاركات بشكل منفصل، باستخدام DISTINCT لضمان عد كل إشارة مرجعية مرة واحدة فقط. يتم ذلك باستخدام التجميع الشرطي مع عبارات CASE:
  • topic_bookmarks يعد الإشارات المرجعية حيث bookmarkable_type هو ‘Topic’.
  • post_bookmarks يعد الإشارات المرجعية حيث bookmarkable_type هو ‘Post’.

الاستعلام الرئيسي

بعد أن يقوم CTE بتنظيم البيانات، يسترد عبارة SELECT النهائية الأعمدة التالية:

1. bookmark_date: التاريخ الذي تم تجميع الأعداد له.
2. total_bookmarks: مجموع إشارات المواضيع والمشاركات لكل يوم.
3. topic_bookmarks: عدد الإشارات المرجعية للمواضيع لكل يوم. مثال:
   
   
4. post_bookmarks: عدد الإشارات المرجعية للمشاركات لكل يوم. مثال:
   
   

ثم يتم ترتيب النتائج حسب bookmark_date لتقديم البيانات بترتيب زمني.

نتائج مثال

bookmark_date	total_bookmarks	topic_bookmarks	post_bookmarks
2023-12-01	16	1	15
2023-12-02	4	1	3
2023-12-03	8	0	8
2023-12-04	19	9	10
2023-12-05	18	3	15
…	…	…	…