Ligne doublée dans résultat d'une requête

**vandman** · 11/05/2017, 11h01

Bonjour,
Je réalise la requête suivante:

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
34
35
36
37
38
39
40
41
42
SELECT rr.intitule AS Nom_Site
,rr.idsite
  ,cr.libelle AS Groupe_site
  ,CASE WHEN r2013.cptcred LIKE '7%' THEN 'X'
  ELSE NULL
  END AS "2013"
  ,CASE WHEN r2014.cptcred LIKE '7%' THEN 'X'
  ELSE NULL
  END AS "2014"
  ,CASE WHEN r2015.cptcred LIKE '7%' THEN 'X'
  ELSE NULL
  END AS "2015"
  ,CASE WHEN r2016.cptcred LIKE '7%' THEN 'X'
  ELSE NULL
  END AS "2016"
  ,CASE WHEN r2017.cptcred LIKE '7%' THEN 'X'
  ELSE NULL
  END AS "2017"
   ,CASE	WHEN r2013.cptcred LIKE '7%' THEN 'X'
		WHEN r2014.cptcred LIKE '7%' THEN 'X'
		WHEN r2015.cptcred LIKE '7%' THEN 'X'
		WHEN r2016.cptcred LIKE '7%' THEN 'X'
		WHEN r2017.cptcred LIKE '7%' THEN 'X'
		ELSE NULL
  END AS "RECETTES"
FROM l.rsite rr
  LEFT JOIN l.asso_cat_site acr
   ON rr.idsite = acr.idsite
    LEFT JOIN l.cat_site cr
     ON acr.id_cat_site = cr.id
  LEFT JOIN l.r2013mou r2013
   ON rr.idsite = r2013.idg_site AND r2013.cptcred LIKE '7%'
  LEFT JOIN l.r2014mou r2014
   ON rr.idsite = r2014.idg_site AND r2014.cptcred LIKE '7%'
  LEFT JOIN l.r2015mou r2015
   ON rr.idsite = r2015.idg_site AND r2015.cptcred LIKE '7%'
  LEFT JOIN l.r2016mou r2016
   ON rr.idsite = r2016.idg_site AND r2016.cptcred LIKE '7%'
  LEFT JOIN l.r2017mou r2017
   ON rr.idsite = r2017.idg_site AND r2017.cptcred LIKE '7%'
GROUP BY rr.intitule,rr.idsite, cr.libelle, r2013.cptcred, r2014.cptcred, r2015.cptcred, r2016.cptcred, r2017.cptcred
ORDER BY cr.libelle, rr.intitule

J'obtiens bien un résultat mais certaines lignes apparaissent plusieurs fois. Pourquoi?

Cordialement,
Vandman

**al1_24** · 11/05/2017, 11h11

Il y a certainement des différences entre les lignes qui te semblent apparaître plusieurs fois dans le résultat de la requête.
Essaye en utilisant les mêmes colonnes dans la clause ORDER BY que dans le GROUP BY, ces différences devraient être plus faciles à identifier.

**skuatamad** · 12/05/2017, 10h46

Je pense que vous souhaitez réaliser un pivot, Il faut donc aggréger les case :

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
SELECT rr.intitule AS Nom_Site
     , rr.idsite
     , cr.libelle AS Groupe_site
     , max(CASE WHEN r2013.cptcred LIKE '7%' THEN 'X' ELSE NULL END) AS "2013"
     , max(CASE WHEN r2014.cptcred LIKE '7%' THEN 'X' ELSE NULL END) AS "2014"
     , max(CASE WHEN r2015.cptcred LIKE '7%' THEN 'X' ELSE NULL END) AS "2015"
     , max(CASE WHEN r2016.cptcred LIKE '7%' THEN 'X' ELSE NULL END) AS "2016"
     , max(CASE WHEN r2017.cptcred LIKE '7%' THEN 'X' ELSE NULL END) AS "2017"
     , max(CASE	WHEN r2013.cptcred LIKE '7%' THEN 'X'
                WHEN r2014.cptcred LIKE '7%' THEN 'X'
		WHEN r2015.cptcred LIKE '7%' THEN 'X'
		WHEN r2016.cptcred LIKE '7%' THEN 'X'
		WHEN r2017.cptcred LIKE '7%' THEN 'X'
		ELSE NULL
            END) AS "RECETTES"
  FROM l.rsite rr
  LEFT JOIN l.asso_cat_site acr
    ON rr.idsite = acr.idsite
  LEFT JOIN l.cat_site cr
    ON acr.id_cat_site = cr.id
  LEFT JOIN l.r2013mou r2013
    ON rr.idsite = r2013.idg_site AND r2013.cptcred LIKE '7%'
  LEFT JOIN l.r2014mou r2014
    ON rr.idsite = r2014.idg_site AND r2014.cptcred LIKE '7%'
  LEFT JOIN l.r2015mou r2015
    ON rr.idsite = r2015.idg_site AND r2015.cptcred LIKE '7%'
  LEFT JOIN l.r2016mou r2016
    ON rr.idsite = r2016.idg_site AND r2016.cptcred LIKE '7%'
  LEFT JOIN l.r2017mou r2017
    ON rr.idsite = r2017.idg_site AND r2017.cptcred LIKE '7%'
 GROUP BY rr.intitule,rr.idsite, cr.libelle
 ORDER BY cr.libelle, rr.intitule

