Отчёты по анализу тональности и эмоций с использованием ИИ

SaraDev · 15.Июль.2025 00:23:12

Плагин Discourse AI включает возможности анализа тональности, которые помогут вам глубже понять эмоциональный фон обсуждений в вашем сообществе. В этой теме рассматриваются два подробных примера запросов для Data Explorer, использующих эти возможности ИИ для получения инсайтов о сообществах Discourse.

AI Sentiment Per Category and Trust Level Total: Анализ временных рядов, отслеживающий тенденции тональности по неделям в конкретных категориях и уровнях доверия.
AI Emotion Outlier Topics: Выявление тем обсуждений, вызывающих значительные эмоциональные реакции на сайте Discourse.

Предварительные требования

Для использования этих отчетов вам необходимо:

Установленный и включенный плагин Discourse AI: Плагин Discourse AI должен быть установлен на вашем экземпляре.
Включенный анализ тональности: Модуль Sentiment Analysis должен быть настроен и активен.
Плагин Data Explorer: Необходим для выполнения этих SQL-запросов.
Исторические данные о тональности: Достаточно проанализированных на предмет тональности постов для получения значимых результатов (может потребоваться операция заполнения данных).

Модели анализа тональности ИИ и принцип их работы

Прежде чем переходить к отчетам, полезно понять, что именно анализируют модели тональности в постах вашего сообщества:

Общая тональность: Модель cardiffnlp/twitter-roberta-base-sentiment-latest классифицирует посты как позитивные, негативные или нейтральные.
Определение эмоций: Модель SamLowe/roberta-base-go_emotions идентифицирует конкретные эмоции в постах, такие как радость, печаль, гнев и т. д.

Эти модели анализируют текст каждого поста и сохраняют их классификации в вашей базе данных, к которым затем может обращаться плагин Data Explorer.

Отчет AI Sentiment Per Category and Trust Level Total

-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2025-12-31
-- category_id :category_id = 6
-- int :min_trust_level = 0
-- boolean :exclude_staff = false

-- Создание временного набора результатов, агрегирующего метрики тональности по неделям для указанной категории
WITH sentiment_counts AS (
  SELECT 
    c.id as category_id,
    c.name as category_name,
    -- Группировка постов по неделям для анализа временных рядов
    DATE_TRUNC('week', p.created_at) as week_starting,
    EXTRACT(YEAR FROM p.created_at) as year,
    EXTRACT(WEEK FROM p.created_at) as week_number,
    -- Подсчет постов с позитивной тональностью (порог > 0.6)
    COUNT(CASE WHEN (cr.classification::jsonb->'positive')::float > 0.6 THEN 1 
              ELSE NULL END) as positive_count,
    -- Подсчет постов с негативной тональностью (порог > 0.6)
    COUNT(CASE WHEN (cr.classification::jsonb->'negative')::float > 0.6 THEN 1 
              ELSE NULL END) as negative_count,
    -- Подсчет постов с нейтральной тональностью (и позитивная, и негативная <= 0.6)
    COUNT(CASE WHEN (cr.classification::jsonb->'positive')::float <= 0.6 
               AND (cr.classification::jsonb->'negative')::float <= 0.6 THEN 1 
              ELSE NULL END) as neutral_count,
    -- Общее количество постов с анализом тональности
    COUNT(*) as total_classifications
  FROM classification_results cr
  -- Присоединение данных постов для получения дат создания и метаданных
  JOIN posts p ON p.id = cr.target_id AND cr.target_type = 'Post'
  -- Присоединение данных тем для фильтрации по категории
  JOIN topics t ON t.id = p.topic_id
  -- Присоединение данных пользователей для фильтрации по уровню доверия
  JOIN users u ON u.id = p.user_id
  -- Присоединение данных категорий для получения названия категории
  JOIN categories c ON c.id = t.category_id
  WHERE 
    -- Включать только результаты тональности от этой конкретной модели
    cr.model_used = 'cardiffnlp/twitter-roberta-base-sentiment-latest'
    -- Включать только обычные темы (без личных сообщений и т. д.)
    AND t.archetype = 'regular'
    -- Исключать системные посты
    AND p.user_id > 0
    -- Фильтрация по выбранной категории
    AND c.id = :category_id
    -- Фильтрация по минимальному уровню доверия
    AND u.trust_level >= :min_trust_level
    -- Исключать пользователей из штата, если параметр установлен
    AND (:exclude_staff = false OR (u.admin = false AND u.moderator = false))
    -- Фильтрация по диапазону дат
    AND p.created_at BETWEEN :start_date AND :end_date
  -- Группировка по неделям и категориям
  GROUP BY c.id, c.name, week_starting, year, week_number
)
-- Форматирование финальных результатов для отображения
SELECT 
  category_id,
  category_name,
  -- Преобразование в Date для более чистого отображения
  week_starting::Date,
  -- Форматирование в нотацию ISO недели (ГГГГ-ВXX)
  year || '-W' || LPAD(week_number::text, 2, '0') as year_week,
  -- Расчет сальдо тональности (позитивные минус негативные)
  positive_count - negative_count as sentiment_balance,
  positive_count,
  negative_count,
  neutral_count,
  -- Расчет процента позитивных постов (округление до 2 знаков после запятой)
  ROUND(
    (positive_count::float / NULLIF(total_classifications, 0) * 100)::numeric,
    2
  ) as positive_percentage
