Denne artikel er den fjerde del i en serie om T-SQL fejl, faldgruber og bedste praksis. Tidligere har jeg dækket determinisme, subqueries og joins. Fokus i denne måneds artikel er fejl, faldgruber og bedste praksis i forbindelse med vinduesfunktioner. Tak Erland Sommarskog, Aaron Bertrand, Alejandro Mesa, Umachandar Jayachandran (UC), Fabiano Neves Amorim, Milos Radivojevic, Simon Sabin, Adam Machanic, Thomas Grohser, Chan Ming Man og Paul White for at tilbyde jeres idéer!

I mine eksempler vil jeg bruge en eksempeldatabase kaldet TSQLV5. Du kan finde det script, der opretter og udfylder denne database her, og dens ER-diagram her.

Der er to almindelige faldgruber, der involverer vinduesfunktioner, som begge er resultatet af kontraintuitive implicitte standardindstillinger, der er pålagt af SQL-standarden. Den ene faldgrube har at gøre med beregninger af løbende totaler, hvor man får en vinduesramme med den implicitte RANGE-indstilling. En anden faldgrube er noget beslægtet, men har mere alvorlige konsekvenser, idet den vedrører en implicit rammedefinition for FIRST_VALUE- og LAST_VALUE-funktionerne.

Fensterramme med implicit RANGE-indstilling

Vores første faldgrube vedrører beregning af løbende totaler ved hjælp af en aggregeret vinduesfunktion, hvor du eksplicit angiver klausulen om vinduesorden, men du angiver ikke eksplicit vinduesrammenheden (ROWS eller RANGE) og dens tilhørende vinduesrammeudstrækning, f.eks, ROWS UNBOUNDED FOREGÅENDE. Den implicitte standard er kontraintuitiv, og dens konsekvenser kan være overraskende og smertefulde.

For at demonstrere denne faldgrube bruger jeg en tabel kaldet Transaktioner, der indeholder to millioner bankkontotransaktioner med kreditter (positive værdier) og debetter (negative værdier). Kør følgende kode for at oprette tabellen Transactions og fylde den med eksempeldata:

 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;

Vores faldgrube har både en logisk side med en potentiel logisk fejl samt en ydelsesside med en ydelsessanktion. Ydelsesstraffen er kun relevant, når vinduesfunktionen er optimeret med behandlingsoperatorer i rækketilstand. SQL Server 2016 introducerer batch-mode Window Aggregate-operatoren, som fjerner ydelsesstrafdelen af faldgruben, men før SQL Server 2019 bruges denne operatør kun, hvis du har et columnstore-indeks til stede på dataene. SQL Server 2019 introducerer batch-mode på rowstore-understøttelse, så du kan få batch-mode behandling, selv om der ikke er kolonnestore indekser til stede på dataene. For at demonstrere ydelsesbortfaldet med row-mode-behandling skal du, hvis du kører kodeeksemplerne i denne artikel på SQL Server 2019 eller senere eller på Azure SQL Database, bruge følgende kode til at indstille databasekompatibilitetsniveauet til 140 for ikke at aktivere batch-mode on rowstore endnu:

 ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 140;

Brug følgende kode for at slå tids- og I/O-statistik til i sessionen:

 SET STATISTICS TIME, IO ON;

For at undgå at vente på, at to millioner rækker bliver udskrevet i SSMS, foreslår jeg, at kodeeksemplerne i dette afsnit køres med indstillingen Kassér resultater efter udførelse slået til (gå til Query Options, Results, Grid, og marker Kassér resultater efter udførelse).

Hvor vi kommer til faldgruben, skal du overveje følgende forespørgsel (kald den forespørgsel 1), som beregner bankkontosaldoen efter hver transaktion ved at anvende en løbende total ved hjælp af en vinduesaggregatfunktion med en eksplicit rammespecifikation:

 SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;

Planen for denne forespørgsel, der anvender behandling i rækketilstand, er vist i figur 1.

Figur 1: Plan for forespørgsel 1, row-mode-behandling

Planen henter dataene forudbestilt fra tabellens clusterede indeks. Derefter bruger den operatørerne Segment og Sequence Project til at beregne rækketal for at finde ud af, hvilke rækker der hører til den aktuelle rækkes ramme. Derefter bruger den operatørerne Segment, Window Spool og Stream Aggregate til at beregne vinduesaggregatfunktionen. Window Spool-operatoren bruges til at spole de rammerækker, som derefter skal aggregeres. Uden en særlig optimering ville planen have været nødt til at skrive alle de relevante rammerækker pr. række til spoolen og derefter aggregere dem. Dette ville have resulteret i en kvadratisk kompleksitet, eller N2. Den gode nyhed er, at når rammen starter med UNBOUNDED PRECEDING, identificerer SQL Server sagen som en fast track-sag, hvor den simpelthen tager den foregående rækkes løbende totalsum og tilføjer den aktuelle rækkes værdi for at beregne den aktuelle rækkes løbende totalsum, hvilket resulterer i lineær skalering. I denne fast track-tilstand skriver planen kun to rækker til spoolen pr. indgangsrække – en med aggregatet og en med detaljerne.

