Rapporto Dashboard - Statistiche visualizzazione argomento

Dati e reportistica
, ,
1

Questa è una versione SQL del Report di Dashboard per le statistiche di visualizzazione degli argomenti.

Questo report raccoglie e aggrega le statistiche di visualizzazione degli argomenti, incluse le visualizzazioni anonime e quelle degli utenti registrati, all’interno di un intervallo di date specificato. Consente inoltre di filtrare per categorie e include facoltativamente le sottocategorie.

:discourse: Questa query e la tabella del database topic_view_stats sono state aggiunte a Discourse nel maggio 2024 e non mostreranno dati precedenti a tale 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

Spiegazione della Query SQL

Parametri

  • :start_date (data): La data di inizio dell’intervallo per l’analisi (inclusa).
  • :end_date (data): La data di fine dell’intervallo per l’analisi (inclusa).
  • :category_id (intero, facoltativo): Filtra gli argomenti per categoria. Utilizzare:
    • 0 per includere tutte le categorie.
    • Qualsiasi ID di categoria specifico per filtrare per quella categoria.
  • :include_subcategories (booleano, facoltativo): Scegli se includere le sottocategorie della categoria specificata.
    • false: Solo la categoria selezionata.
    • true: Includi anche le sottocategorie.

Filtro per categoria
La Common Table Expression (CTE) category_filter recupera gli ID di categoria selezionati in base al parametro :category_id. Se il parametro è 0, vengono incluse tutte le categorie. Se si devono includere le sottocategorie, trova le categorie in cui parent_category_id corrisponde al :category_id specificato.

Recupera statistiche sugli argomenti
Le statistiche di visualizzazione degli argomenti vengono aggregate dalla tabella topic_view_stats, unite alla tabella topics per recuperare i titoli degli argomenti.

Applica filtri per data e categoria
La query filtra i dati di topic_view_stats:

  • Per l’intervallo di date fornito (:start_date e :end_date).
  • Per categoria, confrontando con gli ID in category_filter.

Calcola le visualizzazioni totali
La query somma:

  • anonymous_views: Visualizzazioni degli utenti anonimi.
  • logged_in_views: Visualizzazioni degli utenti registrati.
  • Visualizzazioni totali come somma di entrambe.

Classificazione e limitazione:
La query classifica gli argomenti in base a total_views e limita il risultato ai primi 100 argomenti.

Risultati della query
La query restituisce le seguenti colonne:

Nome colonna Descrizione
topic_id L’identificatore univoco dell’argomento.
topic_title Il titolo dell’argomento.
total_anonymous_views Le visualizzazioni totali degli utenti anonimi.
total_logged_in_views Le visualizzazioni totali degli utenti registrati.
total_views Le visualizzazioni totali (anonime + registrate).

Esempio di risultati

topic_id topic_title total_anonymous_views total_logged_in_views total_views
12345 “Esempio Argomento 1” 500 300 800
67890 “Esempio Argomento 2” 450 350 800
3 Mi Piace