Sélectionner le 1er enregistrement de chaque groupe en SQL

Voici mon casse-tête SQL auquel j'ai eu à faire face hier et dont j'ai trouvé la solution plus tôt aujourd'hui. Pour vous mettre en situation, imaginez un site web où une liste de produits s'affiche en page d'accueil. Bien que chaque produit soit classé dans une catégorie, la requête initiale n'en tenait pas compte et se contentait d'afficher aléatoirement quelques produits du catalogue.

La demande que j'ai reçu consistait à modifier l'affichage de façon à choisir un produit au hasard dans chaque catégorie.

Voici une représentation simplifiée de la structure des tables ainsi que des données bidons pour que vous puissez reproduire ce tutoriel :

CREATE TABLE categories
(
   category_id serial NOT NULL,
   category_name character varying(50) NOT NULL
)

CREATE TABLE products
(
   product_id serial NOT NULL,
   category_id integer NOT NULL,
   product_name character varying(100) NOT NULL,
   price numeric
)

-- populer les tables avec des enregistrements

INSERT INTO categories (category_name)
VALUES
('Catégorie 1'),
('Catégorie 2'),
('Catégorie 3');

INSERT INTO products(category_id, product_name, price)
VALUES
   (1, 'Produit 1', 10),
   (1, 'Produit 2', 25),
   (2, 'Produit 3', 5),
   (2, 'Produit 4', 17),
   (3, 'Produit 5', 22),
   (3, 'Produit 6', 11);

J'ai mis à l'essai quelques modèles de requêtes SQL pour répondre au besoin. La première qui m'est venue en tête consistait à utiliser des sous-requêtes. À partir de la liste des catégories, lancer pour chacune une sous-requête pour obtenir un produit au hasard, avec la fonction random() :

SELECT c.category_id, c.category_name,
   (SELECT product_name FROM products as p WHERE p.category_id = c.category_id ORDER BY random() LIMIT 1)
FROM categories as c
ORDER BY c.category_name

C'est bien, mais on n'obtient qu'un nom aléatoire de produit qui correspond à la catégorie et aucun autre champs de l'enregistrement. De plus, il est impossible de lancer une deuxième sous-requête en parallèle puisque les résultats ne feront pas référence au même enregistrement.

-- mauvais!!!
SELECT c.category_id, c.category_name,
   (SELECT product_name FROM products as p WHERE p.category_id = c.category_id ORDER BY random() LIMIT 1),
   (SELECT product_id FROM products as p WHERE p.category_id = c.category_id ORDER BY random() LIMIT 1)
FROM categories as c
ORDER BY c.category_name

Une autre possibilité aurait été d'utiliser une requête par catégorie de produits et de les combiner avec des UNION. Sauf qu'il faudrait connaître d'avance le nombre de catégories...

Essayons à l'inverse en utilisant la table de produits et une jointure sur la table des catégories.

SELECT c.category_name, p.*
FROM products as p
INNER JOIN categories as c ON c.category_id = p.category_id
ORDER BY c.category_name, random()

Ici, nous obtenons la liste de tous les produits classés par catégories, dont l'ordre des produits est généré aléatoirement à partir du tri secondaire (toujours avec "random()"). Ici encore, nous n'avons pas réussi à filtrer la liste pour ne conserver qu'un seul produit par catégorie.

L'astuce : l'instruction OVER PARTITION, valide autant sous Postgres (comme dans mon cas) que dans SQL Server.

-- solution
SELECT c.category_name, t.*
FROM (
   SELECT *, MAX(random_order) OVER (PARTITION BY category_id)
   FROM (
       SELECT *, random() as random_order
       FROM products
   ) as tmp
) as t
INNER JOIN categories as c ON c.category_id = t.category_id
WHERE MAX = random_order
ORDER BY c.category_id

Pour comprendre la solution, vous devez d'abord savoir qu'il est possible d'utiliser une sous-requête (ou une table dérivée) qui retourne un résultat plutôt que de spécifier le nom d'une table. Pour commencer l'analyse, jetez un oeil à la sous-requête la plus imbriquée. J'ajoute une valeur aléatoire avec random() (de type double, comprise entre 0 et 1) à chaque enregistrement de la table products que je nomme avec l'alias random_number. Il est ensuite nécessaire d'indiquer le champ utilisé pour le regroupement avec PARTITION. Dans mon cas, je veux que ça se fasse par catégorie. Remarquez qu'il n'y a pas de clause ORDER BY à ma requête puisque je prendrai tout simplement l'enregistrement ayant le numéro aléatoire le plus élevé avec MAX(random_number). La clause MAX = random_order me permettra ensuite de filtrer les résultats pour ne conserver que le premier enregistrement de chaque catégorie.

Important à noter : pour que ça fonctionne, vous devez obligatoirement donner un alias aux sous-requêtes qui servent de tables (dans mon exemple final : "tmp" et "t"). Autrement, vous obtiendrez une erreur de type SQL state: 42601.

En espérant que ça vous évitera de chercher trop longtemps comme je l'ai fait. Si vous avez d'autres solutions originales, n'hésitez pas à me les envoyer. Je serais curieux de les comparer.