SQL - Ordre de tri de données groupées

⏲️ ~13 min de lecture

Publié le par Matéo
Astuces Développement SQL

Introduction #

Un peu de contexte… #

Parmi les fonctionnalités d’un site E-Commerce, on entend souvent parler de produits configurables, de relation parent-enfant entre les produits, de déclinaisons. Tant de termes pour décrire la même chose : la capacité pour le E-Commerçant de décliner un produit possédant un jeu de caractéristiques communes autour d’un ou plusieurs attribut(s) déterminant(s). Cela est utile par exemple dans le cas où l’on souhaite vendre des vêtements disponibles en plusieurs tailles. Ainsi, on présentera un produit générique à l’utilisateur, puis on lui permettra de choisir la configuration qui lui convient au moment de l’ajout au panier.

Seulement, une des problématiques qui survient lorsque l’on utilise cette fonctionnalité est le calcul du prix à afficher sur le produit générique. La réponse la plus courante est la suivante : on prend le prix de la déclinaison possédant le prix le plus bas.

Solution technique #

Parlons maintenant un peu de la façon de récupérer cette information. Dans cet article, on va demander le prix à l’aide d’une requête à la base de données dont voici le contenu.

+----+--------+--------+----------+--------+------+
| id | parent | type   | nom      | taille | prix |
+----+--------+--------+----------+--------+------+
|  1 |   NULL | parent | t-shirt  | NULL   | NULL |
|  2 |      1 | enfant | NULL     | L      |   11 |
|  3 |      1 | enfant | NULL     | S      |    9 |
|  4 |      1 | enfant | NULL     | M      |   10 |
|  5 |   NULL | parent | pantalon | NULL   | NULL |
|  6 |      5 | enfant | NULL     | L      |   21 |
|  7 |      5 | enfant | NULL     | S      |   19 |
|  8 |      5 | enfant | NULL     | M      |   20 |
+----+--------+--------+----------+--------+------+

Le but du jeu est maintenant de déterminer la requête qui nous renverra une liste des produits parent avec leur prix minimum. On note que la base de données est initialisée avec des entrées qui ne sont pas insérées dans l’ordre croissant de leur prix. C’est très important pour reproduire les conditions qui mèneront aux constats suivants.

MariaDB 10.4 #

Testons d’abord notre opération avec MariaDB 10.4. Une première approche consisterait à tenter de grouper les résultats en s’assurant qu’ils soient déjà bien triés. L’idée serait donc de construire une table intermédiaire (à l’intérieur du FROM) qui soit triée par prix croissant, puis de grouper les enregistrements par parent. Ainsi, on essaye de faire en sorte que le premier produit de chaque parent soit le moins cher.

SELECT parent, prix
FROM (
  SELECT parent, prix
  FROM produits
  WHERE parent IS NOT NULL
  ORDER BY prix ASC
) AS sub
GROUP BY parent
ORDER BY prix;

+--------+------+
| parent | prix |
+--------+------+
|      1 |   11 |
|      5 |   21 |
+--------+------+

Cela ne fonctionne pas du tout. Les prix sont renvoyés dans l’ordre dans lequel ils ont été insérés dans la base de données. En effet, MariaDB ne prend pas en compte l’ordre d’une sous-requête non limitée. Réessayons.

SELECT parent, prix
FROM (
  SELECT parent, prix
  FROM produits
  WHERE parent IS NOT NULL
  ORDER BY prix ASC
  LIMIT 10
) AS sub
GROUP BY parent
ORDER BY prix;

+--------+------+
| parent | prix |
+--------+------+
|      1 |    9 |
|      5 |   19 |
+--------+------+

C’est mieux, mais il reste un problème. Les approches ci-dessus ne sont pas vraiment valides car le prix n’est pas une agrégation.

On appelle agrégation le résultat d’une fonction retournant une valeur unique à partir de plusieurs tuples d’entrée. La fonction d’agrégation la plus connue est la fonction SUM() qui renvoie la somme des valeurs de tous les enregistrements pour une colonne donnée. Lors de l’utilisation d’un GROUP BY, il n’est normalement possible que de sélectionner la colonne groupée ou des valeurs calculées à l’aide de fonctions d’agrégations.

MariaDB accepte cependant de retourner un résultat non agrégé en considérant que la valeur d’une telle colonne sera la première valeur rencontrée dans le sous-ensemble groupé. Ce n’est pas conseillé car nous allons le voir : les implémentations n’ont pas tous le même comportement sur ce point. Oublions les mauvaises pratiques et tâchons maintenant de travailler uniquement avec des agrégations.

SELECT p.id AS parent, MIN(sub.prix) AS prix
FROM produits p
INNER JOIN (
  SELECT MIN(prix) AS prix, parent
  FROM produits
  GROUP BY parent
) AS sub
ON sub.parent = p.id
GROUP by parent
ORDER by prix;

