Rapport de tableau de bord - Émotion du message

Ceci est une version SQL du rapport de tableau de bord pour Post Emotion.

Ce rapport nécessite que le plugin Discourse AI et l’Analyse de Sentiment soient activés.

Ce rapport de tableau de bord montre le nombre de publications classées avec l’une des émotions suivantes, regroupées par niveau de confiance de l’auteur, dans une plage de dates spécifiée :

• Tristesse
• Surprise
• Peur
• Colère
• Joie
• Dégoût
• Admiration
• Amusement
• Agacement
• Approbation
• Attention
• Confusion
• Curiosité
• Désir
• Déception
• Désapprobation
• Embarras
• Excitation
• Gratitude
• Chagrin
• Amour
• Nervosité
• Neutre
• Optimisme
• Fierté
• Réalisation
• Soulagement
• Remords

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

SELECT
    u.trust_level AS trust_level,
    -- Émotions de base de la requête d'origine
    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,
    
    -- Émotions supplémentaires de la deuxième requête
    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,
    
    -- Nombre total de publications avec une émotion supérieure au seuil
    COUNT(*) AS total_posts,
    
    -- Nombre total de publications avec au moins une émotion supérieure au seuil
    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

Explication de la requête SQL

La requête SQL fonctionne en suivant ces étapes :

• Définition des paramètres :
  • :start_date et :end_date pour spécifier la plage de dates pour l’analyse.
  • :threshold pour définir le score minimum pour la classification de l’émotion du sentiment sur les publications. La valeur par défaut pour le :threshold est définie à 0,30 pour correspondre au rapport du tableau de bord.
• Sélection et jointure des données :
  • La requête sélectionne les données de la table classification_results, qui contient les résultats d’un modèle de classification d’émotions appliqué aux publications.
  • Elle joint la table classification_results avec la table posts pour ne filtrer que les classifications appartenant à des publications (cr.target_type = 'Post').
  • Elle joint ensuite avec les tables users et topics pour accéder aux niveaux de confiance des utilisateurs et s’assurer que les publications font partie de sujets réguliers (pas de messages privés ou d’autres types spéciaux).
• Filtrage :
  • La requête filtre les publications créées dans la plage de dates spécifiée (p.created_at > :start_date AND p.created_at < :end_date).
  • Elle garantit que les publications proviennent de sujets réguliers (t.archetype = 'regular'), qu’elles ont été créées par des utilisateurs enregistrés (p.user_id > 0), et cible spécifiquement les classifications émotionnelles (cr.model_used = 'emotion').
• Comptage des classifications :
  • Pour chaque émotion (tristesse, surprise, peur, colère, joie, dégoût), la requête compte le nombre de publications classées avec une intensité supérieure au seuil spécifié (:threshold).
• Regroupement : Les résultats sont regroupés par le niveau de confiance des utilisateurs (u.trust_level), fournissant une ventilation du contenu émotionnel par niveau de confiance des utilisateurs.

Exemple de résultats