Отчет по панели управления - Пост Эмоция

SaraDev · 16.Февраль.2024 01:04:05

Это SQL-версия отчета панели управления для Post Emotion.

Для работы этого отчета необходимо включить плагины Discourse AI и Sentiment Analysis.

Этот отчет панели управления показывает количество сообщений, классифицированных по одному из следующих эмоций, сгруппированных по уровню доверия автора, в указанном диапазоне дат:

Грусть
Удивление
Страх
Гнев
Радость
Отвращение
Восхищение
Развлечение
Раздражение
Одобрение
Забота
Путаница
Любопытство
Желание
Разочарование
Непризнание
Смущение
Волнение
Благодарность
Печаль
Любовь
Тревожность
Нейтральность
Оптимизм
Гордость
Осознание
Облегчение
Раскаяние

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

SELECT
    u.trust_level AS trust_level,
    -- Базовые эмоции из исходного запроса
    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,
    
    -- Дополнительные эмоции из второго запроса
    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,
    
    -- Общее количество сообщений с любой эмоцией выше порога
    COUNT(*) AS total_posts,
    
    -- Общее количество сообщений, где хотя бы одна эмоция выше порога
    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

Пояснение к SQL-запросу

SQL-запрос работает следующим образом:

Определение параметров:
- :start_date и :end_date для указания диапазона дат анализа.
- :threshold для установки минимального порога классификации эмоциональной окраски сообщений. Значение по умолчанию для :threshold установлено на 0.30, чтобы соответствовать отчету панели управления.
Выборка и соединение данных:
- Запрос выбирает данные из таблицы classification_results, содержащей результаты модели классификации эмоций, примененной к сообщениям.
- Он соединяет таблицу classification_results с таблицей posts, чтобы отфильтровать только те классификации, которые относятся к сообщениям (cr.target_type = 'Post').
- Далее он соединяется с таблицами users и topics, чтобы получить уровни доверия пользователей и убедиться, что сообщения относятся к обычным темам (а не к личным сообщениям или другим специальным типам).
Фильтрация:
- Запрос фильтрует сообщения, созданные в указанном диапазоне дат (p.created_at > :start_date AND p.created_at < :end_date).
- Он гарантирует, что сообщения относятся к обычным темам (t.archetype = 'regular'), созданы зарегистрированными пользователями (p.user_id > 0) и конкретно направлены на эмоциональные классификации (cr.model_used = 'SamLowe/roberta-base-go_emotions').
Подсчет классификаций:
- Для каждой эмоции (грусть, удивление, страх, гнев, радость, отвращение) запрос подсчитывает количество сообщений, классифицированных с интенсивностью выше указанного порога (:threshold).
Группировка: Результаты группируются по уровню доверия пользователей (u.trust_level), предоставляя разбивку эмоционального контента по уровням доверия пользователей.

Пример результатов

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

Тема		Ответов	Просм.
AI sentiment and emotion analysis reports Data & reporting sql-query , ai , ai-sentiment	0	146	15.07.2025
Dashboard Report - Overall Sentiment Data & reporting dashboard-reports , sql-query , dashboard-sql , ai , ai-sentiment	3	794	03.07.2025
Community Sentiment and Toxicity queries Data & reporting ai , sql-query	14	1155	13.11.2024
Add Sentiment score per topic Feature ai , ai-sentiment	1	172	23.06.2023
Discourse AI - Sentiment Site Management ai , content , ai-sentiment , how-to	14	5578	24.10.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

Отчет по панели управления - Пост Эмоция

Пояснение к SQL-запросу

Пример результатов

Связанные темы

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