Estadísticas sobre los reportes de spam marcados por la automatización del triaje de IA

Datos e informes
, ,
1

Esta consulta SQL proporciona estadísticas sobre las marcas de spam reportadas por la automatización de Discourse AI - AI triage. Calcula el número total de marcas, cuántas fueron aprobadas o rechazadas y los porcentajes correspondientes. Este informe es útil para comprender la eficacia de la detección automática de spam en tu foro.

-- [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 (Marcar como spam y ocultar publicación)
    AND r.created_at BETWEEN :start_date AND :end_date
    AND r.status IN (1, 2) -- Marcas aceptadas y rechazadas
    AND r.created_by_id = -1 -- ID de usuario del sistema

Explicación de la consulta SQL

  • Parámetros: Los parámetros :start_date y :end_date en formato YYYY-MM-DD con valores predeterminados.
  • Join: Conecta la tabla reviewables con la tabla post_actions para garantizar que solo se cuenten las marcas de spam que pasaron por el proceso de revisión.
  • Filtrado:
    • post_action_type_id = 8: Restringe solo a las marcas de spam.
    • Filtrado por rango de fechas con los parámetros especificados.
    • status IN (1, 2): Solo incluye las marcas que se han resuelto (aprobadas o rechazadas).
    • created_by_id = -1: Solo incluye las marcas creadas por el usuario del sistema (análisis de IA).
  • Agregaciones:
    • total_flags: El número total de marcas de spam por el sistema.
    • approved_flags: Recuento de marcas que los usuarios del personal aprobaron (estado = 1).
    • rejected_flags: Recuento de marcas que los usuarios del personal rechazaron (estado = 2).
    • approved_percentage: Porcentaje de marcas que fueron aprobadas.
    • rejected_percentage: Porcentaje de marcas que fueron rechazadas.

Ejemplo de resultados

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

Y obtuve

PG::DivisionByZero: ERROR:  división por cero
3

Ese error ocurre porque no hubo marcas de spam por parte de @system con las que estuvieras de acuerdo o en desacuerdo durante ese período de tiempo.
La consulta intenta dividir por el número total de marcas que cumplen las condiciones. En tu caso, es 0, así que se rompe.
Si quieres que la consulta siga devolviendo un resultado, puedes usar NULLIF para comprobarlo y obtener NULL cuando no haya nada que 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 y ocultar la publicación)
    AND r.created_at BETWEEN :start_date AND :end_date
    AND r.status IN (1, 2) -- Marcas aceptadas y rechazadas
    AND r.created_by_id = -1 -- ID de usuario del sistema
2 Me gusta
4

Tengo algunos, todos falsos positivos, pero no recuerdo cómo los manejé. Pero algo así también lo supuse (soy realmente malo leyendo SQL).