FROM sentiment_counts
-- Сортировка хронологически для отображения тенденций тональности во времени
ORDER BY week_starting ASC
```\n
Этот отчет предоставляет еженедельный анализ тенденций тональности в конкретной категории, показывая:

- Количество позитивных, негативных и нейтральных постов для каждой недели.
- Расчет сальдо тональности (позитивные посты минус негативные).
- Процент позитивных постов относительно общего числа проанализированных.
- Фильтрацию по уровню доверия пользователей и возможность исключения постов сотрудников.

Этот отчет полезен для:

- Отслеживания тенденций тональности сообщества во времени в конкретных категориях.
- Выявления колебаний настроения сообщества, которые могут коррелировать с определенными событиями или изменениями.
- Сравнения тональности между различными сегментами пользователей (по уровню доверия).
- Оценки влияния вмешательств модерации на общую тональность сообщества.

### Параметры

Запрос принимает несколько параметров для настройки вашего анализа:

- **Диапазон дат**: Установите даты начала и конца для периода анализа.
- **Категория**: Выберите категорию для анализа.
- **Минимальный уровень доверия**: Отфильтруйте посты, чтобы включить только посты пользователей с уровнем доверия, равным или превышающим определенный.
- **Исключить штат**: Опция удаления постов сотрудников из анализа (чтобы сосредоточиться на обычных членах сообщества).

### Результаты

Результаты представлены в таблице, где каждая строка представляет неделю данных:

- **Информация о категории**: ID и название проанализированной категории.
- **Периоды времени**: Дата начала недели и нотация ISO недели (ГГГГ-ВXX).
- **Метрики тональности**:
  - **Сальдо тональности**: Разница между позитивными и негативными постами (положительное значение указывает на общую позитивную тональность).
  - **Количество позитивных/негативных/нейтральных**: Количество постов в каждой категории тональности.
  - **Процент позитивных**: Процент постов, классифицированных как позитивные.

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

| category_name | week_starting | year_week | sentiment_balance | positive_count | negative_count | neutral_count | positive_percentage |
|---------------|--------------|-----------|-------------------|----------------|----------------|---------------|---------------------|
| Product Discussion | 2025-01-06 | 2025-W01 | -8 | 24 | 32 | 145 | 11.94 |
| Product Discussion | 2025-01-13 | 2025-W02 | -11 | 30 | 41 | 210 | 10.68 |
| Product Discussion | 2025-01-20 | 2025-W03 | -9 | 28 | 37 | 220 | 9.82 |
| Product Discussion | 2025-01-27 | 2025-W04 | -13 | 33 | 46 | 260 | 9.74 |
| Product Discussion | 2025-02-03 | 2025-W05 | -15 | 22 | 37 | 180 | 9.21 |
| Product Discussion | 2025-02-10 | 2025-W06 | -6 | 37 | 43 | 195 | 13.45 |

## Отчет AI Emotion Outlier Topics

``` sql
-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2025-12-31
-- category_id :category_id = 6
-- int :min_trust_level = 1
-- int :emotion_threshold = 10  

