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

**URL:** https://meta.discourse.org/t/dashboard-report-post-emotion/295553
**Category:** Data & reporting
**Tags:** ai, sql-query, dashboard-reports, ai-sentiment, dashboard-sql
**Created:** [2월 16, 2024, 1:04오전 UTC](https://meta.discourse.org/t/dashboard-report-post-emotion/295553 "2024-02-16T01:04:04Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [2월 16, 2024, 1:04오전 UTC](https://meta.discourse.org/t/dashboard-report-post-emotion/295553/1 "2024-02-16T01:04:05Z")

</div>

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

> :discourse: 이 보고서는 [Discourse AI](https://meta.discourse.org/t/discourse-ai/259214) 플러그인과 [감정 분석](https://meta.discourse.org/t/enable-sentiment-analysis/259599)이 활성화되어 있어야 합니다.

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

- 슬픔 (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)

```sql
-- [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 |