커뮤니티 감정 및 독성 쿼리

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

Community Sentiment 및 Toxicity 모듈의 분류 결과 데이터베이스를 어떻게 확인하는지 궁금하신 분들을 위해, Data Explorer 플러그인과 classification_results 테이블을 사용하면 됩니다.

이 방법은 사이트에서 AI 플러그인이 어떻게 작동하며 게시물을 분류하는지 확인하는 데 유용합니다.

AI Sentiment

SELECT target_id as post_id,
model_used,
classification->'negative' as negative,
classification->'neutral' as neutral,
classification->'positive' as positive
from classification_results
WHERE model_used = 'sentiment'
order by id desc

09cb357d6c2799a50b88c9051c47f9529525bd9f_2_690x119
09cb357d6c2799a50b88c9051c47f9529525bd9f_2_690x119690×119 5.25 KB

AI Emotion:

SELECT target_id as post_id,
model_used,
classification->'neutral' as neutral,
classification->'sadness' as sadness,
classification->'surprise' as surprise,
classification->'fear' as fear,
classification->'anger' as anger,
classification->'joy' as joy,
classification->'disgust' as disgust
from classification_results
WHERE model_used = 'emotion'
order by id desc

image
image690×199 8.83 KB

AI Toxicity:

SELECT target_id as post_id,
classification->'toxicity' as toxicity,
classification->'severe_toxicity' as severe_toxicity,
classification->'obscene' as obscene,
classification->'identity_attack' as identity_attack,
classification->'insult' as insult,
classification->'threat' as threat,
classification->'sexual_explicit' as sexual_explicit
From classification_results
WHERE classification_type = 'toxicity'
order by id desc

image
image690×122 6.28 KB

2

이 쿼리에 특정 스레드의 게시글만 가져오도록 필터를 추가할 방법이 있을까요? 현재는 커뮤니티 전체의 게시글이 모두 조회되는 것 같네요. 이 점은 좋은 면도 있지만, 원하는 특정 게시글이나 스레드를 찾는 데에는 다소 번거롭고, 이 쿼리의 행 수 제한에 도달할 수도 있습니다.

3

안녕하세요 @Samantha_O :wave:

이것은 특정 주제에 대해 작동하나요? 매개변수에 주제 ID를 지정해야 합니다.

-- [params]
-- int :topic_id = 

SELECT cr.target_id as post_id,
cr.model_used,
cr.classification->'negative' as negative,
cr.classification->'neutral' as neutral,
cr.classification->'positive' as positive
FROM classification_results cr
JOIN posts p ON p.id = cr.target_id
WHERE cr.model_used = 'sentiment'
AND p.topic_id = :topic_id
ORDER BY cr.id DESC
4 
-- [params]
-- topic_id :topic_id

SELECT 
    cr.target_id as post_id,
    cr.model_used,
    cr.classification->'negative' as negative,
    cr.classification->'neutral' as neutral,
    cr.classification->'positive' as positive
FROM classification_results cr
  JOIN posts p ON p.id = cr.target_id
WHERE cr.model_used = 'sentiment'
  AND p.topic_id = :topic_id
ORDER BY p.id

아, 내가 한 박자 늦었네. :slight_smile:

하지만 답글이 허투루 쓰인 것 같지 않으려면 특정 게시물에 대한 쿼리도 하나 가져왔습니다:

-- [params]
-- topic_id :topic_id
-- int :post_number

SELECT 
    cr.target_id as post_id,
    cr.model_used,
    cr.classification->'negative' as negative,
    cr.classification->'neutral' as neutral,
    cr.classification->'positive' as positive
FROM classification_results cr
  JOIN posts p ON p.id = cr.target_id
WHERE cr.model_used = 'sentiment'
  AND p.topic_id = :topic_id
  AND p.post_number = :post_number

topic_id와 post_number는 모두 URL에서 찾을 수 있으므로 사용자 친화적입니다.

Topic_id:

