Select avec group by et classement

10 réponses

Auteur	Message
krucial \| Administrateur Inscrit le : 09/03/2005	# Le 12/12/2011 à 10:28 Salut J'ai un petit problème SQL. J'ai des entrées : ENSEIGNE A, MAGASIN A1, CA = 250€ ENSEIGNE A, MAGASIN A2, CA = 150€ ENSEIGNE A, MAGASIN A3, CA = 550€ ENSEIGNE A, MAGASIN A4, CA = 650€ ENSEIGNE B, MAGASIN B1, CA = 150€ ENSEIGNE B, MAGASIN B2, CA = 350€ ENSEIGNE B, MAGASIN B3, CA = 550€ ENSEIGNE B, MAGASIN B4, CA = 450€ Je veux afficher les magasins classés par CA, mais un seul magasin par enseigne : ENSEIGNE A, MAGASIN A4, CA = 650€ ENSEIGNE B, MAGASIN B3, CA = 550€ Le probleme, c'est que si je fais : SELECT * FROM magasin ORDER BY ca DESC, GROUP BY enseigne Et bien, ce qui va sortir du SELECT ne va pas forcement etre le magasin qui fait le plus de CA ... Comment faire ? Imbriquer plusieurs requetes ? Merci pour vos idées. JC - Mes sites \| Affiliation devis travaux \| Cotes voitures anciennes
TomPascal \| Pascal Membre Inscrit le : 08/11/2006	# Le 12/12/2011 à 10:41 Bonjour JC, Tu peux effectivement surement utiliser des requêtes imbriquées... mais il y a aussi la méthode dite du "MAX-CONCAT" qui n'est pas forcément ultra connue mais assez élégante. Toutes les informations ici : http://dev.mysql.com/doc/refman/5.0/fr/example-max... Ce qui donne simplement dans ton cas : SELECT enseigne, SUBSTRING( MAX( CONCAT(LPAD(ca,10,'0'),magasin) ), 11) AS magasin, 0.00+LEFT( MAX( CONCAT(LPAD(ca,10,'0'),magasin) ), 10) AS ca FROM magasin GROUP BY enseigne ORDER BY ca DESC ==> enseigne magasin ca A A4 650 B B3 550 Archipel Web Conception, réalisation, référencement de sites internet.
krucial \| Jean Christophe Administrateur Inscrit le : 09/03/2005	# Le 12/12/2011 à 15:03 Ok merci. C'est enorme comme requete. Un peu chiant a utiliser, mais enorme. JC - Mes sites \| Affiliation devis travaux \| Cotes voitures anciennes
Salemioche \| Nicolas Membre Inscrit le : 26/12/2008	# Le 13/12/2011 à 07:38 TomPascal a dit : http://dev.mysql.com/doc/refman/5.0/fr/example-max... Oui, mais en utilisant une astuce inefficace que j'appelle ``astuce du MAX-CONCAT''
Bool \| Olivier Modérateur Inscrit le : 09/05/2005	# Le 13/12/2011 à 08:00 C'est surtout l'adjectif élégante qui m'a surpris daevel : infogérance et conseil \|\| moi
TomPascal \| Pascal Membre Inscrit le : 08/11/2006	# Le 13/12/2011 à 11:32 Bonjour, élégante j'aurais du mettre entre guillemets en effet ; ça ne colle pas avec le fait que la requête est moche et longue... MAIS élégante dans le sens où ça permet d'obtenir le résultat souhaité en faisant une seule requête (sans créer de table temporaire ou faire de requêtes imbriquées). Par contre, je ne comprend pas pourquoi "inefficace" ? ça fonctionne non ? et questions perfs ça ne doit pas être pire que les autres méthodes, si ? je veux bien ton retour Salemioche, peut être que je devrais toujours éviter cette façon de faire (si c'est inefficace)... mais je ne vois pas pourquoi (à part éventuellement que ça peut être jugé comme "moche", on est d'accord). Archipel Web Conception, réalisation, référencement de sites internet.
MathieuC \| Mathieu Modérateur Inscrit le : 15/07/2005	# Le 13/12/2011 à 11:41 Vu que cette requête n'a AUCUN moyen d'être optimisée, sur une table avec beaucoup d'enregistrements (millions) ça va ramer a bloc Les sous requête sont souvent très simple et donc facilement ultra-optimisées, donc très rapides. Mais comme toujours, la "bonne" solution dépends principalement des contraintes de travail, apparemment dans ton cas, c'est la "bonne" solution
Bool \| Olivier Modérateur Inscrit le : 09/05/2005	# Le 13/12/2011 à 11:46 TomPascal : justement, selon les cas la requête imbriquée pourra peut-être utiliser des indexes, tandis que le concat n'a aucune chance de pouvoir en tirer parti. Les requêtes imbriquées, ça permet généralement au moteur d'optimiser ça, et ça peut être très performant. Mais comme le rappel Mathieu, ça dépend évidement de tes données derrière. Bref cette "astuce" du concat était surtout de rigueur à l'époque où MySQL ne savait pas faire de requête imbriquée. Mais depuis MySQL 4.1, j'en vois pas trop l'intérêt perso. daevel : infogérance et conseil \|\| moi
TomPascal \| Pascal Membre Inscrit le : 08/11/2006	# Le 13/12/2011 à 12:00 Tout à fait, d'accord. Et en effet, c'est une méthode que j'avais découvert/utilisé/bookmarké à l'époque où mysql ne permettait pas de faire des requêtes imbriquées (j'ai tendance à continuer à les éviter lorsque c'est possible). Mais vous avez raison sur le fait qu'on ne peut pas utiliser les éventuels indexs... tout dépend de la taille de la table sur laquelle on a besoin de ce résultat. Pour une petite table de quelques dizaines d'enregistrements, pourquoi pas.. Sur des grosses tables c'est sûr que ce ne sera pas bon du tout ! Merci pour ces retours plus complets . Archipel Web Conception, réalisation, référencement de sites internet.
bhamp0 \| Benjamin Membre Inscrit le : 09/05/2005	# Le 13/12/2011 à 14:31 Etape 1 : créer la bonne vue. CREATE VIEW v_max_ca_by_enseigne AS SELECT enseigne, MAX(ca) AS max_ca FROM magasin GROUP BY enseigne; Etape 2 : l'utiliser. SELECT m.enseigne, m.magasin, v.max_ca FROM v_max_ca_by_enseigne v LEFT JOIN magasin m ON v.enseigne = m.enseigne AND m.ca = v.max_ca Le dernier "LEFT JOIN" sert à n'avoir qu'une seule ligne quand deux magasins d'une même enseigne ont le même CA max. Quand on voit c'qu'on voit, et qu'on entend c'qu'on entend, on a raison d'penser c'qu'on pense !
Julgates \| Julien Administrateur Inscrit le : 09/03/2005	# Le 13/12/2011 à 15:12 Ah ca fait longtemps qu'on avait pas eu un bon post techos sur ce forum, merci Krucial ! Shopping Time Network - Founder / CTO

