Esta es una versión SQL del informe del panel para temas sin respuesta.
El informe del panel está diseñado para contar el número de temas creados dentro de un rango de fechas especificado que no han recibido ninguna respuesta de otros usuarios. Este informe se puede filtrar por una categoría específica y opcionalmente puede incluir subcategorías.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
-- null int :category_id
-- boolean :include_subcategories = false
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
DATE(nrt.created_at) AS date,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY date
ORDER BY date ASC
Parámetros
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.
Parámetros de categoría:
:category_id: Un parámetro entero que se puede establecer en el ID de una categoría específica para reducir el análisis a las publicaciones dentro de esa categoría. Si se establece en nulo o no se proporciona, se consideran temas de todas las categorías.
:include_subcategories: Un parámetro booleano que controla si se incluyen las publicaciones de las subcategorías del :category_id especificado. Si se establece en true, el informe incluirá enlaces a publicaciones tanto en la categoría especificada como en sus subcategorías; si es false, solo se considerará la categoría especificada.
Explicación de la consulta SQL
La consulta comienza con una Expresión Común de Tabla (CTE) llamada no_response_total. Esta CTE realiza los siguientes pasos:
Selección de temas: Selecciona todos los temas (t.id) y sus fechas de creación (t.created_at) de la tabla topics.
Unión izquierda con publicaciones: Realiza una unión izquierda con la tabla posts para encontrar la primera respuesta a cada tema. Las condiciones de unión garantizan que la publicación no sea del creador del tema (p.user_id != t.user_id), que la publicación no haya sido eliminada (p.deleted_at IS NULL) y que la publicación sea de tipo 1, que normalmente representa una respuesta estándar.
Filtrado de temas: La consulta filtra los temas que son mensajes privados (t.archetype <> 'private_message') y los temas que han sido eliminados (t.deleted_at ISNULL).
Filtrado de categorías: Si se proporciona un :category_id, la consulta filtrará los temas para incluir solo aquellos en la categoría especificada. Si :include_subcategories es true, también incluirá temas de las subcategorías de la categoría especificada.
Agrupación y número mínimo de publicación: Los temas se agrupan por su ID y se calcula el número mínimo de publicación (MIN(p.post_number)) para encontrar la primera respuesta.
Filtrado para no respuesta: La subconsulta tt filtra los temas que tienen una primera respuesta con un número de publicación mayor o igual a 2, dejando solo los temas sin respuesta (tt.first_reply IS NULL) o solo la publicación original (tt.first_reply < 2).
Después de definir la CTE no_response_total, la consulta principal hace lo siguiente:
Filtrar por rango de fechas: Filtra los temas de la CTE por las fechas de inicio y fin proporcionadas (:start_date y :end_date).
Contar temas sin respuesta: Cuenta el número de temas sin respuesta para cada fecha dentro del rango especificado.
Agrupar por fecha: Los resultados se agrupan por la fecha de creación del tema (DATE(nrt.created_at)).
Ordenar: Los resultados se ordenan por fecha en orden ascendente.
¿Puedes hacer una versión que no contenga parámetros? Quiero crear una versión que mire hacia atrás 7 días para luego enviarla por correo electrónico a las personas y me está costando usar este código ya que tiene parámetros establecidos en él.
Sí, aquí tienes una versión actualizada de la consulta que retrocede 7 días desde la fecha actual sin usar parámetros.
Esta versión no incluye ningún filtrado por categorías o subcategorías.
WITH no_response_total AS (
SELECT *
FROM (
SELECT
t.id,
t.created_at,
MIN(p.post_number) AS first_reply
FROM
topics t
LEFT JOIN
posts p
ON
p.topic_id = t.id
AND p.user_id != t.user_id
AND p.deleted_at IS NULL
AND p.post_type = 1
WHERE
t.archetype <> 'private_message'
AND t.deleted_at IS NULL
AND (
t.category_id = :category_id
OR t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
GROUP BY
t.id
) tt
WHERE
tt.first_reply IS NULL
OR tt.first_reply < 2
)
SELECT
DATE(nrt.created_at) AS date,
COUNT(nrt.id) AS topics_without_response
FROM
no_response_total nrt
WHERE
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND CURRENT_DATE
GROUP BY
date
ORDER BY
date ASC
Si quisieras ajustar cuánto tiempo atrás mira la consulta, solo necesitarías cambiar esta línea en la consulta:
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND
Gracias por la respuesta, volveré a esto la próxima vez que lo necesite, ya que el enfoque ahora se ha desplazado a otra cosa, por lo que no he tenido tiempo de volver a esto.
Tengo muchos problemas para poder convertir esto de que no dependa de la fecha, sino del mes y el año.
He hecho varias cosas para intentar que funcione, pero me sigue diciendo que la columna no existe (cuando sí existe, ya que la acabo de crear dentro de una sentencia with y luego la estoy referenciando).
¿Cómo podría alguien modificar este código para que en lugar de ver los temas sin respuesta día a día, podamos verlo año a año, mes a mes, etc.?
Para modificar la consulta de modo que pueda agregar temas sin respuestas por año, mes u otros intervalos de tiempo, podría agregar un parámetro para especificar el intervalo deseado en la función date_trunc para lograr esto.
Por ejemplo:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = day -- Opciones: day, week, month, year
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
date_trunc(:interval, nrt.created_at)::date AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period
ORDER BY period ASC
Si quisiera eliminar los parámetros, alternativamente podría usar una consulta como:
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
date_trunc('year', nrt.created_at)::date AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN '2024-01-01' AND '2025-01-01'
GROUP BY period
ORDER BY period ASC
Para cambiar el formato de fecha de 10-22 a Oct-22 en PostgreSQL, puedes usar la función TO_CHAR. Esta función te permite formatear fechas de diversas maneras, por ejemplo:
SELECT
TO_CHAR(TO_DATE('10-22', 'MM-YY'), 'Mon-YY') AS formatted_date
En esta sentencia SQL:
TO_DATE('10-22', 'MM-YY') convierte la cadena 10-22 en un tipo de dato de fecha usando el formato MM-YY.
TO_CHAR(..., 'Mon-YY') luego formatea esta fecha para mostrar el nombre abreviado del mes seguido del año, resultando en Oct-22.
Aquí tienes otro ejemplo basado en la versión de la consulta Topics with No Response con el parámetro interval compartido anteriormente:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = month
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
TO_CHAR(date_trunc(:interval, nrt.created_at)::date, 'Mon-YY') AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period, date_trunc(:interval, nrt.created_at)::date
ORDER BY date_trunc(:interval, nrt.created_at)::date ASC
Como referencia, los resultados de esta consulta se verían así:
