تقرير لوحة البيانات - الإعجابات

مجتمع البيانات والتقارير
, ,
4

ليس لدي منتديات كبيرة، ومعظم ردود الفعل التي نحصل عليها تأتي من “الموظفين” (المسؤولون، المشرفون، المستوى 4). أردت أن أرى كيف تقارن الإعجابات من المستخدمين العاديين مقابل “الموظفين”، وأن أسرد عدد المشاركات/اليوم للحصول على فهم أفضل لما يحدث وأين نحتاج إلى تركيز الجهود لتحسين استخدام ردود الفعل.

أنا وصديقي ChatGPT توصلنا إلى هذا:

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31

WITH date_range AS (
  SELECT date_trunc('day', series) AS date
  FROM generate_series(
    :start_date::timestamp,
    :end_date::timestamp,
    '1 day'::interval
  ) series
),
staff_users AS (
  SELECT id
  FROM users
  WHERE admin = true OR moderator = true OR trust_level = 4
)

SELECT
  dr.date::date,
  COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
  COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
  COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
  COALESCE(posts_count.posts_per_day, 0) AS posts_per_day
FROM
  date_range dr
LEFT JOIN (
  SELECT
    date_trunc('day', pa.created_at) AS action_date,
    COUNT(*) AS regular_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id NOT IN (SELECT id FROM staff_users)
    AND pa.created_at >= :start_date
    AND pa.created_at <= (:end_date::date + 1)
  GROUP BY action_date
) pa_non_staff ON dr.date = pa_non_staff.action_date
LEFT JOIN (
  SELECT
    date_trunc('day', pa.created_at) AS action_date,
    COUNT(*) AS staff_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id IN (SELECT id FROM staff_users)
    AND pa.created_at >= :start_date
    AND pa.created_at <= (:end_date::date + 1)
  GROUP BY action_date
) pa_staff ON dr.date = pa_staff.action_date
LEFT JOIN (
  SELECT
    date_trunc('day', p.created_at) AS post_date,
    COUNT(*) AS posts_per_day
  FROM posts p
  WHERE p.created_at >= :start_date
    AND p.created_at <= (:end_date::date + 1)
  GROUP BY post_date
) posts_count ON dr.date = posts_count.post_date
ORDER BY dr.date

التغييرات على استعلام @SaraDev الأصلي (شكرًا، سارة!):
ملخص تغييرات SQL

  1. إنشاء مجموعة الموظفين:
    تمت إضافة CTE staff_users لتحديد مستخدمي الموظفين من جدول users. يُعرَّف مستخدم الموظف بأنه أي مما يلي:
  • admin = true
  • moderator = true
  • trust_level = 4
  1. فصل إعجابات الموظفين:
    تمت إضافة استعلام فرعي لحساب عدد الإعجابات من مستخدمي الموظفين (staff_likes_count) عن طريق تصفية post_actions لـ user_id في مجموعة staff_users.
  2. إعادة تسمية عمود إعجابات غير الموظفين:
    تم تغيير تسمية الإخراج لإعجابات غير الموظفين من likes_count إلى regular_likes_count.
  3. إضافة إجمالي الإعجابات:
    تم تقديم عمود total_likes لجمع regular_likes_count و staff_likes_count.
  4. إضافة المشاركات في اليوم:
    تمت إضافة استعلام فرعي لحساب عدد المشاركات في اليوم (posts_per_day) وربطه بنطاق التاريخ.
    (نعم، ChatGPT قام بهذه القائمة من التغييرات لي أيضًا.)

نتائج مثال:

التاريخ regular_likes_count staff_likes_count posts_per_day
1/1/24 0 6 7
1/2/24 0 5 3
1/3/24 1 0 4
1/4/24 1 2 5
1/5/24 9 9 30
1/6/24 0 1 11
1/7/24 2 4 11
1/8/24 0 5 18
1/9/24 0 0 2
1/10/24 0 0 7
1/11/24 0 4 5
1/12/24 4 0 4
1/13/24 6 0 10
1/14/24 1 7 18
1/15/24 2 4 7
نفس الاستعلام المبلغ عنه حسب الأسابيع للتنعيم 
-- [params]
-- integer :weeks_ago = 52

