Obtenir les n premiers éléments de chaque catégorie

[ced]

Bonjour,

Je vous propose un nouvel élément à utiliser : Obtenir les n premiers éléments de chaque catégorie

Supposons qu'une table ELEMENT et une table CATEGORIE sont composées comme suit :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE `ma_base`.`CATEGORIE` (
	`id_categorie` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
	`nom_categorie` VARCHAR(45) NOT NULL,
	PRIMARY KEY (`id_categorie`)
)
ENGINE = InnoDB;
 
CREATE TABLE `ma_base`.`ELEMENT` (
	`id_element` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
	`nom_element` VARCHAR(45) NOT NULL,
	`id_categorie` INTEGER UNSIGNED NOT NULL,
	PRIMARY KEY (`id_element`),
	CONSTRAINT `FK_ELT_CAT` FOREIGN KEY `FK_ELT_CAT` (`id_categorie`)
		REFERENCES `categorie` (`id_categorie`)
		ON DELETE CASCADE
		ON UPDATE CASCADE
)
ENGINE = InnoDB;

Le but de la requête est de ramener les n premiers éléments de chaque catégorie par ordre d'identifiant. Pour cela, on fait ce qu'on appelle une division relationnelle, comme suit :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
	e.id_element, e.nom_element, c.id_categorie, c.nom_categorie
FROM
	ELEMENT e
INNER JOIN
	CATEGORIE c ON e.id_categorie = c.id_categorie
WHERE (
	SELECT COUNT(*)
    FROM ELEMENT e1
    WHERE e1.id_categorie = e.id_categorie
    AND e1.id_element < e.id_element
) < n

Il suffit de remplacer n par la valeur souhaitée.

Pour obtenir les n derniers éléments de chaque catégorie, il suffit d'inverser le sens de l'inégalité dans la sous-requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
	e.id_element, e.nom_element, c.id_categorie, c.nom_categorie
FROM
	ELEMENT e
INNER JOIN
	CATEGORIE c ON e.id_categorie = c.id_categorie
WHERE (
    SELECT COUNT(*)
    FROM ELEMENT e1
    WHERE e1.id_categorie = e.id_categorie
    AND e1.id_element > e.id_element
) < n

Attention, cette requête ne fonctionne qu'avec une version de MySQL supportant les sous-requêtes (version 4.1 ou postérieure).

Qu'en pensez-vous ?

[gene69]

ça à l'air sympa. ya l'équivalent d'une base scott/tiger pour mysql? je veux dire la même chose avec des données.

point de vue calculs, c'est gourmand?

[pacmann]

Salut !

Je trouve ça juste un peu dommage que l'exemple donne un tri sur une PK technique

Genre j'aurais bien vu par exemple une date de création à la place, et combiner l'ordre date_creation + increment (pour départager les ex-aequos et garantir qu'on en sorte au plus n)

[CinePhil]

Je me suis posé la même question tout récemment car j'ai eu à sortir un top 5 selon deux critères, l'un classé en ASC et l'autre en DESC.

Comme il n'y a pas beaucoup de catégories, je suis passé par des requêtes UNION et LIMIT 5. Un truc du genre :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT tmp.les_colonnes
FROM
(
	(
		SELECT la_categorie, les_colonnes
		FROM la_table
		WHERE la_categorie = 1
		ORDER BY un_critere ASC, un_autre_critere DESC
		LIMIT 5
	)
	UNION ALL
	(
		SELECT la_categorie, les_colonnes
		FROM la_table
		WHERE la_categorie = 2
		ORDER BY un_critere ASC, un_autre_critere DESC
		LIMIT 5
	)
	UNION ALL
-- etc
) tmp
ORDER BY la_categorie, un_critere ASC, un_autre_critere DESC

[pacmann]

Quand tu as deux (ou plus) critères, il faut réaliser les combinaisons en priorisant.

Dans ton cas, la condition du count(*) de ced serait un truc du genre :

Code :

1
2
 
WHERE a.un_critere < b.un_critèreASC OR (a.un_critere = b.un_critere AND a.un_autre_critere > b.un_autre_critere)

[Sergejack]

Ça ressemble (en même temps il n'y a pas des milliers de possibilité en MySQL) à un query que j'ai écrit récemment.
Il y a une subtilité supplémentaire dans mon query, c'est une optimisation prenant tout son sens lorsque des critères de recherche impliquent des jointures.

En effet, si des lignes doivent être éliminées du résultats, elles doivent l'être autant dans le sous query qui calcul l'ordre de la ligne (dépendante d'un tri) que dans le query principal.
Pour ne pas répéter deux fois des jointures qui ne serviraient qu'au filtrage, je procède donc ainsi :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
SELECT
	H.id
	, H.stockID
	, H.value
	, H.date
	, H.userID
	, U.name AS userName
FROM #__mystock_history AS H
LEFT JOIN #__users AS U ON (U.id = H.userID)
WHERE H.stockID IN (%1$s)
AND (
	SELECT
		 COUNT(*) * SUM(CASE WHEN H.id = H2.id THEN 1 ELSE 0 END)
	FROM #__mystock_history AS H2
	LEFT JOIN #__mystock_cancel AS C1 ON (C1.canceledID = H2.id)
	LEFT JOIN #__mystock_cancel AS C2 ON (C2.correctionID = H2.id)
	WHERE H2.stockID = H.stockID
	AND C1.canceledID IS NULL
	AND C2.correctionID IS NULL
	AND H2.Date >= H.Date
	AND (
		H2.Date > H.Date
		OR
		H2.id >= H.id
	)
) BETWEEN 1 AND %2$d
ORDER BY H.stockID ASC, H.date DESC

%1$s = une liste d'identifiant séparés par des virgules (ex "1,2,3,4")
%2$d = Le nombre maximale de lignes à obtenir par "groupe"

L'idée c'est d'obtenir dans le sous query une formule qui puisse m'inidquer deux choses :
1) l'ordre de la ligne
2) si la ligne convient au filtre

Le sous query renverra 0 si la ligne ne convient pas au filtre et l'ordre de la ligne sinon.

[SQLpro]

Elle n'est valable QUE dans le cas ou vous utilisez une clef primaire comme comparaison d’inégalité. Si vous utilisez une colonne quelconque, cela peut conduire à des résultats faux.

Dans ce ac, seule la fonction ROW_NUMBER() ou RANK(), implémentée dans tous les SGBDR sauf MySQL sera la seule solution.

A +

[Sergejack]

En MySQL, vous pouvez définir une variable entière à 0 et renvoyé sa valeur tout en l'incrémentant dans un select ordonné (order by...).
Je ne me souviens pas de la syntax exact mais c'est une chose à connaître dans ces cas là.

Note : Quel déterrement !

[alassanediakite]

Salut
Un exemple avec variable initialisée à zéro

Code :

1
2
SET @rank=0;
SELECT @rank:=@rank+1 AS rank, ID, Name FROM city;

@+