skip to main | skip to sidebar
Code 18
Manuel du savoir-faire à l'usage des geeks et des curieux
RSS
  • Accueil
  • Le web au Québec
  • Liens
  • Twitter
  • Facebook
  • À propos

mercredi 7 septembre 2011

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

Publié par Infinite Loop, à 20 h 29 3 commentaires

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.


Tags: PostgreSQL, SQL Server

3 réponses à "Sélectionner le 1er enregistrement de chaque groupe en SQL"

  1. Pierre a dit...
    8 septembre 2011 à 07 h 34

    a l'ancienne ca donne quelque chose comme ca, et a la clause "limit" pret ca marche sous oracle.

    SELECT * FROM products p1 LEFT JOIN categories c1 ON c1.category_id = p1.category_id WHERE product_id IN (
    SELECT ( SELECT product_id FROM products p WHERE c.category_id = p.category_id ORDER BY random() LIMIT 1) as product_id FROM categories c)

    Antoine a dit...
    8 septembre 2011 à 10 h 02

    Ca ne fonctionne pas sous Mysql, ou le mot clé partition lui pose probleme :(

    Concernant mon expérience personnelle, j'avais déja eu ce probleme. J'avais été particulièrement decu de ne pouvoir faire un order by avant le group by !

    Je m'en étais sorti avec qqch de la sorte :
    SELECT c.category_name, tmp.*
    FROM (
    SELECT *
    FROM products
    ORDER BY RAND()
    ) as tmp
    INNER JOIN categories as c ON c.category_id = tmp.category_id
    GROUP BY tmp.category_id
    ORDER BY c.category_id

    Mias c'est vrai que le order by sur l'ensemble des produits peut etre long et que la solution de Pierre se trouve sans doute être plus rapide sur un très grand nombre de produits.

    Paul a dit...
    28 novembre 2012 à 05 h 02

    Merci cette fonction m'a été super utile


Publier un commentaire

Message plus récent Messages plus anciens Accueil
S'abonner à : Publier des commentaires (Atom)
    Suivre @code18 sur Twitter

    Catégories

    • Apache (21)
    • Citations (167)
    • Club Vidéo (24)
    • Coffre à outils (56)
    • CSS (8)
    • Curiosités (117)
    • Design Pattern (2)
    • Drupal (8)
    • Easter Eggs (22)
    • Extensions Firefox (20)
    • GIMP (7)
    • Histoire (21)
    • HTML (32)
    • Humour (57)
    • Intégration (34)
    • iPod (12)
    • JavaScript (110)
    • Jeu de combat (6)
    • Le coin du geek (128)
    • Liens (12)
    • Linux (56)
    • Livres (78)
    • Lois et principes (46)
    • Marché des saveurs (26)
    • Mathématique (18)
    • Mobile (5)
    • Montréal (32)
    • Musique (112)
    • Pancartes et écriteaux (16)
    • Perl (8)
    • Pérou (1)
    • PHP (130)
    • PostgreSQL (44)
    • Programmation (105)
    • Saviez-vous que (55)
    • Sécurité (22)
    • SEO (5)
    • SQL Server (22)
    • Vieilles publicités (6)
    • Virtualisation (8)
    • Voyages (1)
    • Zend Framework (26)

    Divers

    Archives

    • ►  2015 (6)
      • ►  août 2015 (1)
      • ►  juillet 2015 (1)
      • ►  février 2015 (3)
      • ►  janvier 2015 (1)
    • ►  2014 (8)
      • ►  décembre 2014 (1)
      • ►  novembre 2014 (1)
      • ►  octobre 2014 (1)
      • ►  août 2014 (2)
      • ►  juillet 2014 (2)
      • ►  janvier 2014 (1)
    • ►  2013 (53)
      • ►  décembre 2013 (2)
      • ►  novembre 2013 (1)
      • ►  octobre 2013 (3)
      • ►  septembre 2013 (2)
      • ►  août 2013 (5)
      • ►  juillet 2013 (3)
      • ►  juin 2013 (5)
      • ►  mai 2013 (3)
      • ►  avril 2013 (7)
      • ►  mars 2013 (7)
      • ►  février 2013 (11)
      • ►  janvier 2013 (4)
    • ►  2012 (105)
      • ►  décembre 2012 (8)
      • ►  novembre 2012 (5)
      • ►  octobre 2012 (4)
      • ►  septembre 2012 (1)
      • ►  août 2012 (8)
      • ►  juillet 2012 (7)
      • ►  juin 2012 (7)
      • ►  mai 2012 (10)
      • ►  avril 2012 (13)
      • ►  mars 2012 (15)
      • ►  février 2012 (15)
      • ►  janvier 2012 (12)
    • ▼  2011 (146)
      • ►  décembre 2011 (14)
      • ►  novembre 2011 (11)
      • ►  octobre 2011 (12)
      • ▼  septembre 2011 (13)
        • Vérifier si une table existe dans Postgres
        • Devant une bibliothèque imposante
        • Les jeunes condamnés à la location perpétuelle ?
        • Citation no. 135 sur les grands penseurs
        • Tri conditionnel dans un énoncé SQL
        • Bouton personnalisé dans un datepicker jQueryUI
        • Mon top 3 photos du 11 septembre 2011
        • Citation no. 134 sur le mensonge
        • Sélectionner le 1er enregistrement de chaque group...
        • La réponse sera toujours 8
        • Récupérer l'ID d'un enregistrement inséré sous Pos...
        • Citation no. 133 sur l'art d'être bien informé
        • La survie du petit commerce
      • ►  août 2011 (15)
      • ►  juillet 2011 (17)
      • ►  juin 2011 (18)
      • ►  mai 2011 (15)
      • ►  avril 2011 (9)
      • ►  mars 2011 (7)
      • ►  février 2011 (3)
      • ►  janvier 2011 (12)
    • ►  2010 (398)
      • ►  décembre 2010 (29)
      • ►  novembre 2010 (28)
      • ►  octobre 2010 (32)
      • ►  septembre 2010 (34)
      • ►  août 2010 (22)
      • ►  juillet 2010 (35)
      • ►  juin 2010 (42)
      • ►  mai 2010 (36)
      • ►  avril 2010 (37)
      • ►  mars 2010 (34)
      • ►  février 2010 (32)
      • ►  janvier 2010 (37)
    • ►  2009 (430)
      • ►  décembre 2009 (32)
      • ►  novembre 2009 (34)
      • ►  octobre 2009 (33)
      • ►  septembre 2009 (37)
      • ►  août 2009 (37)
      • ►  juillet 2009 (39)
      • ►  juin 2009 (38)
      • ►  mai 2009 (37)
      • ►  avril 2009 (35)
      • ►  mars 2009 (37)
      • ►  février 2009 (32)
      • ►  janvier 2009 (39)
    • ►  2008 (84)
      • ►  décembre 2008 (34)
      • ►  novembre 2008 (39)
      • ►  octobre 2008 (11)

    Abonnés

Copyright © All Rights Reserved. Code 18 | Converted into Blogger Templates by Theme Craft