Auteur

Message

krucial |
Administrateur

Inscrit le : 09/03/2005

# Le 12/12/2011 à 10:28

Salut

J'ai un petit problème SQL.

J'ai des entrées :

ENSEIGNE A, MAGASIN A1, CA = 250€
ENSEIGNE A, MAGASIN A2, CA = 150€
ENSEIGNE A, MAGASIN A3, CA = 550€
ENSEIGNE A, MAGASIN A4, CA = 650€
ENSEIGNE B, MAGASIN B1, CA = 150€
ENSEIGNE B, MAGASIN B2, CA = 350€
ENSEIGNE B, MAGASIN B3, CA = 550€
ENSEIGNE B, MAGASIN B4, CA = 450€

Je veux afficher les magasins classés par CA, mais un seul magasin par enseigne :

ENSEIGNE A, MAGASIN A4, CA = 650€
ENSEIGNE B, MAGASIN B3, CA = 550€

Le probleme, c'est que si je fais :

SELECT * FROM magasin ORDER BY ca DESC, GROUP BY enseigne

Et bien, ce qui va sortir du SELECT ne va pas forcement etre le magasin qui fait le plus de CA ... Comment faire ? Imbriquer plusieurs requetes ?

Merci pour vos idées.

JC - Mes sites | Affiliation devis travaux | Cotes voitures anciennes

TomPascal | Pascal
Membre

Inscrit le : 08/11/2006

# Le 12/12/2011 à 10:41

Bonjour JC,

Tu peux effectivement surement utiliser des requêtes imbriquées... mais il y a aussi la méthode dite du "MAX-CONCAT" qui n'est pas forcément ultra connue mais assez élégante. Toutes les informations ici : http://dev.mysql.com/doc/refman/5.0/fr/example-max...

Ce qui donne simplement dans ton cas :

SELECT enseigne,

       SUBSTRING( MAX( CONCAT(LPAD(ca,10,'0'),magasin) ), 11) AS magasin,

  0.00+LEFT( MAX( CONCAT(LPAD(ca,10,'0'),magasin) ), 10) AS ca

FROM   magasin

GROUP BY enseigne

ORDER BY ca DESC

==>



enseigne 	magasin 	ca

A 	A4 	650

B 	B3 	550

Archipel Web Conception, réalisation, référencement de sites internet.

krucial | Jean Christophe
Administrateur

Inscrit le : 09/03/2005

# Le 12/12/2011 à 15:03

Ok merci. C'est enorme comme requete. Un peu chiant a utiliser, mais enorme.

