Athos99, PHP et développement WEB

Un site consacré au développement web en php

Des tris et classements évolués avec mySql

Date: 
12 sep 2015

Le langage SQL permet de faire des requêtes évoluées de manière assez simple. Mais paradoxalement, certaines requêtes qui paraissent très simples ne sont pas simples à réaliser. 

Prenons un exemple tout simple, toutes les ventes d'un fabricant de couleur sont enregistrée par pays et couleurs dans une table mySql comme suit :

id pays couleur quantite
1 France vert 876
2 France rouge 84
3 France blanc 742
4 France noir 56
5 Suisse blanc 364
6 Suisse rouge 786
7 Suisse blanc 365
8 Belgique rouge 456
9 Belgique Jaune 762
10 Belgique blanc 234

 

Si vous voulez importer la table et ses données, voila le script. 

--
-- Structure de la table `vente`
--

CREATE TABLE IF NOT EXISTS `vente` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pays` varchar(64) COLLATE utf8_bin NOT NULL,
  `couleur` varchar(64) COLLATE utf8_bin NOT NULL,
  `quantite` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=11 ;

--
-- Contenu de la table `vente`
--

INSERT INTO `vente` (`id`, `pays`, `couleur`, `quantite`) VALUES
(1, 'France', 'vert', 876),
(2, 'France', 'rouge', 84),
(3, 'France', 'blanc', 742),
(4, 'France', 'noir', 56),
(5, 'Suisse', 'blanc', 364),
(6, 'Suisse', 'rouge', 786),
(7, 'Suisse', 'blanc', 365),
(8, 'Belgique', 'rouge', 456),
(9, 'Belgique', 'Jaune', 762),
(10, 'Belgique', 'blanc', 234);

 

Le service marketing aimerait connaître, pour chaque pays, quelle est la couleur la plus vendue.  Trop simple, il suffit de sortir la liste en groupant par pays et de prendre la valeur maximale des quantités vendues. 

Vous faites la requête suivante : 

SELECT pays, MAX(quantite) as max_quantite, couleur
FROM vente 
GROUP BY pays 
ORDER BY pays 

 

Résultat : 

couleur max_quantite pays
rouge 762 Belgique
vert 876 France
blanc 786 Suisse

 

Si la quantité et le pays sont justes, il y a un problème avec la couleur. La couleur ne correspond pas à la quantité la plus vendue, en fait pour la Belgique c'est le jaune et la Suisse c'est la couleur rouge. Seul le résultat pour la France est juste.

En fait, la couleur correspond au premier enregistrement pour un pays.

Vous pourriez développer un programme ou utiliser une procédure SQL pour obtenir les résultats. Mais vous êtes persuadé qu'il est possible d'obtenir ce résultat avec une seule requête SQL. En effet c'est possible....

La première idée, c'est de préparer la liste avant de faire de faire le regroupement. On peut trier la liste en fonction des quantité de manière décroissante, vu que couleur est assigné au premier enregistrement pour un pays. Pour cela, une sous requête est créée.  

SELECT pays, MAX(quantite) as max_quantite, couleur
FROM (SELECT * FROM vente ORDER BY quantite DESC) AS v
GROUP BY pays 
ORDER BY pays

 

Résultat : 

pays max_quantite couleur
Belgique 762 Jaune
France 876 vert
Suisse 786 rouge

 

La liste est correcte, la Belgique et la Suisse ont retrouvé leurs couleurs favorites !!!. Par contre cette méthode n'est pas recommandée, il n'y a pas de documentation sur le fait que le premier enregistrement rencontré soit assigné à couleur. Il n'y a pas de garantie que les prochaines versions de mySql aient le même comportement.

 Il faut repartir sur une autre solution....

L'idée est de regouper dnas une nouvelle colonne la quantité avec l'id de l'enregistrement. Je converti la quantité en un string de 10 caractères préfixé avec des 0 et on postfixe avec la valeur de l'index. Le caractère # est inséré pour faciliter la visibilité de la séparation entre les deux valeurs. Comme le tri se fait de manière alphanumérique, la liste peut être triée par quantité en utilisant un "ORDER BY quantite_id" 

SELECT
CONCAT(LPAD(CAST(quantite AS CHAR),10,'0'),'#',id) AS quantite_id,
quantite,
id,
couleur,
pays
FROM vente
ORDER BY QUANTITE_ID

 Résultat : 

QUANTITE_ID QUANTITE ID COULEUR PAYS
0000000056#4 56 4 noir France
0000000084#2 84 2 rouge France
0000000234#10 234 10 blanc Belgique
0000000364#5 364 5 blanc Suisse
0000000365#7 365 7 blanc Suisse
0000000456#8 456 8 rouge Belgique
0000000742#3 742 3 blanc France
0000000762#9 762 9 Jaune Belgique
0000000786#6 786 6 rouge Suisse
0000000876#1 876 1 vert France

 

Je peux faire un regroupement en fonction du pays et afficher la quantité la plus vendue avec l'indication de l'id de l'enregistrement. 

SELECT
max(CONCAT(LPAD(CAST(quantite AS CHAR),10,'0'),'#',id)) AS quantite_id, pays
FROM vente
GROUP BY pays

 

Résultat:

quantite_id pays
0000000762#9 Belgique
0000000876#1 France
0000000786#6 Suisse

 

Vous me direz que c'est bien joli, mais cela ne sert pas à grand-chose.....

Mais si je garde que la valeur de l'index en enlevant la quantité, j'ai la liste des id des enregistrement de la liste des couleurs les plus vendues par pays. Il suffit de joindre cette liste avec la table vente. 

SELECT v1.*
FROM vente AS v1
INNER JOIN 
( select substring(max(CONCAT(LPAD(CAST(quantite AS CHAR),10,'0'),'#',id)),12) AS id
FROM vente 
GROUP BY pays) as v2
ON v1.id = v2.id

 

Résultat:

id pays couleur quantite
9 Belgique Jaune 762
1 France vert 876
6 Suisse rouge 786

 

Le résultat est correcte et il est garanti de fonctionner sur les différentes versions de mySql. Cette méthode fonctionne aussi pour les autres système de base de donnée.

Tout content, vous transmettez votre liste au service marketing qui est très satisfait de votre travail.

Mais au bout de quelques jours, le service marketing voudrait connaître les deux couleurs les plus vendues dans chaque pays....

La méthode utilisée ne convient pas, il faut encore trouver une autre solution.

L'idée, relativement simple, est de trier la liste en groupant par pays et en classant la quantité de manière décroissante. On ajoute ajoute un nouveau champ classement qui est un compteur qui est incrémenté à chaque enregistrement mais remis à 1 quand le pays change. 

Avec MySql Il est possible d'utiliser des variables qui seront utilisées comme compteur mais c'est un peu compliqué et donne une syntaxe des plus étrange. La transaction suivant exploite cette propriété. 

SELECT id,pays,couleur,quantite,
@x:=IF(@precedent_pays=v.pays,@x+1,1) as classement, 
@precedent_pays:=v.pays as dummy1 
FROM vente as v  ,(
   SELECT 
    @x:=0 , 
    @precedent_pays:='' ) AS dummy2
order by pays,quantite DESC 

 

La sous-requête est utilisée pour initialiser les variables. La variable @x est le compteur et @precedent_pays la valeur du pays lu dans le dernier enregistrement.

L'instruction @x:=IF(@precdent_pays=v.pays,@x+1,1) as classement incrémente le compteur si le pays ne change pas sinon le compteur est remis à 1.

L'instruction @precedent_pays:=v.pays as dummy1 mémorise la pays courant.

 

Le résultat : 

id pays couleur quantite classement dummy1
9 Belgique Jaune 762 1 Belgique
8 Belgique rouge 456 2 Belgique
10 Belgique blanc 234 3 Belgique
1 France vert 876 1 France
3 France blanc 742 2 France
2 France rouge 84 3 France
4 France noir 56 4 France
6 Suisse rouge 786 1 Suisse
7 Suisse blanc 365 2 Suisse
5 Suisse blanc 364 3 Suisse

 

Maintenant il suffit de prendre les deux premières valeur pour chaque pays, c'est à dire que classement <=2 pour avoir le résultat final. On utilise une requête et une sous requête. 

SELECT pays,couleur, quantite, classement FROM 
(SELECT id,pays,couleur,quantite,
@x:=IF(@precedent_pays=v.pays,@x+1,1) as classement, 
@precedent_pays:=v.pays as dummy1 
FROM vente as v  ,(
   SELECT 
    @x:=0 , 
    @precedent_pays:='' ) AS dummy2
order by pays,quantite DESC 
) as dummy3 WHERE classement <= 2

 

Le résultat : 

pays couleur quantite classement
Belgique Jaune 762 1
Belgique rouge 456 2
France vert 876 1
France blanc 742 2
Suisse rouge 786 1
Suisse blanc 365 2

 

Conclusion

Comme on l'a vu, certaines demande simples de tri de liste peuvent être un vrai casse tête pour un développeur, il n'est pas facile de trouver les bonnes requêtes mySql.

 

Rubrique: 
Trucs et astuces
Tags: 
Sql
mySql
tri
classement