**vandman** · 12/05/2017, 18h47

Bonjour,

Oui, c'est bien un pivot que je veux faire. Il semble que ca fonctionne bien. Cependant, ca prend vraiment beaucoup de temps.
Y-a-t-il un moyen d'optimiser l'exécution de la requette?

Cordialement,
Vandman

**krysztophe** · 15/05/2017, 16h38

En vitesse : il y a peut-être quelque chose à voir de la fonction crosstab (cf https://www.postgresql.org/docs/9.1/...tablefunc.html, chap F.41.1.2., et l'exemple).

**SQLpro** · 16/05/2017, 15h12

Envoyé par vandman

Bonjour,

Oui, c'est bien un pivot que je veux faire. Il semble que ca fonctionne bien. Cependant, ca prend vraiment beaucoup de temps.
Y-a-t-il un moyen d'optimiser l'exécution de la requette?

Cordialement,
Vandman

PIVOT est une fonction cosmétique qui n'a pas sa place sur un SGBDR. Ce genre de "truc" a été rajouté à la demande (imbécile) des utilisateurs dans différents SGBDR.... Comme un SGBDR n'est pas fait pour faire de la cosmétique (il n'est ni conçu ni optimisé pour cela) ça prendra toujours beaucoup de temps et il n'y a aucun moyen d'optimiser cela !

Solution : faire cela avec des outils de présentation spécialisés : :

Excel
PowerPivot
...

ou une base de données décisionnelle ;

SSAS de Microsoft
TerraData
...

Qui disposent à la fois de langages spécialisés pour ce faire (exemple langage MDX) et d'outils de présentation.

A +

**krysztophe** · 16/05/2017, 18h28

Envoyé par SQLpro

PIVOT est une fonction cosmétique qui n'a pas sa place sur un SGBDR. Ce genre de "truc" a été rajouté à la demande (imbécile) des utilisateurs dans différents SGBDR..

Ouais, sans doute les mêmes que ceux qui ont imposé ORDER BY : tiens, encore un ordre purement cosmétique alors qu'il est serait si simple de claquer deux semaines et 20 k€ à monter un ETL, un datawarehouse, du SSIS/SSAS, et de mettre en danger sa santé mentale à apprendre le MDX pour trier.

**krysztophe** · 16/05/2017, 18h42

Envoyé par vandman

Bonjour,
Oui, c'est bien un pivot que je veux faire. Il semble que ca fonctionne bien. Cependant, ca prend vraiment beaucoup de temps.
Y-a-t-il un moyen d'optimiser l'exécution de la requette?

Ce qui prend du temps ce sont très probablement les jointures externes, pas les MAX.

Tout est correctement indexé ?
Peut-être ajouter un index partiel sur " r2013mou (idg_site) WHERE cptcred LIKE '7%' " ? s'il est vraiment discriminant ?
Voire sur "(idg_site,cptcred) WHERE cptcred LIKE '7%' " en espérant se limiter à lire l'index.

Si le problème est encore d'actualité, vous pouvez copier-coller le plan d'exécution (EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT... ) sur https://explain.depesz.com/ et poster le lien ici, ainsi que le résultat de \d+ pour chaque table impliquée.

**aieeeuuuuu** · 17/05/2017, 10h08

Bonjour

Que donne ceci ?

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
 
SELECT rr.intitule AS Nom_Site
     , rr.idsite
     , cr.libelle AS Groupe_site
     , max(CASE WHEN Annee = 2013 THEN 'X' ELSE NULL END) AS "2013"
     , max(CASE WHEN Annee = 2014 THEN 'X' ELSE NULL END) AS "2014"
     , max(CASE WHEN Annee = 2015 THEN 'X' ELSE NULL END) AS "2015"
     , max(CASE WHEN Annee = 2016 THEN 'X' ELSE NULL END) AS "2016"
     , max(CASE WHEN Annee = 2017 THEN 'X' ELSE NULL END) AS "2017"
     , max(CASE	WHEN Annee IS NOT NULL THEN 'X' ELSE NULL     END) AS "RECETTES"
  FROM l.rsite rr
  LEFT JOIN l.asso_cat_site acr
    ON rr.idsite = acr.idsite
  LEFT JOIN l.cat_site cr
    ON acr.id_cat_site = cr.id
  LEFT JOIN (
	SELECT idg_site, 2013 as Annee FROM l.r2013mou  WHERE cptcred LIKE '7%'
    UNION 
	SELECT idg_site, 2014 FROM l.r2014mou  WHERE cptcred LIKE '7%'
	UNION 	
	SELECT idg_site, 2015 FROM l.r2015mou  WHERE cptcred LIKE '7%'
	UNION 	
	SELECT idg_site, 2016 FROM l.r2016mou  WHERE cptcred LIKE '7%'
	UNION 	
	SELECT idg_site, 2017 FROM l.r2017mou  WHERE cptcred LIKE '7%'
	) r
	ON rr.idsite = r.idg_site
 GROUP BY rr.intitule,rr.idsite, cr.libelle
 ORDER BY cr.libelle, rr.intitule

Effectivement, la question des index disponibles se pose...

**krysztophe** · 17/05/2017, 10h45

Envoyé par aieeeuuuuu

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT rr.intitule AS Nom_Site
     ,...
  LEFT JOIN (
	SELECT idg_site, 2013 as Annee FROM l.r2013mou  WHERE cptcred LIKE '7%'
    UNION 
	SELECT idg_site, 2014 FROM l.r2014mou  WHERE cptcred LIKE '7%'
	UNION 	
...'
	) r
	ON rr.idsite = r.idg_site
...

Attention, un UNION implique un DISTINCT, et avec une volumétrie non triviale, ça va faire un beau tri sur disque. Il faut pour le moins un UNION ALL et je doute fort que cela améliore grand chose.

**SQLpro** · 17/05/2017, 13h17

Envoyé par krysztophe

Ouais, sans doute les mêmes que ceux qui ont imposé ORDER BY : tiens, encore un ordre purement cosmétique alors qu'il est serait si simple de claquer deux semaines et 20 k€ à monter un ETL, un datawarehouse, du SSIS/SSAS, et de mettre en danger sa santé mentale à apprendre le MDX pour trier.

L'order by est rendu nécessaire par le fait qu'il n'existe pas d'ordre de restitution par défaut des données et que le SGBDR peut utiliser un index pour faire ce tri. Ce n'est pas le cas de PIVOT qui ne peut jamais utiliser efficacement le moindre index du fait que l'opération n'est pas "sargable".

A +

**aieeeuuuuu** · 17/05/2017, 14h49

Attention, un UNION implique un DISTINCT, et avec une volumétrie non triviale, ça va faire un beau tri sur disque. Il faut pour le moins un UNION ALL et je doute fort que cela améliore grand chose.

L'un dans l'autre, que ce soit pour le UNION de ma requête ou pour le GROUP BY de la requête initiale, tri il y aura.

Et si la volumétrie n'est pas triviale, disons arbitrairement 1000 lignes par année, ça donne pour ma requête, un tri sur 5000 lignes. Dans la requête initiale, il s'agira d'un tri sur un million de milliards de lignes...
En effet, dans la requête initiale, il y a un pseudo produit cartésien entre les différentes tables des années.

Une autre approche serait de faire les regroupements dans des sous requêtes pour chaque table d'année.

**krysztophe** · 17/05/2017, 21h13

Envoyé par aieeeuuuuu

L'un dans l'autre, que ce soit pour le UNION de ma requête ou pour le GROUP BY de la requête initiale, tri il y aura.

Non. Il n'y a pas besoin de faire un tri complet des données pour un MAX/GROUP BY. Il suffit de parcourir les données et de noter les différentes valeurs rencontrées (la clé du GROUP BY, soit une poignée de lignes, et les valeurs successives du MAX rencontrées). Alors qu'un UNION va forcer le chargement en mémoire et le tri de toutes les lignes des données sources.

<Edit: zappé ici une bêtise ; oui il y a bien un produit cartésien masqué et dangereux.>

[/QUOTE]Une autre approche serait de faire les regroupements dans des sous requêtes pour chaque table d'année.[/QUOTE]

J'aime bien aussi ; ou un sous-select pour chaque année à la place de chaque attribut, puisqu'après tout la recherche du MAX (ou de la simple présence, si je comprends bien la requête originale) d'une année ne dépend que du idsite.

Pour départager il faudrait aussi le besoin originel, le schéma, une idée des données...

**krysztophe** · 18/05/2017, 10h11

Envoyé par SQLpro

L'order by est rendu nécessaire par le fait qu'il n'existe pas d'ordre de restitution par défaut des données et que le SGBDR peut utiliser un index pour faire ce tri. Ce n'est pas le cas de PIVOT qui ne peut jamais utiliser efficacement le moindre index du fait que l'opération n'est pas "sargable".

Je doute que pivoter sans index les quelques ko (dans le pire des cas) renvoyés par la requête de vandman mettent le serveur à genoux. En tout cas ce n'est pas son problème.

Ligne doublée dans résultat d'une requête

Requêtes PostgreSQL

Discussions similaires

Partager

Partager