Rapport du tableau de bord - Publications

Données et rapports
, ,
1

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

Ce rapport fournit un décompte quotidien des publications créées dans une plage de dates spécifiée. Il est conçu pour suivre l’activité dans les sujets réguliers, en excluant les publications des messages privés et d’autres archétypes spéciaux.

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

SELECT
    p.created_at::date AS "Jour",
    COUNT(p.id) AS "Nombre"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at ISNULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
GROUP BY p.created_at::date
ORDER BY 1

Explication de la requête SQL

  • Paramètres :
    • 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.

La requête SQL effectue les opérations suivantes :

  • Sélection des données :
    • Elle sélectionne la date (created_at::date) à laquelle chaque publication a été créée et la convertit au format date pour ignorer la composante horaire.
    • Elle compte également le nombre de publications (COUNT(p.id)) créées chaque jour.
  • Jointures :
    • La requête joint la table posts à la table topics à l’aide d’une INNER JOIN. Cette jointure garantit que seules les publications associées à des sujets existants sont prises en compte.
    • Elle exclut les sujets qui ont été supprimés (t.deleted_at ISNULL).
  • Filtres :
    • Elle filtre les publications pour inclure uniquement celles comprises dans la plage de dates spécifiée (p.created_at::date BETWEEN :start_date AND :end_date).
    • Elle exclut les publications supprimées (p.deleted_at ISNULL).
    • Elle limite les résultats aux publications des sujets réguliers (t.archetype = 'regular').
    • Elle ne prend en compte que les publications de type p.post_type = 1, excluant les actions de modération, les murmures et les petites actions.
  • Groupement et tri :
    • Les résultats sont groupés par la date de création de la publication (GROUP BY p.created_at::date).
    • La sortie finale est triée par date dans l’ordre croissant (ORDER BY 1), où 1 fait référence à la première colonne de l’instruction SELECT, qui est la date.

Exemple de résultats

Jour Nombre
2023-11-12 25
2023-11-13 35
2023-11-14 38
2023-11-15 47
2023-11-16 36
2023-11-17 79
… …
2 « J'aime »
2

C’est super.
Y aurait-il un moyen de pouvoir définir la catégorie/sous-catégorie à l’exécution en tant que date ?
Et en bonus, pouvons-nous soit lister les résultats par utilisateur, soit définir l’utilisateur également ?

Ce que j’essaie de faire, c’est de voir combien de publications sont faites pendant (plage) dans mes zones de tickets de support par mon personnel de support.

3

Oui, vous pouvez utiliser la requête suivante pour cela :

--[params]
-- date :start_date
-- date :end_date
-- null category_id :category_id 
-- null user_id :user_id
-- boolean :include_subcategories = false

SELECT 
    u.username AS "Utilisateur",
    p.created_at::date AS "Date",
    COUNT(p.id) AS "Nombre"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at IS NULL
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON t.category_id = c.id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at IS NULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
    AND (
        :category_id IS NULL 
        OR t.category_id = :category_id
        OR (:include_subcategories AND c.parent_category_id = :category_id)
    )
    AND (:user_id IS NULL OR p.user_id = :user_id)
GROUP BY u.username, p.created_at::date
ORDER BY p.created_at::date ASC, u.username

Paramètres :

  • :start_date et :end_date : Définissent la période de reporting (obligatoire)
  • :category_id : Filtre optionnel pour une catégorie spécifique
  • :user_id : Filtre optionnel pour un utilisateur spécifique
  • :include_subcategories : Option pour inclure les sous-catégories de la catégorie choisie

Cette requête affiche :

  • Utilisateur : Nom d’utilisateur de l’auteur du message
  • Date : La date calendaire à laquelle les messages ont été créés
  • Nombre : Nombre de messages créés par cet utilisateur à cette date

Exemple de données :

Utilisateur Date Nombre
utilisateur 1 2023-01-01 3
utilisateur 2 2023-01-01 2
utilisateur 3 2023-01-01 1
utilisateur 1 2023-01-02 2
utilisateur 2 2023-01-02 3
utilisateur 1 2023-01-03 1
… … …
2 « J'aime »
4

Merci, c’est d’une grande aide !

2 « J'aime »