Informe del panel - Emoción posterior

SaraDev · 16 Febrero, 2024 01:04

Esta es una versión SQL del Informe del Panel para Post Emotion.

Este informe requiere que el plugin Discourse AI y Análisis de Sentimiento estén habilitados.

Este informe del panel muestra el número de publicaciones clasificadas con una de las siguientes emociones, agrupadas por nivel de confianza del publicador, dentro de un rango de fechas especificado:

Tristeza
Sorpresa
Miedo
Ira
Alegría
Asco
Admiración
Diversión
Molestia
Aprobación
Preocupación
Confusión
Curiosidad
Deseo
Decepción
Desaprobación
Vergüenza
Emoción
Gratitud
Duelo
Amor
Nerviosismo
Neutral
Optimismo
Orgullo
Realización
Alivio
Remordimiento

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

SELECT
    u.trust_level AS trust_level,
    -- Emociones básicas de la 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,
    
    -- Emociones adicionales de la 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,
    
    -- Recuento total de publicaciones con cualquier emoción por encima del umbral
    COUNT(*) AS total_posts,
    
    -- Recuento total de publicaciones con al menos una emoción por encima del umbral
    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

Explicación de la consulta SQL

La consulta SQL opera realizando los siguientes pasos:

Definición de Parámetros:
- :start_date y :end_date para especificar el rango de fechas para el análisis.
- :threshold para establecer la puntuación mínima para clasificar la emoción del sentimiento en las publicaciones. El valor predeterminado para :threshold se establece en 0.30 para que coincida con el informe del panel.
Selección y Unión de Datos:
- La consulta selecciona datos de la tabla classification_results, que contiene los resultados de un modelo de clasificación de emociones aplicado a las publicaciones.
- Une la tabla classification_results con la tabla posts para filtrar solo aquellas clasificaciones que pertenecen a publicaciones (cr.target_type = 'Post').
- Une además con las tablas users y topics para acceder a los niveles de confianza de los usuarios y asegurar que las publicaciones formen parte de temas regulares (no mensajes privados u otros tipos especiales).
Filtrado:
- La consulta filtra las publicaciones creadas dentro del rango de fechas especificado (p.created_at > :start_date AND p.created_at < :end_date).
- Asegura que las publicaciones sean de temas regulares (t.archetype = 'regular'), realizadas por usuarios registrados (p.user_id > 0) y apunta específicamente a clasificaciones emocionales (cr.model_used = 'emotion').
Conteo de Clasificación:
- Para cada emoción (tristeza, sorpresa, miedo, ira, alegría, asco), la consulta cuenta el número de publicaciones clasificadas con una intensidad superior al umbral especificado (:threshold).
Agrupación: Los resultados se agrupan por el nivel de confianza de los usuarios (u.trust_level), proporcionando un desglose del contenido emocional por nivel de confianza del usuario.

Resultados de Ejemplo

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

Tema		Respuestas	Vistas
AI sentiment and emotion analysis reports Data & reporting ai , sql-query , ai-sentiment	0	75	15 Julio 2025
Dashboard Report - Overall Sentiment Data & reporting ai , sql-query , dashboard-reports , ai-sentiment , dashboard-sql	3	735	3 Julio 2025
Community Sentiment and Toxicity queries Data & reporting ai , sql-query	14	1076	13 Noviembre 2024
Add Sentiment score per topic Feature ai , ai-sentiment	1	148	23 Junio 2023
Discourse AI - Sentiment Site Management how-to , ai , ai-sentiment , content	14	5284	24 Octubre 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

Informe del panel - Emoción posterior

Explicación de la consulta SQL

Resultados de Ejemplo

Temas 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