סטטיסטיקות על דיווחי סימון ספאם על ידי אוטומציית טריאז' AI

נתונים ודיווח
, ,
1

This SQL query provides statistics about spam flags reported by the Discourse AI - AI triage automation. It calculates the total number of flags, how many were approved or rejected, and the corresponding percentages. This report is useful for understanding how effective automated spam detection is on your forum.

-- [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 query explanation

  • Parameters: :start_date and :end_date parameters in YYYY-MM-DD format with default values.
  • Join: Connects the reviewables table with the post_actions table to ensure only spam flags that went through the review process are counted.
  • Filtering:
    • post_action_type_id = 8: Restricts to spam flags only.
    • Date range filtering with the specified parameters.
    • status IN (1, 2): Only includes flags that have been resolved (approved or rejected).
    • created_by_id = -1: Only includes flags created by the system user (AI triage).
  • Aggregations:
    • total_flags: The total number of spam flags by the system.
    • approved_flags: Count of flags that staff users approved (status = 1).
    • rejected_flags: Count of flags that staff users rejected (status = 2).
    • approved_percentage: Percentage of flags that were approved.
    • rejected_percentage: Percentage of flags that were rejected.

Example results

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 -- מזהה עבור ספאם (סמן כספאם והסתר פוסט)
    AND r.created_at BETWEEN :start_date AND :end_date
    AND r.status IN (1, 2) -- דגלים שאושרו ונדחו
    AND r.created_by_id = -1 -- מזהה משתמש מערכת
2 לייקים
4

יש לי כמה, כולם אזעקות שווא, אבל אני לא זוכר איך טיפלתי בהם.

אבל משהו כזה גם אני ניחשתי (אני ממש גרוע בקריאת SQL).