https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/4

Post_number:

https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/4

5

이것은 category id를 대상으로 해야 한다고 생각합니다:

-- [params]
-- int :category_id = 

SELECT cr.target_id as post_id,
cr.model_used,
cr.classification->'negative' as negative,
cr.classification->'neutral' as neutral,
cr.classification->'positive' as positive
FROM classification_results cr
JOIN posts p ON p.id = cr.target_id
JOIN topics t ON t.id = p.topic_id
WHERE cr.model_used = 'sentiment'
AND t.category_id = :category_id
ORDER BY cr.id DESC

category_id
category_id550×122 15 KB

6

두 분 모두 감사합니다! 이 명령어를 실행해 보니 이전 토픽/토픽 ID를 찾지 못합니다. 궁금한 점이 하나 있는데, 감정 분석은 새 게시물에서만 이루어지는 건가요?

명확성을 위해 수정

7

@JammyDodger가 더 잘 알겠지만, 네, 감정 분석이 새 게시물에 작동하는 것 같습니다. 기존 게시물에 적용하려면, Rails 명령을 사용해 해당 게시물들을 감정 분석 모델로 처리하고, 새 게시물의 분석 결과가 저장되는 classification_results 테이블에 저장해야 할 가능성이 높습니다.

8

켜면 새 게시물에는 적용됩니다. :+1: 기존 게시물에 적용하는 옵션이 있는지 확실하지 않아요?

10

앞으로 새 게시물만 대상으로 하겠습니다. 해당 정보를 과거분으로 채우는 작업은 데이터 보고를 시작하고 검증한 이후에 검토할 수 있습니다.

11

여러분 중 한 분 (또는 다른 훌륭한 쿼리 전문가) 이 이 부분을 조금 더 세밀하게 다듬어 주실 수 있을까요? 날짜를 매개변수로 받아서, 해당 날짜가 게시 날짜와 대응되도록 하고 싶습니다. 궁극적인 목표는 대시보드가 나중에 이 기능을 지원할 때까지, 시간에 따른 변화를 살펴보는 것입니다 :crossed_fingers:

12

사만타님, 안녕하세요.

쿼리에 시작 날짜와 종료 날짜 매개변수를 포함하도록 개선하고 싶으시다면, 다음과 같은 쿼리를 사용할 수 있습니다:

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01

SELECT 
p.created_at,
cr.target_id as post_id,
cr.model_used,
cr.classification->'negative' as negative,
cr.classification->'neutral' as neutral,
cr.classification->'positive' as positive
FROM classification_results cr
JOIN posts p ON p.id = cr.target_id
WHERE cr.model_used = 'sentiment'
AND p.created_at BETWEEN :start_date AND :end_date
ORDER BY cr.id DESC

예시 결과:

created_at post model_used negative neutral positive
2023-11-08T21:21:23.913Z post_id sentiment 58 38 2

여기서 추가된 중요한 부분은 posts 테이블과 classification_results 테이블을 조인(join)하여 다음을 추가할 수 있게 된 것입니다:

AND p.created_at BETWEEN :start_date AND :end_date

이것은 WHERE 절에 추가하여 게시물을 created_at 날짜 기준으로 필터링하는 데 사용됩니다.

emotion과 toxicity 분류 쿼리도 유사하므로, 이 추가 사항을 통해 시작 날짜와 종료 날짜 매개변수를 포함하도록 해당 쿼리들을 수정할 수 있습니다. :slightly_smiling_face:

13
14

이것도 여기에 추가합니다:

15

이 쿼리를 사용해 보았지만(감정 설정은 이미 되어 있습니다) 결과가 아무것도 반환되지 않습니다. 문제 해결을 위해 시도해 볼 방법이 있을까요?

16

Dashboard Report - Overall Sentiment 과 Dashboard Report - Post Emotion 에 있는 가이드를 업데이트했습니다. 업데이트된 쿼리로 다시 시도해 주시겠어요?