Vinduesspoolen kan fysisk implementeres på en af to måder. Enten som en hurtig in-memory spool, der er specielt designet til vinduesfunktioner, eller som en langsom on-disk spool, som i det væsentlige er en midlertidig tabel i tempdb. Hvis antallet af rækker, der skal skrives til spoolen pr. underliggende række, kan overstige 10.000, eller hvis SQL Server ikke kan forudsige antallet, vil den bruge den langsommere on-disk spool. I vores forespørgselsplan har vi præcis to rækker skrevet til spoolen pr. underliggende række, så SQL Server bruger in-memory-spoolen. Desværre er der ingen måde at se i planen, hvilken slags spool du får. Der er to måder at finde ud af det på. Den ene er at bruge en udvidet begivenhed kaldet window_spool_ondisk_warning. En anden mulighed er at aktivere STATISTICS IO og kontrollere antallet af logiske læsninger, der er rapporteret for en tabel kaldet Worktable. Et tal, der er større end nul, betyder, at du har fået on-disk spool. Nul betyder, at du fik spoolen i hukommelsen. Her er I/O-statistikkerne for vores forespørgsel:

Som du kan se, fik vi brugt in-memory-spoolen. Det er generelt tilfældet, når du bruger ROWS-vinduets rammeenhed med UNBOUNDED PRECEDING som den første afgrænser.

Her er tidsstatistikken for vores forespørgsel:

Det tog denne forespørgsel ca. 4,5 sekunder at gennemføre på min maskine med resultaterne kasserede.

Nu kommer fangsten. Hvis du bruger RANGE-indstillingen i stedet for ROWS, med de samme afgrænsere, kan der være en subtil forskel i betydning, men en stor forskel i ydeevne i rækketilstand. Forskellen i betydning er kun relevant, hvis du ikke har total ordnering, dvs. hvis du ordner efter noget, der ikke er unikt. Indstillingen ROWS UNBOUNDED PRECEDING stopper med den aktuelle række, så i tilfælde af uafgjorthed er beregningen ikke deterministisk. Omvendt ser indstillingen RANGE UNBOUNDED PRECEDING fremad fra den aktuelle række og medregner eventuelle uafgjortheder. Den anvender samme logik som indstillingen TOP WITH TIES. Når der er tale om en samlet rækkefølge, dvs. når der er tale om en unik rækkefølge, er der ingen bindinger, der skal medtages, og derfor er ROWS og RANGE logisk set ækvivalente i et sådant tilfælde. Problemet er, at når du bruger RANGE, bruger SQL Server altid on-disk spool under row-mode behandling, da den ved behandling af en given række ikke kan forudsige, hvor mange flere rækker der vil blive inkluderet. Dette kan have en alvorlig ydelsesstraf.

Opmærksomheden henledes på følgende forespørgsel (kald den forespørgsel 2), som er den samme som forespørgsel 1, blot med RANGE-indstillingen i stedet for ROWS:

 SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;

Planen for denne forespørgsel er vist i figur 2.

Figur 2: Plan for forespørgsel 2, row-mode processing

Forespørgsel 2 svarer logisk set til forespørgsel 1, fordi vi har total orden; men da den bruger RANGE, bliver den optimeret med on-disk-spoolen. Bemærk, at i planen for forespørgsel 2 ser Window Spool ud som i planen for forespørgsel 1, og de anslåede omkostninger er de samme.

Her er tids- og I/O-statistikkerne for udførelsen af forespørgsel 2:

Bemærk det store antal logiske læsninger mod Worktable, hvilket indikerer, at du har fået spoolen på disken. Køretiden er mere end fire gange længere end for forespørgsel 1.

Hvis du tænker, at hvis det er tilfældet, vil du simpelthen undgå at bruge RANGE-indstillingen, medmindre du virkelig har brug for at medtage bånd, er det en god idé. Problemet er, at hvis du bruger en vinduesfunktion, der understøtter en ramme (aggregater, FIRST_VALUE, LAST_VALUE) med en eksplicit vinduesordreklausul, men uden omtale af vinduesrammeenheden og dens tilhørende omfang, får du som standard RANGE UNBOUNDED PRECEDING. Denne standard er dikteret af SQL-standarden, og standarden har valgt den, fordi den generelt foretrækker mere deterministiske muligheder som standardindstillinger. Følgende forespørgsel (kald den forespørgsel 3) er et eksempel, der falder i denne fælde:

 SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;

