Отчет по дашборду - Темы

[SaraDev (Sara Devlaeminck)]

Это SQL-версия отчёта по темам из панели управления.

Данный отчёт предоставляет количество новых тем, созданных ежедневно в указанном диапазоне дат. В отчёт не включены личные сообщения, только обычные темы, видимые на форуме.

--[params]
-- date :start_date
-- date :end_date

SELECT 
     p.created_at::date as day,
    COUNT(p.id) AS topics_created
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.post_number = 1
    AND p.user_id > 0 
    AND p.deleted_at ISNULL
    AND t.archetype = 'regular'
GROUP BY p.created_at::date
ORDER BY p.created_at::date ASC

Объяснение SQL-запроса

SQL-запрос выполняет следующие действия:

• Определение параметров: Принимает два параметра, :start_date и :end_date, которые определяют диапазон дат для отчёта. Оба параметра даты принимают формат YYYY-MM-DD.
• Выбор данных: Запрос выбирает дату создания постов (p.created_at) и количество идентификаторов постов (COUNT(p.id)) как topics_created.
• Соединения: Объединяет таблицу posts с таблицей topics по соответствующим полям id, обеспечивая учёт только не удалённых тем (t.deleted_at ISNULL).
• Фильтры: Запрос фильтрует посты, включая только те, которые:
  • Были созданы в указанном диапазоне дат (p.created_at::date BETWEEN :start_date AND :end_date).
  • Являются первым постом в теме (p.post_number = 1), что указывает на начало новой темы.
  • Были созданы зарегистрированными пользователями (p.user_id > 0), исключая системные посты.
  • Не были удалены (p.deleted_at ISNULL).
  • Относятся к темам архетипа «regular» (t.archetype = 'regular'), исключая личные сообщения.
• Группировка: Результаты группируются по дате создания постов (p.created_at::date).
• Сортировка: Итоговый вывод сортируется по дате создания в порядке возрастания (ORDER BY p.created_at::date ASC), обеспечивая хронологический вид создания новых тем.

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

day	topics_created
2023-11-11	18
2023-11-12	10
2023-11-13	21
2023-11-14	19
2023-11-15	22
…	…