-- Сначала создаем общее табличное выражение (CTE), агрегирующее эмоциональные реакции по темам
WITH topic_emotions AS (
  SELECT
    topics.id AS topic_id,                 -- Сохраняем ID темы для последующего присоединения/фильтрации
    topics.title,                          -- Включаем заголовок темы для читаемости результатов
    topics.created_at::date AS topic_date, -- Сохраняем дату создания темы
    
    -- Для каждого типа эмоций считаем посты, где эта эмоция превышает порог уверенности 0.1
    -- Таблица classification_results хранит баллы эмоций как значения JSON
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'admiration')::float > 0.1) AS admiration_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'amusement')::float > 0.1) AS amusement_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'anger')::float > 0.1) AS anger_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'annoyance')::float > 0.1) AS annoyance_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'approval')::float > 0.1) AS approval_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'caring')::float > 0.1) AS caring_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'confusion')::float > 0.1) AS confusion_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'curiosity')::float > 0.1) AS curiosity_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'desire')::float > 0.1) AS desire_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'disappointment')::float > 0.1) AS disappointment_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'disapproval')::float > 0.1) AS disapproval_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'disgust')::float > 0.1) AS disgust_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'embarrassment')::float > 0.1) AS embarrassment_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'excitement')::float > 0.1) AS excitement_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'fear')::float > 0.1) AS fear_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'gratitude')::float > 0.1) AS gratitude_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'grief')::float > 0.1) AS grief_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'joy')::float > 0.1) AS joy_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'love')::float > 0.1) AS love_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'nervousness')::float > 0.1) AS nervousness_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'neutral')::float > 0.1) AS neutral_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'optimism')::float > 0.1) AS optimism_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'pride')::float > 0.1) AS pride_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'realization')::float > 0.1) AS realization_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'relief')::float > 0.1) AS relief_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'remorse')::float > 0.1) AS remorse_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'sadness')::float > 0.1) AS sadness_count,
    COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'surprise')::float > 0.1) AS surprise_count,
    
    -- Расчет общего количества эмоциональных реакций для целей ранжирования
    COUNT(*) AS total_emotional_reactions
  FROM
    classification_results
  -- Присоединение к таблице posts для получения метаданных постов и фильтрации удаленных постов
  INNER JOIN
    posts ON posts.id = classification_results.target_id AND
    posts.deleted_at IS NULL               -- Исключить удаленные посты
  
  -- Присоединение к таблице topics для получения метаданных тем и фильтрации по типу/статусу темы
  INNER JOIN
    topics ON topics.id = posts.topic_id AND
    topics.archetype = 'regular' AND       -- Включать только стандартные темы (не личные сообщения и системные сообщения)
    topics.deleted_at IS NULL              -- Исключить удаленные темы
  
  -- Присоединение к таблице users для получения уровня доверия пользователя для фильтрации
  INNER JOIN
    users ON users.id = posts.user_id
  
  WHERE
    -- Включать только классификации эмоций для постов (не для других типов контента)
    classification_results.target_type = 'Post' AND
    
    -- Использовать только результаты от этой конкретной модели определения эмоций
    classification_results.model_used = 'SamLowe/roberta-base-go_emotions' AND
    
    -- Фильтрация по диапазону дат с использованием параметризованных значений
    posts.created_at BETWEEN :start_date AND :end_date AND
    
    -- Фильтрация по указанной категории
    (topics.category_id = :category_id) AND
    
    -- Включать только посты от пользователей с достаточным уровнем доверия
    (users.trust_level >= :min_trust_level)
    
  -- Группировка всех подсчетов по темам
  GROUP BY 
    topics.id, topics.title, topics.created_at::date
)

