Dashboard-Bericht - Lesezeichen

[SaraDev (Sara Devlaeminck)]

Dies ist eine SQL-Version des Dashboard-Berichts für Lesezeichen.

Dieser Dashboard-Bericht liefert eine tägliche Zählung der erstellten Lesezeichen innerhalb eines bestimmten Datumsbereichs.

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

Erklärung der SQL-Abfrage

Parameter

Um den Bericht zu generieren, müssen Sie zwei Parameter angeben: :start_date und :end_date. Diese Parameter definieren den Datumsbereich, für den Sie die Lesezeichenaktivität analysieren möchten. Beide Datumsparameter akzeptieren das Datumsformat JJJJ-MM-TT.

Bookmark_Counts (CTE)

Eine Common Table Expression (CTE) namens bookmark_counts wird verwendet, um die Daten vor der endgültigen Auswahl zu organisieren. Die CTE führt die folgenden Aktionen aus:

• Datumsfilterung: Sie filtert die Lesezeichen nach ihrem Erstellungsdatum, um nur diejenigen einzuschließen, die innerhalb der angegebenen Start- und Enddaten (:start_date und :end_date) erstellt wurden.
• Datumsgruppierung: Sie gruppiert die Ergebnisse nach dem Erstellungsdatum der Lesezeichen, um sicherzustellen, dass die Zählungen täglich aggregiert werden.
• Zählen von Lesezeichen: Sie zählt die Anzahl der Lesezeichen für Themen und Beiträge separat und verwendet DISTINCT, um sicherzustellen, dass jedes Lesezeichen nur einmal gezählt wird. Dies geschieht mithilfe von bedingter Aggregation mit CASE-Anweisungen:
  • topic_bookmarks zählt die Lesezeichen, bei denen bookmarkable_type ‘Topic’ ist.
  • post_bookmarks zählt die Lesezeichen, bei denen bookmarkable_type ‘Post’ ist.

Hauptabfrage

Nachdem die CTE die Daten organisiert hat, ruft die endgültige SELECT-Anweisung die folgenden Spalten ab:

1. bookmark_date: Das Datum, für das die Zählungen aggregiert werden.
2. total_bookmarks: Die Summe der Themen- und Beitragslesezeichen für jeden Tag.
3. topic_bookmarks: Die Anzahl der Lesezeichen für Themen für jeden Tag. Z.B.:
   
   
4. post_bookmarks: Die Anzahl der Lesezeichen für Beiträge für jeden Tag. Z.B.:
   
   

Die Ergebnisse werden dann nach bookmark_date sortiert, um die Daten in chronologischer Reihenfolge darzustellen.

Beispielergebnisse

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