Contexte historique
La norme ANSI SQL:2011 a introduit des clauses d'exclusion de cadre dans la syntaxe des fonctions de fenêtre, abordant la limitation selon laquelle les cadres de fenêtre incluaient nécessairement la ligne actuelle. Avant cette amélioration, les développeurs devaient recourir à des auto-joins encombrants ou à des manipulations algébriques (soustrayant la valeur actuelle du total) pour calculer des agrégats excluant la ligne clé. La norme définit quatre options d'exclusion : EXCLUDE NO OTHERS, EXCLUDE CURRENT ROW, EXCLUDE GROUP, et EXCLUDE TIES, offrant des sémantiques déterministes pour les opérations sur les ensembles au sein des partitions ordonnées.
Le problème
Lors de l'analyse des métriques concurrentielles, par exemple pour calculer le prix de vente moyen de produits similaires en excluant le produit lui-même de cette moyenne, une requête doit définir une fenêtre englobant toutes les lignes liées sauf la ligne actuelle. Les fonctions de fenêtre traditionnelles comme AVG() OVER (PARTITION BY category) incluent la ligne actuelle, faussant ainsi le résultat. Mettre en œuvre cela via des sous-requêtes ou des joints introduit une complexité inutile et une dégradation des performances, en particulier lorsqu'il s'agit de grands ensembles de données partitionnées où les produits cartésiens ou les sous-requêtes corrélées seraient coûteux.
La solution
Utiliser la clause d'exclusion de cadre dans la spécification de fenêtre : AVG(price) OVER (PARTITION BY category ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW). Cette syntaxe indique au moteur SQL d'abord d'établir le cadre de partition complet, puis de retirer logiquement la ligne actuelle avant de calculer l'agrégat. Pour les scénarios nécessitant l'exclusion de tous les égaux (par exemple, tous les produits au même point de prix), EXCLUDE GROUP retire à la fois la ligne actuelle et ses pairs d'ordre, tandis que EXCLUDE TIES conserve la ligne actuelle mais retire les valeurs d'ordre en double.
Une équipe d'analytique d'e-commerce doit générer un rapport "Position sur le marché". Pour chaque annonce d'un vendeur d'un gadget électronique, elle doit afficher le prix de ce vendeur à côté du prix moyen de tous les autres vendeurs proposant le même modèle de gadget.
Une approche d'auto-join a d'abord été prototypée, où la table des annonces était jointe à elle-même sur model_id excluant les clés primaires correspondantes. Avantages : elle est universellement prise en charge par tous les dialectes SQL et conceptuellement simple. Inconvénients : l'exécution présente une complexité de O(n²) dans le pire des cas, provoquant un ralentissement exponentiel sur des millions de lignes ; de plus, l'optimiseur de requêtes a souvent du mal avec le prédicat de jointure inégal, générant des plans d'exécution inefficients avec des débordements de hachage ou des joints en boucle imbriquée.
Une solution algébrique a également été évaluée, calculant la somme et le compte globaux par modèle, puis dérivant la moyenne des autres via (SUM(price) - current_price) / (COUNT(*) - 1). Avantages : elle évite les joints et nécessite seulement un scan de fonction de fenêtre. Inconvénients : elle échoue catastrophiquement lorsque COUNT(*) = 1 (division par zéro) ou lorsque les prix sont NULL, nécessitant des protections CASE verbeuses ; de plus, elle ne peut pas être appliquée à des agrégats non algébriques comme MEDIAN ou MODE.
L'équipe a finalement sélectionné la spécification de cadre EXCLUDE CURRENT ROW. Raisonnement : elle est déclarative, élimine le besoin d'expressions CASE de vérification de NULL en retournant naturellement NULL pour des cadres vides, et s'exécute en O(n) avec un passage trié unique et un minimum de surcharge mémoire. La requête résultante a réduit le temps de génération de rapport de douze minutes à moins de dix secondes.
Résultat : Le rapport de production calcule désormais avec précision les références des concurrents pour 50 millions d'annonces quotidiennement, gérant gracieusement les objets rares avec des vendeurs uniques en affichant NULL (interprété comme "Pas de concurrence") plutôt que des erreurs ou des valeurs nulles.
Comment se comporte EXCLUDE CURRENT ROW lorsqu'elle est utilisée avec des cadres de fenêtres basés sur RANGE versus basés sur ROWS, en particulier concernant les groupes de pairs ?
Lorsque le cadre de fenêtre utilise ROWS, EXCLUDE CURRENT ROW enlève exactement une ligne physique—la ligne actuelle—de l'agrégation. Cependant, lorsqu'elle utilise RANGE (par exemple, RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING), la "ligne actuelle" représente conceptuellement toutes les lignes partageant la même valeur d'ordre que la ligne actuelle dans la plage spécifiée. Dans ce contexte, EXCLUDE CURRENT ROW ne supprime que l'instance de ligne spécifique, laissant d'autres pairs (liens) dans le cadre. À l'inverse, EXCLUDE GROUP enlève la ligne actuelle et tous les pairs, quel que soit l'unité du cadre, tandis que EXCLUDE TIES retire tous les pairs sauf la ligne actuelle. Les candidats confondent souvent ces deux notions, supposant que EXCLUDE CURRENT ROW avec RANGE se comporte comme EXCLUDE GROUP, ce qui conduit à des résultats d'agrégation incorrects en cas de clés d'ordre dupliquées.
Pourquoi une requête utilisant EXCLUDE CURRENT ROW sur une partition à une seule ligne renvoie-t-elle NULL, et en quoi cela diffère-t-il des méthodes de soustraction manuelle ?
La norme ANSI SQL définit qu'un agrégat sur un ensemble vide renvoie NULL. Lorsqu'EXCLUDE CURRENT ROW est appliqué à une partition contenant une seule ligne, le cadre devient vide, provoquant AVG, SUM, ou COUNT de renvoyer automatiquement NULL. En revanche, des méthodes manuelles comme (SUM(col) - col) / (COUNT(*) - 1) rencontrent des problèmes de division par zéro ou de propagation de NULL dans l'arithmétique, nécessitant des déclarations CASE explicites pour gérer en toute sécurité les partitions uniques. Les candidats négligent souvent ce comportement automatique de gestion des NULL, s'attendant à zéro ou à la valeur actuelle, et échouent à apprécier qu'EXCLUDE offre une meilleure sécurité null pour les conditions limites.
EXCLUDE peut-il être combiné avec des étendues de cadre arbitraires comme des fenêtres glissantes (par exemple, ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING), et quelles sont les implications en termes de performance ?
Oui, les clauses EXCLUDE sont valides avec toute étendue de cadre, y compris les fenêtres glissantes BETWEEN. Par exemple, AVG(val) OVER (ORDER BY time ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW) calcule la moyenne des deux valeurs précédentes et des deux suivantes, créant ainsi efficacement une moyenne mobile à 4 points centrée sur—mais excluant—le point actuel. En termes de performance, les optimisateurs modernes mettent en œuvre cela via un algorithme de streaming avec un tampon circulaire ou une deque, maintenant une complexité de O(n) par partition. Les candidats supposent souvent qu'EXCLUDE nécessite une matérialisation complète de la partition ou ne fonctionne qu'avec des cadres UNBOUNDED, négligeant qu'il s'intègre parfaitement avec des fenêtres glissantes limitées pour des calculs comme les corrélations mobiles centrées ou lissage robuste aux valeurs aberrantes où le point central ne doit pas influencer la statistique.