Dashboard-Bericht - System

Daten & Berichterstattung
, ,
1

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

Dieser Dashboard-Bericht liefert eine tägliche Zählung der Anzahl der vom System automatisch gesendeten persönlichen Nachrichten.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

SELECT
  DATE(created_at) AS day,
  COUNT(*) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  AND created_at BETWEEN :start_date AND :end_date
  AND deleted_at IS NULL
  AND user_id > 0
GROUP BY DATE(created_at)
ORDER BY day

Erklärung der SQL-Abfrage

Die Abfrage extrahiert Daten aus der Tabelle topics – insbesondere solche, die als persönliche Nachrichten an Benutzer mit dem Untertyp system_message innerhalb eines bestimmten Zeitraums qualifiziert sind. Lassen Sie uns dies aufschlüsseln:

  • Datumsparameter:
    • Die Abfrage akzeptiert zwei Parameter, :start_date und :end_date, die den Datumsbereich für den Bericht definieren. Beide Datumsparameter akzeptieren das Datumsformat JJJJ-MM-TT.
  • SELECT: Die Abfrage wählt zwei Felder aus:
    • DATE(created_at) AS day: Dies extrahiert den Datumsteil des created_at-Zeitstempels und gruppiert Datensätze effektiv nach dem Tag, an dem sie erstellt wurden.
    • COUNT(*) AS notifications_count: Dies zählt die Gesamtzahl der systemgenerierten PMs für jeden Tag.
  • FROM: Gibt die Tabelle topics als Datenquelle an, die Datensätze aller Themen, einschließlich persönlicher Nachrichten, enthält.
  • WHERE: Enthält mehrere Filter, um den Datensatz einzugrenzen:
    • archetype = 'private_message': Schließt nur Einträge ein, die persönliche Nachrichten sind.
    • subtype = 'system_message': Schränkt die Auswahl weiter auf nur systemgenerierte Nachrichten ein.
    • created_at BETWEEN :start_date AND :end_date: Filtert die PMs nach denen, die im durch die Parameter angegebenen Bereich erstellt wurden.
    • deleted_at IS NULL: Schließt gelöschte Nachrichten aus.
    • user_id > 0: Stellt sicher, dass Nachrichten mit echten Benutzerkonten und nicht mit System- oder anonymen Konten verknüpft sind.
  • GROUP BY: Gruppiert die Ergebnisse nach dem Tag ihrer Erstellung.
  • ORDER BY: Ordnet den endgültigen Ergebnissatz nach dem Tag in aufsteigender Reihenfolge, um eine chronologische Abfolge der täglichen Zählungen zu gewährleisten.

Beispielergebnisse

Tag Benachrichtigungsanzahl
2024-01-01 5
2024-01-02 7
2024-01-03 11
2024-01-04 14
2024-01-05 8
3 „Gefällt mir“
2

Kann dies an ein bestimmtes PM gebunden werden, wie z. B. das Willkommens-PM?

Ich würde gerne wissen, wie viele davon täglich versendet werden.

Gedanken?

1 „Gefällt mir“
3

Ja, am besten fügen Sie dies hinzu, indem Sie einen Abschnitt zur WHERE-Klausel in der Abfrage hinzufügen und nach dem Thema title filtern.

Zum Beispiel:

WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  AND created_at BETWEEN :start_date AND :end_date
  AND title = 'Greetings!'

würde alle Greetings!-Nachrichten finden.

Beachten Sie, dass einige der Systemnachrichten keinen echten Benutzer enthalten. Daher ist es möglicherweise notwendig, die Zeile AND user_id > 0 bei dieser Art von Abfrage zu entfernen.

Sie möchten möglicherweise auch AND deleted_at IS NULL entfernen, um Willkommensnachrichten mitzuzählen, die Benutzer möglicherweise löschen.

Sie könnten Regex verwenden, um Themen mit ähnlichen Titeln abzugleichen.

Um ein Thema anhand seines Titels mit einem regulären Ausdruck (Regex) in PostgreSQL abzugleichen, können Sie den Operator ~ verwenden, der einen regulären Ausdruck mit einer Zeichenkette abgleicht. Die Abfragestruktur würde wie folgt aussehen:

SELECT *
FROM topics
WHERE title ~ 'YourRegexPatternHere'

Ersetzen Sie 'YourRegexPatternHere' durch das tatsächliche Regex-Muster, das Sie gegen das Feld title abgleichen möchten.

Wenn Sie beispielsweise nach Themen suchen, deren Titel das Wort „Welcome“ (Groß-/Kleinschreibung wird nicht beachtet) enthalten, könnten Sie Folgendes verwenden:

SELECT *
FROM topics
WHERE title ~* 'Welcome'

Der Operator ~* wird für den Abgleich ohne Berücksichtigung der Groß-/Kleinschreibung verwendet.

3 „Gefällt mir“
4

Das ist ja großartig. Vielen Dank! Ich werde das mal ausprobieren!

1 „Gefällt mir“