Ofte skriver folk sådan her, idet de antager, at de får ROWS UNBOUNDED PRECEDING som standard, uden at de er klar over, at de faktisk får RANGE UNBOUNDED PRECEDING. Sagen er den, at da funktionen bruger total orden, får du det samme resultat som med ROWS, så du kan ikke se, at der er et problem ud fra resultatet. Men de præstationstal, som du får, er som for forespørgsel 2. Jeg ser folk falde i denne fælde hele tiden.

Den bedste praksis for at undgå dette problem er i tilfælde, hvor du bruger en vinduesfunktion med en ramme, at være eksplicit om vinduesrammeenheden og dens omfang, og generelt at foretrække ROWS. Reserver brugen af RANGE kun til tilfælde, hvor rækkefølgen ikke er entydig, og hvor du har brug for at medtage bindinger.

Overvej følgende forespørgsel, der illustrerer et tilfælde, hvor der er en konceptuel forskel mellem ROWS og 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;

Denne forespørgsel genererer følgende output:

 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 ...

Opmærksomheden henledes på forskellen i resultaterne for de rækker, hvor den samme bestillingsdato optræder mere end én gang, som det er tilfældet for 8. juli 2017. Bemærk, hvordan ROWS-indstillingen ikke omfatter bindinger og derfor er ikke-deterministisk, og hvordan RANGE-indstillingen omfatter bindinger og derfor altid er deterministisk.

Det er dog tvivlsomt, om man i praksis har tilfælde, hvor man bestiller efter noget, der ikke er unikt, og hvor man virkelig har brug for at medtage bindinger for at gøre beregningen deterministisk. Det, der nok er langt mere almindeligt i praksis, er at gøre en af to ting. Den ene er at bryde bånd ved at tilføje noget til vinduesordren for at gøre den unik og på den måde resultere i en deterministisk beregning, som f.eks. således:

 SELECT orderdate, orderid, val, SUM(val) OVER( ORDER BY orderdate, orderid ROWS UNBOUNDED PRECEDING ) AS runningsum FROM Sales.OrderValues ORDER BY orderdate;

 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 ...

 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 ...

 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;

Denne forespørgsel genererer følgende output, hvor hver ordredato kun vises én gang:

 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 ...

Sørg under alle omstændigheder for at huske den bedste praksis her!

Den gode nyhed er, at hvis du kører på SQL Server 2016 eller nyere og har et columnstore-indeks til stede på dataene (selv om det er et falsk filtreret columnstore-indeks), eller hvis du kører på SQL Server 2019 eller nyere eller på Azure SQL Database, uanset tilstedeværelsen af columnstore-indekser, bliver alle tre førnævnte forespørgsler optimeret med operatoren Window Aggregate i batch-tilstand. Med denne operatør elimineres mange af de ineffektiviteter, der er forbundet med behandling i rækketilstand. Denne operatør bruger slet ikke en spool, så der er ikke noget problem med spool i hukommelsen versus spool på disken. Den anvender en mere sofistikeret behandling, hvor den kan anvende flere parallelle gennemløb over vinduet med rækker i hukommelsen for både ROWS og RANGE.

 SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;

Planen for denne forespørgsel er vist i figur 3.

Figur 3: Plan for forespørgsel 1, batch-mode processing

Her er de ydelsesstatistikker, som jeg fik for denne forespørgsel:

Kørselstiden faldt til 1 sekund!

Kør forespørgsel 2 med den eksplicitte RANGE-indstilling igen:

 SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;

Planen for denne forespørgsel er vist i figur 4.

Figur 2: Plan for forespørgsel 2, batch-mode processing

Her er de ydelsesstatistikker, som jeg fik for denne forespørgsel:

Præstationen er den samme som for forespørgsel 1.

Kør forespørgsel 3 igen med den implicitte RANGE-indstilling:

 SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;

 SET STATISTICS TIME, IO OFF;

Glem heller ikke at slå indstillingen “Discard results after execution” fra i SSMS.

Implicit ramme med FIRST_VALUE og LAST_VALUE

Funktionerne FIRST_VALUE og LAST_VALUE er offset-vinduefunktioner, der returnerer et udtryk fra henholdsvis den første eller sidste række i vinduesrammen. Det vanskelige ved dem er, at når folk ofte bruger dem for første gang, er de ikke klar over, at de understøtter en ramme, men tror snarere, at de gælder for hele partitionen.

