Discussion :

[Pago]

Bonjour
j'essaie de construire une requete si je mets *3 LEFT JOIN à la suite cela va impeccable dès que je mets le 4ème LEFT JOIN elle devient très lente, et je devrais faire 12 LEFT JOIN pour les 12 mois comment faire ?

Voici le code

Code sql :

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

1
2
3
4
5
6
7
8
str = " SELECT Base_Tournee.Groupe, " & _
"Avg(SousRequete1.SommeDeTT) AS Moy_t_1, Avg(SousRequete1.Km_Total) AS Moy_km_1,Avg(SousRequete2.SommeDeTT) AS Moy_t_2, Avg(SousRequete2.Km_Total) AS Moy_km_2,Avg(SousRequete3.SommeDeTT) AS Moy_t_3, Avg(SousRequete3.Km_Total) AS Moy_km_3,Avg(SousRequete4.SommeDeTT) AS Moy_t_4, Avg(SousRequete4.Km_Total) AS Moy_km_4 " & _
"FROM (((Base_Tournee " & _
"LEFT JOIN (SELECT Base_Fiche.Date, Sum(Base_Fiche.TT) AS SommeDeTT, Sum([Km_Arrivee]-[Km_Depart]) AS Km_Total, Base_Tournee.Groupe FROM Base_Tournee INNER JOIN Base_Fiche ON Base_Tournee.Tournee=Base_Fiche.No_Tournee GROUP BY Base_Fiche.Date, Base_Tournee.Groupe HAVING (Month([Date])=1) AND (Year([Date])=2007))  AS SousRequete1 ON Base_Tournee.Groupe=SousRequete1.Groupe) " & _
"LEFT JOIN (SELECT Base_Fiche.Date, Sum(Base_Fiche.TT) AS SommeDeTT, Sum([Km_Arrivee]-[Km_Depart]) AS Km_Total, Base_Tournee.Groupe FROM Base_Tournee INNER JOIN Base_Fiche ON Base_Tournee.Tournee=Base_Fiche.No_Tournee GROUP BY Base_Fiche.Date, Base_Tournee.Groupe HAVING (Month([Date])=2) AND (Year([Date])=2007))  AS SousRequete2 ON Base_Tournee.Groupe=SousRequete2.Groupe) " & _
"LEFT JOIN (SELECT Base_Fiche.Date, Sum(Base_Fiche.TT) AS SommeDeTT, Sum([Km_Arrivee]-[Km_Depart]) AS Km_Total, Base_Tournee.Groupe FROM Base_Tournee INNER JOIN Base_Fiche ON Base_Tournee.Tournee=Base_Fiche.No_Tournee GROUP BY Base_Fiche.Date, Base_Tournee.Groupe HAVING (Month([Date])=3) AND (Year([Date])=2007))  AS SousRequete3 ON Base_Tournee.Groupe=SousRequete3.Groupe) " & _
"LEFT JOIN (SELECT Base_Fiche.Date, Sum(Base_Fiche.TT) AS SommeDeTT, Sum([Km_Arrivee]-[Km_Depart]) AS Km_Total, Base_Tournee.Groupe FROM Base_Tournee INNER JOIN Base_Fiche ON Base_Tournee.Tournee=Base_Fiche.No_Tournee GROUP BY Base_Fiche.Date, Base_Tournee.Groupe HAVING (Month([Date])=4) AND (Year([Date])=2007))  AS SousRequete4 ON Base_Tournee.Groupe=SousRequete4.Groupe " & _
"GROUP BY Base_Tournee.Groupe;"

Ci-dessous représentation graphique de ma requete

Merci

[philben]

bonjour,

Plus simple et plus rapide à mon humble avis est de faire 2 requetes d'analyse croisée, une pour le km et une pour le temps qui s'appuient sur <Base_Fiche> avec la colonne month(Date) en pivot.

On peut ensuite regrouper ces 2 requetes en une pour retrouver votre requete initiale (Groupe, Mois, temps, Km sur la même ligne)

cordialement,

philippe

[Pago]

Bonjour, mais ensuite comment faire pour que j'obtienne le résultat suivant,par colonne...

Groupe Km_Janvier T_Janvier Km_Février T_Février, etc......

Merci


Au fait qqn peut-il me dire pourquoi la requête SQL devient lente?

[philben]

bonjour,

Tu imposes aux requetes d'analyse croisée de renvoyer chacune les 12 mois même si il n'y a rien dedans en utilisant : Month(Mois) In (1,2,3,4...)

