Relatório do Painel - Emoção do Post

SaraDev · Fevereiro 16, 2024, 1:04am

Este é um relatório de painel SQL para Post Emotion.

Este relatório requer que o plugin Discourse AI e Análise de Sentimento estejam ativados.

Este relatório de painel mostra o número de posts classificados com uma das seguintes emoções, agrupados por nível de confiança do autor, dentro de um intervalo de datas especificado:

Tristeza
Surpresa
Medo
Raiva
Alegria
Nojo
Admiração
Diversão
Irritação
Aprovação
Cuidado
Confusão
Curiosidade
Desejo
Decepção
Desaprovação
Vergonha
Excitação
Gratidão
Luto
Amor
Nervosismo
Neutro
Otimismo
Orgulho
Realização
Alívio
Remorso

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

SELECT
    u.trust_level AS trust_level,
    -- Emoções básicas da consulta original
    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,
    
    -- Emoções adicionais da segunda consulta
    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,
    
    -- Contagem total de posts com qualquer emoção acima do limite
    COUNT(*) AS total_posts,
    
    -- Contagem total de posts com pelo menos uma emoção acima do limite
    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

Explicação da Consulta SQL

A consulta SQL opera realizando os seguintes passos:

Definição de Parâmetros:
- :start_date e :end_date para especificar o intervalo de datas para a análise.
- :threshold para definir a pontuação mínima para classificar a emoção de sentimento em posts. O valor padrão para o :threshold é definido como 0.30 para corresponder ao relatório do painel.
Seleção e Junção de Dados:
- A consulta seleciona dados da tabela classification_results, que contém os resultados de um modelo de classificação de emoções aplicado a posts.
- Ela junta a tabela classification_results com a tabela posts para filtrar apenas as classificações que pertencem a posts (cr.target_type = 'Post').
- Ela se junta ainda mais às tabelas users e topics para acessar os níveis de confiança do usuário e garantir que os posts façam parte de tópicos regulares (não mensagens privadas ou outros tipos especiais).
Filtragem:
- A consulta filtra posts criados dentro do intervalo de datas especificado (p.created_at > :start_date AND p.created_at < :end_date).
- Ela garante que os posts sejam de tópicos regulares (t.archetype = 'regular'), feitos por usuários registrados (p.user_id > 0) e especificamente visa classificações emocionais (cr.model_used = 'SamLowe/roberta-base-go_emotions').
Contagem de Classificação:
- Para cada emoção (tristeza, surpresa, medo, raiva, alegria, nojo), a consulta conta o número de posts classificados com uma intensidade maior que o limite especificado (:threshold).
Agrupamento: Os resultados são agrupados pelo nível de confiança dos usuários (u.trust_level), fornecendo uma discriminação do conteúdo emocional por nível de confiança do usuário.

Exemplo de Resultados

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

Tópico		Respostas	Visualizações
AI sentiment and emotion analysis reports Data & reporting ai , sql-query , ai-sentiment	0	75	15 de Julho de 2025
Dashboard Report - Overall Sentiment Data & reporting ai , sql-query , dashboard-reports , ai-sentiment , dashboard-sql	3	735	3 de Julho de 2025
Community Sentiment and Toxicity queries Data & reporting ai , sql-query	14	1076	13 de Novembro de 2024
Add Sentiment score per topic Feature ai , ai-sentiment	1	148	23 de Junho de 2023
Discourse AI - Sentiment Site Management how-to , ai , ai-sentiment , content	14	5284	24 de Outubro de 2025

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

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

Relatório do Painel - Emoção do Post

Explicação da Consulta SQL

Exemplo de Resultados

Tópicos relacionados

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