Tænk på følgende forsøg på at returnere ordreoplysninger plus værdierne for kundens første og sidste ordre:

 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;

Hvis man fejlagtigt tror, at disse funktioner virker på hele vinduespartitionen, hvilket mange mennesker, der bruger disse funktioner for første gang, tror, forventer man naturligvis, at FIRST_VALUE returnerer ordreværdien for kundens første ordre, og at LAST_VALUE returnerer ordreværdien for kundens sidste ordre. I praksis understøtter disse funktioner dog en ramme. Til minde om, at når du med funktioner, der understøtter en ramme, angiver klausulen om vinduesorden, men ikke vinduesrammenheden og dens tilhørende omfang, får du som standard RANGE UNBOUNDED PRECEDING (Række ubegrænset forudgående). Med funktionen FIRST_VALUE får du det forventede resultat, men hvis din forespørgsel optimeres med operatorer i rækketilstand, vil du betale straffen for at bruge on-disk-spoolen. Med LAST_VALUE-funktionen er det endnu værre. Ikke alene betaler du straffen for on-disk spool, men i stedet for at få værdien fra den sidste række i partitionen, får du værdien fra den aktuelle række!

Her er output af ovenstående forespørgsel:

 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 ...

Ofte når folk ser et sådant output for første gang, tror de, at SQL Server har en fejl. Men det har den selvfølgelig ikke; det er simpelthen SQL-standardens standard. Der er en fejl i forespørgslen. Når man er klar over, at der er en ramme involveret, skal man være eksplicit omkring rammespecifikationen og bruge den mindste ramme, der fanger den række, som man er ude efter. Du skal også sikre dig, at du bruger ROWS-enheden. Så for at få fat i den første række i partitionen skal du bruge funktionen FIRST_VALUE med rammen ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Hvis du vil have den sidste række i partitionen, skal du bruge funktionen LAST_VALUE med rammen ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING (mellem CURRENT ROW og UNBUNDED FOLLOWING).

Her er vores reviderede forespørgsel med fejlen rettet:

 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;

Denne gang får man det korrekte resultat:

 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 ...

Man kan undre sig over, hvad der var motivationen for standarden til overhovedet at understøtte en ramme med disse funktioner. Hvis man tænker over det, vil man for det meste bruge dem til at få noget fra de første eller sidste rækker i partitionen. Hvis man har brug for værdien fra f.eks. to rækker før den aktuelle, i stedet for at bruge FIRST_VALUE med en ramme, der starter med 2 PRECEDING, er det så ikke meget nemmere at bruge LAG med en eksplicit offset på 2, som f.eks:

 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;

Denne forespørgsel genererer følgende output:

 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 ...

Der er tilsyneladende en semantisk forskel mellem ovenstående brug af LAG-funktionen og FIRST_VALUE med en ramme, der starter med 2 PRECEDING. Med førstnævnte får man som standard NULL, hvis der ikke findes en række i den ønskede offset, hvis der ikke findes en række i den ønskede offset. Med sidstnævnte får du stadig værdien fra den første række, der er til stede, dvs. værdien fra den første række i partitionen. Overvej følgende forespørgsel:

 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;

Denne forespørgsel genererer følgende output:

 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 ...

Opmærksomheden henledes på, at der denne gang ikke er nogen NULL’er i outputtet. Der er altså en vis værdi i at understøtte en ramme med FIRST_VALUE og LAST_VALUE. Du skal blot sørge for at huske den bedste praksis om altid at være eksplicit med hensyn til ramespecifikationen med disse funktioner og at bruge ROWS-indstillingen med den minimale ramme, der indeholder den række, du er ude efter.

Slutning

Denne artikel fokuserede på fejl, faldgruber og bedste praksis i forbindelse med vinduesfunktioner. Husk, at både vinduesaggregatfunktioner og vinduesforskydningsfunktionerne FIRST_VALUE og LAST_VALUE understøtter en ramme, og at hvis du angiver vinduesordreklausulen, men ikke angiver vinduets rammeenhed og den tilhørende udstrækning, får du som standard RANGE UNBOUNDED PRECEDING (Række ubegrænset forudgående). Dette medfører en ydelsesmæssig straf, når forespørgslen bliver optimeret med row-mode-operatører. Med LAST_VALUE-funktionen resulterer dette i at få værdierne fra den aktuelle række i stedet for den sidste række i partitionen. Husk at være eksplicit om rammen og generelt at foretrække ROWS-indstillingen frem for RANGE. Det er dejligt at se ydelsesforbedringerne med Window Aggregate-operatoren i batch-mode. Når den er anvendelig, er i det mindste ydelsesfælden elimineret.