تقرير لوحة التحكم - النظام

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

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

يوفر تقرير لوحة المعلومات هذا عددًا يوميًا للرسائل الشخصية التي يرسلها النظام تلقائيًا.

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

SELECT
  DATE(created_at) AS day,
  COUNT(*) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  AND created_at BETWEEN :start_date AND :end_date
  AND deleted_at IS NULL
  AND user_id > 0
GROUP BY DATE(created_at)
ORDER BY day

شرح استعلام SQL

يعمل الاستعلام عن طريق استخراج البيانات من جدول topics – وتحديدًا، تلك التي تتأهل كرسائل خاصة للمستخدمين بنوع فرعي system_message ضمن إطار زمني معين. دعنا نفصل ذلك:

  • معلمات التاريخ:
    • يقبل الاستعلام معلمتين، :start_date و :end_date، اللتين تحددان النطاق الزمني للتقرير. تقبل كلتا معلمتي التاريخ تنسيق التاريخ YYYY-MM-DD.
  • SELECT: يحدد الاستعلام حقلين:
    • DATE(created_at) AS day: يستخرج هذا جزء التاريخ من الطابع الزمني created_at، مما يؤدي فعليًا إلى تجميع السجلات حسب اليوم الذي تم إنشاؤها فيه.
    • COUNT(*) AS notifications_count: يحسب هذا العدد الإجمالي للرسائل الخاصة التي تم إنشاؤها بواسطة النظام لكل يوم.
  • FROM: يحدد جدول topics كمصدر للبيانات، والذي يحتوي على سجلات لجميع المواضيع، بما في ذلك الرسائل الخاصة.
  • WHERE: يحتوي على عوامل تصفية متعددة لتضييق نطاق مجموعة البيانات:
    • archetype = 'private_message': يتضمن فقط الإدخالات التي هي رسائل خاصة.
    • subtype = 'system_message': يضيق الاختيار بشكل أكبر ليشمل فقط الرسائل التي تم إنشاؤها بواسطة النظام.
    • created_at BETWEEN :start_date AND :end_date: يقوم بتصفية الرسائل الخاصة لتلك التي تم إنشاؤها ضمن النطاق المحدد بالمعلمات.
    • deleted_at IS NULL: يستبعد الرسائل التي تم حذفها.
    • user_id > 0: يضمن ربط الرسائل بحسابات مستخدمين حقيقية بدلاً من حسابات النظام أو الحسابات المجهولة.
  • GROUP BY: يجمع النتائج بناءً على اليوم الذي تم إنشاؤها فيه.
  • ORDER BY: يرتب مجموعة النتائج النهائية حسب اليوم بترتيب تصاعدي، مما يضمن تسلسلًا زمنيًا للأعداد اليومية.

نتائج مثال

day notifications_count
2024-01-01 5
2024-01-02 7
2024-01-03 11
2024-01-04 14
2024-01-05 8
3 إعجابات
2

هل يمكن ربط هذا بـ PM محدد، مثل PM الترحيب؟

أود أن أعرف عدد هذه التي يتم إرسالها يوميًا.

أفكار؟

إعجاب واحد (1)
3

نعم، أفضل طريقة لإضافة هذا هي إضافة قسم إلى عبارة WHERE في الاستعلام، والتصفية حسب عنوان الموضوع.

على سبيل المثال:

WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  AND created_at BETWEEN :start_date AND :end_date
  AND title = 'Greetings!'

سيؤدي ذلك إلى العثور على جميع رسائل Greetings!.

لاحظ أن بعض الرسائل النظام لا تتضمن مستخدمًا حقيقيًا، لذلك قد يكون من الضروري إزالة السطر AND user_id > 0 مع هذا النوع من الاستعلام.

قد ترغب أيضًا في إزالة AND deleted_at IS NULL لحساب رسائل الترحيب التي قد يحذفها المستخدمون.

يمكنك استخدام التعبيرات النمطية لمطابقة المواضيع ذات العناوين المماثلة.

لمطابقة موضوع حسب العنوان باستخدام تعبير نمطي (regex) في PostgreSQL، يمكنك استخدام المعامل ~، الذي يطابق تعبيرًا نمطيًا مع سلسلة نصية. سيكون هيكل الاستعلام كالتالي:

SELECT *
FROM topics
WHERE title ~ 'YourRegexPatternHere'

استبدل 'YourRegexPatternHere' بنمط التعبير النمطي الفعلي الذي تريد مطابقته مع حقل title.

على سبيل المثال، إذا كنت تبحث عن مواضيع بعناوين تحتوي على كلمة “Welcome” (غير حساسة لحالة الأحرف)، يمكنك استخدام:

SELECT *
FROM topics
WHERE title ~* 'Welcome'

يتم استخدام المعامل ~* للمطابقة غير الحساسة لحالة الأحرف.

3 إعجابات
4

هذا رائع. شكراً جزيلاً! سأجرب هذا!

إعجاب واحد (1)