JC - Mes sites | Affiliation devis travaux | Cotes voitures anciennes

Salemioche | Nicolas
Membre

Inscrit le : 26/12/2008

# Le 13/12/2011 à 07:38

TomPascal a dit :
http://dev.mysql.com/doc/refman/5.0/fr/example-max...

Oui, mais en utilisant une astuce inefficace que j'appelle ``astuce du MAX-CONCAT''

Bool | Olivier
Modérateur

Inscrit le : 09/05/2005

# Le 13/12/2011 à 08:00

C'est surtout l'adjectif élégante qui m'a surpris

daevel : infogérance et conseil || moi

TomPascal | Pascal
Membre

Inscrit le : 08/11/2006

# Le 13/12/2011 à 11:32

Bonjour,

élégante j'aurais du mettre entre guillemets en effet ; ça ne colle pas avec le fait que la requête est moche et longue... MAIS élégante dans le sens où ça permet d'obtenir le résultat souhaité en faisant une seule requête (sans créer de table temporaire ou faire de requêtes imbriquées).

Par contre, je ne comprend pas pourquoi "inefficace" ? ça fonctionne non ? et questions perfs ça ne doit pas être pire que les autres méthodes, si ? je veux bien ton retour Salemioche, peut être que je devrais toujours éviter cette façon de faire (si c'est inefficace)... mais je ne vois pas pourquoi (à part éventuellement que ça peut être jugé comme "moche", on est d'accord).

Archipel Web Conception, réalisation, référencement de sites internet.

MathieuC | Mathieu
Modérateur

Inscrit le : 15/07/2005

# Le 13/12/2011 à 11:41

Vu que cette requête n'a AUCUN moyen d'être optimisée, sur une table avec beaucoup d'enregistrements (millions) ça va ramer a bloc

Les sous requête sont souvent très simple et donc facilement ultra-optimisées, donc très rapides.

Mais comme toujours, la "bonne" solution dépends principalement des contraintes de travail, apparemment dans ton cas, c'est la "bonne" solution

Bool | Olivier
Modérateur

Inscrit le : 09/05/2005

# Le 13/12/2011 à 11:46

TomPascal : justement, selon les cas la requête imbriquée pourra peut-être utiliser des indexes, tandis que le concat n'a aucune chance de pouvoir en tirer parti.
Les requêtes imbriquées, ça permet généralement au moteur d'optimiser ça, et ça peut être très performant.

Mais comme le rappel Mathieu, ça dépend évidement de tes données derrière.

Bref cette "astuce" du concat était surtout de rigueur à l'époque où MySQL ne savait pas faire de requête imbriquée. Mais depuis MySQL 4.1, j'en vois pas trop l'intérêt perso.

daevel : infogérance et conseil || moi

TomPascal | Pascal
Membre

Inscrit le : 08/11/2006

# Le 13/12/2011 à 12:00

Tout à fait, d'accord.

Et en effet, c'est une méthode que j'avais découvert/utilisé/bookmarké à l'époque où mysql ne permettait pas de faire des requêtes imbriquées (j'ai tendance à continuer à les éviter lorsque c'est possible).

Mais vous avez raison sur le fait qu'on ne peut pas utiliser les éventuels indexs... tout dépend de la taille de la table sur laquelle on a besoin de ce résultat. Pour une petite table de quelques dizaines d'enregistrements, pourquoi pas.. Sur des grosses tables c'est sûr que ce ne sera pas bon du tout !

Merci pour ces retours plus complets .

Archipel Web Conception, réalisation, référencement de sites internet.

bhamp0 | Benjamin
Membre

Inscrit le : 09/05/2005

# Le 13/12/2011 à 14:31

Etape 1 : créer la bonne vue.

CREATE VIEW v_max_ca_by_enseigne AS

SELECT enseigne, MAX(ca) AS max_ca FROM magasin GROUP BY enseigne;

Etape 2 : l'utiliser.

SELECT m.enseigne, m.magasin, v.max_ca

FROM v_max_ca_by_enseigne v

LEFT JOIN magasin m ON v.enseigne = m.enseigne AND m.ca = v.max_ca

Le dernier "LEFT JOIN" sert à n'avoir qu'une seule ligne quand deux magasins d'une même enseigne ont le même CA max.

Quand on voit c'qu'on voit, et qu'on entend c'qu'on entend, on a raison d'penser c'qu'on pense !

Julgates | Julien
Administrateur

Inscrit le : 09/03/2005

# Le 13/12/2011 à 15:12

Ah ca fait longtemps qu'on avait pas eu un bon post techos sur ce forum, merci Krucial !

Shopping Time Network - Founder / CTO

Répondre

Vous ne pouvez pas participer au forum, car votre inscription n'a pas été validée. Pour vous faire valider en tant que Membre, cliquez ici.