Similaire au temps de première réponse, je cherche un moyen de déterminer le temps moyen (sur n’importe quelle période) entre les deux dernières réponses dans les sujets d’une liste de catégories sélectionnées.
Le cas d’utilisation est de déterminer si un réglage de fermeture automatique est approprié.
J’ai installé le plugin Data Explorer, mais je n’en sais pas assez sur le schéma de la base de données (ou, d’ailleurs, je ne me souviens pas de grand-chose de mon cours de base de données/SQL au début des années 90) pour créer le rapport moi-même.
J’apprécierais des indications et des suggestions sur la manière d’extraire ces données.
ETA : Il semble que l’approche consiste à faire une jointure entre les sujets et les messages (pour pouvoir filtrer par catégorie), puis à trouver les messages avec les deux horodatages de mise à jour les plus récents et à les différencier.
Puisque le but de l’exercice n’est pas de savoir si le dernier est une réponse à l’avant-dernier, mais de déterminer la différence de temps maximale entre les deux réponses les plus récentes (quelle que soit leur relation spécifique) afin de voir si le sujet était assez ancien pour être fermé, puis de calculer la moyenne sur toutes les catégories (ou par catégorie, ce qui serait probablement plus utile),
En y réfléchissant un peu plus, il semble que cela pourrait être juste une variation de la requête utilisée dans le processus rake topics:auto_close (la différence étant que celle-ci utilise l’heure actuelle plutôt que de différencier les deux derniers messages du sujet).
Pour ceux qui sont intéressés, voici ce qui semble fonctionner :
-- [params]
-- null date :start_date
-- null date :end_date
-- null int_list :category_ids
WITH RankedPosts AS (
SELECT
p.topic_id,
p.created_at,
ROW_NUMBER() OVER (PARTITION BY p.topic_id ORDER BY p.post_number DESC) AS rank
FROM
posts p
WHERE
p.created_at BETWEEN :start_date AND :end_date
AND EXISTS (
SELECT 1
FROM topics t
WHERE t.id = p.topic_id
AND t.category_id IN (:category_ids)
)
),
FilteredPosts AS (
SELECT
topic_id,
created_at,
rank
FROM
RankedPosts
WHERE
rank <= 2
),
PostDifferences AS (
SELECT
topic_id,
EXTRACT(days FROM (MAX(created_at) FILTER (WHERE rank = 1) - MAX(created_at) FILTER (WHERE rank = 2)))::numeric(9,2) AS days_difference
FROM
FilteredPosts
GROUP BY
topic_id
)
SELECT
t.category_id,
AVG(pd.days_difference) AS avg_days_difference,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pd.days_difference) AS median_days_difference
FROM
topics t
JOIN
PostDifferences pd ON t.id = pd.topic_id
GROUP BY
t.category_id
ORDER BY
avg_days_difference DESC
Cela devrait donner la moyenne et la médiane des temps entre les horodatages de création. Les horodatages de mise à jour semblaient problématiques (j’ai obtenu des valeurs négatives pour une raison quelconque en faisant simplement des moyennes).
J’ai eu une aide de l’IA pour créer la requête, il est donc possible qu’il y ait des erreurs - et je suis heureux si quelqu’un a des suggestions sur la façon de l’améliorer (ou de corriger des erreurs). Les résultats que j’ai obtenus semblaient raisonnables sur la base de vérifications effectuées sur des groupes plus petits, ainsi que des changements résultant de l’augmentation de la plage de dates entraînant des tendances qui correspondaient aux attentes que j’avais de l’utilisation d’un échantillon de données plus large.