ensuite on accumule les colonnes des 2 requetes dans la troisième en faisant une jointure sur le groupe, => 24 colonnes + groupe

Bon, ce n'est pas très propre mais c'est une solution en attendant mieux !

J'essaye de te donner ça tout à l'heure...

cordialement,

Philippe

[philben]

Voici les requetes :

1) une première requete enregistrée nommée <RqBaseCalculs>
elle ne conserve que les dates de l'année 2007

Code :

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
 
SELECT
 Base_Fiche.Date,
 Sum(Base_Fiche.TT) AS SommeDeTT,
 Sum([Km_Arrivee]-[Km_Depart]) AS Km_Total,
 Base_Tournee.Groupe
FROM
 Base_Tournee
INNER JOIN
 Base_Fiche
 ON
  Base_Tournee.Tournee=Base_Fiche.No_Tournee
GROUP BY
 Base_Fiche.Date,
 Base_Tournee.Groupe
HAVING
 (Year([Date])=2007)

Ensuite on enregistre les 2 requetes d'analyse croisée :
La première nommée <RqTemps> :

Code :

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

1
2
3
4
5
6
7
8
 
TRANSFORM AVG(T.SommeDeTT) AS MoyenneDeSommeDeTT
SELECT
 T.Groupe
FROM
 RqBaseCalculs AS T
GROUP BY
 T.Groupe PIVOT Month([Date]) IN (1,2,3,4,5,6,7,8,9,10,11,12);

La deuxième nommée <RqKmT> :

Code :

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

1
2
3
4
5
6
7
8
 
TRANSFORM AVG(T.Km_Total) AS MoyenneDeKm_Total
SELECT
 T.Groupe
FROM
 RqBaseCalculs AS T
GROUP BY
 T.Groupe PIVOT Month([Date]) IN (1,2,3,4,5,6,7,8,9,10,11,12);

et enfin la requete finale :

Code :

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
 
SELECT
 TK.Groupe,
 TT.[1],
 TK.[1],
 TT.[2],
 TK.[2],
 TT.[3],
 TK.[3],
 TT.[4],
 TK.[4],
 TT.[5],
 TK.[5],
 TT.[6],
 TK.[6],
 TT.[7],
 TK.[7],
 TT.[8],
 TK.[8],
 TT.[9],
 TK.[9],
 TT.[10],
 TK.[10],
 TT.[11],
 TK.[11],
 TT.[12],
 TK.[12]
FROM
 RqKmt AS TK
INNER JOIN
 RqTemps AS TT
 ON
  TK.Groupe = TT.Groupe;

Si la vitesse n'est pas bonne, on peut optimiser je pense :
- Interet de lier <Base_Tournee> dans la 1ère requete ?
- Ne peut-on pas calculer directement les moyennes par mois au lieu de calculer les sommes par jour ?

cordialement,

Philippe

[Pago]

Salut Philben,

j'ai suivi ton conseil et fait 2 requête analyse croisée comme cela

1ère requête

Code sql :

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

