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（スパムとしてフラグを立て、投稿を非表示にする）
    AND r.created_at BETWEEN :start_date AND :end_date
    AND r.status IN (1, 2) -- 承認および却下されたフラグ
    AND r.created_by_id = -1 -- システムユーザー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
「いいね！」 3
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 for spam (スパムとしてフラグを立てて投稿を非表示にする)
    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（システムユーザーID）
「いいね！」 2
4

いくつかありますが、すべて誤検出です。でも、どう処理したか思い出せません。

でも、そんな感じかなと推測しました（SQLを読むのが本当に苦手です）。