Отчет по панели управления - Сообщения

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

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

Этот отчет предоставляет ежедневное количество сообщений, созданных в указанном диапазоне дат. Он предназначен для отслеживания активности в обычных темах, исключая сообщения из личных переписок и других специальных архетипов.

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

SELECT 
    p.created_at::date AS "День",
    COUNT(p.id) AS "Количество"
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.deleted_at ISNULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
GROUP BY p.created_at::date
ORDER BY 1

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

  • Параметры:
    • Запрос принимает два параметра: :start_date и :end_date, которые определяют диапазон дат для отчета. Оба параметра даты принимают формат YYYY-MM-DD.

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

  • Выборка данных:
    • Выбирается дата (created_at::date) создания каждого сообщения, которая приводится к формату даты, чтобы игнорировать компонент времени.
    • Также подсчитывается количество сообщений (COUNT(p.id)), созданных в каждый день.
  • Соединения:
    • Запрос соединяет таблицу posts с таблицей topics с помощью INNER JOIN. Это соединение гарантирует, что учитываются только сообщения, связанные с существующими темами.
    • Исключаются любые темы, которые были удалены (t.deleted_at ISNULL).
  • Фильтры:
    • Сообщения фильтруются так, чтобы включать только те, которые находятся в указанном диапазоне дат (p.created_at::date BETWEEN :start_date AND :end_date).
    • Исключаются удаленные сообщения (p.deleted_at ISNULL).
    • Результаты ограничиваются сообщениями из обычных тем (t.archetype = 'regular').
    • Учитываются только сообщения с p.post_type = 1, исключая действия модераторов, шепот и сообщения типа small_action.
  • Группировка и сортировка:
    • Результаты группируются по дате создания сообщения (GROUP BY p.created_at::date).
    • Итоговый вывод сортируется по дате в порядке возрастания (ORDER BY 1), где 1 ссылается на первый столбец в операторе SELECT, то есть на дату.

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

День Количество
2023-11-12 25
2023-11-13 35
2023-11-14 38
2023-11-15 47
2023-11-16 36
2023-11-17 79
… …
2

Это здорово.
Можно ли определять категорию/подкатегорию во время выполнения в зависимости от даты?
И в качестве бонуса, можно ли либо отсортировать результаты по пользователям, либо также определять пользователя?

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

3

Да, для этого можно использовать следующий запрос:

--[params]
-- date :start_date
-- date :end_date
-- null category_id :category_id 
-- null user_id :user_id
-- boolean :include_subcategories = false

SELECT 
    u.username AS "Пользователь",
    p.created_at::date AS "Дата",
    COUNT(p.id) AS "Количество"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at IS NULL
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON t.category_id = c.id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at IS NULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
    AND (
        :category_id IS NULL 
        OR t.category_id = :category_id
        OR (:include_subcategories AND c.parent_category_id = :category_id)
    )
    AND (:user_id IS NULL OR p.user_id = :user_id)
GROUP BY u.username, p.created_at::date
ORDER BY p.created_at::date ASC, u.username

Параметры:

  • :start_date и :end_date: Определение временного диапазона отчёта (обязательно)
  • :category_id: Опциональный фильтр для конкретной категории
  • :user_id: Опциональный фильтр для конкретного пользователя
  • :include_subcategories: Опция включения подкатегорий выбранной категории

Этот запрос показывает:

  • Пользователь: Имя пользователя автора поста
  • Дата: Календарная дата создания постов
  • Количество: Количество постов, созданных этим пользователем в эту дату

Пример данных:

Пользователь Дата Количество
user 1 2023-01-01 3
user 2 2023-01-01 2
user 3 2023-01-01 1
user 1 2023-01-02 2
user 2 2023-01-02 3
user 1 2023-01-03 1
… … …
4

Спасибо, это очень большая помощь!