대시보드 보고서 - 게시글 감정

SaraDev · 2월 16, 2024, 1:04오전

이것은 Post Emotion 대시보드 보고서의 SQL 버전입니다.

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

이 대시보드 보고서는 지정된 날짜 범위 내에서 게시자의 신뢰 수준별로 그룹화되어 다음 감정 중 하나로 분류된 게시물의 수를 보여줍니다:

슬픔 (Sadness)
놀라움 (Surprise)
공포 (Fear)
분노 (Anger)
기쁨 (Joy)
혐오 (Disgust)
경외 (Admiration)
유머 (Amusement)
짜증 (Annoyance)
동의 (Approval)
배려 (Caring)
혼란 (Confusion)
호기심 (Curiosity)
욕구 (Desire)
실망 (Disappointment)
불만 (Disapproval)
부끄러움 (Embarrassment)
흥분 (Excitement)
감사 (Gratitude)
슬픔/애통 (Grief)
사랑 (Love)
긴장 (Nervousness)
중립 (Neutral)
낙관 (Optimism)
자부심 (Pride)
깨달음 (Realization)
안도 (Relief)
후회 (Remorse)

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

SELECT
    u.trust_level AS trust_level,
    -- Basic emotions from original query
    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,
    
    -- Additional emotions from the second query
    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,
    
    -- Total count of posts with any emotion above threshold
    COUNT(*) AS total_posts,
    
    -- Total count of posts with at least one emotion above threshold
    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 = 'emotion')를 대상으로 한다는 것을 보장합니다.
분류 카운팅:
- 각 감정(슬픔, 놀라움, 공포, 분노, 기쁨, 혐오)에 대해, 쿼리는 지정된 임계값(: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 ai , sql-query , ai-sentiment	0	302	7월 15, 2025
Dashboard Report - Overall Sentiment Data & reporting ai , sql-query , dashboard-reports , ai-sentiment , dashboard-sql	2	945	7월 3, 2025
Community Sentiment and Toxicity queries Data & reporting ai , sql-query	14	1487	11월 13, 2024
Add Sentiment score per topic Feature ai , ai-sentiment	1	233	6월 23, 2023
Discourse AI - Sentiment Site Management how-to , ai , ai-sentiment , content	12	6273	10월 24, 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 쿼리 설명

결과 예시

Related topics

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