Tento článek je čtvrtým dílem seriálu o chybách, nástrahách a osvědčených postupech v jazyce T-SQL. V předchozím díle jsem se věnoval determinismu, poddotazům a spojování. Tento měsíc se zaměřím na chyby, nástrahy a osvědčené postupy týkající se okenních funkcí. Děkuji Erlandu Sommarskogovi, Aaronu Bertrandovi, Alejandru Mesovi, Umachandaru Jayachandranovi (UC), Fabianu Nevesovi Amorimovi, Miloši Radivojevičovi, Simonu Sabinovi, Adamu Machanicovi, Thomasi Grohserovi, Chan Ming Manovi a Paulu Whiteovi za nabídnuté nápady!“
V příkladech budu používat ukázkovou databázi TSQLV5. Skript, který tuto databázi vytváří a naplňuje, najdete zde a její ER diagram zde.
Existují dvě častá úskalí týkající se okenních funkcí, přičemž obě jsou důsledkem neintuitivních implicitních výchozích nastavení, která jsou uložena standardem SQL. Jedno úskalí souvisí s výpočty průběžných součtů, kdy získáte rámec okna s implicitní volbou RANGE. Další úskalí s tím poněkud souvisí, ale má závažnější důsledky a týká se implicitní definice rámce pro funkce FIRST_VALUE a LAST_VALUE.
Rámec okna s implicitní volbou RANGE
Naše první úskalí se týká výpočtu průběžných součtů pomocí souhrnné funkce okna, kde sice explicitně zadáte klauzuli pořadí okna, ale explicitně nezadáte jednotku rámce okna (ROWS nebo RANGE) a s ní související rozsah rámce okna, např, ROWS UNBOUNDED PRECEDING. Implicitní výchozí nastavení je neintuitivní a jeho důsledky mohou být překvapivé a bolestivé.
Pro demonstraci tohoto úskalí použiji tabulku Transakce obsahující dva miliony transakcí na bankovních účtech s kredity (kladné hodnoty) a debety (záporné hodnoty). Spusťte následující kód, který vytvoří tabulku Transakce a naplní ji ukázkovými daty:
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;
Naše úskalí má jak logickou stránku s potenciální logickou chybou, tak výkonnostní stránku s postihem za výkon. Výkonnostní postih má význam pouze v případě, že je funkce okna optimalizována pomocí operátorů pro zpracování v řádkovém režimu. SQL Server 2016 zavádí operátor Window Aggregate v dávkovém režimu, který odstraňuje výkonnostní penalizaci této pasti, ale před SQL Serverem 2019 se tento operátor používá pouze v případě, že je u dat přítomen index sloupcového skladu. SQL Server 2019 zavádí podporu dávkového režimu na řádkovém skladu, takže můžete získat zpracování v dávkovém režimu, i když na datech nejsou přítomny indexy sloupcového skladu. Chcete-li demonstrovat snížení výkonu při zpracování v řádkovém režimu, pokud spouštíte ukázky kódu v tomto článku na serveru SQL Server 2019 nebo novějším nebo na databázi Azure SQL Database, použijte následující kód a nastavte úroveň kompatibility databáze na 140, aby ještě nebyl povolen dávkový režim na řádkovém úložišti:
ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 140;
Pomocí následujícího kódu zapnete statistiky času a I/O v relaci:
SET STATISTICS TIME, IO ON;
Abyste nemuseli čekat na vypsání dvou milionů řádků v SSMS, doporučuji spouštět ukázky kódu v této části se zapnutou volbou Discard results after execution (přejděte do Query Options, Results, Grid a zaškrtněte Discard results after execution).
Než se dostaneme k úskalí, uvažujme následující dotaz (nazvěme jej Dotaz 1), který počítá zůstatek na bankovním účtu po každé transakci použitím průběžného součtu pomocí okenní agregační funkce s explicitní specifikací rámce:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Plán tohoto dotazu, využívajícího zpracování v řádkovém režimu, je znázorněn na obr. 1.
Obrázek 1: Plán pro dotaz 1, zpracování v řádkovém režimu
Plán vytáhne data předřazená ze shlukového indexu tabulky. Poté pomocí operátorů Segment a Sequence Project vypočítá čísla řádků, aby zjistil, které řádky patří do rámce aktuálního řádku. Poté použije operátory Segment, Okenní zásobník a Proudová agregace k výpočtu okenní agregační funkce. Operátor Window Spool slouží k seskupení řádků rámce, které je pak třeba agregovat. Bez speciální optimalizace by plán musel na každý řádek zapsat všechny příslušné řádky rámce na cívku a pak je agregovat. To by vedlo ke kvadratické nebo N2 složitosti. Dobrou zprávou je, že pokud rámec začíná s UNBOUNDED PRECEDING, SQL Server identifikuje tento případ jako zrychlený případ, ve kterém jednoduše vezme průběžný součet předchozího řádku a přičte hodnotu aktuálního řádku pro výpočet průběžného součtu aktuálního řádku, což vede k lineárnímu škálování. V tomto zrychleném režimu zapisuje plán do zásobníku pouze dva řádky na jeden vstupní řádek – jeden se součtem a jeden s detailem.
Zásobník oken lze fyzicky realizovat jedním ze dvou způsobů. Buď jako rychlý in-memory spool, který byl speciálně navržen pro okenní funkce, nebo jako pomalý on-disk spool, což je v podstatě dočasná tabulka v tempdb. Pokud by počet řádků, které je třeba zapsat do spoolu na jeden základní řádek, mohl překročit 10 000, nebo pokud SQL Server nemůže tento počet předvídat, použije pomalejší spool na disku. V našem plánu dotazu máme přesně dva řádky zapsané do spoolu na jeden podkladový řádek, takže SQL Server používá spool v paměti. Bohužel z plánu nelze zjistit, jaký druh spoolu získáte. Existují dva způsoby, jak to zjistit. Jedním z nich je použití rozšířené události s názvem window_spool_ondisk_warning. Druhou možností je zapnout STATISTICS IO a zkontrolovat počet logických čtení hlášených pro tabulku s názvem Worktable. Číslo větší než nula znamená, že jste dostali spool na disku. Nula znamená, že jste dostali spool v paměti. Zde je statistika I/O pro náš dotaz:
Jak vidíte, máme využitý spool v paměti. To je obecně případ, kdy použijete jednotku rámce okna ROWS s UNBOUNDED PRECEDING jako prvním oddělovačem.
Zde jsou časové statistiky našeho dotazu:
Přibližně 4,5 sekundy trvalo dokončení tohoto dotazu na mém počítači se zahozenými výsledky.
Teď háček. Pokud použijete volbu RANGE místo ROWS se stejnými oddělovači, může být nepatrný rozdíl ve významu, ale velký rozdíl ve výkonu v řádkovém režimu. Rozdíl ve významu má význam pouze v případě, že nemáte celkové řazení, tj. pokud řadíte podle něčeho, co není jedinečné. Volba ROWS UNBOUNDED PRECEDING se zastaví na aktuálním řádku, takže v případě vazeb je výpočet nedeterministický. Naopak volba RANGE UNBOUNDED PRECEDING se dívá před aktuální řádek a zahrnuje vazby, pokud jsou přítomny. Používá podobnou logiku jako volba TOP WITH TIES. Pokud máte celkové řazení, tj. řadíte podle něčeho jedinečného, není třeba zahrnovat vazby, a proto se v takovém případě stávají volby ROWS a RANGE logicky ekvivalentní. Problém je v tom, že když použijete RANGE, SQL Server při zpracování v řádkovém režimu vždy použije spool na disku, protože při zpracování daného řádku nemůže předvídat, kolik dalších řádků bude zahrnuto. To může mít vážné dopady na výkon.
Považte následující dotaz (nazvěme jej dotaz 2), který je stejný jako dotaz 1, pouze místo možnosti ROWS používá možnost RANGE:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Plán pro tento dotaz je znázorněn na obrázku 2.
Obrázek 2: Plán pro dotaz 2, zpracování v řádkovém režimu
Dotaz 2 je logicky ekvivalentní dotazu 1, protože máme celkové pořadí; protože však používá RANGE, optimalizuje se pomocí spoolu na disku. Všimněte si, že v plánu pro dotaz 2 vypadá Window Spool stejně jako v plánu pro dotaz 1 a odhadované náklady jsou stejné.
Zde jsou časové a I/O statistiky pro provedení dotazu 2:
Logické čtení tabulky ‚Worktable‘: 12044701. Tabulka „Transakce“ logicky čte: 44441:
Všimněte si velkého počtu logických čtení proti tabulce Worktable, což naznačuje, že jste získali spool na disku. Doba běhu je více než čtyřikrát delší než u dotazu 1.
Pokud si myslíte, že v takovém případě se prostě vyhnete použití volby RANGE, pokud opravdu nepotřebujete zahrnout vazby, je to dobrá úvaha. Problém je v tom, že pokud použijete okenní funkci, která podporuje rámec (agregáty, FIRST_VALUE, LAST_VALUE) s explicitní klauzulí o pořadí oken, ale bez zmínky o jednotce rámce okna a jeho přidruženém rozsahu, dostanete ve výchozím nastavení RANGE UNBOUNDED PRECEDING. Toto výchozí nastavení je diktováno standardem SQL a standard jej zvolil proto, že jako výchozí nastavení obecně preferuje determinističtější možnosti. Následující dotaz (říkejme mu dotaz 3) je příkladem, který spadá do této pasti:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;
Často lidé takto píší v domnění, že ve výchozím nastavení dostanou ROWS UNBOUNDED PRECEDING, aniž by si uvědomili, že ve skutečnosti dostanou RANGE UNBOUNDED PRECEDING. Jde o to, že vzhledem k tomu, že funkce používá celkové pořadí, dostanete stejný výsledek jako u ROWS, takže z výsledku nepoznáte, že je nějaký problém. Ale čísla výkonu, která získáte, jsou jako u dotazu 2. Stále vidím lidi, kteří se do této pasti chytají.
Nejlepším postupem, jak se tomuto problému vyhnout, je v případech, kdy používáte funkci okna s rámcem, explicitně uvádět jednotku rámce okna a jeho rozsah a obecně preferovat ROWS. Použití RANGE si vyhraďte pouze pro případy, kdy uspořádání není jednoznačné a potřebujete zahrnout vazby.
Uvažujte následující dotaz ilustrující případ, kdy existuje koncepční rozdíl mezi ROWS a 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;
Tento dotaz generuje následující výstup:
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 ...
Všimněte si rozdílu ve výsledcích u řádků, kde se stejné datum objednávky vyskytuje vícekrát, jako je tomu v případě 8. července 2017. Všimněte si, že možnost ROWS nezahrnuje vazby, a je tedy nedeterministická, a že možnost RANGE vazby zahrnuje, a je tedy vždy deterministická.
Je však otázkou, zda se v praxi vyskytují případy, kdy objednáváte podle něčeho, co není jedinečné, a skutečně potřebujete zahrnutí vazeb, aby byl výpočet deterministický. V praxi se pravděpodobně mnohem častěji dělá jedna ze dvou věcí. Jednou z nich je přerušit vazby přidáním něčeho do pořadí oken, aby bylo jedinečné, a tím docílit deterministického výpočtu, například takto:
SELECT orderdate, orderid, val, SUM(val) OVER( ORDER BY orderdate, orderid ROWS UNBOUNDED PRECEDING ) AS runningsum FROM Sales.OrderValues ORDER BY orderdate;
Tento dotaz generuje následující výstup:
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 ...
Další možností je použít předběžné seskupení, v našem případě podle data objednávky, takto:
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;
Tento dotaz vygeneruje následující výstup, kde se každé datum objednávky objeví pouze jednou:
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 ...
V každém případě nezapomeňte na osvědčený postup!
Dobrou zprávou je, že pokud používáte SQL Server 2016 nebo novější a máte na datech přítomen index sloupcového skladu (i když se jedná o falešný filtrovaný index sloupcového skladu) nebo pokud používáte SQL Server 2019 nebo novější nebo Azure SQL Database, bez ohledu na přítomnost indexů sloupcového skladu se všechny tři výše uvedené dotazy optimalizují pomocí operátoru Window Aggregate v dávkovém režimu. Pomocí tohoto operátoru se odstraní mnoho neefektivit při zpracování v řádkovém režimu. Tento operátor vůbec nepoužívá spool, takže odpadá otázka spoolu v paměti a na disku. Používá sofistikovanější zpracování, kdy může použít více paralelních průchodů nad oknem řádků v paměti jak pro ROWS, tak pro RANGE.
Pro demonstraci použití optimalizace v dávkovém režimu se ujistěte, že je úroveň kompatibility databáze nastavena na 150 nebo vyšší:
ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 150;
Znovu spusťte dotaz č. 1:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Plán tohoto dotazu je znázorněn na obrázku 3.
Obrázek 3: Plán pro dotaz 1, zpracování v dávkovém režimu
Tady jsou statistiky výkonu, které jsem získal pro tento dotaz:
Tabulka ‚Transakce‘ logické čtení:
Znovu spustit dotaz 2 s explicitní volbou RANGE:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Plán pro tento dotaz je zobrazen na obrázku 4.
Obrázek 2: Plán pro dotaz 2, zpracování v dávkovém režimu
Tady jsou statistiky výkonu, které jsem získal pro tento dotaz:
Tabulka ‚Transakce‘ logické čtení:
Znovu spustit dotaz 3 s implicitní volbou RANGE:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;
Plán a čísla výkonu jsou samozřejmě stejné jako u dotazu 2.
Když jste hotovi, spusťte následující kód pro vypnutí statistik výkonu:
SET STATISTICS TIME, IO OFF;
Také nezapomeňte vypnout volbu Discard results after execution v SSMS.
Implicitní rámec s FIRST_VALUE a LAST_VALUE
Funkce FIRST_VALUE a LAST_VALUE jsou odsazené okenní funkce, které vracejí výraz z prvního, resp. posledního řádku v okenním rámci. Jejich záludnost spočívá v tom, že když je lidé použijí poprvé, často si neuvědomí, že podporují rámec, a spíše si myslí, že se vztahují na celý oddíl.
Podívejte se na následující pokus o vrácení informací o objednávce, navíc s hodnotami první a poslední objednávky zákazníka:
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;
Pokud se nesprávně domníváte, že tyto funkce působí na celý oddíl okna, což je přesvědčení mnoha lidí, kteří tyto funkce používají poprvé, přirozeně očekáváte, že FIRST_VALUE vrátí hodnotu první objednávky zákazníka a LAST_VALUE vrátí hodnotu poslední objednávky zákazníka. V praxi však tyto funkce podporují rámec. Připomínáme, že pokud u funkcí, které podporují rámec, zadáte klauzuli o pořadí okna, ale nezadáte jednotku rámce okna a její přidružený rozsah, dostanete ve výchozím nastavení RANGE UNBOUNDED PRECEDING. S funkcí FIRST_VALUE získáte očekávaný výsledek, ale pokud se váš dotaz optimalizuje pomocí operátorů v řádkovém režimu, zaplatíte daň za použití spoolu na disku. S funkcí LAST_VALUE je to ještě horší. Nejen že zaplatíte pokutu za použití on-disk spoolu, ale místo toho, abyste dostali hodnotu z posledního řádku v oddílu, dostanete hodnotu z aktuálního řádku!“
Tady je výstup výše uvedeného dotazu:
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 ...
Často, když lidé vidí takový výstup poprvé, myslí si, že SQL Server má chybu. Ale samozřejmě nemá; je to prostě výchozí nastavení standardu SQL. Chyba je v dotazu. Uvědomíte-li si, že se jedná o rámec, chcete být ve specifikaci rámce explicitní a použít minimální rámec, který zachycuje požadovaný řádek. Také se ujistěte, že používáte jednotku ROWS. Chcete-li tedy získat první řádek v oddílu, použijte funkci FIRST_VALUE s rámcem ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Chcete-li získat poslední řádek v oddílu, použijte funkci LAST_VALUE s rámcem ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
Tady je náš opravený dotaz s opravenou chybou:
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;
Tentokrát dostanete správný výsledek:
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 ...
Jeden se diví, jaká byla motivace normy vůbec podporovat rámec s těmito funkcemi. Když se nad tím zamyslíte, většinou je budete používat, abyste získali něco z prvních nebo posledních řádků v oddílu. Pokud potřebujete hodnotu řekněme ze dvou řádků před aktuálním, není místo použití FIRST_VALUE s rámcem, který začíná 2 PRECEDING, mnohem jednodušší použít LAG s explicitním offsetem 2, např:
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;
Tento dotaz generuje následující výstup:
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 ...
Zřejmě existuje sémantický rozdíl mezi výše uvedeným použitím funkce LAG a FIRST_VALUE s rámcem, který začíná 2 PRECEDING. V prvním případě, pokud řádek v požadovaném odsazení neexistuje, získáte ve výchozím nastavení hodnotu NULL. U druhé funkce stále dostanete hodnotu z prvního řádku, který je přítomen, tj. hodnotu z prvního řádku v oddílu. Uvažujme následující dotaz:
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;
Tento dotaz generuje následující výstup:
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 ...
Všimněte si, že tentokrát ve výstupu nejsou žádné NULL. Má tedy nějaký význam podporovat rámec s FIRST_VALUE a LAST_VALUE. Jen se ujistěte, že máte na paměti osvědčený postup, abyste u těchto funkcí vždy explicitně uvedli specifikaci rámce a abyste použili volbu ROWS s minimálním rámcem, který obsahuje požadovaný řádek.
Závěr
Tento článek se zaměřil na chyby, úskalí a osvědčené postupy týkající se okenních funkcí. Nezapomeňte, že jak agregační funkce okna, tak funkce posunu oken FIRST_VALUE a LAST_VALUE podporují rámec, a že pokud zadáte klauzuli pořadí okna, ale nezadáte jednotku rámce okna a s ní spojený rozsah, dostanete ve výchozím nastavení RANGE UNBOUNDED PRECEDING. To přináší výkonnostní postih, když se dotaz optimalizuje pomocí operátorů v řádkovém režimu. S funkcí LAST_VALUE to vede k získání hodnot z aktuálního řádku namísto posledního řádku v oddílu. Nezapomínejte na explicitní vyjádření rámce a obecně dávejte přednost možnosti ROWS před RANGE. Je skvělé vidět zlepšení výkonu pomocí operátoru Window Aggregate v dávkovém režimu. Pokud je použitelný, je alespoň eliminováno výkonnostní úskalí.
.