Rapport du tableau de bord - Temps de première réponse

Données et rapports
, ,
1

Ceci est une version SQL du rapport de tableau de bord pour le délai de première réponse.

Ce rapport de tableau de bord fournit des informations sur le temps de réponse moyen aux sujets dans une période de dates spécifiée. Le rapport calcule le temps nécessaire pour que la première réponse soit publiée par une personne autre que le créateur du sujet.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-12-31
-- null category_id :category_id
-- boolean :include_subcategories = false
-- null user_id :user_ids 

-- Temps de première réponse par date
SELECT 
  t.created_at AS "date",
  CAST(AVG(t.hours)::numeric(10,2) AS float) AS "response_time_hours"
FROM (
  SELECT 
    t.id, 
    t.created_at::date AS created_at, 
    EXTRACT(EPOCH FROM MIN(p.created_at) - t.created_at)::float / 3600.0 AS "hours"
  FROM topics t
  JOIN posts p ON p.topic_id = t.id
  WHERE 
    t.created_at >= :start_date
    AND t.created_at < :end_date
    AND t.archetype <> 'private_message'
    AND t.deleted_at IS NULL
    AND p.deleted_at IS NULL
    AND p.post_number > 1
    AND p.user_id != t.user_id
    AND p.post_type = 1  -- Type de publication régulier
    AND EXTRACT(EPOCH FROM p.created_at - t.created_at) > 0
    AND (:category_id IS NULL OR 
         CASE WHEN :include_subcategories THEN 
           t.category_id IN (
             WITH RECURSIVE subcategories AS (
               SELECT id FROM categories WHERE id = :category_id
               UNION
               SELECT c.id FROM categories c
               JOIN subcategories sc ON sc.id = c.parent_category_id
             )
             SELECT id FROM subcategories
           )
         ELSE 
           t.category_id = :category_id
         END
        )
    AND (:user_ids IS NULL OR p.user_id IN (SELECT unnest(string_to_array(:user_ids, ','))::int))
  GROUP BY t.id
) t
GROUP BY t.created_at
ORDER BY t.created_at

Explication de la requête SQL

Ce rapport mesure la rapidité avec laquelle les sujets reçoivent leur première réponse significative en :

  • Calculant le temps de réponse : Pour chaque sujet, trouve la première publication qui :
    • N’est pas du créateur du sujet d’origine
    • A un numéro de publication > 1 (pas la publication initiale)
    • Est une publication régulière (post_type = 1)
    • A été créée après le sujet (différence de temps positive)
    • N’a pas été supprimée
  • Regroupant par date : Agrège ces temps de réponse par la date de création du sujet
  • Les résultats montrent :
    • « date » : La date de création des sujets
    • « hours » : Temps moyen (en heures) jusqu’à la première réponse pour les sujets créés à cette date
  • Exclusions :
    • Les messages privés sont exclus
    • Les sujets et publications supprimés sont exclus
    • Les auto-réponses sont exclues

Paramètres

  • :start_date (date) - format AAAA-MM-JJ
    • Date de début de la période de rapport
    • Seuls les sujets créés à cette date ou après seront inclus
  • :end_date (date) - format AAAA-MM-JJ
    • Date de fin de la période de rapport
    • Seuls les sujets créés avant cette date seront inclus
  • :category_id (category_id, nullable)
    • Lorsqu’il est fourni, filtre les résultats pour une catégorie spécifique
    • Lorsqu’il est nul, inclut les sujets de toutes les catégories
  • :include_subcategories (boolean) - Par défaut : false
    • Lorsqu’il est vrai ET qu’un category_id est fourni, inclut les sujets de toutes les sous-catégories
    • Lorsqu’il est faux, inclut uniquement les sujets de la catégorie spécifiée exacte
  • :user_ids (user_id, nullable)
    • Lorsqu’il est fourni, inclut uniquement les réponses d’utilisateurs spécifiques
    • Lorsqu’il est nul, inclut les réponses de tous les utilisateurs
    • Peut accepter plusieurs identifiants d’utilisateur sous forme de liste séparée par des virgules

Résultats d’exemple

date response_time_hours
2023-11-12 29.87
2023-11-13 81.52
2023-11-14 5.17
2023-11-15 6.51
2023-11-16 7.75
… …
3 « J'aime »