Статистика по флагам спама, выявленным автоматизацией первичной обработки ИИ

Создание сообщества Данные и отчетность
, ,
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-запроса

  • Параметры: Параметры :start_date и :end_date в формате ГГГГ-ММ-ДД со значениями по умолчанию.
  • Соединение (JOIN): Связывает таблицу reviewables с таблицей post_actions, чтобы учитывать только флаги спама, прошедшие процесс проверки.
  • Фильтрация:
    • post_action_type_id = 8: Ограничивает выборку только флагами спама.
    • Фильтрация по диапазону дат с использованием указанных параметров.
    • status IN (1, 2): Включает только флаги, которые были разрешены (одобрены или отклонены).
    • created_by_id = -1: Включает только флаги, созданные системным пользователем (AI triage).
  • Агрегация:
    • 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: ОШИБКА: деление на ноль
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

У меня есть несколько, все ложные срабатывания, но я не помню, как с ними справлялся.

Но что-то подобное я тоже предположил (я очень плохо читаю SQL).