1
2
3
4
5
6
TRANSFORM Avg([Km_Arrivee]-[Km_Depart]) AS Expr1
SELECT Base_Fiche.Groupe
FROM Base_Fiche
WHERE (((Year([Date]))=[Forms]![MenuRecherche]![Annee].[value]))
GROUP BY Base_Fiche.Groupe
PIVOT Format([Date],"mmm") In ("janv","févr","mars","avr","mai","juin","juil","août","sept","oct","nov","déc;

2ème requête

Code sql :

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

1
2
3
4
5
6
TRANSFORM Avg(Base_Fiche.TT) AS MoyenneDeTT
SELECT Base_Fiche.Groupe
FROM Base_Fiche
WHERE (((Year([Date]))=[Forms]![MenuRecherche]![Annee].[value]))
GROUP BY Base_Fiche.Groupe
PIVOT Format([Date],"mmm") In ("janv","févr","mars","avr","mai","juin","juil","août","sept","oct","nov","déc");

et ensuite j'ai fait une requête utilisant ces 2 requêtes

Code sql :

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

1
2
SELECT RqtMoyKmAnnuelTournee.Groupe, RqtMoyKmAnnuelTournee.janv, RqtMoyTTAnnuelTournee.janv, RqtMoyKmAnnuelTournee.févr, RqtMoyTTAnnuelTournee.févr, RqtMoyKmAnnuelTournee.mars, RqtMoyTTAnnuelTournee.mars, RqtMoyKmAnnuelTournee.avr, RqtMoyTTAnnuelTournee.avr, RqtMoyKmAnnuelTournee.mai, RqtMoyTTAnnuelTournee.mai, RqtMoyKmAnnuelTournee.juin, RqtMoyTTAnnuelTournee.juin, RqtMoyKmAnnuelTournee.juil, RqtMoyTTAnnuelTournee.juil, RqtMoyKmAnnuelTournee.août, RqtMoyTTAnnuelTournee.août, RqtMoyKmAnnuelTournee.sept, RqtMoyTTAnnuelTournee.sept, RqtMoyKmAnnuelTournee.oct, RqtMoyTTAnnuelTournee.oct, RqtMoyKmAnnuelTournee.nov, RqtMoyTTAnnuelTournee.nov, RqtMoyKmAnnuelTournee.déc, RqtMoyTTAnnuelTournee.déc
FROM RqtMoyKmAnnuelTournee INNER JOIN RqtMoyTTAnnuelTournee ON RqtMoyKmAnnuelTournee.Groupe = RqtMoyTTAnnuelTournee.Groupe;

Dans ma solution je vois qu'il y a la clause WHERE et chez toi la clause HAVING qu'elle est la différence?


j'âi vu ta solution ,si je comprend bien ta solution est similaire sauf que je mets le paramêtre Année dans chacune des des requetes croisées

REMARQUE....Chapeau pour avoir créer de toute pièces ta solution uniquement par code SQL, moi j'ai créé mes requêtes avec l'assistant

BRAVO et merci

DEMANDE: QUE CONSEILLES-TU POUR ARRIVER A MAITRISER LE CODE SQL


Dans ton code tu mets AS MoyenneDeSommeDeTT

Code sql :

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

TRANSFORM AVG(T.SommeDeTT) AS MoyenneDeSommeDeTT

en regardant dans ta 3ème requête tu parles de TT ne serait-ce pas TT à la place de MoyenneDeSommeDeTT

j'essaie de comprendre ta solution merci

[philben]

re bonjour,

DEMANDE: QUE CONSEILLES-TU POUR ARRIVER A MAITRISER LE CODE SQL

c'est un langage comme un autre, il faut peut être commencer avec un petit bouquin orienté SQL Access car il ne respecte pas trop les normes mais surtout de la pratique !

Est-ce que c'est plus rapide avec tes nouvelles requetes ?

Philippe

[Pago]

Salut Philben,

le temps d'exécution est semblable je ne vois pas de différence.

ou est utilisé l'Alias TRANSFORM AVG(T.SommeDeTT) AS MoyenneDeSommeDeTT

est-il utile?


et peux-tu m'expliquer la différence entre WHERE et HAVING

merci

[philben]

le temps d'exécution est semblable je ne vois pas de différence.

il est semblable par rapport à ta première requete avec les X left join ?
ou par rapport à mes requetes ?

et peux-tu m'expliquer la différence entre WHERE et HAVING

Je pense qu'il vaut mieux faire des WHERE plutot que des having qui sont en faite des WHERE post regroupement donc a priori moins rapides.

A mon avis, tes requetes sont plus rapide que les miennes car tes requetes d'analyse croisée se font directement sur tes tables si je ne me trompe pas.

ou est utilisé l'Alias TRANSFORM AVG(T.SommeDeTT) AS MoyenneDeSommeDeTT

il n'est pas utilisé mais il faut le nommer.

Pour alléger ta dernière requete en taille, tu peux utiliser des alias :

Code :

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
 
SELECT
 R1.Groupe,
 R1.janv,
 R2.janv,
 R1.févr,
 R2.févr,
 R1.mars,
 R2.mars,
 R1.avr,
 R2.avr,
 R1.mai,
 R2.mai,
 R1.juin,
 R2.juin,
 R1.juil,
 R2.juil,
 R1.août,
 R2.août,
 R1.sept,
 R2.sept,
 R1.oct,
 R2.oct,
 R1.nov,
 R2.nov,
 R1.déc,
 R2.déc
FROM
 R1
INNER JOIN
 R2
 ON
  R1.Groupe = R2.Groupe;

Philippe

[Pago]

Salut Philben,

mes premières requêtes avec LEFT JOIN sont dès 4 imbrications non-utilisables,car il fallait env 30sec pour voir apparaître qqch donc 12 plus loin je n'ai même pas essayé, il est semblable avec les tiennes.
Maintenant la différence si il y en avait une viendrait du fait que le paramètre ANNEE dans ma requête vient d'un formulaire et qu'il va chercher l'information hors requête.

Bonnes Salutations

Pascal