تقرير لوحة القيادة - مشاعر المنشور

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

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

:discourse: يتطلب هذا التقرير تمكين المكون الإضافي Discourse AI و تحليل المشاعر.

يعرض تقرير لوحة المعلومات هذا عدد المنشورات المصنفة بواحدة من المشاعر التالية، مجمعة حسب مستوى ثقة الناشر، ضمن نطاق زمني محدد:

  • حزن
  • مفاجأة
  • خوف
  • غضب
  • فرح
  • اشمئزاز
  • إعجاب
  • تسلية
  • انزعاج
  • موافقة
  • اهتمام
  • ارتباك
  • فضول
  • رغبة
  • خيبة أمل
  • عدم موافقة
  • إحراج
  • حماس
  • امتنان
  • حزن
  • حب
  • توتر
  • محايد
  • تفاؤل
  • فخر
  • إدراك
  • ارتياح
  • ندم
-- [params]
-- date :start_date = 2024-01-16
-- date :end_date = 2024-02-16
-- double :threshold = 0.30

SELECT
    u.trust_level AS trust_level,
    -- Basic emotions from original query
    COUNT(CASE WHEN (classification->>'sadness')::float > :threshold THEN 1 ELSE NULL END) AS sadness,
    COUNT(CASE WHEN (classification->>'surprise')::float > :threshold THEN 1 ELSE NULL END) AS surprise,
    COUNT(CASE WHEN (classification->>'fear')::float > :threshold THEN 1 ELSE NULL END) AS fear,
    COUNT(CASE WHEN (classification->>'anger')::float > :threshold THEN 1 ELSE NULL END) AS anger,
    COUNT(CASE WHEN (classification->>'joy')::float > :threshold THEN 1 ELSE NULL END) AS joy,
    COUNT(CASE WHEN (classification->>'disgust')::float > :threshold THEN 1 ELSE NULL END) AS disgust,
    
    -- Additional emotions from the second query
    COUNT(CASE WHEN (classification->>'admiration')::float > :threshold THEN 1 ELSE NULL END) AS admiration,
    COUNT(CASE WHEN (classification->>'amusement')::float > :threshold THEN 1 ELSE NULL END) AS amusement,
    COUNT(CASE WHEN (classification->>'annoyance')::float > :threshold THEN 1 ELSE NULL END) AS annoyance,
    COUNT(CASE WHEN (classification->>'approval')::float > :threshold THEN 1 ELSE NULL END) AS approval,
    COUNT(CASE WHEN (classification->>'caring')::float > :threshold THEN 1 ELSE NULL END) AS caring,
    COUNT(CASE WHEN (classification->>'confusion')::float > :threshold THEN 1 ELSE NULL END) AS confusion,
    COUNT(CASE WHEN (classification->>'curiosity')::float > :threshold THEN 1 ELSE NULL END) AS curiosity,
    COUNT(CASE WHEN (classification->>'desire')::float > :threshold THEN 1 ELSE NULL END) AS desire,
    COUNT(CASE WHEN (classification->>'disappointment')::float > :threshold THEN 1 ELSE NULL END) AS disappointment,
    COUNT(CASE WHEN (classification->>'disapproval')::float > :threshold THEN 1 ELSE NULL END) AS disapproval,
    COUNT(CASE WHEN (classification->>'embarrassment')::float > :threshold THEN 1 ELSE NULL END) AS embarrassment,
    COUNT(CASE WHEN (classification->>'excitement')::float > :threshold THEN 1 ELSE NULL END) AS excitement,
    COUNT(CASE WHEN (classification->>'gratitude')::float > :threshold THEN 1 ELSE NULL END) AS gratitude,
    COUNT(CASE WHEN (classification->>'grief')::float > :threshold THEN 1 ELSE NULL END) AS grief,
    COUNT(CASE WHEN (classification->>'love')::float > :threshold THEN 1 ELSE NULL END) AS love,
    COUNT(CASE WHEN (classification->>'nervousness')::float > :threshold THEN 1 ELSE NULL END) AS nervousness,
    COUNT(CASE WHEN (classification->>'neutral')::float > :threshold THEN 1 ELSE NULL END) AS neutral,
    COUNT(CASE WHEN (classification->>'optimism')::float > :threshold THEN 1 ELSE NULL END) AS optimism,
    COUNT(CASE WHEN (classification->>'pride')::float > :threshold THEN 1 ELSE NULL END) AS pride,
    COUNT(CASE WHEN (classification->>'realization')::float > :threshold THEN 1 ELSE NULL END) AS realization,
    COUNT(CASE WHEN (classification->>'relief')::float > :threshold THEN 1 ELSE NULL END) AS relief,
    COUNT(CASE WHEN (classification->>'remorse')::float > :threshold THEN 1 ELSE NULL END) AS remorse,
    
    -- Total count of posts with any emotion above threshold
    COUNT(*) AS total_posts,
    
    -- Total count of posts with at least one emotion above threshold
    COUNT(CASE WHEN 
        (classification->>'sadness')::float > :threshold OR
        (classification->>'surprise')::float > :threshold OR
        (classification->>'fear')::float > :threshold OR
        (classification->>'anger')::float > :threshold OR
        (classification->>'joy')::float > :threshold OR
        (classification->>'disgust')::float > :threshold OR
        (classification->>'admiration')::float > :threshold OR
        (classification->>'amusement')::float > :threshold OR
        (classification->>'annoyance')::float > :threshold OR
        (classification->>'approval')::float > :threshold OR
        (classification->>'caring')::float > :threshold OR
        (classification->>'confusion')::float > :threshold OR
        (classification->>'curiosity')::float > :threshold OR
        (classification->>'desire')::float > :threshold OR
        (classification->>'disappointment')::float > :threshold OR
        (classification->>'disapproval')::float > :threshold OR
        (classification->>'embarrassment')::float > :threshold OR
        (classification->>'excitement')::float > :threshold OR
        (classification->>'gratitude')::float > :threshold OR
        (classification->>'grief')::float > :threshold OR
        (classification->>'love')::float > :threshold OR
        (classification->>'nervousness')::float > :threshold OR
        (classification->>'neutral')::float > :threshold OR
        (classification->>'optimism')::float > :threshold OR
        (classification->>'pride')::float > :threshold OR
        (classification->>'realization')::float > :threshold OR
        (classification->>'relief')::float > :threshold OR
        (classification->>'remorse')::float > :threshold
    THEN 1 ELSE NULL END) AS emotional_posts
