Rapport du tableau de bord - Statistiques de visualisation du sujet

Données et rapports
, ,
1

Ceci est une version SQL du rapport de tableau de bord pour les statistiques de vues de sujets.

Ce rapport collecte et agrège les statistiques de vues de sujets, y compris les vues anonymes et connectées, dans une plage de dates donnée. Il permet également de filtrer par catégories et inclut éventuellement des sous-catégories.

:discourse: Cette requête et la table de base de données topic_view_stats ont été ajoutées à Discourse en mai 2024, et n’afficheront aucune donnée antérieure à cette date.

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

Explication de la requête SQL

Paramètres

  • :start_date (date) : La date de début de la plage pour l’analyse (incluse).
  • :end_date (date) : La date de fin de la plage pour l’analyse (incluse).
  • :category_id (entier, facultatif) : Filtrer les sujets par catégorie. Utiliser :
    • 0 pour inclure toutes les catégories.
    • Un ID de catégorie spécifique pour filtrer par cette catégorie.
  • :include_subcategories (booléen, facultatif) : Choisir d’inclure ou non les sous-catégories de la catégorie spécifiée.
    • false : Uniquement la catégorie sélectionnée.
    • true : Inclure également les sous-catégories.

Filtrage par catégorie
L’expression de table commune (CTE) category_filter récupère les ID de catégorie sélectionnés en fonction du paramètre :category_id. Si le paramètre est 0, toutes les catégories sont incluses. Si les sous-catégories doivent être incluses, elle trouve les catégories dont parent_category_id correspond au :category_id spécifié.

Récupération des statistiques de sujets
Les statistiques de vues de sujets sont agrégées à partir de la table topic_view_stats, jointes à la table topics pour récupérer les titres des sujets.

Application des filtres de date et de catégorie
La requête filtre les données de topic_view_stats :

  • Par la plage de dates fournie (:start_date et :end_date).
  • Par catégorie, en faisant correspondre les ID de la category_filter.

Calcul des vues totales
La requête additionne :

  • anonymous_views : Vues des utilisateurs anonymes.
  • logged_in_views : Vues des utilisateurs connectés.
  • Vues totales comme la somme des deux.

Classement et limitation :
La requête classe les sujets par total_views et limite le résultat aux 100 premiers sujets.

Résultats de la requête
La requête renvoie les colonnes suivantes :

Nom de la colonne Description
topic_id L’identifiant unique du sujet.
topic_title Le titre du sujet.
total_anonymous_views Le nombre total de vues des utilisateurs anonymes.
total_logged_in_views Le nombre total de vues des utilisateurs connectés.
total_views Le nombre total de vues (anonymes + connectés).

Exemple de résultats

topic_id topic_title total_anonymous_views total_logged_in_views total_views
12345 “Exemple Sujet 1” 500 300 800
67890 “Exemple Sujet 2” 450 350 800
3 « J'aime »