Report Dashboard - Emozione del Post

Questa è una versione SQL del report di dashboard per Post Emotion.

Questo report richiede che il plugin Discourse AI e Sentiment Analysis siano abilitati.

Questo report di dashboard mostra il numero di post classificati con una delle seguenti emozioni, raggruppati per livello di fiducia del poster, entro un intervallo di date specificato:

• Tristezza
• Sorpresa
• Paura
• Rabbia
• Gioia
• Disgusto
• Ammirazione
• Divertimento
• Fastidio
• Approvazione
• Cura
• Confusione
• Curiosità
• Desiderio
• Delusione
• Disapprovazione
• Imbarazzo
• Eccitazione
• Gratitudine
• Dolore
• Amore
• Nervosismo
• Neutrale
• Ottimismo
• Orgoglio
• Realizzazione
• Sollievo
• Rimorso

-- [params]
-- date :start_date = 2024-01-16
-- date :end_date = 2024-02-16
-- double :threshold = 0.30

SELECT
    u.trust_level AS trust_level,
    -- Emozioni di base dalla query originale
    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,
    
    -- Emozioni aggiuntive dalla seconda 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,
    
    -- Conteggio totale dei post con qualsiasi emozione sopra la soglia
    COUNT(*) AS total_posts,
    
    -- Conteggio totale dei post con almeno un'emozione sopra la soglia
    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

Spiegazione della query SQL

La query SQL opera eseguendo i seguenti passaggi:

• Definizione dei parametri:
  • :start_date e :end_date per specificare l’intervallo di date per l’analisi.
  • :threshold per impostare il punteggio minimo per la classificazione delle emozioni di sentimento sui post. Il valore predefinito per :threshold è impostato su 0.30 per corrispondere al report di dashboard.
• Selezione e unione dei dati:
  • La query seleziona i dati dalla tabella classification_results, che contiene i risultati di un modello di classificazione delle emozioni applicato ai post.
  • Unisce la tabella classification_results con la tabella posts per filtrare solo le classificazioni che appartengono ai post (cr.target_type = 'Post').
  • Unisce ulteriormente con le tabelle users e topics per accedere ai livelli di fiducia degli utenti e garantire che i post facciano parte di argomenti regolari (non messaggi privati o altri tipi speciali).
• Filtraggio:
  • La query filtra i post creati nell’intervallo di date specificato (p.created_at > :start_date AND p.created_at < :end_date).
  • Assicura che i post provengano da argomenti regolari (t.archetype = 'regular'), siano stati creati da utenti registrati (p.user_id > 0) e prenda di mira specificamente le classificazioni emotive (cr.model_used = 'emotion').
• Conteggio delle classificazioni:
  • Per ogni emozione (tristezza, sorpresa, paura, rabbia, gioia, disgusto), la query conta il numero di post classificati con un’intensità maggiore della soglia specificata (:threshold).
• Raggruppamento: I risultati sono raggruppati per livello di fiducia degli utenti (u.trust_level), fornendo una ripartizione del contenuto emotivo per livello di fiducia dell’utente.

Risultati di esempio