FROM
    classification_results AS cr
    INNER JOIN posts p ON p.id = cr.target_id AND cr.target_type = 'Post'
    INNER JOIN users u ON p.user_id = u.id
    INNER JOIN topics t ON t.id = p.topic_id
WHERE
    t.archetype = 'regular' AND
    p.user_id > 0 AND
    cr.model_used = 'SamLowe/roberta-base-go_emotions' AND
    (p.created_at > :start_date AND p.created_at < :end_date)
GROUP BY
    u.trust_level
ORDER BY
    u.trust_level

شرح استعلام SQL

يقوم استعلام SQL بتنفيذ الخطوات التالية:

  • تعريف المعلمات:
    • :start_date و :end_date لتحديد النطاق الزمني للتحليل.
    • :threshold لتعيين الحد الأدنى للدرجة لتصنيف مشاعر المشاعر في المنشورات. تم تعيين القيمة الافتراضية لـ :threshold إلى 0.30 لتتناسب مع تقرير لوحة المعلومات.
  • اختيار البيانات وربطها:
    • يختار الاستعلام البيانات من جدول classification_results الذي يحتوي على نتائج نموذج تصنيف المشاعر المطبق على المنشورات.
    • يربط جدول classification_results بجدول posts لتصفية التصنيفات التي تنتمي إلى المنشورات فقط (cr.target_type = 'Post').
    • يربط بشكل إضافي بجدول users و topics للوصول إلى مستويات ثقة المستخدم والتأكد من أن المنشورات جزء من المواضيع العادية (وليس الرسائل الخاصة أو أنواع خاصة أخرى).
  • التصفية:
    • يقوم الاستعلام بتصفية المنشورات التي تم إنشاؤها ضمن النطاق الزمني المحدد (p.created_at > :start_date AND p.created_at < :end_date).
    • يضمن أن المنشورات من مواضيع عادية (t.archetype = 'regular')، وأنها من مستخدمين مسجلين (p.user_id > 0)، ويستهدف بشكل خاص تصنيفات المشاعر (cr.model_used = 'emotion').
  • عد التصنيف:
    • لكل شعور (حزن، مفاجأة، خوف، غضب، فرح، اشمئزاز)، يحسب الاستعلام عدد المنشورات المصنفة بكثافة أكبر من العتبة المحددة (:threshold).
  • التجميع: يتم تجميع النتائج حسب مستوى ثقة المستخدمين (u.trust_level)، مما يوفر تفصيلاً للمحتوى العاطفي حسب مستوى ثقة المستخدم.

نتائج مثال

trust_level sadness surprise fear anger joy disgust admiration amusement … emotional_posts total_posts
0 12 8 5 15 20 3 18 25 … 78 120
1 35 42 18 29 64 12 57 82 … 245 310
2 67 85 32 48 112 23 124 156 … 487 520
3 45 63 24 37 95 18 102 124 … 326 380
4 21 36 14 18 53 9 67 72 … 175 210
3 إعجابات