Sélection sur plusieurs tables, groupé par date

Version imprimable

03/07/2019, 09h00
Teckyl

Sélection sur plusieurs tables, groupé par date

Bonjour,

Suite à un projet personnel, je fais face à un problème que je n'arrive pas à régler, ni trouver de solution.

Alors voilà, j'ai 8 tables dans ma base de données, suivant la même structure : myDate, myLibellé, myValeur, ID.

Je souhaite faire la moyenne par jour, sur une longueur de 30 jours, voici ce que j'ai :

Code:

SELECT AVG(myValeur) FROM myTable WHERE myLibellé REGEXP 'XXXX' AND myDate > DATE(SUBDATE(NOW(),31)) GROUP BY DATE(myDate);

Cette requête me sors bien ce que je souhaite, soit des moyennes par jour, j'ai donc en retour 30 lignes de myValeur.

J'ai oublié de dire que je récupère ces données sur Excel via un client ODBC. Je rentre donc mes 8 requêtes et tire de ces requêtes 8 tableaux. Je fais un 9eme tableaux avec les maximums par jour. Jusque là, rien de compliquer.

Cependant, je suis confronté à un problème : Sur mes 8 tableaux, je n'ai pas forcément des valeurs tous les jours. Il se peut que (par exemple) le 10/06/19 j'ai des données dans toutes mes tables sauf une et de ce fait, le maximum de mon tableau est faussé (décallage au niveau du tableau qui n'a pas eu de donnée le 10/06/19.

J'ai donc pensé à faire une seule requête, de sorte à ce que je ressorte un seul tableau de 9x30 (8 tables + dates x 30 jours de valeur moyenne). De ce fait, je n'aurais plus à me poser la question de si ma table a effectué des enregistrements ou non. Si elle n'en a pas fait, cela laissera un vide et ne sera pas compter dans le graphique que je tracerais.

J'ai voulu donc faire des UNION, JOIN, etc... mais sans succès.

Si vous avez des idées ou des explications quant a l'utilisation de ces dernières, je suis preneur.

Merci d'avance.

C'est ça que tu cherches à faire ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT  DATE(myDate)
    ,   AVG(myValeur) 
FROM    (   SELECT  myDate
                ,   myLibellé
                ,   myValeur
            FROM    myTable_1
        UNION ALL
            SELECT  myDate
                ,   myLibellé
                ,   myValeur
            FROM    myTable_2
        )    as TMP
WHERE   myLibellé REGEXP 'XXXX' 
    AND myDate > DATE(SUBDATE(NOW(),31)) 
GROUP BY DATE(myDate)
;

03/07/2019, 15h10
aieeeuuuuu

bonjour,

Une solution consiste à partir d'une table calendrier, et de faire des jointures externes dessus.ainsi, même si une table n'a pas une date données, la ligne sortira quand même en raison de la jointure externe.

Bonjour ale1_24 et aieeeuuuuu,

J'essaie de faire fonctionner la requête dont tu m'as fait part mais sans succès (la brèle que je suis prend le dessus sur le génie ponctuel que je peut être :oops:). D'un point de vue algorithmique, c'est bien ça. Mais j'ai une erreur, si tu vois où elle est, je suis preneur. Voilà ce que j'ai :
Code:

1 2 3 4 5 6 SELECT DATE(num_date), AVG(num_chaine) FROM (SELECT rub.num_date, rub.num_chaine FROM m5tcyrubi as rub UNION ALL SELECT ale.num_date, ale.num_chaine FROM m5tcyalesane as ale) as TMP WHERE num_caract REGEXP 'TOTAL_Nmoins1' AND num_date > DATE(SUBDATE(NOW(),31)) GROUP BY DATE(num_date);
Apparemment MySQL ne reconnait pas num_caract et il faut prendre en considération qu'il y aura donc 8 lignes du type :

Code:

SELECT rub.num_date, rub.num_chaine FROM m5tcyrubi as rub

Je n'ai pas bien compris ta solution aieeeuuuu. Tu me conseilles de faire une table calendrier sur Excel ? Et de faire des jointures externes ? Je t'avoue que c'est un peu mystique pour moi tout ça.

Cordialement.

#EDIT

J'ai compris mon erreur dans la requête, je ne récupérais pas de num_caract. Par contre, cela ne me donne pas ce que je souhaite. J'aimerais avoir un tableau avec première colonne la date, deuxième colonne les moyennes de ma première table, troisième colonne de ma deuxième table, etc...

Ce serait plus ce genre de solution :

Code:

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
SELECT  cal.myDate
    ,   tb1.Valeur_1
    ,   tb2.Valeur_2
FROM    (   SELECT  distinct myDate
            FROM    myTable_1
        UNION
            SELECT  distinct myDate
            FROM    myTable_2
        )   as  cal
    LEFT JOIN
        (   SELECT  myDate
                    AVG(myValeur)   AS Valeur_1
            FROM    myTable_1
            WHERE   myLibellé REGEXP 'XXXX' 
            GROUP BY myDate
        )   AS  tb1
    LEFT JOIN
        (   SELECT  myDate
                    AVG(myValeur)   AS Valeur_2
            FROM    myTable_2
            WHERE   myLibellé REGEXP 'XXXX' 
            GROUP BY myDate
        )   AS  tb2
WHERE   myDate > SUBDATE(NOW(),31)
;

Tu auras toutes les dates pour lesquelles il y a au moins une valeur dans une des tables de détail, mais seulement celles-là.

J'ai adapté ton code à mon application, ce qui donne ceci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT cal.num_date, ale.num_chaine, rub.num_chaine FROM ((SELECT DISTINCT ale1.num_date FROM m5tcyalesane as ale1 UNION SELECT DISTINCT rub1.num_date FROM m5tcyrubi as rub1 ) as cal) LEFT JOIN (( SELECT ale1.num_date, AVG(ale1.num_chaine) as 'Tcy ALESANE' FROM m5tcyalesane as ale1 WHERE ale1.num_caract REGEXP 'TOTAL_Nmoins1' GROUP BY ale1.num_date ) as ale) LEFT JOIN (( SELECT rub1.num_date, AVG(rub1.num_chaine) AS 'Tcy RUBI' FROM m5tcyrubi as rub1 WHERE rub1.num_caract REGEXP 'TOTAL_Nmoins1' GROUP BY rub1.num_date ) as rub) WHERE num_date > DATE(SUBDATE(NOW(),31));
Cependant, il manque la jointure des tables avec des, par exemple, ale.num_date = rub.num_date non ?

En tout cas, cette requête ne fonctionne pas telle quelle et je ne vois absolument pas d'où peut venir le problème, si ce n'est pas ça. Si c'est ça, où suis-je sensé mettre cette égalité ? Sachant que les date-heures de mes valeurs ne sont pas identiques d'une table à l'autre.

En effet, la condition de jointure a sauté au cours de la rédaction ed la requête :oops:

Code:

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
SELECT  cal.myDate
    ,   tb1.Valeur_1
    ,   tb2.Valeur_2
FROM    (   SELECT  distinct myDate
            FROM    myTable_1
        UNION
            SELECT  distinct myDate
            FROM    myTable_2
        )   as  cal
    LEFT JOIN
        (   SELECT  myDate
                ,   AVG(myValeur)   AS Valeur_1
            FROM    myTable_1
            WHERE   myLibellé REGEXP 'XXXX' 
            GROUP BY myDate
        )   AS  tb1
        ON  tb1.myDate = cal.myDate
    LEFT JOIN
        (   SELECT  myDate
                ,   AVG(myValeur)   AS Valeur_2
            FROM    myTable_2
            WHERE   myLibellé REGEXP 'XXXX' 
            GROUP BY myDate
        )   AS  tb2
        ON  tb2.myDate = cal.myDate
WHERE   cal.myDate > SUBDATE(NOW(),31)
;

9a devrait aller mieux comme ça ;)

