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

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

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

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

:discourse: Этот запрос и таблица базы данных topic_view_stats были добавлены в Discourse в мае 2024 года, поэтому данные за период до этой даты отображаться не будут.

-- [params]
-- date :start_date
-- date :end_date
-- int :category_id = 0
-- boolean :include_subcategories = false

WITH category_filter AS (
  SELECT
    id
  FROM categories
  WHERE
    (:category_id = 0 OR id = :category_id)
    OR (:include_subcategories AND parent_category_id = :category_id)
)

SELECT
  topic_view_stats.topic_id,
  topics.title AS topic_title,
  SUM(topic_view_stats.anonymous_views) AS total_anonymous_views,
  SUM(topic_view_stats.logged_in_views) AS total_logged_in_views,
  SUM(topic_view_stats.anonymous_views + topic_view_stats.logged_in_views) AS total_views
FROM topic_view_stats
INNER JOIN topics ON topics.id = topic_view_stats.topic_id
WHERE
  topic_view_stats.viewed_at BETWEEN :start_date AND :end_date
  AND (
    :category_id = 0
    OR topics.category_id IN (SELECT id FROM category_filter)
  )
GROUP BY topic_view_stats.topic_id, topics.title
ORDER BY total_views DESC
LIMIT 100

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

Параметры

  • :start_date (дата): Начальная дата диапазона для анализа (включительно).
  • :end_date (дата): Конечная дата диапазона для анализа (включительно).
  • :category_id (целое число, опционально): Фильтрация тем по категории. Используйте:
    • 0 для включения всех категорий.
    • Конкретный ID категории для фильтрации по этой категории.
  • :include_subcategories (логическое значение, опционально): Выбор, включать ли подкатегории указанной категории.
    • false: Только выбранная категория.
    • true: Включить также подкатегории.

Фильтрация по категориям
Общее табличное выражение (CTE) category_filter извлекает выбранные ID категорий на основе параметра :category_id. Если параметр равен 0, включаются все категории. Если необходимо включить подкатегории, находятся категории, у которых parent_category_id совпадает с указанным :category_id.

Получение статистики по темам
Статистика просмотров тем агрегируется из таблицы topic_view_stats и соединяется с таблицей topics для получения заголовков тем.

Применение фильтров по дате и категории
Запрос фильтрует данные topic_view_stats:

  • По заданному диапазону дат (:start_date и :end_date).
  • По категории, сопоставляя с ID из category_filter.

Расчёт общего числа просмотров
Запрос суммирует:

  • anonymous_views: Просмотры от анонимных пользователей.
  • logged_in_views: Просмотры от авторизованных пользователей.
  • Общее число просмотров как сумму обоих показателей.

Ранжирование и ограничение:
Запрос ранжирует темы по total_views и ограничивает результат топ-100 темами.

Результаты запроса
Запрос возвращает следующие столбцы:

Имя столбца Описание
topic_id Уникальный идентификатор темы.
topic_title Заголовок темы.
total_anonymous_views Общее число просмотров от анонимных пользователей.
total_logged_in_views Общее число просмотров от авторизованных пользователей.
total_views Общее число просмотров (анонимные + авторизованные).

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

topic_id topic_title total_anonymous_views total_logged_in_views total_views
12345 “Пример темы 1” 500 300 800
67890 “Пример темы 2” 450 350 800
3 лайка
Admin dashboard report reference guide