Avoir un tableau dynamique sur 12 mois glissants

**bndd24** · 21/10/2021, 19h11

Bonjour,

Je bosse sur des statistiques graphiques (lib chartjs que j'intègre a Sf5). J'ai une bdd MySQL avec une table d'utilisateur, chaque utilisateur a un date_time d'inscription sur le site. Jusqu'ici rien de très passionnant

Pour la construction de mon graphique j'ai besoin d'avoir un tableau php représentant les 12 mois de l'année glissants, c'est à dire si nous sommes en Octobre 2021, je veux que le tableau démarre en octobre 2021 et termine en septembre 2022. Le problème c'est que je ne sais pas du tout comment je dois procéder, quelle est selon vous la meilleure façon (la plus conventionnelle) sachant que je dois ensuite récupérer la valeur (nombre d'inscrit) pour chaque mois dans ma table User grâce au date_time

Avez vous quelques pistes ?

Je vous remercie pour votre aide.

**Séb.** · 22/10/2021, 15h09

Si tu as un afflux régulier d'utilisateurs, ceci peut suffire :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT ALL EXTRACT(YEAR_MONTH FROM user_datetime) AS yearmonth, COUNT(*) AS n
FROM users
GROUP BY yearmonth
HAVING yearmonth >= EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 11 MONTH) -- 11 mois précédents
ORDER BY yearmonth DESC

Tu auras un résultat de ce genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Si par contre tu as un mois sans inscription, celui-ci n'apparaîtra pas du tout (pas de valeur 0)
=> À gérer en PHP ou en construisant dynamiquement la période avec SQL + une OUTER JOIN

**bndd24** · 23/10/2021, 16h57

excellent ! je te remercie, ton code fonctionne nickel

J'ai essayé d'intégrer la requête dans mon repo sf avec le createQueryBuilder mais j'y suis pas arrivé

(j'ai bien envie (pour ma culture personnelle) de savoir si c'est possible ou non)

du coup, j'ai intégré le code dans le repo mais en native

**Séb.** · 23/10/2021, 18h03

Envoyé par bndd24

J'ai essayé d'intégrer la requête dans mon repo sf avec le createQueryBuilder mais j'y suis pas arrivé

(j'ai bien envie (pour ma culture personnelle) de savoir si c'est possible ou non)

Perso je ne pourrai pas t'aider sur ce point.

**bndd24** · 23/10/2021, 19h37

pas de souci, c'est déjà super pour le coup de main pour la requête !

Je vais maintenant me pencher sur comment faire pour avoir les mois avec zéro entrée sql ! selon toi le mieux est en php ou en sql (enfin.. pas sur qu'en sql ce soit possible!) ?

**Séb.** · 23/10/2021, 21h33

Je vais maintenant me pencher sur comment faire pour avoir les mois avec zéro entrée sql ! selon toi le mieux est en php ou en sql (enfin.. pas sur qu'en sql ce soit possible!) ?

Si si c'est possible (on peut faire plein de choses maintenant avec MySQL

), et c'est mieux directement en SQL, ainsi tu as ton résultat sans traitements supplémentaires.

La difficulté est de calculer les mois glissants dynamiquement. Pour cela tu peux faire une sous-requête ou une CTE (construction dynamiquement de table). Avec la CTE :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
WITH periods (period) AS (
    VALUES
        ROW (EXTRACT(YEAR_MONTH FROM NOW())),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 1 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 2 MONTH)),
        -- etc.
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 11 MONTH))
)
SELECT ALL * FROM periods;

Tu peux déjà voir ce que donne la requête ci-dessus. Normalement 12 lignes, de la période courante aux 11 précédentes.

On peut même faire des CTE récursive pour éviter de se taper les 12 EXTRACT() à la main
=> https://dev.mysql.com/doc/refman/8.0/en/with.html

À partir de là, quand tu as ta _table_ "periods", tu peux faire un OUTER JOIN pour récupérer toutes les périodes de users y compris celles sans data :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH periods (period) AS (...)
SELECT ALL period, COALESCE(n, 0) AS n -- Si pas de valeur sur la période (= NULL), on valorise "0"
FROM periods LEFT OUTER JOIN (
    -- Reprise de la requête du message précédent qui va servir de base et que j'annote
    -- On aurait aussi pu la mettre dans une CTE...
    SELECT ALL EXTRACT(YEAR_MONTH FROM user_datetime) AS yearmonth, COUNT(*) AS n
    FROM users
    -- Si tu as vraiment *beaucoup* de lignes dans users il faudrait voir pour un WHERE sur la période pour ne pas mouliner pour rien
    GROUP BY yearmonth
    -- On devrait pouvoir supprimer le HAVING et laisser le OUTER JOIN filtrer
    HAVING yearmonth >= EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 11 MONTH) -- 11 mois précédents
) AS _ ON period = yearmonth
ORDER BY period DESC

