Dashboard-Bericht - Emotion nach Beitrag

Daten & Berichterstattung
, , , ,
1

Dies ist eine SQL-Version des Dashboard-Berichts für Post Emotion.

:discourse: Für diesen Bericht ist das Plugin Discourse AI und Sentiment Analysis aktiviert.

Dieses Dashboard zeigt die Anzahl der Beiträge, die mit einer der folgenden Emotionen klassifiziert wurden, gruppiert nach Vertrauensstufe des Posters, innerhalb eines bestimmten Datumsbereichs:

  • Traurigkeit
  • Überraschung
  • Angst
  • Wut
  • Freude
  • Ekel
  • Bewunderung
  • Belustigung
  • Verärgerung
  • Zustimmung
  • Fürsorge
  • Verwirrung
  • Neugier
  • Verlangen
  • Enttäuschung
  • Missbilligung
  • Verlegenheit
  • Aufregung
  • Dankbarkeit
  • Kummer
  • Liebe
  • Nervosität
  • Neutral
  • Optimismus
  • Stolz
  • Erkenntnis
  • Erleichterung
  • Reue
-- [params]
-- date :start_date = 2024-01-16
-- date :end_date = 2024-02-16
-- double :threshold = 0.30

SELECT
    u.trust_level AS trust_level,
    -- Grundlegende Emotionen aus der ursprünglichen Abfrage
    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,
    
    -- Zusätzliche Emotionen aus der zweiten Abfrage
    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,
    
    -- Gesamtzahl der Beiträge mit einer Emotion über dem Schwellenwert
    COUNT(*) AS total_posts,
    
    -- Gesamtzahl der Beiträge mit mindestens einer Emotion über dem Schwellenwert
    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

Erklärung der SQL-Abfrage

Die SQL-Abfrage führt folgende Schritte aus:

  • Parameterdefinition:
    • :start_date und :end_date zur Angabe des Datumsbereichs für die Analyse.
    • :threshold zur Festlegung des Mindestwerts für die Klassifizierung von Sentiment-Emotionen in Beiträgen. Der Standardwert für :threshold ist auf 0,30 gesetzt, um dem Dashboard-Bericht zu entsprechen.
  • Datenauswahl und Verknüpfung:
    • Die Abfrage wählt Daten aus der Tabelle classification_results aus, die Ergebnisse eines Modells zur Emotionsklassifizierung enthält, das auf Beiträge angewendet wurde.
    • Sie verknüpft die Tabelle classification_results mit der Tabelle posts, um nur die Klassifizierungen zu filtern, die zu Beiträgen gehören (cr.target_type = 'Post').
    • Sie verknüpft weiter mit den Tabellen users und topics, um auf die Vertrauensstufen der Benutzer zuzugreifen und sicherzustellen, dass die Beiträge Teil regulärer Themen sind (keine privaten Nachrichten oder andere spezielle Typen).
  • Filterung:
    • Die Abfrage filtert Beiträge, die innerhalb des angegebenen Datumsbereichs erstellt wurden (p.created_at > :start_date AND p.created_at < :end_date).
    • Sie stellt sicher, dass die Beiträge aus regulären Themen stammen (t.archetype = 'regular'), von registrierten Benutzern erstellt wurden (p.user_id > 0) und gezielt emotionale Klassifizierungen (cr.model_used = 'emotion') verwendet werden.
  • Zählung der Klassifizierungen:
    • Für jede Emotion (Traurigkeit, Überraschung, Angst, Wut, Freude, Ekel) zählt die Abfrage die Anzahl der Beiträge, die mit einer Intensität größer als der angegebene Schwellenwert (:threshold) klassifiziert wurden.
  • Gruppierung: Die Ergebnisse werden nach der Vertrauensstufe der Benutzer (u.trust_level) gruppiert, wodurch eine Aufschlüsselung der emotionalen Inhalte nach Benutzervertrauensstufe bereitgestellt wird.

Beispielergebnisse

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 „Gefällt mir“