Rapport de tableau de bord - Système

Données et rapports
, ,
1

Ceci est une version SQL du rapport de tableau de bord pour le système.

Ce rapport de tableau de bord fournit un décompte quotidien du nombre de messages personnels envoyés automatiquement par le système.

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

Explication de la requête SQL

La requête fonctionne en extrayant des données de la table topics – spécifiquement, celles qui sont qualifiées de messages privés aux utilisateurs avec un sous-type de system_message dans un délai donné. Décomposons-la :

  • Paramètres de date :
    • La requête accepte deux paramètres, :start_date et :end_date, qui définissent la plage de dates pour le rapport. Les deux paramètres de date acceptent le format de date AAAA-MM-JJ.
  • SELECT : La requête sélectionne deux champs :
    • DATE(created_at) AS day : Ceci extrait la partie date de l’horodatage created_at, regroupant efficacement les enregistrements par le jour où ils ont été créés.
    • COUNT(*) AS notifications_count : Ceci compte le nombre total de MP générés par le système pour chaque jour.
  • FROM : Spécifie la table topics comme source de données, qui contient les enregistrements de tous les sujets, y compris les messages privés.
  • WHERE : Contient plusieurs filtres pour affiner l’ensemble de données :
    • archetype = 'private_message' : Inclut uniquement les entrées qui sont des messages privés.
    • subtype = 'system_message' : Réduit davantage la sélection aux seuls messages générés par le système.
    • created_at BETWEEN :start_date AND :end_date : Filtre les MP pour ceux créés dans la plage spécifiée par les paramètres.
    • deleted_at IS NULL : Exclut les messages qui ont été supprimés.
    • user_id > 0 : Garantit que les messages sont associés à des comptes d’utilisateurs réels plutôt qu’à des comptes système ou anonymes.
  • GROUP BY : Regroupe les résultats en fonction du jour de leur création.
  • ORDER BY : Ordonne le jeu de résultats final par jour dans l’ordre croissant, garantissant une séquence chronologique des décomptes quotidiens.

Résultats d’exemple

jour notifications_count
2024-01-01 5
2024-01-02 7
2024-01-03 11
2024-01-04 14
2024-01-05 8
3 « J'aime »
2

Est-ce que cela peut être lié à un PM spécifique, comme le PM de bienvenue ?

J’aimerais savoir combien de ces éléments sont envoyés quotidiennement.

Des idées ?

1 « J'aime »
3

Oui, la meilleure façon d’ajouter cela serait d’ajouter une section à l’instruction WHERE dans la requête, en filtrant par le title du sujet.

Par exemple :

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

Trouverait tous les messages Greetings!.

Notez que certains des messages système n’incluent pas de véritable utilisateur, il peut donc être nécessaire de supprimer la ligne AND user_id > 0 avec ce type de requête.

Vous pourriez également vouloir supprimer AND deleted_at IS NULL pour continuer à compter les messages de bienvenue que les utilisateurs pourraient supprimer.

Vous pourriez utiliser des expressions régulières pour faire correspondre des sujets avec un titre similaire.

Pour faire correspondre un sujet par son titre à l’aide d’une expression régulière (regex) dans PostgreSQL, vous pouvez utiliser l’opérateur ~, qui fait correspondre une expression régulière à une chaîne de caractères. La structure de la requête ressemblerait à ceci :

SELECT *
FROM topics
WHERE title ~ 'YourRegexPatternHere'

Remplacez 'YourRegexPatternHere' par le modèle d’expression régulière réel que vous souhaitez faire correspondre au champ title.

Par exemple, si vous recherchez des sujets dont le titre contient le mot « Welcome » (insensible à la casse), vous pourriez utiliser :

SELECT *
FROM topics
WHERE title ~* 'Welcome'

L’opérateur ~* est utilisé pour la correspondance insensible à la casse.

3 « J'aime »
4

C’est génial. Merci beaucoup ! Je vais essayer ça !

1 « J'aime »