Cet article est le quatrième volet d’une série sur les bogues, pièges et meilleures pratiques T-SQL. Précédemment, j’ai couvert le déterminisme, les sous-requêtes et les jointures. L’article de ce mois-ci porte sur les bogues, les pièges et les meilleures pratiques liés aux fonctions de fenêtre. Merci Erland Sommarskog, Aaron Bertrand, Alejandro Mesa, Umachandar Jayachandran (UC), Fabiano Neves Amorim, Milos Radivojevic, Simon Sabin, Adam Machanic, Thomas Grohser, Chan Ming Man et Paul White pour avoir proposé vos idées !
Dans mes exemples, j’utiliserai une base de données exemple appelée TSQLV5. Vous pouvez trouver le script qui crée et alimente cette base de données ici, et son diagramme ER ici.
Il y a deux pièges communs impliquant les fonctions de fenêtre, qui sont tous deux le résultat de défauts implicites contre-intuitifs qui sont imposés par le standard SQL. L’un des écueils concerne les calculs de totaux courants pour lesquels vous obtenez un cadre de fenêtre avec l’option implicite RANGE. Un autre écueil est quelque peu lié, mais a des conséquences plus graves, impliquant une définition implicite de cadre pour les fonctions FIRST_VALUE et LAST_VALUE.
Cadre de fenêtre avec l’option implicite RANGE
Notre premier écueil implique le calcul de totaux courants en utilisant une fonction de fenêtre agrégée, où vous spécifiez explicitement la clause d’ordre de la fenêtre, mais vous ne spécifiez pas explicitement l’unité de cadre de fenêtre (ROWS ou RANGE) et son étendue de cadre de fenêtre liée, par ex, ROWS UNBOUNDED PRECEDING. Le défaut implicite est contre-intuitif et ses conséquences pourraient être surprenantes et douloureuses.
Pour démontrer ce piège, je vais utiliser une table appelée Transactions contenant deux millions de transactions de comptes bancaires avec des crédits (valeurs positives) et des débits (valeurs négatives). Exécutez le code suivant pour créer la table Transactions et la remplir avec des données échantillons:
SET NOCOUNT ON; USE TSQLV5; -- http://tsql.solidq.com/SampleDatabases/TSQLV5.zip DROP TABLE IF EXISTS dbo.Transactions; CREATE TABLE dbo.Transactions ( actid INT NOT NULL, tranid INT NOT NULL, val MONEY NOT NULL, CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid) -- creates POC index ); DECLARE @num_partitions AS INT = 100, @rows_per_partition AS INT = 20000; INSERT INTO dbo.Transactions WITH (TABLOCK) (actid, tranid, val) SELECT NP.n, RPP.n, (ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM(NEWID())%5)) FROM dbo.GetNums(1, @num_partitions) AS NP CROSS JOIN dbo.GetNums(1, @rows_per_partition) AS RPP;
Notre écueil a à la fois un côté logique avec un bug logique potentiel ainsi qu’un côté performance avec une pénalité de performance. La pénalité de performance n’est pertinente que lorsque la fonction fenêtre est optimisée avec des opérateurs de traitement en mode rangée. SQL Server 2016 introduit l’opérateur Window Aggregate en mode batch, qui supprime la partie pénalité de performance du piège, mais avant SQL Server 2019, cet opérateur n’est utilisé que si vous avez un index columnstore présent sur les données. SQL Server 2019 introduit le mode batch sur la prise en charge rowstore, de sorte que vous pouvez obtenir un traitement en mode batch même si aucun index columnstore n’est présent sur les données. Pour démontrer la pénalité de performance avec le traitement en mode row, si vous exécutez les échantillons de code de cet article sur SQL Server 2019 ou une version ultérieure, ou sur Azure SQL Database, utilisez le code suivant pour définir le niveau de compatibilité de la base de données sur 140 afin de ne pas encore activer le mode batch on rowstore :
ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 140;
Utilisez le code suivant pour activer les statistiques de temps et d’E/S dans la session :
SET STATISTICS TIME, IO ON;
Pour éviter d’attendre l’impression de deux millions de lignes dans SSMS, je suggère d’exécuter les échantillons de code de cette section avec l’option Jeter les résultats après exécution activée (allez dans Options de requête, Résultats, Grille, et cochez Jeter les résultats après exécution).
Avant d’arriver au piège, considérez la requête suivante (appelez-la Requête 1) qui calcule le solde du compte bancaire après chaque transaction en appliquant un total courant à l’aide d’une fonction d’agrégat de fenêtre avec une spécification de cadre explicite :
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Le plan de cette requête, en utilisant le traitement en mode ligne, est présenté dans la Figure 1.
Figure 1 : Plan de la requête 1, traitement en mode rangée
Le plan tire les données préordonnées de l’index en cluster de la table. Puis il utilise les opérateurs Segment et Sequence Project pour calculer les numéros de ligne afin de déterminer quelles lignes appartiennent au cadre de la ligne actuelle. Il utilise ensuite les opérateurs Segment, Window Spool et Stream Aggregate pour calculer la fonction d’agrégation de fenêtre. L’opérateur Window Spool est utilisé pour spooler les lignes du cadre qui doivent ensuite être agrégées. Sans optimisation particulière, le plan aurait dû écrire par rangée toutes les rangées de cadre applicables sur le spool, puis les agréger. Cela aurait entraîné une complexité quadratique, ou N2. La bonne nouvelle, c’est que lorsque le cadre commence par UNBOUNDED PRECEDING, SQL Server identifie le cas comme un cas rapide, dans lequel il prend simplement le total courant de la ligne précédente et ajoute la valeur de la ligne actuelle pour calculer le total courant de la ligne actuelle, ce qui donne une mise à l’échelle linéaire. Dans ce mode accéléré, le plan n’écrit que deux lignes dans le spool par ligne d’entrée – une avec l’agrégat, et une avec le détail.
Le spool de fenêtre peut être implémenté physiquement de deux manières. Soit comme un spool rapide en mémoire qui a été spécialement conçu pour les fonctions de fenêtre, soit comme un spool lent sur disque, qui est essentiellement une table temporaire dans tempdb. Si le nombre de lignes qui doivent être écrites dans le spool par ligne sous-jacente peut dépasser 10 000, ou si SQL Server ne peut pas prévoir ce nombre, il utilisera le spool on-disk plus lent. Dans notre plan de requête, nous avons exactement deux lignes écrites dans le spool par ligne sous-jacente, donc SQL Server utilise le spool en mémoire. Malheureusement, il n’y a aucun moyen de savoir à partir du plan quel type de spool vous obtenez. Il y a deux façons de le savoir. La première consiste à utiliser un événement étendu appelé window_spool_ondisk_warning. Une autre option est d’activer STATISTICS IO, et de vérifier le nombre de lectures logiques rapportées pour une table appelée Worktable. Un nombre supérieur à zéro signifie que vous avez reçu le spool sur le disque. Zéro signifie que vous avez reçu le spool en mémoire. Voici les statistiques d’E/S pour notre requête :
Comme vous pouvez le voir, nous avons obtenu l’utilisation du spool en mémoire. C’est généralement le cas lorsque vous utilisez l’unité de cadre de la fenêtre ROWS avec UNBOUNDED PRECEDING comme premier délimiteur.
Voici les statistiques de temps pour notre requête:
Il a fallu à cette requête environ 4,5 secondes pour se terminer sur ma machine avec les résultats écartés.
Maintenant pour le piège. Si vous utilisez l’option RANGE au lieu de ROWS, avec les mêmes délimiteurs, il peut y avoir une différence subtile de signification, mais une grande différence de performance en mode rangée. La différence de signification n’est pertinente que si vous ne disposez pas d’un classement total, c’est-à-dire si vous effectuez un classement par un élément qui n’est pas unique. L’option ROWS UNBOUNDED PRECEDING s’arrête à la ligne courante, donc en cas d’égalité, le calcul n’est pas déterministe. À l’inverse, l’option RANGE UNBOUNDED PRECEDING s’arrête à la ligne actuelle et inclut les égalités si elles existent. Elle utilise une logique similaire à celle de l’option TOP WITH TIES. Lorsque vous disposez d’un classement total, c’est-à-dire que vous effectuez un classement par un élément unique, il n’y a pas de liens à inclure et, par conséquent, ROWS et RANGE deviennent logiquement équivalents dans ce cas. Le problème est que lorsque vous utilisez RANGE, SQL Server utilise toujours le spool sur le disque dans le cadre d’un traitement en mode ligne, car lors du traitement d’une ligne donnée, il ne peut pas prévoir combien d’autres lignes seront incluses. Cela peut avoir une pénalité de performance sévère.
Considérez la requête suivante (appelée requête 2), qui est la même que la requête 1, mais en utilisant l’option RANGE au lieu de ROWS :
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Le plan de cette requête est présenté dans la figure 2.
Figure 2 : Plan pour la requête 2, traitement en mode rangée
La requête 2 est logiquement équivalente à la requête 1 car nous avons l’ordre total ; cependant, comme elle utilise RANGE, elle est optimisée avec le spool sur disque. Observez que dans le plan de la requête 2, le Spool de fenêtre a la même apparence que dans le plan de la requête 1, et les coûts estimés sont les mêmes.
Voici les statistiques de temps et d’E/S pour l’exécution de la requête 2:
Table ‘Worktable’ lectures logiques : 12044701. Lecture logique de la table ‘Transactions’ : 6208.
Notez le grand nombre de lectures logiques contre Worktable, ce qui indique que vous avez obtenu le spool sur disque. Le temps d’exécution est plus de quatre fois plus long que pour la requête 1.
Si vous pensez que si c’est le cas, vous éviterez simplement d’utiliser l’option RANGE, à moins que vous ayez vraiment besoin d’inclure des liens, c’est une bonne réflexion. Le problème est que si vous utilisez une fonction de fenêtre qui prend en charge un cadre (agrégats, FIRST_VALUE, LAST_VALUE) avec une clause d’ordre de fenêtre explicite, mais sans mention de l’unité de cadre de fenêtre et de son étendue associée, vous obtenez RANGE UNBOUNDED PRECEDING par défaut. Cette valeur par défaut est dictée par la norme SQL, et la norme l’a choisie parce qu’elle préfère généralement des options plus déterministes comme valeurs par défaut. La requête suivante (appelez-la requête 3) est un exemple qui tombe dans ce piège:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;
Souvent les gens écrivent comme cela en supposant qu’ils obtiennent ROWS UNBOUNDED PRECEDING par défaut, sans réaliser qu’ils obtiennent en fait RANGE UNBOUNDED PRECEDING. Le fait est que, puisque la fonction utilise l’ordre total, vous obtenez le même résultat qu’avec ROWS, donc vous ne pouvez pas dire qu’il y a un problème à partir du résultat. Mais les chiffres de performance que vous obtiendrez seront les mêmes que pour la requête 2. Je vois des gens tomber dans ce piège tout le temps.
La meilleure pratique pour éviter ce problème est dans les cas où vous utilisez une fonction de fenêtre avec un cadre, soyez explicite sur l’unité du cadre de la fenêtre et son étendue, et préférez généralement ROWS. Réservez l’utilisation de RANGE uniquement aux cas où l’ordonnancement n’est pas unique et où vous devez inclure des liens.
Considérez la requête suivante qui illustre un cas où il existe une différence conceptuelle entre ROWS et RANGE :
SELECT orderdate, orderid, val, SUM(val) OVER( ORDER BY orderdate ROWS UNBOUNDED PRECEDING ) AS sumrows, SUM(val) OVER( ORDER BY orderdate RANGE UNBOUNDED PRECEDING ) AS sumrange FROM Sales.OrderValues ORDER BY orderdate;
Cette requête génère la sortie suivante :
orderdate orderid val sumrows sumrange ---------- -------- -------- -------- --------- 2017-07-04 10248 440.00 440.00 440.00 2017-07-05 10249 1863.40 2303.40 2303.40 2017-07-08 10250 1552.60 3856.00 4510.06 2017-07-08 10251 654.06 4510.06 4510.06 2017-07-09 10252 3597.90 8107.96 8107.96 ...
Observez la différence dans les résultats pour les lignes où la même date de commande apparaît plus d’une fois, comme c’est le cas pour le 8 juillet 2017. Remarquez comment l’option ROWS n’inclut pas les liens et est donc non déterministe, et comment l’option RANGE inclut les liens, et est donc toujours déterministe.
On peut cependant se demander si, en pratique, vous avez des cas où vous commandez par quelque chose qui n’est pas unique, et où vous avez vraiment besoin de l’inclusion des liens pour rendre le calcul déterministe. Ce qui est probablement beaucoup plus commun dans la pratique est de faire l’une des deux choses suivantes. L’une est de briser les liens en ajoutant quelque chose à l’ordre de la fenêtre pour le rendre unique et ainsi aboutir à un calcul déterministe, comme ceci:
SELECT orderdate, orderid, val, SUM(val) OVER( ORDER BY orderdate, orderid ROWS UNBOUNDED PRECEDING ) AS runningsum FROM Sales.OrderValues ORDER BY orderdate;
Cette requête génère la sortie suivante :
orderdate orderid val runningsum ---------- -------- --------- ----------- 2017-07-04 10248 440.00 440.00 2017-07-05 10249 1863.40 2303.40 2017-07-08 10250 1552.60 3856.00 2017-07-08 10251 654.06 4510.06 2017-07-09 10252 3597.90 8107.96 ...
Une autre option consiste à appliquer un regroupement préliminaire, dans notre cas, par date de commande, comme suit :
SELECT orderdate, SUM(val) AS daytotal, SUM(SUM(val)) OVER( ORDER BY orderdate ROWS UNBOUNDED PRECEDING ) AS runningsum FROM Sales.OrderValues GROUP BY orderdate ORDER BY orderdate;
Cette requête génère la sortie suivante où chaque date de commande n’apparaît qu’une seule fois :
orderdate daytotal runningsum ---------- --------- ----------- 2017-07-04 440.00 440.00 2017-07-05 1863.40 2303.40 2017-07-08 2206.66 4510.06 2017-07-09 3597.90 8107.96 ...
En tout cas, assurez-vous de vous rappeler la meilleure pratique ici !
La bonne nouvelle est que si vous exécutez sur SQL Server 2016 ou une version ultérieure et que vous avez un index columnstore présent sur les données (même s’il s’agit d’un faux index columnstore filtré), ou si vous exécutez sur SQL Server 2019 ou une version ultérieure, ou sur Azure SQL Database, indépendamment de la présence d’index columnstore, les trois requêtes susmentionnées sont optimisées avec l’opérateur Window Aggregate en mode batch. Grâce à cet opérateur, de nombreuses inefficacités du traitement en mode ligne sont éliminées. Cet opérateur n’utilise pas du tout de spool, il n’y a donc pas de problème de spool en mémoire ou sur disque. Il utilise un traitement plus sophistiqué où il peut appliquer plusieurs passages parallèles sur la fenêtre de lignes en mémoire à la fois pour ROWS et RANGE.
Pour démontrer l’utilisation de l’optimisation en mode batch, assurez-vous que le niveau de compatibilité de votre base de données est défini sur 150 ou plus :
ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 150;
Exécutez à nouveau la requête 1 :
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Le plan de cette requête est présenté à la figure 3.
Figure 3 : Plan de la requête 1, traitement en mode batch
Voici les statistiques de performance que j’ai obtenues pour cette requête :
Lectures logiques de la table ‘Transactions’ : 6208.
Le temps d’exécution est tombé à 1 seconde !
Exécutez à nouveau la requête 2 avec l’option RANGE explicite :
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Le plan de cette requête est présenté dans la figure 4.
Figure 2 : Plan de la requête 2, traitement en mode batch
Voici les statistiques de performance que j’ai obtenues pour cette requête :
Lectures logiques de la table ‘Transactions’ : 6208.
Les performances sont les mêmes que pour la requête 1.
Exécutez à nouveau la requête 3, avec l’option RANGE implicite:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;
Le plan et les chiffres de performances sont bien sûr les mêmes que pour la requête 2.
Quand vous avez terminé, exécutez le code suivant pour désactiver les statistiques de performance :
SET STATISTICS TIME, IO OFF;
N’oubliez pas non plus de désactiver l’option Discard results after execution dans SSMS.
Cadre implicite avec FIRST_VALUE et LAST_VALUE
Les fonctions FIRST_VALUE et LAST_VALUE sont des fonctions de fenêtre décalée qui renvoient une expression de la première ou de la dernière ligne du cadre de la fenêtre, respectivement. La partie délicate à leur sujet est que souvent, lorsque les gens les utilisent pour la première fois, ils ne réalisent pas qu’ils supportent un cadre, plutôt penser qu’ils s’appliquent à la partition entière.
Considérez la tentative suivante de renvoyer les infos de commande, plus les valeurs de la première et de la dernière commande du client :
SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ) AS firstval, LAST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ) AS lastval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
Si vous croyez à tort que ces fonctions fonctionnent sur la partition entière de la fenêtre, ce qui est la croyance de beaucoup de gens qui utilisent ces fonctions pour la première fois, vous vous attendez naturellement à ce que FIRST_VALUE renvoie la valeur de la première commande du client, et LAST_VALUE la valeur de la dernière commande du client. En pratique, cependant, ces fonctions prennent en charge un cadre. Pour rappel, avec les fonctions qui prennent en charge un cadre, lorsque vous spécifiez la clause d’ordre de fenêtre mais pas l’unité de cadre de fenêtre et son étendue associée, vous obtenez RANGE UNBOUNDED PRECEDING par défaut. Avec la fonction FIRST_VALUE, vous obtiendrez le résultat escompté, mais si votre requête est optimisée avec des opérateurs en mode rangée, vous paierez le prix de l’utilisation du spool sur disque. Avec la fonction LAST_VALUE, c’est encore pire. Non seulement vous paierez la pénalité du spool sur disque, mais au lieu d’obtenir la valeur de la dernière ligne de la partition, vous obtiendrez la valeur de la ligne actuelle !
Voici la sortie de la requête ci-dessus :
custid orderdate orderid val firstval lastval ------- ---------- -------- ---------- ---------- ---------- 1 2018-08-25 10643 814.50 814.50 814.50 1 2018-10-03 10692 878.00 814.50 878.00 1 2018-10-13 10702 330.00 814.50 330.00 1 2019-01-15 10835 845.80 814.50 845.80 1 2019-03-16 10952 471.20 814.50 471.20 1 2019-04-09 11011 933.50 814.50 933.50 2 2017-09-18 10308 88.80 88.80 88.80 2 2018-08-08 10625 479.75 88.80 479.75 2 2018-11-28 10759 320.00 88.80 320.00 2 2019-03-04 10926 514.40 88.80 514.40 3 2017-11-27 10365 403.20 403.20 403.20 3 2018-04-15 10507 749.06 403.20 749.06 3 2018-05-13 10535 1940.85 403.20 1940.85 3 2018-06-19 10573 2082.00 403.20 2082.00 3 2018-09-22 10677 813.37 403.20 813.37 3 2018-09-25 10682 375.50 403.20 375.50 3 2019-01-28 10856 660.00 403.20 660.00 ...
Souvent, lorsque les gens voient une telle sortie pour la première fois, ils pensent que SQL Server a un bug. Mais bien sûr, ce n’est pas le cas ; c’est simplement la valeur par défaut de la norme SQL. Il y a un bug dans la requête. Sachant qu’un cadre est impliqué, vous devez être explicite sur la spécification du cadre et utiliser le cadre minimum qui capture la ligne que vous recherchez. Veillez également à utiliser l’unité ROWS. Ainsi, pour obtenir la première ligne de la partition, utilisez la fonction FIRST_VALUE avec le cadre ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Pour obtenir la dernière ligne de la partition, utilisez la fonction LAST_VALUE avec le cadre ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
Voici notre requête révisée avec le bug corrigé:
SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS firstval, LAST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS lastval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
Cette fois, vous obtenez le résultat correct:
custid orderdate orderid val firstval lastval ------- ---------- -------- ---------- ---------- ---------- 1 2018-08-25 10643 814.50 814.50 933.50 1 2018-10-03 10692 878.00 814.50 933.50 1 2018-10-13 10702 330.00 814.50 933.50 1 2019-01-15 10835 845.80 814.50 933.50 1 2019-03-16 10952 471.20 814.50 933.50 1 2019-04-09 11011 933.50 814.50 933.50 2 2017-09-18 10308 88.80 88.80 514.40 2 2018-08-08 10625 479.75 88.80 514.40 2 2018-11-28 10759 320.00 88.80 514.40 2 2019-03-04 10926 514.40 88.80 514.40 3 2017-11-27 10365 403.20 403.20 660.00 3 2018-04-15 10507 749.06 403.20 660.00 3 2018-05-13 10535 1940.85 403.20 660.00 3 2018-06-19 10573 2082.00 403.20 660.00 3 2018-09-22 10677 813.37 403.20 660.00 3 2018-09-25 10682 375.50 403.20 660.00 3 2019-01-28 10856 660.00 403.20 660.00 ...
On se demande quelle était la motivation de la norme pour même supporter un cadre avec ces fonctions. Si vous y réfléchissez, vous les utiliserez surtout pour obtenir quelque chose des premières ou dernières lignes de la partition. Si vous avez besoin de la valeur de, disons, deux rangées avant la courante, au lieu d’utiliser FIRST_VALUE avec un cadre qui commence par 2 PRECEDING, n’est-il pas beaucoup plus facile d’utiliser LAG avec un décalage explicite de 2, comme ceci :
SELECT custid, orderdate, orderid, val, LAG(val, 2) OVER( PARTITION BY custid ORDER BY orderdate, orderid ) AS prevtwoval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
Cette requête génère la sortie suivante:
custid orderdate orderid val prevtwoval ------- ---------- -------- ---------- ----------- 1 2018-08-25 10643 814.50 NULL 1 2018-10-03 10692 878.00 NULL 1 2018-10-13 10702 330.00 814.50 1 2019-01-15 10835 845.80 878.00 1 2019-03-16 10952 471.20 330.00 1 2019-04-09 11011 933.50 845.80 2 2017-09-18 10308 88.80 NULL 2 2018-08-08 10625 479.75 NULL 2 2018-11-28 10759 320.00 88.80 2 2019-03-04 10926 514.40 479.75 3 2017-11-27 10365 403.20 NULL 3 2018-04-15 10507 749.06 NULL 3 2018-05-13 10535 1940.85 403.20 3 2018-06-19 10573 2082.00 749.06 3 2018-09-22 10677 813.37 1940.85 3 2018-09-25 10682 375.50 2082.00 3 2019-01-28 10856 660.00 813.37 ...
Apparemment, il y a une différence sémantique entre l’utilisation ci-dessus de la fonction LAG et FIRST_VALUE avec un cadre qui commence avec 2 PRECEDING. Avec la première, si une ligne n’existe pas dans le décalage souhaité, vous obtenez un NULL par défaut. Avec la seconde, vous obtenez toujours la valeur de la première ligne présente, c’est-à-dire la valeur de la première ligne de la partition. Considérez la requête suivante:
SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS prevtwoval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
Cette requête génère la sortie suivante:
custid orderdate orderid val prevtwoval ------- ---------- -------- ---------- ----------- 1 2018-08-25 10643 814.50 814.50 1 2018-10-03 10692 878.00 814.50 1 2018-10-13 10702 330.00 814.50 1 2019-01-15 10835 845.80 878.00 1 2019-03-16 10952 471.20 330.00 1 2019-04-09 11011 933.50 845.80 2 2017-09-18 10308 88.80 88.80 2 2018-08-08 10625 479.75 88.80 2 2018-11-28 10759 320.00 88.80 2 2019-03-04 10926 514.40 479.75 3 2017-11-27 10365 403.20 403.20 3 2018-04-15 10507 749.06 403.20 3 2018-05-13 10535 1940.85 403.20 3 2018-06-19 10573 2082.00 749.06 3 2018-09-22 10677 813.37 1940.85 3 2018-09-25 10682 375.50 2082.00 3 2019-01-28 10856 660.00 813.37 ...
Observez que cette fois il n’y a pas de NULL dans la sortie. Il y a donc une certaine valeur à supporter un cadre avec FIRST_VALUE et LAST_VALUE. Assurez-vous simplement que vous vous rappelez la meilleure pratique de toujours être explicite sur la spécification du cadre avec ces fonctions, et d’utiliser l’option ROWS avec le cadre minimal qui contient la ligne que vous recherchez.
Conclusion
Cet article s’est concentré sur les bogues, les pièges et les meilleures pratiques liés aux fonctions de fenêtre. Rappelez-vous que les fonctions d’agrégation de fenêtre et les fonctions de décalage de fenêtre FIRST_VALUE et LAST_VALUE prennent en charge un cadre, et que si vous spécifiez la clause d’ordre de fenêtre mais que vous ne spécifiez pas l’unité de cadre de fenêtre et son étendue associée, vous obtenez RANGE UNBOUNDED PRECEDING par défaut. Cela entraîne une pénalité de performance lorsque la requête est optimisée avec des opérateurs en mode ligne. Avec la fonction LAST_VALUE, cela se traduit par l’obtention des valeurs de la ligne actuelle au lieu de la dernière ligne de la partition. N’oubliez pas d’être explicite sur le cadre et de préférer généralement l’option ROWS à RANGE. C’est formidable de voir les améliorations de performance avec l’opérateur Window Aggregate en mode batch. Lorsqu’il est applicable, au moins l’écueil des performances est éliminé.