-- Основной запрос, который форматирует и фильтрует агрегированные данные из CTE
SELECT
  topic_id,                                -- Отображение ID темы (будет отображаться как ссылка в Discourse)
  --title,                                   -- Отображение заголовка темы
  topic_date,                              -- Отображение даты создания темы
  total_emotional_reactions,               -- Показать общее количество обнаруженных эмоций
  
  -- Преобразование массива значимых эмоций в форматированную строку
  -- Включаются только эмоции, превышающие порог, остальные становятся NULL и пропускаются
  -- Каждая эмоция форматируется как "НазваниеЭмоции(количество)"
  ARRAY_TO_STRING(ARRAY[
    CASE WHEN admiration_count >= :emotion_threshold THEN 'Admiration(' || admiration_count || ')' ELSE NULL END,
    CASE WHEN amusement_count >= :emotion_threshold THEN 'Amusement(' || amusement_count || ')' ELSE NULL END,
    CASE WHEN anger_count >= :emotion_threshold THEN 'Anger(' || anger_count || ')' ELSE NULL END,
    CASE WHEN annoyance_count >= :emotion_threshold THEN 'Annoyance(' || annoyance_count || ')' ELSE NULL END,
    CASE WHEN approval_count >= :emotion_threshold THEN 'Approval(' || approval_count || ')' ELSE NULL END,
    CASE WHEN caring_count >= :emotion_threshold THEN 'Caring(' || caring_count || ')' ELSE NULL END,
    CASE WHEN confusion_count >= :emotion_threshold THEN 'Confusion(' || confusion_count || ')' ELSE NULL END,
    CASE WHEN curiosity_count >= :emotion_threshold THEN 'Curiosity(' || curiosity_count || ')' ELSE NULL END,
    CASE WHEN desire_count >= :emotion_threshold THEN 'Desire(' || desire_count || ')' ELSE NULL END,
    CASE WHEN disappointment_count >= :emotion_threshold THEN 'Disappointment(' || disappointment_count || ')' ELSE NULL END,
    CASE WHEN disapproval_count >= :emotion_threshold THEN 'Disapproval(' || disapproval_count || ')' ELSE NULL END,
    CASE WHEN disgust_count >= :emotion_threshold THEN 'Disgust(' || disgust_count || ')' ELSE NULL END,
    CASE WHEN embarrassment_count >= :emotion_threshold THEN 'Embarrassment(' || embarrassment_count || ')' ELSE NULL END,
    CASE WHEN excitement_count >= :emotion_threshold THEN 'Excitement(' || excitement_count || ')' ELSE NULL END,
    CASE WHEN fear_count >= :emotion_threshold THEN 'Fear(' || fear_count || ')' ELSE NULL END,
    CASE WHEN gratitude_count >= :emotion_threshold THEN 'Gratitude(' || gratitude_count || ')' ELSE NULL END,
    CASE WHEN grief_count >= :emotion_threshold THEN 'Grief(' || grief_count || ')' ELSE NULL END,
    CASE WHEN joy_count >= :emotion_threshold THEN 'Joy(' || joy_count || ')' ELSE NULL END,
    CASE WHEN love_count >= :emotion_threshold THEN 'Love(' || love_count || ')' ELSE NULL END,
    CASE WHEN nervousness_count >= :emotion_threshold THEN 'Nervousness(' || nervousness_count || ')' ELSE NULL END,
    CASE WHEN optimism_count >= :emotion_threshold THEN 'Optimism(' || optimism_count || ')' ELSE NULL END,
    CASE WHEN pride_count >= :emotion_threshold THEN 'Pride(' || pride_count || ')' ELSE NULL END,
    CASE WHEN realization_count >= :emotion_threshold THEN 'Realization(' || realization_count || ')' ELSE NULL END,
    CASE WHEN relief_count >= :emotion_threshold THEN 'Relief(' || relief_count || ')' ELSE NULL END,
    CASE WHEN remorse_count >= :emotion_threshold THEN 'Remorse(' || remorse_count || ')' ELSE NULL END,
    CASE WHEN sadness_count >= :emotion_threshold THEN 'Sadness(' || sadness_count || ')' ELSE NULL END,
    CASE WHEN surprise_count >= :emotion_threshold THEN 'Surprise(' || surprise_count || ')' ELSE NULL END
  ], ', ', '') AS significant_emotions     -- Объединение с разделителями-запятыми, пустая строка, если разделитель не нужен
  
FROM 
  topic_emotions
WHERE 
  -- Включать только темы, у которых хотя бы одна эмоция превышает порог
  -- Это идентифицирует темы со значительным эмоциональным воздействием
  (
    admiration_count >= :emotion_threshold OR
    amusement_count >= :emotion_threshold OR
    anger_count >= :emotion_threshold OR
    annoyance_count >= :emotion_threshold OR
    approval_count >= :emotion_threshold OR
    caring_count >= :emotion_threshold OR
    confusion_count >= :emotion_threshold OR
    curiosity_count >= :emotion_threshold OR
    desire_count >= :emotion_threshold OR
    disappointment_count >= :emotion_threshold OR
    disapproval_count >= :emotion_threshold OR
    disgust_count >= :emotion_threshold OR
    embarrassment_count >= :emotion_threshold OR
    excitement_count >= :emotion_threshold OR
    fear_count >= :emotion_threshold OR
    gratitude_count >= :emotion_threshold OR
    grief_count >= :emotion_threshold OR
    joy_count >= :emotion_threshold OR
    love_count >= :emotion_threshold OR
    nervousness_count >= :emotion_threshold OR
    optimism_count >= :emotion_threshold OR
    pride_count >= :emotion_threshold OR
    realization_count >= :emotion_threshold OR
    relief_count >= :emotion_threshold OR
    remorse_count >= :emotion_threshold OR
    sadness_count >= :emotion_threshold OR
    surprise_count >= :emotion_threshold
  )
