Comment récupérer les N supérieurs pour chaque groupe dans SQL?

Récupération des N enregistrements des N par groupe dans SQL

Ce guide montre comment récupérer efficacement les n enregistrements supérieurs de chaque groupe dans une table SQL, commandés par âge (descendant), puis par ordre alphabétique par le nom de la personne pour gérer les liens.

Scénario:

Imaginez un tableau contenant des informations sur les gens, leur affiliation de groupe et leur âge:

Person	Group	Age
Bob	1	32
Jill	1	34
Shawn	1	42
Jake	2	29
Paul	2	36
Laura	2	39

L'objectif est de récupérer les 2 premiers enregistrements (n = 2) pour chaque groupe, de prioriser les personnes âgées et de résoudre les liens en utilisant l'ordre alphabétique. La sortie souhaitée est:

Person	Group	Age
Shawn	1	42
Jill	1	34
Laura	2	39
Paul	2	36

Solutions :

Deux approches courantes sont présentées : UNION ALL et la fonction ROW_NUMBER() window.

Méthode 1 : Utiliser UNION ALL (moins efficace pour les grands ensembles de données)

Cette méthode est simple mais peut devenir inefficace pour les tables comportant de nombreux groupes et un grand nombre d'enregistrements par groupe. Cela implique de créer une requête distincte pour chaque groupe et de combiner les résultats à l'aide de UNION ALL. Cet exemple récupère les 2 premiers enregistrements (N=2) pour chaque groupe :

<code class="language-sql">(
  SELECT *
  FROM mytable
  WHERE `group` = 1
  ORDER BY age DESC, person
  LIMIT 2
)
UNION ALL
(
  SELECT *
  FROM mytable
  WHERE `group` = 2
  ORDER BY age DESC, person
  LIMIT 2
)</code>

Cette approche nécessite des modifications pour chaque groupe supplémentaire. Ce n'est pas évolutif pour un grand nombre de groupes.

Méthode 2 : utiliser ROW_NUMBER() (plus efficace)

La fonction de fenêtre ROW_NUMBER() offre une solution plus efficace et évolutive. Il attribue un rang unique à chaque ligne de chaque groupe en fonction de l'ordre spécifié.

<code class="language-sql">SELECT person, `group`, age
FROM (
   SELECT person, `group`, age,
      ROW_NUMBER() OVER (PARTITION BY `group` ORDER BY age DESC, person) as rn
   FROM mytable
) as ranked_data
WHERE rn <= 2;</code>

Cette requête attribue d'abord un rang (rn) à chaque ligne de chaque groupe, en les classant par âge (décroissant), puis par nom de personne. La requête externe filtre ensuite les résultats pour inclure uniquement les lignes dont le rang est inférieur ou égal à 2 (N=2). Cette approche est bien plus efficace et facilement adaptable à différentes valeurs de N et à un nombre variable de groupes.

Lectures complémentaires :

Pour une exploration complète des techniques de sélection des N meilleurs enregistrements par groupe, reportez-vous à cette ressource : https://www.php.cn/link/131632cb7eeb986974e1be59af67e8fe

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!