AI 감정 및 정서 분석 보고서

Discourse AI 플러그인은 커뮤니티 전반의 토론 감정 톤에 대한 더 깊은 통찰력을 얻을 수 있도록 돕는 감정 분석 기능을 포함하고 있습니다. 이 주제는 이러한 AI 기능을 활용하여 Discourse 커뮤니티에 대한 통찰력을 제공하는 두 가지 상세한 데이터 탐색기(Data Explorer) 쿼리를 다룹니다.

  1. 카테고리별 및 신뢰 수준별 AI 감정 합계: 특정 카테고리 및 신뢰 수준 내에서 주간별 감정 추세를 추적하는 시계열 분석
  2. AI 감정 이상치 주제: Discourse 사이트에서 상당한 감정적 반응을 유발하는 토론 주제를 식별합니다.

사전 요구 사항

이 리포트를 사용하려면 다음이 필요합니다:

  1. Discourse AI 플러그인 설치 및 활성화: Discourse AI 플러그인이 인스턴스에 설치되어 있어야 합니다.
  2. 감정 분석 활성화: Sentiment Analysis 모듈이 구성되고 활성화되어 있어야 합니다.
  3. Data Explorer 플러그인: 이러한 SQL 쿼리를 실행하는 데 필요합니다.
  4. 역사적 감정 데이터: 의미 있는 결과를 얻기 위해 충분한 수의 감정 분석이 완료된 게시글이 필요합니다(백필(backfill) 작업이 필요할 수 있음).

AI 감정 모델 및 작동 방식

리포트로 넘어가기 전에, 커뮤니티 게시글에서 감정 모델이 분석하는 내용을 이해하는 것이 도움이 됩니다:

이러한 모델은 각 게시글의 텍스트를 분석하고 그 분류 결과를 데이터베이스에 저장하며, 이후 Data Explorer 플러그인을 통해 이를 조회할 수 있습니다.

카테고리별 및 신뢰 수준별 AI 감정 합계 리포트