-- Сортировка результатов по наибольшему количеству эмоциональных реакций
ORDER BY
  total_emotional_reactions DESC

Этот отчет идентифицирует темы, вызвавшие значительные эмоциональные реакции в вашем сообществе, на основе:

Подсчета каждого типа эмоций, обнаруженных в постах внутри темы.
Настраиваемого порога для определения того, что считается “значительной” эмоциональной реакцией.
Фильтрации по категории, диапазону дат и уровню доверия пользователей.

Этот отчет помогает вам:

Выявлять потенциально проблемные обсуждения, вызывающие сильные негативные эмоции.
Находить высоко вовлекающий контент, который эмоционально резонирует с вашим сообществом.
Обнаруживать темы, которые могут потребовать внимания модерации до их эскалации.
Выявлять тематические направления контента, вызывающие определенные эмоциональные реакции.
Лучше понимать, что стимулирует эмоциональную вовлеченность в вашем сообществе.

Параметры

Запрос принимает несколько параметров:

Диапазон дат: Установите даты начала и конца для периода анализа.
Категория: Выберите категорию для анализа.
Минимальный уровень доверия: Отфильтруйте посты, чтобы включить только посты пользователей с уровнем доверия, равным или превышающим определенный.
Порог эмоций: Установите, сколько случаев эмоции необходимо для того, чтобы считать её значимой.

Результаты

Результаты показывают:

ID темы: Ссылка напрямую на тему (кликабельна в Data Explorer).
Дата темы: Когда тема была создана.
Общее количество эмоциональных реакций: Общее количество обнаруженных эмоциональных реакций.
Значимые эмоции: Отформатированный список эмоций, превысивших ваш порог, с указанием их количества в скобках.

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

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

topic	topic_date	total_emotional_reactions	significant_emotions
Feature Request: Increased API Rate Limits	2025-03-06	42	Approval(15), Confusion(9), Curiosity(7), Gratitude(8)
Authentication Error with Third-Party Integration	2025-01-07	33	Curiosity(6), Gratitude(5), Disapproval(8), Frustration(9)
Best Practices for Configuration Settings	2025-02-16	31	Curiosity(9), Excitement(6), Gratitude(5), Optimism(5)
Troubleshooting Database Connection Issues	2025-01-15	29	Curiosity(7), Confusion(8), Disappointment(6), Frustration(5)
Critical Bug in Latest Beta Release	2025-02-02	26	Confusion(7), Concern(6), Disapproval(5), Urgency(6)

Практическое применение в управлении сообществом

Эти отчеты могут улучшить ваш рабочий процесс управления сообществом несколькими способами:

Раннее вмешательство: Выявление эмоционально заряженных тем, которые могут потребовать модерации до того, как они станут проблемными.
Планирование контента: Использование инсайтов о том, что вызывает позитивные эмоции, для информирования вашей контент-стратегии.
Оценка воздействия: Оценка того, как изменения политики, новые функции или события влияют на тональность сообщества.
Целевое взаимодействие: Фокусировка внимания сотрудников на темах с сильными эмоциональными реакциями, которые могут выиграть от официальных ответов.

Тема		Ответов	Просм.
Dashboard Report - Post Emotion Data & reporting dashboard-reports , sql-query , dashboard-sql , ai-sentiment , ai	0	482	16.02.2024
Dashboard Report - Overall Sentiment Data & reporting dashboard-reports , sql-query , dashboard-sql , ai , ai-sentiment	3	805	03.07.2025
Community Sentiment and Toxicity queries Data & reporting ai , sql-query	14	1182	13.11.2024
Add Sentiment score per topic Feature ai , ai-sentiment	1	174	23.06.2023
Discourse AI - Sentiment Site Management ai , content , ai-sentiment , how-to	14	5645	24.10.2025