تقرير لوحة التحكم - تفاعلات

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

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

يقدم هذا التقرير عددًا يوميًا لجميع الإعجابات وردود الفعل على المنشورات عبر موقع ما خلال نطاق زمني محدد.

يساعد هذا التقرير في تقديم لمحة عن تفاعل المستخدمين داخل مجتمع Discourse من خلال قياس تكرار رموز ردود الفعل المختلفة والإعجابات على المنشورات. من خلال تحليل استخدام رموز ردود الفعل المختلفة، يمكن للمسؤولين الحصول على رؤى حول عدد المرات التي يتفاعل فيها المستخدمون مع المحتوى، والاستجابات العاطفية للمستخدمين للمنشورات، وتحديد رموز ردود الفعل الشائعة وغير المستخدمة.

:information_source: يتطلب هذا التقرير تمكين إضافة Discourse Reactions على موقعك. ستعتمد ردود الفعل المتاحة في التقرير على ردود الفعل المحددة التي تم تمكينها باستخدام إعداد الموقع discourse_reactions_enabled_reactions.

-- [params]
-- date :start_date = 2023-12-16
-- date :end_date = 2024-01-17
 
SELECT
  r.day,
  COALESCE(l.likes_count, 0) as likes_count,
  sum(case when reaction_value = 'laughing' then reactions_count else 0 end)::int as laughing,
  sum(case when reaction_value = 'cry' then reactions_count else 0 end)::int as cry,
  sum(case when reaction_value = 'exploding_head' then reactions_count else 0 end)::int as exploding_head,
  sum(case when reaction_value = 'clap' then reactions_count else 0 end)::int as clap,
  sum(case when reaction_value = 'confetti_ball' then reactions_count else 0 end)::int as confetti_ball,
  sum(case when reaction_value = 'hugs' then reactions_count else 0 end)::int as hugs,
  sum(case when reaction_value = 'chefs_kiss' then reactions_count else 0 end)::int as chefs_kiss,
  sum(case when reaction_value = '100' then reactions_count else 0 end)::int as one_hundred,
  sum(case when reaction_value = '+1' then reactions_count else 0 end)::int as plus_one,
  sum(case when reaction_value = 'rocket' then reactions_count else 0 end)::int as rocket,
  sum(case when reaction_value = 'star_struck' then reactions_count else 0 end)::int as star_struck,
  sum(case when reaction_value = 'eyes' then reactions_count else 0 end)::int as eyes,
  sum(case when reaction_value = 'discourse' then reactions_count else 0 end)::int as discourse
FROM (
  SELECT
    date_trunc('day', drru.created_at)::date as day,
    drr.reaction_value,
    count(drru.id) as reactions_count
  FROM discourse_reactions_reactions as drr
  LEFT OUTER JOIN discourse_reactions_reaction_users as drru on drr.id = drru.reaction_id
  WHERE drr.reaction_users_count IS NOT NULL
  AND drru.created_at::date >= :start_date::date AND drru.created_at::date <= :end_date::date
  GROUP BY drr.reaction_value, day
) r
LEFT JOIN (
  SELECT
    count(pa.id) as likes_count,
    date_trunc('day', pa.created_at)::date as day
  FROM post_actions as pa
  WHERE pa.post_action_type_id = 2 
  AND pa.created_at::date >= :start_date::date AND pa.created_at::date <= :end_date::date 
  GROUP BY day
) l ON r.day = l.day
GROUP BY r.day, l.likes_count
ORDER BY r.day

شرح استعلام SQL

المعلمات

  • يقبل الاستعلام معلمتين، :start_date و :end_date، اللتين تحددان النطاق الزمني للتقرير. تقبل كلتا معلمتي التاريخ تنسيق التاريخ YYYY-MM-DD.

هيكل الاستعلام

  • الاستعلام الداخلي (ردود الفعل): يبدأ الاستعلام باستعلام فرعي يحدد التاريخ (day) ونوع رد الفعل (reaction_value) وعدد ردود الفعل (reactions_count) من جدول discourse_reactions_reactions المرتبط بـ discourse_reactions_reaction_users عبر reaction_id. يضمن هذا الارتباط أننا نحسب ردود الفعل الخاصة بالمستخدم.
  • التجميع حسب أنواع ردود الفعل: يتم بعد ذلك تجميع البيانات المحددة حسب day و reaction_value، مع تقييد النطاق بالتواريخ المحددة للبداية والنهاية. يُستخدم هذا لحساب العدد الإجمالي لكل نوع من ردود الفعل لكل يوم في النطاق الزمني المحدد.
  • فرز إجمالي ردود الفعل: لكل نوع من ردود الفعل، يستخدم الاستعلام عبارة CASE لتجميع عدد المرات التي تم فيها استخدام رد الفعل المحدد وتحويل النتيجة إلى عدد صحيح لعد نظيف.
  • قد تحتاج إلى تعديل reaction_value = '...' في هذا القسم اعتمادًا على ردود الفعل التي تم تمكينها على موقعك.
  • الاستعلام الفرعي لعدد الإعجابات: يُستخدم استعلام فرعي منفصل لحساب العدد الإجمالي للإعجابات (likes_count) لكل يوم باستخدام جدول post_actions حيث يتوافق post_action_type_id مع الإعجابات.
  • دمج البيانات: يجمع الاستعلام الخارجي بعد ذلك بين حساب الإعجابات وعدد ردود الفعل عن طريق الربط في حقل day.
  • الاختيار النهائي: يقوم SELECT الخارجي بإنشاء الإخراج النهائي مع day وعدد الإعجابات (likes_count) وعدد كل نوع من ردود الفعل. إذا لم تكن هناك بيانات للإعجابات في يوم معين، تضمن الدالة COALESCE عرض صفر بدلاً من NULL.
  • ترتيب النتائج: يتم ترتيب النتائج حسب التاريخ (r.day) لإنشاء سلسلة زمنية للتفاعل على الموقع.

الأعمدة

  • day: التاريخ الذي تم فيه حساب ردود الفعل والإعجابات.
  • likes_count: العدد الإجمالي للإعجابات لكل يوم.
  • كل نوع من ردود الفعل (ضحك، بكاء، رأس متفجر، إلخ): أعمدة فردية تعرض العدد الإجمالي لكل نوع من ردود الفعل في اليوم.

نتائج مثال

day likes_count laughing cry exploding_head clap confetti_ball hugs chefs_kiss one_hundred plus_one rocket star_struck eyes discourse
2023-12-16 13 0 3 0 3 1 0 0 0 5 2 2 1 0
2023-12-17 17 1 0 2 2 0 0 0 0 4 4 1 2 0
2023-12-18 46 0 1 0 6 0 1 3 0 27 3 4 5 0
إعجاب واحد (1)
2

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

لدي شعور بأن هذا يحسب بشكل خاطئ إذا تغير الافتراضي من :heart: إلى :+1: على سبيل المثال.

3

@JammyDodger قام بإصلاح استعلام كنت أستخدمه يأخذ هذا في الاعتبار، على ما أعتقد:

إعجابَين (2)