Отчет панели управления - Система

Создание сообщества Данные и отчетность
, ,
1

Это SQL-версия отчёта панели мониторинга для системы.

Данный отчёт панели мониторинга предоставляет ежедневное количество автоматических личных сообщений, отправленных системой.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

SELECT
  DATE(created_at) AS day,
  COUNT(*) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  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 — конкретно тех записей, которые являются личными сообщениями для пользователей с подтипом system_message в заданный период времени. Давайте разберём его по частям:

  • Параметры даты:
    • Запрос принимает два параметра: :start_date и :end_date, определяющие диапазон дат для отчёта. Оба параметра даты принимают формат YYYY-MM-DD.
  • SELECT: Запрос выбирает два поля:
    • DATE(created_at) AS day: Извлекает часть даты из временной метки created_at, фактически группируя записи по дню их создания.
    • COUNT(*) AS notifications_count: Подсчитывает общее количество сгенерированных системой ЛС для каждого дня.
  • FROM: Указывает таблицу topics как источник данных, содержащий записи всех тем, включая личные сообщения.
  • WHERE: Содержит несколько фильтров для сужения набора данных:
    • archetype = 'private_message': Включает только записи, являющиеся личными сообщениями.
    • subtype = 'system_message': Дополнительно сужает выборку до сообщений, сгенерированных системой.
    • created_at BETWEEN :start_date AND :end_date: Фильтрует ЛС, созданные в диапазоне, указанном параметрами.
    • deleted_at IS NULL: Исключает сообщения, которые были удалены.
    • user_id > 0: Гарантирует, что сообщения связаны с реальными учётными записями пользователей, а не с системой или анонимными аккаунтами.
  • GROUP BY: Группирует результаты по дню их создания.
  • ORDER BY: Сортирует итоговый набор результатов по дню в порядке возрастания, обеспечивая хронологическую последовательность ежедневных подсчётов.

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

day notifications_count
2024-01-01 5
2024-01-02 7
2024-01-03 11
2024-01-04 14
2024-01-05 8
2

Можно ли привязать это к конкретному личному сообщению, например, к приветственному?

Мне бы хотелось узнать, сколько таких сообщений отправляется ежедневно.

Что вы думаете по этому поводу?

3

Да, лучший способ добавить это — включить дополнительный раздел в оператор WHERE запроса, фильтруя по title темы.

Например:

WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  AND created_at BETWEEN :start_date AND :end_date
  AND title = 'Greetings!'

Найдёт все сообщения с заголовком Greetings!.

Обратите внимание, что некоторые системные сообщения не связаны с реальным пользователем, поэтому при таком типе запроса может потребоваться удалить строку AND user_id > 0.

Также, возможно, стоит убрать AND deleted_at IS NULL, чтобы учитывать приветственные сообщения, которые пользователи могли удалить.

Вы также можете использовать регулярные выражения (regex) для поиска тем с похожими заголовками.

Чтобы сопоставить тему по заголовку с помощью регулярного выражения в PostgreSQL, можно использовать оператор ~, который проверяет соответствие строки регулярному выражению. Структура запроса будет выглядеть так:

SELECT *
FROM topics
WHERE title ~ 'ВашШаблонRegexЗдесь'

Замените 'ВашШаблонRegexЗдесь' на фактический шаблон регулярного выражения, который вы хотите использовать для сопоставления поля title.

Например, если вы ищете темы, в заголовках которых содержится слово «Welcome» (без учёта регистра), можно использовать:

SELECT *
FROM topics
WHERE title ~* 'Welcome'

Оператор ~* используется для сопоставления без учёта регистра.

4

О, это потрясающе. Большое спасибо! Я обязательно попробую!