AI 트리아지 자동화에서 보고된 스팸 플래그 통계

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

이 SQL 쿼리는 Discourse AI - AI triage 자동화 기능에 의해 보고된 스팸 플래그에 대한 통계를 제공합니다. 총 플래그 수, 승인 또는 거부된 플래그 수, 그리고 이에 해당하는 백분율을 계산합니다. 이 보고서는 포럼에서 자동 스팸 탐지가 얼마나 효과적인지 이해하는 데 유용합니다.

-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2025-05-30

SELECT 
    COUNT(*) AS total_flags,
    COUNT(*) FILTER (WHERE r.status = 1) AS approved_flags,
    COUNT(*) FILTER (WHERE r.status = 2) AS rejected_flags,
    ROUND(100.0 * COUNT(*) FILTER (WHERE r.status = 1) / COUNT(*), 2) AS approved_percentage,
    ROUND(100.0 * COUNT(*) FILTER (WHERE r.status = 2) / COUNT(*), 2) AS rejected_percentage
FROM 
    reviewables r
JOIN 
    post_actions pa ON pa.post_id = r.target_id AND r.target_type = 'Post'
WHERE
    pa.post_action_type_id = 8 -- ID for spam (Flag as spam and hide post)
    AND r.created_at BETWEEN :start_date AND :end_date
    AND r.status IN (1, 2) -- Accepted and rejected flags
    AND r.created_by_id = -1 -- System user ID

SQL 쿼리 설명

  • 매개변수: 기본값이 포함된 YYYY-MM-DD 형식의 :start_date 및 :end_date 매개변수.
  • 조인: reviewables 테이블을 post_actions 테이블과 연결하여 검토 과정을 거친 스팸 플래그만 집계되도록 합니다.
  • 필터링:
    • post_action_type_id = 8: 스팸 플래그로만 제한합니다.
    • 지정된 매개변수를 사용하여 날짜 범위 필터링을 수행합니다.
    • status IN (1, 2): 해결된(승인 또는 거부된) 플래그만 포함합니다.
    • created_by_id = -1: 시스템 사용자(AI 트리아지)에 의해 생성된 플래그만 포함합니다.
  • 집계:
    • total_flags: 시스템에 의해 생성된 스팸 플래그의 총 수.
    • approved_flags: 스태프 사용자가 승인한 플래그 수(status = 1).
    • rejected_flags: 스태프 사용자가 거부한 플래그 수(status = 2).
    • approved_percentage: 승인된 플래그의 백분율.
    • rejected_percentage: 거부된 플래그의 백분율.

결과 예시

total_flags approved_flags rejected_flags approved_percentage rejected_percentage
152 128 24 84.21 15.79
2

그리고 나는

PG::DivisionByZero: ERROR:  division by zero

를 받았습니다.

3

해당 시간 동안 @system이 생성한 스팸 플래그에 대해 동의하거나 반대표를 던진 기록이 없어서 이러한 오류가 발생합니다.
쿼리는 조건을 충족하는 플래그의 총 수로 나누려고 시도합니다. 이 경우 그 값이 0이므로 오류가 발생합니다.
쿼리가 결과값을 반환하도록 하려면 NULLIF를 사용하여 해당 조건을 확인하면 나누기 대상이 없을 때 NULL을 반환합니다.

-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2025-05-30

SELECT 
    COUNT(*) AS total_flags,
    COUNT(*) FILTER (WHERE r.status = 1) AS approved_flags,
    COUNT(*) FILTER (WHERE r.status = 2) AS rejected_flags,
    ROUND(100.0 * COUNT(*) FILTER (WHERE r.status = 1) / NULLIF(COUNT(*), 0), 2) AS approved_percentage,
    ROUND(100.0 * COUNT(*) FILTER (WHERE r.status = 2) / NULLIF(COUNT(*), 0), 2) AS rejected_percentage
FROM 
    reviewables r
JOIN 
    post_actions pa ON pa.post_id = r.target_id AND r.target_type = 'Post'
WHERE
    pa.post_action_type_id = 8 -- 스팸 ID (스팸으로 표시 및 게시물 숨김)
    AND r.created_at BETWEEN :start_date AND :end_date
    AND r.status IN (1, 2) -- 승인 및 거부 플래그
    AND r.created_by_id = -1 -- 시스템 사용자 ID
4

몇 개가 있는데 전부 오탐(false positive)이었지만, 그때 어떻게 처리했는지 기억이 나지 않습니다.

하지만 그런 식으로 추측해 본 적도 있습니다. (저는 SQL을 읽는 데 정말 서투릅니다.)