Comment puis-je récupérer efficacement des lignes avec la valeur maximale par groupe dans une base de données relationnelle ?

Rechercher efficacement des valeurs maximales au sein de groupes dans des bases de données relationnelles

Extraire des lignes contenant les valeurs maximales pour des colonnes spécifiques tout en préservant l'intégrité du groupe au sein de bases de données relationnelles peut être complexe. Cet article aborde le défi consistant à récupérer la ligne avec le numéro rond le plus élevé pour chaque ID unique, en donnant la priorité à l'efficacité des requêtes.

Une méthode utilise des sous-requêtes :

<code class="language-sql">SELECT * FROM
(SELECT id, round,
CASE WHEN (MAX(round) OVER (PARTITION BY id)) = round THEN score ELSE NULL END score
 FROM
 SCORES
 where id in (1,2,3)
) scorevals
WHERE
scorevals.round is not null;</code>

Cependant, cette approche est inefficace en raison de l'étape de filtrage post-traitement.

Pour des performances améliorées, pensez à utiliser les fonctions de fenêtre :

<code class="language-sql">SELECT DISTINCT
       id
      ,max(round) OVER (PARTITION BY id) AS round
      ,first_value(score) OVER (PARTITION BY id ORDER BY round DESC) AS score
FROM   SCORES
WHERE  id IN (1,2,3)
ORDER  BY id;</code>

Cette requête utilise des fonctions de fenêtre pour déterminer le tour maximum pour chaque ID, puis récupère le score correspondant. Le mot-clé DISTINCT garantit une seule ligne par ID.

Une alternative potentiellement plus rapide, utilisant deux fois la même fonction de fenêtre :

<code class="language-sql">SELECT DISTINCT
       id
      ,first_value(round) OVER (PARTITION BY id ORDER BY round DESC) AS round
      ,first_value(score) OVER (PARTITION BY id ORDER BY round DESC) AS score
FROM   SCORES
WHERE  id IN (1,2,3)
ORDER  BY id;</code>

Les deux solutions optimisées évitent les filtrages inutiles, ce qui entraîne des temps d'exécution des requêtes plus rapides par rapport à l'approche des sous-requêtes.

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!