1. #1
    Membre actif

    Homme Profil pro
    Consultant informatique
    Inscrit en
    septembre 2011
    Messages
    323
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Industrie

    Informations forums :
    Inscription : septembre 2011
    Messages : 323
    Points : 241
    Points
    241

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

    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

  2. #2
    Modérateur
    Avatar de al1_24
    Homme Profil pro
    Ingénieur d'études décisionnel
    Inscrit en
    mai 2002
    Messages
    7 463
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 56
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 7 463
    Points : 22 659
    Points
    22 659

    Par défaut

    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.
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  3. #3
    Expert confirmé
    Profil pro
    Inscrit en
    août 2008
    Messages
    2 623
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : août 2008
    Messages : 2 623
    Points : 5 064
    Points
    5 064

    Par défaut

    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

  4. #4
    Membre actif

    Homme Profil pro
    Consultant informatique
    Inscrit en
    septembre 2011
    Messages
    323
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Industrie

    Informations forums :
    Inscription : septembre 2011
    Messages : 323
    Points : 241
    Points
    241

    Par défaut

    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

  5. #5
    Futur Membre du Club
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    mai 2017
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : mai 2017
    Messages : 7
    Points : 5
    Points
    5

    Par défaut

    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).

  6. #6
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 385
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 385
    Points : 40 291
    Points
    40 291
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par vandman Voir le message
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  7. #7
    Futur Membre du Club
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    mai 2017
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : mai 2017
    Messages : 7
    Points : 5
    Points
    5

    Par défaut

    Citation Envoyé par SQLpro Voir le message
    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.

  8. #8
    Futur Membre du Club
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    mai 2017
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : mai 2017
    Messages : 7
    Points : 5
    Points
    5

    Par défaut

    Citation Envoyé par vandman Voir le message
    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.

  9. #9
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    4 614
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 4 614
    Points : 9 351
    Points
    9 351

    Par défaut

    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...

  10. #10
    Futur Membre du Club
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    mai 2017
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : mai 2017
    Messages : 7
    Points : 5
    Points
    5

    Par défaut

    Citation Envoyé par aieeeuuuuu Voir le message
    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.

  11. #11
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 385
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 385
    Points : 40 291
    Points
    40 291
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par krysztophe Voir le message
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  12. #12
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    4 614
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 4 614
    Points : 9 351
    Points
    9 351

    Par défaut

    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.

  13. #13
    Futur Membre du Club
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    mai 2017
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : mai 2017
    Messages : 7
    Points : 5
    Points
    5

    Par défaut

    Citation Envoyé par aieeeuuuuu Voir le message
    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...

  14. #14
    Futur Membre du Club
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    mai 2017
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : mai 2017
    Messages : 7
    Points : 5
    Points
    5

    Par défaut

    Citation Envoyé par SQLpro Voir le message
    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.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Calcul ligne par ligne sur le résultat d'une requête
    Par CanardJM dans le forum Langage SQL
    Réponses: 5
    Dernier message: 15/04/2008, 14h06
  2. générer des n° de lignes dans le résultat d'une requête
    Par karimspace dans le forum Requêtes et SQL.
    Réponses: 5
    Dernier message: 13/09/2007, 21h33
  3. Enregistrer le résultat d'une requête dans un fichier Excel
    Par Isa31 dans le forum Bases de données
    Réponses: 4
    Dernier message: 24/05/2005, 15h31
  4. Résultat d'une requête dans une variable...
    Par Hoegaarden dans le forum Bases de données
    Réponses: 6
    Dernier message: 28/04/2004, 14h51

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo