Informe del Panel - Estadísticas de vista de temas

Datos e informes
, ,
1

Esta es una versión SQL del Informe del Panel para Estadísticas de Visualización de Temas.

Este informe recopila y agrega estadísticas de visualización de temas, incluidas las visualizaciones anónimas y de usuarios registrados, dentro de un rango de fechas determinado. También permite filtrar por categorías e incluye opcionalmente subcategorías.

:discourse: Esta consulta y la tabla de base de datos topic_view_stats se agregaron a Discourse en mayo de 2024, y no mostrarán datos anteriores a esta fecha.

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

Explicación de la consulta SQL

Parámetros

  • :start_date (fecha): La fecha de inicio del rango para el análisis (incluida).
  • :end_date (fecha): La fecha de finalización del rango para el análisis (incluida).
  • :category_id (entero, opcional): Filtra los temas por categoría. Usa:
    • 0 para incluir todas las categorías.
    • Cualquier ID de categoría específico para filtrar por esa categoría.
  • :include_subcategories (booleano, opcional): Elige si incluir o no las subcategorías de la categoría especificada.
    • false: Solo la categoría seleccionada.
    • true: Incluir también las subcategorías.

Filtrado por categoría
La Expresión Común de Tabla (CTE) category_filter recupera los ID de categoría seleccionados según el parámetro :category_id. Si el parámetro es 0, se incluyen todas las categorías. Si se deben incluir subcategorías, encuentra categorías donde parent_category_id coincide con el :category_id especificado.

Recuperar estadísticas de temas
Las estadísticas de visualización de temas se agregan de la tabla topic_view_stats, unidas con la tabla topics para recuperar los títulos de los temas.

Aplicar filtros de fecha y categoría
La consulta filtra los datos de topic_view_stats:

  • Por el rango de fechas proporcionado (:start_date y :end_date).
  • Por categoría, coincidiendo con los ID en category_filter.

Calcular vistas totales
La consulta suma:

  • anonymous_views: Vistas de usuarios anónimos.
  • logged_in_views: Vistas de usuarios registrados.
  • Vistas totales como la suma de ambas.

Clasificación y limitación:
La consulta clasifica los temas por total_views y limita el resultado a los 100 temas principales.

Resultados de la consulta
La consulta devuelve las siguientes columnas:

Nombre de la columna Descripción
topic_id El identificador único del tema.
topic_title El título del tema.
total_anonymous_views Las vistas totales de usuarios anónimos.
total_logged_in_views Las vistas totales de usuarios registrados.
total_views Las vistas totales (anónimas + registradas).

Resultados de ejemplo

topic_id topic_title total_anonymous_views total_logged_in_views total_views
12345 “Ejemplo Tema 1” 500 300 800
67890 “Ejemplo Tema 2” 450 350 800
3 Me gusta