Informe del panel - Sistema

Datos e informes
, ,
1

Esta es una versión SQL del Informe del Panel para el Sistema.

Este informe del panel proporciona un recuento diario del número de mensajes personales enviados automáticamente por el sistema.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

SELECT
  DATE(created_at) AS day,
  COUNT(*) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  AND created_at BETWEEN :start_date AND :end_date
  AND deleted_at IS NULL
  AND user_id > 0
GROUP BY DATE(created_at)
ORDER BY day

Explicación de la consulta SQL

La consulta funciona extrayendo datos de la tabla topics, específicamente aquellos que califican como mensajes privados a usuarios con un subtipo de system_message dentro de un período de tiempo determinado. Desglosemos:

  • Parámetros de fecha:
    • La consulta acepta dos parámetros, :start_date y :end_date, que definen el rango de fechas para el informe. Ambos parámetros de fecha aceptan el formato de fecha YYYY-MM-DD.
  • SELECT: La consulta selecciona dos campos:
    • DATE(created_at) AS day: Esto extrae la parte de la fecha de la marca de tiempo created_at, agrupando efectivamente los registros por el día en que fueron creados.
    • COUNT(*) AS notifications_count: Esto cuenta el número total de PM generados por el sistema para cada día.
  • FROM: Especifica la tabla topics como la fuente de datos, que contiene registros de todos los temas, incluidos los mensajes privados.
  • WHERE: Contiene múltiples filtros para reducir el conjunto de datos:
    • archetype = 'private_message': Solo incluye entradas que son mensajes privados.
    • subtype = 'system_message': Reduce aún más la selección solo a los mensajes generados por el sistema.
    • created_at BETWEEN :start_date AND :end_date: Filtra los PM a aquellos creados dentro del rango especificado por los parámetros.
    • deleted_at IS NULL: Excluye los mensajes que han sido eliminados.
    • user_id > 0: Asegura que los mensajes estén asociados con cuentas de usuario reales en lugar de cuentas del sistema o anónimas.
  • GROUP BY: Agrupa los resultados según el día en que fueron creados.
  • ORDER BY: Ordena el conjunto de resultados final por el día en orden ascendente, asegurando una secuencia cronológica de los recuentos diarios.

Resultados de ejemplo

day notifications_count
2024-01-01 5
2024-01-02 7
2024-01-03 11
2024-01-04 14
2024-01-05 8
3 Me gusta
2

¿Se puede vincular esto a un PM específico, como el PM de bienvenida?

Me gustaría saber cuántos de esos salen diariamente.

¿Opiniones?

1 me gusta
3

Sí, la mejor manera de añadir esto sería añadir una sección a la declaración WHERE en la consulta, filtrando por el title del tema.

Por ejemplo:

WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  AND created_at BETWEEN :start_date AND :end_date
  AND title = 'Greetings!'

Encontraría todos los mensajes de Greetings!.

Tenga en cuenta que algunos de los mensajes del sistema no incluyen un usuario real, por lo que puede ser necesario eliminar la línea AND user_id > 0 con este tipo de consulta.

Es posible que también desee eliminar AND deleted_at IS NULL para seguir contando los mensajes de bienvenida que los usuarios puedan eliminar.

Podría usar regex para que coincidan temas con un título similar.

Para que coincida un tema por título usando una expresión regular (regex) en PostgreSQL, puede usar el operador ~, que compara una expresión regular con una cadena. La estructura de la consulta sería la siguiente:

SELECT *
FROM topics
WHERE title ~ 'YourRegexPatternHere'

Reemplace 'YourRegexPatternHere' con el patrón de regex real que desea comparar con el campo title.

Por ejemplo, si está buscando temas con títulos que contengan la palabra “Welcome” (sin distinguir mayúsculas y minúsculas), podría usar:

SELECT *
FROM topics
WHERE title ~* 'Welcome'

El operador ~* se utiliza para la comparación sin distinguir mayúsculas y minúsculas.

3 Me gusta
4

¡Oh, esto es genial! ¡Muchas gracias! ¡Lo intentaré!

1 me gusta