Отчет по панели управления: общий тон

Создание сообщества Данные и отчетность
, , , ,
1

Это SQL-версия отчёта панели управления по общей тональности.

:discourse: Для работы этого отчёта требуется плагин Discourse AI и включённая анализ тональности.

Этот отчёт панели управления сравнивает количество сообщений, классифицированных как позитивные или негативные с помощью ИИ «Анализ тональности», за указанный период.

Используя анализ тональности, отчёт классифицирует сообщения как позитивные или негативные на основе заданного порога, который можно настроить. Эта классификация затем используется для расчёта общей тональности для каждого дня в заданном диапазоне дат. Этот отчёт особенно полезен для понимания общего настроения или тональности обсуждений в сообществе в определённые периоды.

-- [params]
-- date :start_date = 2024-01-13
-- date :end_date = 2024-02-14
-- double :threshold = 0.60

SELECT
    DATE_TRUNC('day', p.created_at)::DATE AS date,
    COUNT(CASE WHEN (classification->>'positive')::float > :threshold THEN 1 ELSE NULL END) AS positive_sentiment_posts,
    COUNT(CASE WHEN (classification->>'negative')::float > :threshold THEN 1 ELSE NULL END) AS negative_sentiment_posts,
    COUNT(CASE WHEN (classification->>'positive')::float > :threshold THEN 1 ELSE NULL END) - COUNT(CASE WHEN (classification->>'negative')::float > :threshold THEN 1 ELSE NULL END) AS overall_sentiment
FROM
    classification_results AS cr
    INNER JOIN posts p ON p.id = cr.target_id AND cr.target_type = 'Post'
    INNER JOIN topics t ON t.id = p.topic_id
    INNER JOIN categories c ON c.id = t.category_id
WHERE
    t.archetype = 'regular' AND
    p.user_id > 0 AND
    cr.model_used = 'cardiffnlp/twitter-roberta-base-sentiment-latest' AND
    (p.created_at > :start_date AND p.created_at < :end_date)
GROUP BY
    DATE_TRUNC('day', p.created_at)

Объяснение SQL-запроса

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

  • Определение параметров: Сначала определяются три параметра:
    • :start_date и :end_date для указания диапазона дат анализа.
    • :threshold для установки минимального порога классификации тональности как позитивной или негативной. Значение по умолчанию для :threshold установлено на 60, чтобы соответствовать отчёту панели управления.
  • Выборка и соединение данных:
    • Запрос выбирает данные из таблицы classification_results, в которой хранятся результаты анализа тональности сообщений.
    • Он соединяет classification_results с таблицей posts, чтобы отфильтровать сообщения по дате создания и убедиться, что анализ проводится только для сообщений (cr.target_type = 'Post').
    • Дополнительные соединения с таблицами topics и categories ограничивают анализ обычными сообщениями в определённых категориях.
  • Классификация тональности:
    • Для каждого сообщения проверяется показатель тональности из таблицы classification_results. Если показатель позитивной тональности превышает порог, сообщение считается позитивным. Аналогично, сообщение считается негативным, если показатель негативной тональности превышает порог.
  • Агрегация:
    • Затем запрос группирует результаты по дням на основе временной метки created_at каждого сообщения. Для каждого дня рассчитываются:
      • Общее количество позитивных сообщений.
      • Общее количество негативных сообщений.
      • Общая тональность, которая представляет собой разницу между общим количеством позитивных и негативных сообщений.

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

date positive_sentiment_posts negative_sentiment_posts overall_sentiment
2024-01-13 10 21 -11
2024-01-14 11 20 -9
2024-01-15 23 7 16
2024-01-16 27 10 17
2024-01-17 47 22 25
3

Я попробовал запустить этот запрос, но получил 0 результатов. При этом у нас есть анализ тональности и тикеты с ИИ, которые видны в отчёте на фронтенде, но не отображаются в этом запросе.

4

Привет, Софи,

Когда вы включили анализ тональности на своём сайте?

Если в этом запросе вы выбрали февраль 2024 года в качестве конечной даты, был ли анализ включён до этого?

При использовании AI-анализа тональности классифицируются только новые сообщения, опубликованные после даты включения анализа. Поэтому, если он не был включён до дат, указанных в вашем запросе, я ожидаю, что результат будет равен нулю.

Возможно, вам потребуется скорректировать следующие параметры в запросе, чтобы они соответствовали датам, за которые вы хотите просмотреть анализ тональности на своём сайте:

-- date :start_date = 2024-01-13
-- date :end_date = 2024-02-14