Informe del panel - Publicaciones

Datos e informes
, ,
1

Este es una versión SQL del Informe del Panel para Publicaciones.

Este informe proporciona un recuento diario de las publicaciones creadas dentro de un rango de fechas especificado. Está diseñado para rastrear la actividad en temas regulares, excluyendo las publicaciones de mensajes privados y otros arquetipos especiales.

--[params]
-- date :start_date
-- date :end_date

SELECT 
    p.created_at::date AS "Día",
    COUNT(p.id) AS "Recuento"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at ISNULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
GROUP BY p.created_at::date
ORDER BY 1

Explicación de la Consulta SQL

  • Parámetros:
    • 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.

La consulta SQL realiza las siguientes operaciones:

  • Selección de Datos:
    • Selecciona la fecha (created_at::date) en la que se creó cada publicación y la convierte al formato de fecha para ignorar el componente de hora.
    • También cuenta el número de publicaciones (COUNT(p.id)) creadas en cada fecha.
  • Uniones:
    • La consulta une la tabla posts con la tabla topics mediante un INNER JOIN. Esta unión asegura que solo se consideren las publicaciones asociadas con temas existentes.
    • Filtra los temas que han sido eliminados (t.deleted_at ISNULL).
  • Filtros:
    • Filtra las publicaciones para incluir solo aquellas dentro del rango de fechas especificado (p.created_at::date BETWEEN :start_date AND :end_date).
    • Excluye las publicaciones eliminadas (p.deleted_at ISNULL).
    • Restringe los resultados a las publicaciones de temas regulares (t.archetype = 'regular').
    • Solo considera las publicaciones p.post_type = 1, excluyendo acciones de moderador, susurros y publicaciones de acciones pequeñas.
  • Agrupación y Ordenación:
    • Los resultados se agrupan por la fecha de creación de la publicación (GROUP BY p.created_at::date).
    • La salida final se ordena por fecha en orden ascendente (ORDER BY 1), donde 1 se refiere a la primera columna en la declaración SELECT, que es la fecha.

Resultados de Ejemplo

Día Recuento
2023-11-12 25
2023-11-13 35
2023-11-14 38
2023-11-15 47
2023-11-16 36
2023-11-17 79
… …
2 Me gusta
2

Esto es genial.
¿Habría una forma de poder definir la categoría/subcategoría en tiempo de ejecución como la fecha?
Y como extra, ¿podemos listar los resultados por usuario o definir también el usuario?

Lo que estoy intentando hacer es ver cuántas publicaciones se realizan durante (rango) en las áreas de mis tickets de soporte por parte de mi personal de soporte.

3

Sí, puedes usar la siguiente consulta para esto:

--[params]
-- date :start_date
-- date :end_date
-- null category_id :category_id
-- null user_id :user_id
-- boolean :include_subcategories = false

SELECT
    u.username AS "Usuario",
    p.created_at::date AS "Fecha",
    COUNT(p.id) AS "Cantidad"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at IS NULL
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON t.category_id = c.id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at IS NULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
    AND (
        :category_id IS NULL
        OR t.category_id = :category_id
        OR (:include_subcategories AND c.parent_category_id = :category_id)
    )
    AND (:user_id IS NULL OR p.user_id = :user_id)
GROUP BY u.username, p.created_at::date
ORDER BY p.created_at::date ASC, u.username

Parámetros:

  • :start_date y :end_date: Define el período de tiempo del informe (requerido)
  • :category_id: Filtro opcional para una categoría específica
  • :user_id: Filtro opcional para un usuario específico
  • :include_subcategories: Opción para incluir subcategorías de la categoría elegida

Esta consulta muestra:

  • Usuario: Nombre de usuario del autor de la publicación
  • Fecha: La fecha del calendario en que se crearon las publicaciones
  • Cantidad: Número de publicaciones creadas por ese usuario en esa fecha

Datos de ejemplo:

Usuario Fecha Cantidad
usuario 1 2023-01-01 3
usuario 2 2023-01-01 2
usuario 3 2023-01-01 1
usuario 1 2023-01-02 2
usuario 2 2023-01-02 3
usuario 1 2023-01-03 1
… … …
2 Me gusta
4

¡Gracias, eso es de gran ayuda!

2 Me gusta