Comment sélectionner efficacement la ligne maximale des partitions sans plusieurs analyses de table ?

Obtenez efficacement la plus grande ligne de la partition et évitez les accès redondants aux tables

Dans le domaine des requêtes de données, l'efficacité est cruciale, notamment lorsqu'il s'agit de tables volumineuses. Un défi d'optimisation courant consiste à trouver la ligne avec la plus grande valeur dans une colonne spécifique de chaque partition d'une table.

Supposons que nous devions obtenir le score obtenu par chaque ID lors du dernier tour (ROUND) à partir du tableau SCORES :

ID	ROUND	SCORE
1	1	3
1	2	6
1	3	2
2	1	10
2	2	12
3	1	6

Méthode initiale :

Une solution consiste à récupérer toutes les lignes, puis à filtrer celles qui ne représentent pas le ROUND maximum par ID :

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;

Bien que cette méthode fonctionne, elle est inefficace en raison des analyses de tables redondantes.

Plan d'optimisation :

Un autre moyen plus efficace consiste à utiliser les fonctions window et la clause DISTINCT :

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;

Dans cette méthode, la fonction fenêtre max(round) OVER (PARTITION BY id) calcule le ROUND maximum pour chaque ID. Appliquez ensuite une clause DISTINCT après la fonction window, en vous assurant que seule la ligne ROUND la plus élevée pour chaque ID est renvoyée. Enfin, la fonction fenêtre first_value(score) OVER (PARTITION BY id ORDER BY round DESC) récupère le premier SCORE associé au plus grand ROUND pour chaque ID.

Ce schéma optimisé permet d'obtenir les résultats souhaités sans nécessiter plusieurs analyses de table, améliorant considérablement les performances.

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!