Rapport du tableau de bord - Favoris

[SaraDev (Sara Devlaeminck)]

Ceci est une version SQL du rapport de tableau de bord pour les signets.

Ce rapport de tableau de bord fournit un décompte quotidien des signets créés dans une plage de dates spécifiée.

-- [params]
-- date :start_date
-- date :end_date

WITH bookmark_counts AS (
  SELECT
    DATE(bookmarks.created_at) AS bookmark_date,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Topic' THEN bookmarks.id END) AS topic_bookmarks,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Post' THEN bookmarks.id END) AS post_bookmarks
  FROM bookmarks
  WHERE bookmarks.created_at::date BETWEEN :start_date AND :end_date
  GROUP BY bookmark_date
)
SELECT
  bookmark_date,
  topic_bookmarks + post_bookmarks AS total_bookmarks,
  topic_bookmarks,
  post_bookmarks
FROM bookmark_counts
ORDER BY bookmark_date

Explication de la requête SQL

Paramètres

Pour générer le rapport, vous devez fournir deux paramètres : :start_date et :end_date. Ces paramètres définissent la plage de dates pour laquelle vous souhaitez analyser l’activité des signets. Les deux paramètres de date acceptent le format de date AAAA-MM-JJ.

Bookmark_Counts (CTE)

Une expression de table commune (CTE) nommée bookmark_counts est utilisée pour organiser les données avant la sélection finale. La CTE effectue les actions suivantes :

• Filtrage par date : Elle filtre les signets par leur date de création pour inclure uniquement ceux créés dans les dates de début et de fin fournies (:start_date et :end_date).
• Regroupement par date : Elle regroupe les résultats par la date de création des signets, garantissant que les décomptes sont agrégés sur une base quotidienne.
• Comptage des signets : Elle compte le nombre de signets pour les sujets et les articles séparément, en utilisant DISTINCT pour s’assurer que chaque signet n’est compté qu’une seule fois. Ceci est fait à l’aide d’une agrégation conditionnelle avec des instructions CASE :
  • topic_bookmarks compte les signets où bookmarkable_type est ‘Topic’.
  • post_bookmarks compte les signets où bookmarkable_type est ‘Post’.

Requête principale

Une fois que la CTE a organisé les données, la déclaration SELECT finale récupère les colonnes suivantes :

1. bookmark_date : La date pour laquelle les décomptes sont agrégés.
2. total_bookmarks : La somme des signets de sujets et d’articles pour chaque jour.
3. topic_bookmarks : Le nombre de signets pour les sujets pour chaque jour. Par ex. :
   
   
4. post_bookmarks : Le nombre de signets pour les articles pour chaque jour. Par ex. :
   
   

Les résultats sont ensuite triés par bookmark_date pour présenter les données dans l’ordre chronologique.

Résultats d’exemple

bookmark_date	total_bookmarks	topic_bookmarks	post_bookmarks
2023-12-01	16	1	15
2023-12-02	4	1	3
2023-12-03	8	0	8
2023-12-04	19	9	10
2023-12-05	18	3	15
…	…	…	…