Trouver efficacement des valeurs maximales et des données associées dans les grandes tables SQL

Souvent, les requêtes de base de données nécessitent de trouver la valeur maximale dans une colonne et de récupérer les données correspondantes d'autres colonnes dans la même ligne. Cela est particulièrement difficile avec de très grands ensembles de données. Considérez un tableau ayant besoin de trouver le numéro de version le plus élevé pour chaque ID unique, ainsi que sa balise associée:

Exemple de table:

ID  |  tag  |  version
----- ----- -----
1 |  A  |  10
2 |  A  |  20
3 |  B  |  99
4 |  C  |  30
5 |  F  |  40

Résultat souhaité:

ID  |  tag  |  version
----- ----- -----
2  |  A  |  20
3  |  B  |  99
4  |  C  |  30
5  |  F  |  40

Pour les tables avec environ 28 millions de lignes, des méthodes standard comme les instructions Nested SELECT ou le groupe simple par avec max peut être incroyablement lent. Une solution beaucoup plus efficace utilise la fonction de fenêtre row_number () :

SELECT s.id, s.tag, s.version
FROM (
  SELECT t.*,
         ROW_NUMBER() OVER(PARTITION BY t.id ORDER BY t.version DESC) AS rnk
  FROM YourTable t
) s
WHERE s.rnk = 1;

Cette requête fonctionne en deux étapes:

1. requête intérieure: Il attribue un rang unique ( rnk ) à chaque ligne dans chaque partition id (groupe de lignes avec le même id). Le classement est basé sur la colonne version dans l'ordre descendant, ce qui signifie que la version la plus élevée obtient le rang 1.

2. requête extérieure: Il filtre les résultats de la requête intérieure, en sélectionnant uniquement les lignes où rnk = 1 . Cela nous donne effectivement la ligne avec la version maximale pour chaque id .

Cette approche évite les requêtes imbriquées et les opérations de groupe par , ce qui le rend beaucoup plus rapidement pour les grands ensembles de données. L'utilisation de row_number () fournit un moyen propre et efficace d'atteindre le résultat souhaité.