Отчет по панели управления: время до первого ответа

[SaraDev (Sara Devlaeminck)]

Это SQL-версия отчёта панели управления по показателю «Время до первого ответа».

Этот отчёт панели управления предоставляет данные о среднем времени ответа на темы в указанном диапазоне дат. Отчёт рассчитывает время, прошедшее до публикации первого ответа кем-либо, кроме автора темы.

-- [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 

-- Время до первого ответа по датам
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  -- Обычный тип поста
    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

Пояснение к SQL-запросу

Этот отчёт измеряет скорость получения первым значимым ответом на темы, выполняя следующие действия:

• Расчёт времени ответа: Для каждой темы находится первый пост, который:
  • Не принадлежит автору темы
  • Имеет номер поста > 1 (не исходный пост)
  • Является обычным постом (post_type = 1)
  • Был создан после создания темы (положительная разница во времени)
  • Не был удалён
• Группировка по датам: Суммирует эти времена ответа по дате создания темы
• Результаты показывают:
  • «date»: Дата создания тем
  • «hours»: Среднее время (в часах) до первого ответа для тем, созданных в эту дату
• Исключения:
  • Личные сообщения исключаются
  • Удалённые темы и посты исключаются
  • Ответы авторов самим себе исключаются

Параметры

• :start_date (date) - формат YYYY-MM-DD
  • Начальная дата периода отчёта
  • Включаются только темы, созданные в эту дату или позже
• :end_date (date) - формат YYYY-MM-DD
  • Конечная дата периода отчёта
  • Включаются только темы, созданные до этой даты
• :category_id (category_id, nullable)
  • При указании фильтрует результаты по конкретной категории
  • При значении null включает темы из всех категорий
• :include_subcategories (boolean) - По умолчанию: false
  • При значении true И указании category_id включает темы из всех подкатегорий
  • При значении false включает только темы из точно указанной категории
• :user_ids (user_id, nullable)
  • При указании включает только ответы от конкретных пользователей
  • При значении null включает ответы от всех пользователей
  • Может принимать несколько идентификаторов пользователей через запятую

Пример результатов

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
…	…