Это SQL-версия отчёта панели управления для оповещения модераторов.
Данный отчёт панели управления предоставляет ежедневное количество частных сообщений, отправленных модераторам с подтипом уведомления notify_moderators. Это позволяет администраторам понимать периоды высокой нагрузки на внимание модераторов и может указывать на моменты, когда пользователи сталкиваются с проблемами, требующими помощи модерации.
-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16
SELECT
DATE(created_at) AS day,
COUNT(1) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
AND subtype = 'notify_moderators'
AND created_at BETWEEN :start_date AND :end_date
AND deleted_at IS NULL
AND user_id > 0
GROUP BY DATE(created_at)
ORDER BY day
Объяснение SQL-запроса
Запрос работает путём извлечения данных из таблицы topics — конкретно тех записей, которые соответствуют частным сообщениям модераторам в заданный промежуток времени. Давайте разберём его:
- Параметры даты:
- Запрос принимает два параметра:
:start_dateи:end_date, которые определяют диапазон дат для отчёта. Оба параметра даты принимают форматГГГГ-ММ-ДД.
- Запрос принимает два параметра:
- Выборка и подсчёт:
DATE(created_at)преобразует временную меткуcreated_atв формат даты и присваивает ей псевдонимday. Это позволяет группировать и подсчитывать сообщения по дате их создания.COUNT(1)используется для подсчёта количества записей за каждый день, присваивая этому подсчёту псевдонимnotifications_count.
- Критерии фильтрации:
- Запрос фильтрует только те записи, где
archetypeравен'private_message', что гарантирует, что рассматриваются исключительно сообщения, а не другие типы тем. - Подтип
subtypeограничен значением'notify_moderators', изолируя случаи, когда пользователи уведомляют или запрашивают помощь у модераторов. - Проверяется, что временная метка
created_atсообщения находится между указанными:start_dateи:end_date. - Сообщения, которые были удалены (
deleted_at IS NULL), исключаются из подсчёта, чтобы отчёт отражал только активные коммуникации. - Наконец, гарантируется, что сообщения связаны с действительным пользователем, путём проверки условия
user_id > 0.
- Запрос фильтрует только те записи, где
- Группировка и сортировка:
- Запрос использует
GROUP BY DATE(created_at)для агрегации результатов по каждому дню в заданном диапазоне дат. - Затем результаты сортируются хронологически с помощью
ORDER BY day, что делает отчёт удобным для чтения и интерпретации.
- Запрос использует
Пример результатов
| day | notifications_count |
|---|---|
| 2023-12-17 | 3 |
| 2023-12-18 | 1 |
| 2023-12-21 | 1 |
| 2023-12-23 | 2 |
| 2023-12-26 | 1 |