Estatísticas sobre sinalizações de spam reportadas pela automação de triagem de IA

Dados e relatórios
, ,
1

Esta consulta SQL fornece estatísticas sobre sinalizações de spam relatadas pela automação do Discourse AI - AI triage. Ela calcula o número total de sinalizações, quantas foram aprovadas ou rejeitadas e as porcentagens correspondentes. Este relatório é útil para entender a eficácia da detecção automatizada de spam em seu fórum.

-- [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 para spam (Sinalizar como spam e ocultar post)
    AND r.created_at BETWEEN :start_date AND :end_date
    AND r.status IN (1, 2) -- Sinalizações aceitas e rejeitadas
    AND r.created_by_id = -1 -- ID do usuário do sistema

Explicação da consulta SQL

  • Parâmetros: Parâmetros :start_date e :end_date no formato YYYY-MM-DD com valores padrão.
  • Join: Conecta a tabela reviewables com a tabela post_actions para garantir que apenas sinalizações de spam que passaram pelo processo de revisão sejam contadas.
  • Filtragem:
    • post_action_type_id = 8: Restringe apenas a sinalizações de spam.
    • Filtragem por intervalo de datas com os parâmetros especificados.
    • status IN (1, 2): Inclui apenas sinalizações que foram resolvidas (aprovadas ou rejeitadas).
    • created_by_id = -1: Inclui apenas sinalizações criadas pelo usuário do sistema (AI triage).
  • Agregações:
    • total_flags: O número total de sinalizações de spam pelo sistema.
    • approved_flags: Contagem de sinalizações que os usuários da equipe aprovaram (status = 1).
    • rejected_flags: Contagem de sinalizações que os usuários da equipe rejeitaram (status = 2).
    • approved_percentage: Porcentagem de sinalizações que foram aprovadas.
    • rejected_percentage: Porcentagem de sinalizações que foram rejeitadas.

Exemplo de resultados

total_flags approved_flags rejected_flags approved_percentage rejected_percentage
152 128 24 84.21 15.79
3 curtidas
2

E eu recebi

PG::DivisionByZero: ERROR:  division by zero
3

Esse erro acontece porque não houve marcações de spam pelo @system com as quais você concordou ou discordou durante esse período.
A consulta tenta dividir pelo número total de marcações que atendem às condições. No seu caso, é 0, então quebra.
Se você quer que a consulta ainda retorne um resultado, você pode usar NULLIF para verificar isso para que você obtenha NULL quando não houver nada para dividir.

-- [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 para spam (Marcar como spam e ocultar postagem)
    AND r.created_at BETWEEN :start_date AND :end_date
    AND r.status IN (1, 2) -- Marcações aceitas e rejeitadas
    AND r.created_by_id = -1 -- ID do usuário do sistema
2 curtidas
4

Eu tenho alguns, todos falsos positivos, mas não consigo lembrar como lidei com eles.

Mas algo assim eu também imaginei (sou muito ruim em ler SQL).