J'ai pas de serveur MySQL sous la main pour tester, mais l'idée est là

**bndd24** · 24/10/2021, 11h38

Je ne parviens pas a adapter le code a ma table

(mes tests sont bien-sur effectués sur phpmyadmin) j'ai réussi pour le premier mais les deux autres non.

voici ma table avec une dizaines de lignes au cas ou tu souhaites vérifier quelques bricoles :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
--
-- Structure de la table `user`
--
 
CREATE TABLE `user` (
  `id` int NOT NULL,
  `email` varchar(180) COLLATE utf8mb4_unicode_ci NOT NULL,
  `roles` json NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `first_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL COMMENT '(DC2Type:datetime_immutable)',
  `is_verified` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
--
-- Déchargement des données de la table `user`
--
 
INSERT INTO `user` (`id`, `email`, `roles`, `password`, `first_name`, `last_name`, `created_at`, `is_verified`) VALUES
(89, 'email1@email.com', '[]', '$2y$13$eza4AoqNBpSGhB2FNv2Gje1BCe11WV3B7uHIQaQydIsAVYF35g1T2', 'prenom', 'nom', '2020-01-16 17:28:41', 0),
(90, 'email2@email.com', '[]', '$2y$13$98XeUciRDSzYEUjExm/.ruP4jfGo6QU2f6MWaqR/VSFlvlZwbOuou', 'prenom', 'nom', '2020-04-24 09:51:30', 0),
(91, 'email3@email.com', '[]', '$2y$13$do1eHZ4uTbjr7SHGEn7bReaZhsLDjUzI.TfgOyjISsz9L8RuO2tey', 'prenom', 'nom', '2020-04-21 06:34:01', 0),
(92, 'email4@email.com', '[]', '$2y$13$rIXAET1jUP9odHJlRApqiuXCg3tvxL1s8O/w1RvfbETu0/kjtqqnG', 'prenom', 'nom', '2021-05-02 18:31:44', 0),
(93, 'email5@email.com', '[]', '$2y$13$R1lKcZc8nsCY1w.SaQnheuGURf5VIlKIq26IJeNTBKiSW.9auj2/y', 'prenom', 'nom', '2021-05-04 11:47:21', 0),
(94, 'email6@email.com', '[]', '$2y$13$uc8JSvC/ICYvsG.3gg3D1OUwNg0k0t6XLmhkeFJDUcfsBeXde9I2.', 'prenom', 'nom', '2021-08-30 08:39:34', 0),
(95, 'email7@email.com', '[]', '$2y$13$D5aGBpm8jk7zOSlvA9HC4uzecS1.ZmfMy7jDmWgZD7hetBAuO7haa', 'prenom', 'nom', '2020-09-04 20:34:27', 0),
(96, 'email8@email.com', '[]', '$2y$13$sP2j03sAqoRPDZ8HSZyn7udSNkTf9PxeWNuEgJOIBjvM540VYPGpS', 'prenom', 'nom', '2020-07-19 06:13:52', 0),
(97, 'email9@email.com', '[]', '$2y$13$6J/k.c8.YY3p9Lk6M1/KuuBg/PL6JdOYdt9UVnv1c2qjMAweO3RCy', 'prenom', 'nom', '2020-07-06 19:38:26', 0),
(98, 'email10@email.com', '[]', '$2y$13$/HqvO3ex.9iQ7DyeptDDju5K80UuGhHAsa4wHAfJggmjWWXgYvNNK', 'prenom', 'nom', '2020-12-16 01:33:56', 0),
(99, 'email11@email.com', '[]', '$2y$13$7LJizyFp/BkHW.712eH3T.6AuCayeCEgMpodgR5kSCxmWGaYcZQrG', 'prenom', 'nom', '2020-07-06 04:38:46', 0),
(100, 'email12@email.com', '[]', '$2y$13$NHYAQb1xlPYHOmqSCMHzBu0qbuc5s8i7F5yrifULWdFGtLuzDM7uO', 'prenom', 'nom', '2020-05-24 21:45:02', 0),
(101, 'email13@email.com', '[]', '$2y$13$ikPbv7xtQc79C/2ZUFTlB.T6bYUkhZD9YeUZOKLGnWilNVY2orN92', 'prenom', 'nom', '2021-07-01 00:47:19', 0);

ps : c'est passionnant le sql quand même, j'aimerais bien apprendre a faire des requêtes complexe.

**Séb.** · 24/10/2021, 12h15

Tu as un message d'erreur ? Donne les requêtes qui posent problème.
Tu as quelle version de MySQL ? Tu ne serais pas sous MariaDB ?

Tu peux aussi essayer :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
WITH periods (period) AS (
    VALUES
        ROW (EXTRACT(YEAR_MONTH FROM NOW())),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 1 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 2 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 3 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 4 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 5 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 6 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 7 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 8 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 9 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 10 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 11 MONTH))
)
SELECT ALL period, COALESCE(n, 0) AS n -- Si pas de valeur sur la période (= NULL), on valorise "0"
FROM periods LEFT OUTER JOIN (
    -- Reprise de la requête du message précédent qui va servir de base et que j'annote
    -- On aurait aussi pu la mettre dans une CTE...
    SELECT ALL EXTRACT(YEAR_MONTH FROM created_at) AS yearmonth, COUNT(*) AS n
    FROM user
    -- Si tu as vraiment *beaucoup* de lignes dans users il faudrait voir pour un WHERE sur la période pour ne pas mouliner pour rien
    GROUP BY yearmonth
    -- On devrait pouvoir supprimer le HAVING et laisser le OUTER JOIN filtrer
    HAVING yearmonth >= EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 11 MONTH) -- 11 mois précédents
) AS _ ON period = yearmonth
ORDER BY period DESC

ps : c'est passionnant le sql quand même, j'aimerais bien apprendre a faire des requêtes complexe.

Avec ce genre de requêtes tu es sur la bonne voie pour commencer

Dis-toi qu'on peut quasiment tout faire en SQL

**bndd24** · 24/10/2021, 15h46

non je suis bien sous MySQL

Version : 8.0.23

Apres de très longues minutes (pour ne pas dire heures^^) a observer la requête de plus près ligne après ligne, si j'ai bien compris, tu crées une table "virtuelle" au début afin d'avoir les 12 mois nécessaires.

pour la requête suivante :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
WITH periods (period) AS (
    VALUES
        ROW (EXTRACT(YEAR_MONTH FROM NOW())),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 1 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 2 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 3 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 4 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 5 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 6 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 7 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 8 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 9 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 10 MONTH)),
        ROW (EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 11 MONTH))
)
SELECT ALL period, COALESCE(n, 0) AS n -- Si pas de valeur sur la période (= NULL), on valorise "0"
FROM periods LEFT OUTER JOIN (
    -- Reprise de la requête du message précédent qui va servir de base et que j'annote
    -- On aurait aussi pu la mettre dans une CTE...
    SELECT ALL EXTRACT(YEAR_MONTH FROM created_at) AS yearmonth, COUNT(*) AS n
    FROM user
    -- Si tu as vraiment *beaucoup* de lignes dans users il faudrait voir pour un WHERE sur la période pour ne pas mouliner pour rien
    GROUP BY yearmonth
    -- On devrait pouvoir supprimer le HAVING et laisser le OUTER JOIN filtrer
    HAVING yearmonth >= EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 11 MONTH) -- 11 mois précédents
) AS _ ON period = yearmonth
ORDER BY period DESC

J'ai le résultat suivant :

Nom : resultat2.png
Affichages : 162
Taille : 16,0 Ko

Il y a bien un zéro pour les mois sans utilisateur. La requête fonctionne donc très bien !

héhééééééééé !!!

Pour information : J'ai également supprimé le HAVING comme tu le préconise, et elle fonctionne toujours avec les mêmes résultats. Super.

Je suppose qu'elle est optimisable, quelles sont les pistes ? c'est à dire une CTE ?

**Séb.** · 24/10/2021, 16h42

CTE pour "Common Table Expressions", elles sont introduites avec la commande WITH
Et permettent de construire dynamiquement des tables
Cela évite une sous-requête dans un FROM et rend ainsi les requêtes plus lisibles
=> https://dev.mysql.com/doc/refman/8.0/en/with.html

Je suppose qu'elle est optimisable, quelles sont les pistes ?

-- Ajouter un WHERE pour filtrer la période dans la sous-requête (je l'avais fait avec le HAVING ce qui n'est pas top top)
-- Créer un table utilitaire "calendar" qui précalcule le YEAR_MONTH de chaque date => On économise les appels à EXTRACT()

Mais dans tous les cas, à moins que tu aies des centaines de milliers d'utilisateurs, tu ne verras pas la différence, tu peux déjà considérer que c'est bon

**bndd24** · 24/10/2021, 16h45

excellent merci infiniment pour ton coup de main

au top !

Avoir un tableau dynamique sur 12 mois glissants

PHP & Base de données

Discussions similaires

Partager

Partager