04/07/2019, 17h25
aieeeuuuuu

Citation:

Envoyé par Teckyl

Apparemment MySQL ne reconnait pas num_caract et il faut prendre en considération qu'il y aura donc 8 lignes du type :

C'est normal, la colonne ne fait pas partie du SELECT de la table dérivée (sous requête). Il faut l'ajouter (ou déplacer la condition dans la sous requête, mais il faudra alors la mettre a chaqune des requetes UNION)

Bonjour al1_24,

Merci pour ton aide précieuse ! Je ne comprend pas pourquoi cela me dit que tb1.myValeur_1 n'est pas reconnu (j'imagine que le tb2.myValeur_2 ne l'est pas non plus). Je ne trouve pas de solution, j'ai essayé de modifier les alias etc... mais rien n'y fait.

Bonjour aieeeuuuu,

Oui j'ai compris et résolu le soucis une heure après avoir fait part de ma requête, merci ! :)

Ci-après là où j'en suis :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 SELECT cal.num_date, AVG(ale.num_chaine), AVG(rub.num_chaine) FROM ( SELECT DISTINCT num_date FROM m5tcyalesane UNION SELECT DISTINCT num_date FROM m5tcyrubi ) as cal LEFT JOIN ( SELECT num_date, num_chaine as 'Tcy ALESANE' FROM m5tcyalesane WHERE num_caract REGEXP 'TOTAL_Nmoins1' GROUP BY num_date ) as ale ON DATE(ale.num_date) = DATE(cal.num_date) LEFT JOIN ( SELECT num_date, num_chaine AS 'Tcy RUBI' FROM m5tcyrubi WHERE num_caract REGEXP 'TOTAL_Nmoins1' GROUP BY num_date ) as rub ON DATE(rub.num_date) = DATE(cal.num_date) WHERE num_date > DATE(SUBDATE(NOW(),31));
Donc j'ai testé avec des alias dans le LEFT JOIN puis le double alias (alias LEFT JOIN comme rub.rub1.num_chaine) que je ne suis même pas sur que cela fonctionne.