+--------+------+
| parent | prix |
+--------+------+
|      1 |    9 |
|      5 |   19 |
+--------+------+

Cette fois, tout semble bon. Les experts du benchmarking constateront également une nette amélioration. Nous avons donc trouvé notre requête gagnante.

MySQL 8.0 #

Voyons maintenant comment la dernière version en date de MySQL se comporte dans les mêmes conditions.

SELECT parent, prix
FROM (
  SELECT parent, prix
  FROM produits
  WHERE parent IS NOT NULL
  ORDER BY prix ASC
) AS sub
GROUP BY parent
ORDER BY prix;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY
clause and contains nonaggregated column 'sub.prix' which is not
functionally dependent on columns in GROUP BY clause; this is
incompatible with sql_mode=only_full_group_by

On constate tout de suite que ce SGBD est moins permissif sur ce cas précis. Étonnant, quand on connaît les habituels arguments en faveur de MariaDB. Essayons avec le mode ONLY_FULL_GROUP_BY désactivé.

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

SELECT parent, prix
FROM (
  SELECT parent, prix
  FROM produits
  WHERE parent IS NOT NULL
  ORDER BY prix ASC
) AS sub
GROUP BY parent
ORDER BY prix;

+--------+------+
| parent | prix |
+--------+------+
|      1 |   11 |
|      5 |   21 |
+--------+------+

Cette fois la requête est bien exécutée mais le résultat est incorrect. Serait-ce un problème de limitation des résultats comme chez le concurrent ?

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

SELECT parent, prix
FROM (
  SELECT parent, prix
  FROM produits
  WHERE parent IS NOT NULL
  ORDER BY prix ASC
  LIMIT 10
) AS sub
GROUP BY parent
ORDER BY prix;

+--------+------+
| parent | prix |
+--------+------+
|      1 |    9 |
|      5 |   19 |
+--------+------+

Bingo ! Qu’en est-il de la requête finale ? Cette fois, pas besoin d’autoriser les colonnes qui ne sont pas des agrégats en modifiant l’option sql_mode.

SELECT p.id AS parent, MIN(sub.prix) AS prix
FROM produits p
INNER JOIN (
  SELECT MIN(prix) AS prix, parent
  FROM produits
  GROUP BY parent
) AS sub
ON sub.parent = p.id
GROUP by parent
ORDER by prix;

+--------+------+
| parent | prix |
+--------+------+
|      1 |    9 |
|      5 |   19 |
+--------+------+

Une fois encore c’est cette requête qui est à privilégier.

MySQL 5.5 #

Il paraît important de mentionner le comportement d’une version antérieure de MySQL, car elle reste très utilisée et possède également ses spécificités.

Reprenons le cas de la première version de la requête.

SELECT parent, prix
FROM (
  SELECT parent, prix
  FROM produits
  WHERE parent IS NOT NULL
  ORDER BY prix ASC
) AS sub
GROUP BY parent
ORDER BY prix;

+--------+------+
| parent | prix |
+--------+------+
|      1 |    9 |
|      5 |   19 |
+--------+------+

Contrairement à son successeur, ici pas besoin d’autoriser explicitement le comportement permissif de l’instruction GROUP BY concernant les champs qui ne sont pas des agrégats. Notre requête incorrecte fonctionne donc, car MySQL utilise les valeurs du premier tuple rencontré lors du parcours des résultats de la sous-requête et applique le tri quelque soit le nombre d’enregistrements demandés.

Néanmoins, on ne constate aucune surprise quant à l’utilisation de la version avec la jointure.

SELECT p.id AS parent, MIN(sub.prix) AS prix
FROM produits p
INNER JOIN (
  SELECT MIN(prix) AS prix, parent
  FROM produits
  GROUP BY parent
) AS sub
ON sub.parent = p.id
GROUP by parent
ORDER by prix;

+--------+------+
| parent | prix |
+--------+------+
|      1 |    9 |
|      5 |   19 |
+--------+------+

Conclusion #

Le standard SQL stipule qu’une Table est un ensemble non ordonné d’enregistrements. C’est pour cela que la plupart des SGBD ignorent les instructions de tri lorsque celles-ci sont données à des sous-requêtes faisant office de Table. On constate cependant que MySQL, malgré une tendance à revenir sur ses positions à cause des problèmes de performances introduits par ces mauvaises pratiques, est moins stricte que les autres systèmes. Nous n’avons pas parlé de PostgreSQL mais ce dernier, réputé pour son respect des standards, n’autorise pas les colonnes qui ne sont pas des agrégats dans un GROUP BY.

La bonne méthode consiste donc à faire une jointure sur un autre groupement d’enregistrements contenant les agrégats nécessaires. Cela évite notamment de devoir exécuter la sous requête pour chaque enregistrement et améliore drastiquement l’efficacité de la requête.

Cet article vous a plu ? Sachez que nous recrutons !

← Accueil