Relatório do Painel - Estatísticas de Visualização de Tópicos

Dados e relatórios
, ,
1

Este é um relatório de painel em versão SQL para estatísticas de visualização de tópicos.

Este relatório coleta e agrega estatísticas de visualização de tópicos, incluindo visualizações anônimas e de usuários logados, dentro de um determinado intervalo de datas. Ele também permite a filtragem por categorias e, opcionalmente, inclui subcategorias.

:discourse: Esta consulta e a tabela do banco de dados topic_view_stats foram adicionadas ao Discourse em maio de 2024 e não exibirão dados anteriores a esta data.

-- [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

Explicação da Consulta SQL

Parâmetros

  • :start_date (data): A data de início do intervalo para a análise (inclusiva).
  • :end_date (data): A data de término do intervalo para a análise (inclusiva).
  • :category_id (inteiro, opcional): Filtra tópicos por categoria. Use:
    • 0 para incluir todas as categorias.
    • Qualquer ID de categoria específico para filtrar por essa categoria.
  • :include_subcategories (booleano, opcional): Escolha se deseja incluir subcategorias da categoria especificada.
    • false: Apenas a categoria selecionada.
    • true: Incluir também subcategorias.

Filtragem de Categoria
A Expressão de Tabela Comum (CTE) category_filter recupera o(s) ID(s) da categoria selecionado(s) com base no parâmetro :category_id. Se o parâmetro for 0, todas as categorias são incluídas. Se subcategorias devem ser incluídas, ela encontra categorias onde parent_category_id corresponde ao :category_id especificado.

Recuperar estatísticas de tópicos
As estatísticas de visualização de tópicos são agregadas da tabela topic_view_stats, unidas à tabela topics para recuperar os títulos dos tópicos.

Aplicar filtros de data e categoria
A consulta filtra os dados de topic_view_stats:

  • Pelo intervalo de datas fornecido (:start_date e :end_date).
  • Por categoria, correspondendo aos IDs em category_filter.

Calcular visualizações totais
A consulta soma:

  • anonymous_views: Visualizações de usuários anônimos.
  • logged_in_views: Visualizações de usuários logados.
  • Visualizações totais como a soma de ambos.

Classificação e Limitação:
A consulta classifica os tópicos por total_views e limita o resultado aos 100 principais tópicos.

Resultados da Consulta
A consulta retorna as seguintes colunas:

Nome da Coluna Descrição
topic_id O identificador exclusivo do tópico.
topic_title O título do tópico.
total_anonymous_views As visualizações totais de usuários anônimos.
total_logged_in_views As visualizações totais de usuários logados.
total_views As visualizações totais (anônimas + logadas).

Exemplo de Resultados

topic_id topic_title total_anonymous_views total_logged_in_views total_views
12345 “Exemplo Tópico 1” 500 300 800
67890 “Exemplo Tópico 2” 450 350 800
3 curtidas