Voilà, désolé encore de vous déranger autant pour cette requête.:?

#EDIT

J'ai trouvé d'où peut venir mon problème. C'est au moment de la conversion de la donnée. J'ai mis * à la place de

Code:

cal.num_date, AVG(ale.num_chaine), AVG(rub.num_chaine)

dans le premier SELECT (première ligne) et lorsque je lance la requête en formatant la donnée num_date en DATE(num_date), le système me répond que num_date est inconnu.

Voici mon code :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 SELECT * FROM ( SELECT DISTINCT num_date FROM m5tcyalesane UNION SELECT DISTINCT num_date FROM m5tcyrubi ) as cal LEFT JOIN ( SELECT DATE(num_date), AVG(num_chaine) as 'Tcy ALESANE' FROM m5tcyalesane WHERE num_caract REGEXP 'TOTAL_Nmoins1' GROUP BY DATE(num_date) ) as ale ON ale.num_date = DATE(cal.num_date) LEFT JOIN ( SELECT DATE(num_date), AVG(num_chaine) AS 'Tcy RUBI' FROM m5tcyrubi WHERE num_caract REGEXP 'TOTAL_Nmoins1' GROUP BY DATE(num_date) ) as rub ON DATE(rub.num_date) = DATE(cal.num_date) WHERE DATE(cal.num_date) > DATE(SUBDATE(NOW(),31));
Lorsque je lance la requête sans le formatage de la colonne num_date, cela me sors 36000 lignes de la même valeur. Ce qui est assez embêtant vu que ce n'est ni possible, ni voulu :roll:
Code:

1 2 3 4 5 6 # num_date, num_date, Tcy ALESANE, num_date, Tcy RUBI '2019-06-05 00:03:45', '2019-06-05 00:02:57', 'YYYYYYYYY', '2019-06-05 00:00:23', 'XXXXXXXX' '2019-06-05 00:04:09', '2019-06-05 00:02:57', 'YYYYYYYYY', '2019-06-05 00:00:23', 'XXXXXXXX' '2019-06-05 00:04:33', '2019-06-05 00:02:57', 'YYYYYYYYY', '2019-06-05 00:00:23', 'XXXXXXXX' '2019-06-05 00:04:57', '2019-06-05 00:02:57', 'YYYYYYYYY', '2019-06-05 00:00:23', 'XXXXXXXX' '2019-06-05 00:06:56', '2019-06-05 00:02:57', 'YYYYYYYYY', '2019-06-05 00:00:23', 'XXXXXXXX'
Ce que j'aurais voulu, ça aurait été :
Code:

1 2 3 4 5 6 # num_date, Tcy ALESANE, Tcy RUBI '2019-06-05', 'YYYYYYYYY', 'XXXXXXXX' '2019-06-06', 'YYYYYYYYY', 'XXXXXXXX' '2019-06-07', 'YYYYYYYYY', 'XXXXXXXX' '2019-06-08', 'YYYYYYYYY', 'XXXXXXXX' '2019-06-09', 'YYYYYYYYY', 'XXXXXXXX'
#EDIT 2

Ca y est ! J'ai enfin pu avoir seulement mes 30 lignes ! Il me reste plus q'à avoir les colonnes tb1.myValeur_1 et 2 !

05/07/2019, 11h06
Teckyl

Solution trouvée !

Le problème était que l'alias que je donnais à tb1.myValeur_1 était une chaine de caractère. Ce qui signifiais que la colonne change de nom. De ce fait, le premier SELECT ne retrouve pas tb1.myValeur_1 mais XXXX qui est le nom alias que j'ai donné à ma colonne.

Merci beaucoup pour votre aide, al1_24 et aieeeuuuuu !