대시보드 보고서 - 전반적인 감정 분석

커뮤니티 구축 데이터 및 보고
, , , ,
1

이는 전체 감정(Overall Sentiment) 대시보드 보고서의 SQL 버전입니다.

:discourse: 이 보고서는 Discourse AI 플러그인과 감정 분석이 활성화되어 있어야 합니다.

이 대시보드 보고서는 지정된 기간 동안 “Sentiment” 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 타임스탬프를 기준으로 결과를 날짜별로 그룹화합니다. 각 날짜에 대해 다음을 계산합니다:
      • 긍정적 게시물의 총 수.
      • 부정적 게시물의 총 수.
      • 전체 감정(Overall Sentiment), 이는 긍정적 게시물 총수와 부정적 게시물 총수의 차이입니다.

결과 예시

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건으로 나옵니다. 감정 분석과 AI 티켓은 존재하며 프론트엔드 보고서에서도 확인이 되는데, 이 쿼리로는 결과가 나오지 않네요.

4

소피님, 안녕하세요.

사이트에 감정 분석을 언제 활성화하셨나요?

이 쿼리에서 종료 날짜로 2024년 2월을 선택하셨다면, 그 시점 전에 분석이 활성화되어 있었나요?

AI 감정의 경우, 분석이 활성화된 날짜 이후의 새 게시물만 분류되므로, 쿼리에서 선택한 날짜보다 전에 활성화되어 있지 않았다면 0개의 결과가 반환될 것으로 예상됩니다.

사이트에서 감정을 확인하려는 날짜에 맞게 쿼리의 다음 매개변수를 조정해야 할 수 있습니다:

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