-- [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'
    -- 일반 주제만 포함 (PM 등 제외)
    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 주 표기법(YYYY-WXX)으로 형식화
  year || '-W' || LPAD(week_number::text, 2, '0') as year_week,
  -- 순 감정(긍정에서 부정을 뺀 값) 계산
  positive_count - negative_count as sentiment_balance,
  positive_count,
  negative_count,
  neutral_count,
  -- 긍정 게시글 백분율 계산 (소수점 둘째 자리까지 반올림)
  ROUND(
    (positive_count::float / NULLIF(total_classifications, 0) * 100)::numeric,
    2
  ) as positive_percentage
FROM sentiment_counts
-- 시간 경과에 따른 감정 추세를 보여주기 위해 시간순 정렬
ORDER BY week_starting ASC

이 리포트는 특정 카테고리 내에서 주별 감정 추세 분석을 제공하며, 다음을 보여줍니다:

  • 각 주별 긍정, 부정, 중립 게시글 수
  • 감정 균형 계산(긍정 게시글에서 부정 게시글을 뺀 값)
  • 분석된 총 게시글 대비 긍정 게시글의 백분율
  • 사용자 신뢰 수준별 필터링 및 스태프 게시글 제외 옵션

이 리포트는 다음과 같은 점에서 가치가 있습니다:

  • 특정 카테고리에서 시간 경과에 따른 커뮤니티 감정 추세 추적
  • 특정 이벤트나 변화와 상관관계가 있을 수 있는 커뮤니티 분위기 변동 식별
  • 다른 사용자 세그먼트(신뢰 수준별) 간 감정 비교
  • 전반적인 커뮤니티 감정에 대한 조정(moderation) 개입의 영향 측정

매개변수

쿼리는 분석을 사용자 지정하기 위해 여러 매개변수를 허용합니다:

  • 날짜 범위: 분석 기간의 시작 및 종료 날짜 설정
  • 카테고리: 분석할 카테고리 선택
  • 최소 신뢰 수준: 특정 신뢰 수준 이상 사용자의 게시글만 포함하도록 필터링
  • 스태프 제외: 분석에서 스태프 게시글을 제거하는 옵션(일반 커뮤니티 멤버에 초점을 맞추기 위함)

결과

결과는 각 행이 주별 데이터를 나타내는 테이블로 제시됩니다:

  • 카테고리 정보: 분석된 카테고리의 ID 및 이름
  • 시간 기간: 주 시작 날짜 및 ISO 주 표기법(YYYY-WXX)
  • 감정 지표:
    • 감정 균형: 긍정 및 부정 게시글 간의 차이(양수 값은 전반적인 긍정적 감정을 나타냄)
    • 긍정/부정/중립 개수: 각 감정 카테고리의 게시글 수
    • 긍정 백분율: 긍정으로 분류된 게시글의 백분율

결과 예시

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 감정 이상치 주제 리포트

-- [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       -- 표준 주제만 포함 (PM 또는 시스템 메시지 제외)
    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

이 리포트는 커뮤니티에서 상당한 감정적 반응을 유발한 주제를 식별하며, 이는 다음을 기반으로 합니다:

  • 주제 내 게시글에서 감지된 각 유형별 감정 개수
  • “상당한” 감정적 반응을 결정하는 데 사용되는 사용자 지정 가능한 임계값
  • 카테고리, 날짜 범위, 사용자 신뢰 수준별 필터링

이 리포트는 다음과 같은 데 도움이 됩니다:

  • 강한 부정적 감정을 유발하는 잠재적으로 문제적인 토론 식별
  • 커뮤니티와 감정적으로 공감되는 고도로 참여적인 콘텐츠 발견
  • 격화되기 전에 조정(moderation)의 주의를 필요로 할 수 있는 주제 감지
  • 특정 감정적 반응을 유발하는 콘텐츠 테마 발견
  • 커뮤니티의 감정적 참여를 주도하는 요소를 더 잘 이해

매개변수

쿼리는 다음 매개변수를 허용합니다:

  • 날짜 범위: 분석 기간의 시작 및 종료 날짜 설정
  • 카테고리: 분석할 카테고리 선택
  • 최소 신뢰 수준: 특정 신뢰 수준 이상 사용자의 게시글만 포함하도록 필터링
  • 감정 임계값: 감정을 상당하다고 간주하는 데 필요한 감정 발생 횟수 설정

결과

결과는 다음을 보여줍니다:

  • 주제 ID: 주제로 직접 연결되는 링크(Data Explorer에서 클릭 가능)
  • 주제 날짜: 주제가 생성된 시점
  • 총 감정 반응: 감지된 감정 반응의 전체 개수
  • 상당한 감정: 임계값을 초과한 감정의 형식화된 목록, 괄호 안에 개수 표시

감지되는 감정은 광범위한 범위를 포함합니다: 경외(admiration), 재미(amusement), 분노(anger), 짜증(annoyance), 승인(approval), 돌봄(caring), 혼란(confusion), 호기심(curiosity), 욕구(desire), 실망(disappointment), 불승인(disapproval), 혐오(disgust), 수치심(embarrassment), 흥분(excitement), 두려움(fear), 감사(gratitude), 슬픔(grief), 기쁨(joy), 사랑(love), 긴장(nervousness), 중립(neutral), 낙관(optimism), 자부심(pride), 깨달음(realization), 안도(relief), 후회(remorse), 슬픔(sadness), 놀람(surprise).

결과 예시

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)

커뮤니티 관리에서의 실용적 적용

이러한 리포트는 커뮤니티 관리 워크플로를 다음과 같은 여러 방식으로 향상시킬 수 있습니다:

  • 조기 개입: 문제가 되기 전에 조정이 필요한 감정적으로 격앙된 주제 식별
  • 콘텐츠 계획: 긍정적 감정을 유발하는 요인에 대한 통찰력을 사용하여 콘텐츠 전략에 반영
  • 영향 측정: 정책 변경, 새로운 기능 또는 이벤트가 커뮤니티 감정에 미치는 영향을 평가
  • 목표 지향적 참여: 공식적인 응답이 도움이 될 수 있는 강한 감정적 반응을 가진 주제에 스태프의 주의를 집중

추가 리소스

1개의 좋아요