WITH date_range AS (
  SELECT date_trunc('week', series) AS week_start
  FROM generate_series(
    date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval,
    date_trunc('week', now()),
    '1 week'::interval
  ) series
),
staff_users AS (
  SELECT id
  FROM users
  WHERE admin = true OR moderator = true OR trust_level = 4
)

SELECT
  dr.week_start::date AS week_start,
  COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
  COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
  COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
  COALESCE(posts_count.posts_per_week, 0) AS posts_per_week
FROM
  date_range dr
LEFT JOIN (
  SELECT
    date_trunc('week', pa.created_at) AS action_week,
    COUNT(*) AS regular_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id NOT IN (SELECT id FROM staff_users)
    AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND pa.created_at <= date_trunc('week', now())
  GROUP BY action_week
) pa_non_staff ON dr.week_start = pa_non_staff.action_week
LEFT JOIN (
  SELECT
    date_trunc('week', pa.created_at) AS action_week,
    COUNT(*) AS staff_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id IN (SELECT id FROM staff_users)
    AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND pa.created_at <= date_trunc('week', now())
  GROUP BY action_week
) pa_staff ON dr.week_start = pa_staff.action_week
LEFT JOIN (
  SELECT
    date_trunc('week', p.created_at) AS post_week,
    COUNT(*) AS posts_per_week
  FROM posts p
  WHERE p.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND p.created_at <= date_trunc('week', now())
  GROUP BY post_week
) posts_count ON dr.week_start = posts_count.post_week
ORDER BY dr.week_start
في حال كان الأمر مثيرًا للاهتمام، إليك الاستعلامات النهائية التي عدلت استعلام سارة:

لدي استعلام SQL يقدم عدد الإعجابات اليومية (likes_count) بين تاريخين، ولكن أحتاج إلى إجراء التحسينات التالية لإنتاج مخرجات نهائية تجمع البيانات حسب الأسابيع وتتضمن تفاصيل إضافية:

  1. تحديد مجموعة الموظفين:
  • إنشاء مجموعة staff_users من جدول users. يجب اعتبار المستخدم موظفًا إذا استوفى أيًا من المعايير التالية:
    • admin = true
    • moderator = true
    • trust_level = 4
  1. فصل الإعجابات حسب الموظفين وغير الموظفين:
  • إضافة عمودين منفصلين:
    • regular_likes_count: عدد الإعجابات من المستخدمين غير الموظفين.
    • staff_likes_count: عدد الإعجابات من مستخدمي الموظفين.
  • التأكد من أن عمود regular_likes_count يستبعد الإعجابات التي أنشأها مستخدمو الموظفين.
  1. إضافة إجمالي الإعجابات:
  • تضمين عمود total_likes الذي يجمع regular_likes_count و staff_likes_count.
  1. إضافة المشاركات لكل فترة:
  • إضافة عمود posts_per_week الذي يحسب عدد المشاركات التي تم إنشاؤها خلال كل أسبوع.
  1. التجميع حسب الأسابيع:
  • تعديل الاستعلام لتجميع جميع البيانات حسب فترات أسبوعية بدلاً من يومية.
  • تضمين عمود week_start الذي يمثل تاريخ بداية كل أسبوع.
  1. الحد حسب الأسابيع الماضية:
  • تقديم معلمة :weeks_ago لتحديد النتائج إلى آخر N أسبوع. يجب أن تكون القيمة الافتراضية 52 أسبوعًا (سنة واحدة).
  1. الترتيب والأعمدة النهائية:
  • التأكد من أن المخرجات مرتبة حسب week_start وتتضمن الأعمدة التالية بهذا الترتيب:
    1. week_start: تاريخ بداية الأسبوع.
    2. regular_likes_count: عدد الإعجابات من المستخدمين غير الموظفين.
    3. staff_likes_count: عدد الإعجابات من مستخدمي الموظفين.
    4. total_likes: مجموع regular_likes_count و staff_likes_count.
    5. posts_per_week: عدد المشاركات التي تم إنشاؤها خلال الأسبوع.