Ceci est une version SQL du rapport de tableau de bord pour les sujets sans réponse.
Le rapport de tableau de bord est conçu pour compter le nombre de sujets créés dans une plage de dates spécifiée qui n’ont reçu aucune réponse d’autres utilisateurs. Ce rapport peut être filtré par une catégorie spécifique et peut éventuellement inclure des sous-catégories.
-- [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
Paramètres
Paramètres de date :
La requête accepte deux paramètres, :start_date et :end_date, qui définissent la plage de dates pour le rapport. Les deux paramètres de date acceptent le format de date YYYY-MM-DD.
Paramètres de catégorie :
:category_id : un paramètre entier qui peut être défini sur l’ID d’une catégorie spécifique pour affiner l’analyse aux publications au sein de cette catégorie. S’il est défini sur null ou non fourni, les sujets de toutes les catégories sont pris en compte.
:include_subcategories : un paramètre booléen qui contrôle si les publications des sous-catégories du :category_id spécifié doivent être incluses. S’il est défini sur true, le rapport inclura des liens vers les publications dans la catégorie spécifiée et ses sous-catégories ; sinon, seule la catégorie spécifiée sera prise en compte.
Explication de la requête SQL
La requête commence par une Expression de Table Commune (CTE) nommée no_response_total. Cette CTE effectue les étapes suivantes :
Sélection des sujets : elle sélectionne tous les sujets (t.id) et leurs dates de création (t.created_at) à partir de la table topics.
Jointure externe avec les publications : elle effectue une jointure externe avec la table posts pour trouver la première réponse à chaque sujet. Les conditions de jointure garantissent que la publication n’est pas de l’auteur du sujet (p.user_id != t.user_id), que la publication n’a pas été supprimée (p.deleted_at IS NULL) et que la publication est de type 1, ce qui représente généralement une réponse standard.
Filtrage des sujets : la requête filtre les sujets qui sont des messages privés (t.archetype <> 'private_message') et les sujets qui ont été supprimés (t.deleted_at ISNULL).
Filtrage par catégorie : si un :category_id est fourni, la requête filtrera les sujets pour inclure uniquement ceux de la catégorie spécifiée. Si :include_subcategories est vrai, elle inclura également les sujets des sous-catégories de la catégorie spécifiée.
Regroupement et nombre minimal de publications : les sujets sont regroupés par leur ID, et le nombre minimal de publications (MIN(p.post_number)) est calculé pour trouver la première réponse.
Filtrage pour absence de réponse : la sous-requête tt filtre les sujets qui ont une première réponse avec un numéro de publication supérieur ou égal à 2, ne laissant que les sujets sans réponse (tt.first_reply IS NULL) ou uniquement la publication originale (tt.first_reply < 2).
Une fois la CTE no_response_total définie, la requête principale effectue les opérations suivantes :
Filtrage par plage de dates : elle filtre les sujets de la CTE par les dates de début et de fin fournies (:start_date et :end_date).
Comptage des sujets sans réponse : elle compte le nombre de sujets sans réponse pour chaque date dans la plage spécifiée.
Regroupement par date : les résultats sont regroupés par la date de création du sujet (DATE(nrt.created_at)).
Tri : les résultats sont triés par date dans l’ordre croissant.
Êtes-vous capable de créer une version qui ne contient pas de paramètres ? Je souhaite créer une version qui remonte à 7 jours pour être ensuite envoyée par e-mail aux personnes et j’ai du mal à utiliser ce code car il contient des paramètres
Oui, voici une version mise à jour de la requête qui remonte à 7 jours à partir de la date actuelle sans utiliser de paramètres.
Cette version n’inclut aucun filtrage par catégories ou sous-catégories.
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 vous souhaitiez ajuster la période de recherche de la requête, il vous suffirait de modifier cette ligne dans la requête :
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND
Merci pour votre réponse, je reviendrai à cela la prochaine fois que j’en aurai besoin car l’attention s’est maintenant déplacée vers autre chose, je n’ai donc pas eu le temps d’y revenir.
J’ai vraiment du mal à transformer cela pour que ce ne soit pas basé sur la date, mais sur le mois et l’année.
J’ai fait plusieurs choses pour essayer de faire fonctionner cela, mais cela continue de me dire que la colonne n’existe pas (alors qu’elle existe car je viens de la créer dans une instruction with, et que je la référence ensuite).
Comment quelqu’un pourrait-il modifier ce code pour qu’au lieu de regarder les sujets sans réponse jour par jour, nous puissions les voir année par année, mois par mois, etc. ?
Pour modifier la requête afin qu’elle puisse agréger les sujets sans réponse par année, mois ou autres intervalles de temps, vous pourriez ajouter un paramètre pour spécifier l’intervalle désiré à la fonction date_trunc pour y parvenir.
Par exemple :
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = day -- Options : 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 vous souhaitiez supprimer les paramètres, vous pourriez alternativement utiliser une requête comme celle-ci :
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
Merci, cela a résolu la question de la façon d’obtenir l’année.
Je suis de nouveau bloqué car les dates dans PostgreSQL semblent se comporter différemment.
to_char(t.created_at, 'MM-YY') as Yearmonth,
Cela me donne 10-22, ce qui représente « Oct-22 ».
Comment puis-je changer 10-22 en Oct-22 ? J’ai essayé de consulter des guides sur Discourse mais je n’ai pas trouvé, à moins que je ne sache pas où chercher ?
Pour changer le format de date de 10-22 à Oct-22 dans PostgreSQL, vous pouvez utiliser la fonction TO_CHAR. Cette fonction vous permet de formater les dates de différentes manières, par exemple :
SELECT
TO_CHAR(TO_DATE('10-22', 'MM-YY'), 'Mon-YY') AS formatted_date
Dans cette instruction SQL :
TO_DATE('10-22', 'MM-YY') convertit la chaîne 10-22 en un type date en utilisant le format MM-YY.
TO_CHAR(..., 'Mon-YY') formate ensuite cette date pour afficher le nom du mois abrégé suivi de l’année, ce qui donne Oct-22.
Voici un autre exemple basé sur la version de la requête Topics with No Response avec le paramètre interval partagé ci-dessus :
-- [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
Pour référence, les résultats de cette requête ressembleraient à ceci :
