Ez a cikk a negyedik része a T-SQL hibákról, buktatókról és legjobb gyakorlatokról szóló sorozatnak. Korábban a determinizmust, a subquerie-ket és a joinokat tárgyaltam. E havi cikkünk középpontjában az ablakfüggvényekkel kapcsolatos hibák, buktatók és legjobb gyakorlatok állnak. Köszönöm Erland Sommarskog, Aaron Bertrand, Alejandro Mesa, Umachandar Jayachandran (UC), Fabiano Neves Amorim, Milos Radivojevic, Simon Sabin, Adam Machanic, Thomas Grohser, Chan Ming Man és Paul White ötleteit!
A példáimban a TSQLV5 nevű mintaadatbázist fogom használni. Az ezt az adatbázist létrehozó és feltöltő szkriptet itt, az ER-diagramját pedig itt találja.
Az ablakfüggvényekkel kapcsolatban két gyakori buktató van, mindkettő az SQL-szabvány által előírt, ellenkező értelmű implicit alapértelmezések eredménye. Az egyik buktató a futó összegek számításaihoz kapcsolódik, ahol egy ablakkeretet kapunk az implicit RANGE opcióval. A másik buktató némileg kapcsolódik ehhez, de súlyosabb következményekkel jár, és a FIRST_VALUE és LAST_VALUE függvények implicit keretdefinícióját érinti.
Ablakkeret implicit RANGE opcióval
Az első buktató a futó összegek aggregált ablakfüggvény segítségével történő kiszámítását érinti, ahol explicit módon megadjuk az ablakrendezési záradékot, de nem adjuk meg explicit módon az ablakkeret egységét (ROWS vagy RANGE) és a kapcsolódó ablakkeret kiterjedését, például, SOROK KORLÁTLAN ELŐZMÉNY. Az implicit alapértelmezés ellenkezik az intuícióval, és következményei meglepőek és fájdalmasak lehetnek.
Ennek a buktatónak a bemutatására egy Tranzakciók nevű táblázatot használok, amely kétmillió bankszámla-tranzakciót tartalmaz jóváírásokkal (pozitív értékekkel) és terhelésekkel (negatív értékekkel). Futtassa a következő kódot a Tranzakciók tábla létrehozásához és mintaadatokkal való feltöltéséhez:
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;
A buktatónknak van egy logikai oldala egy lehetséges logikai hibával, valamint egy teljesítményoldala egy teljesítménykieséssel. A teljesítménybüntetés csak akkor releváns, ha az ablakfüggvényt sormódú feldolgozási operátorokkal optimalizáljuk. Az SQL Server 2016 bevezeti a kötegelt módú Window Aggregate operátort, amely megszünteti a buktató teljesítménybüntetés részét, de az SQL Server 2019 előtt ez az operátor csak akkor használható, ha az adatokon oszloptároló index van jelen. Az SQL Server 2019 bevezeti a rowstore-támogatásra vonatkozó kötegelt üzemmódot, így a kötegelt üzemmódú feldolgozás akkor is elérhető, ha az adatokon nincs jelen oszloptároló index. A soros módú feldolgozással járó teljesítménycsökkenés bemutatásához, ha a cikkben szereplő kódmintákat SQL Server 2019 vagy újabb SQL Server 2019 vagy Azure SQL Database rendszeren futtatja, az alábbi kóddal állítsa az adatbázis-kompatibilitási szintet 140-re, hogy a soros tárolón még ne legyen engedélyezve a kötegelt mód a soros tárolón:
ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 140;
A következő kódot használja az idő- és I/O-statisztikák bekapcsolásához a munkamenetben:
SET STATISTICS TIME, IO ON;
Az SSMS-ben a kétmillió sor kinyomtatására való várakozás elkerülése érdekében javaslom, hogy az ebben a szakaszban található kódmintákat a Discard results after execution opció bekapcsolásával futtassa (menjen a Query Options, Results, Grid menüpontba, és jelölje be az Discard results after execution (Eredmények elvetése végrehajtás után) lehetőséget).
Mielőtt rátérnénk a buktatóra, tekintsük a következő lekérdezést (nevezzük 1. lekérdezésnek), amely minden egyes tranzakció után kiszámítja a bankszámla egyenlegét egy futó összeg alkalmazásával, egy explicit keretmeghatározással rendelkező ablak-aggregátum függvény segítségével:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Az 1. ábrán látható a lekérdezés terve, sormódú feldolgozással.
1. ábra: Az 1. lekérdezés terve, sormódú feldolgozással
A terv a tábla fürtözött indexéből húzza ki az adatokat előre rendezve. Ezután a Segment és a Sequence Project operátorok segítségével kiszámítja a sorszámokat, hogy kiderítse, mely sorok tartoznak az aktuális sor keretéhez. Ezután a Segment, Window Spool és Stream Aggregate operátorokat használja az ablak összesítő függvény kiszámításához. Az Window Spool operátort a keret sorainak orsózására használja, amelyeket azután aggregálni kell. Különleges optimalizálás nélkül a tervnek soronként az összes vonatkozó keretes sort az orsóra kellett volna írnia, majd aggregálnia. Ez négyzetes vagy N2-es komplexitást eredményezett volna. A jó hír az, hogy amikor a keret UNBOUNDED PRECEDING-gel kezdődik, az SQL Server gyorsított esetként azonosítja az esetet, amelyben egyszerűen az előző sor futó összegét veszi, és hozzáadja az aktuális sor értékét az aktuális sor futó összegének kiszámításához, ami lineáris skálázást eredményez. Ebben a gyorsított üzemmódban a terv bemeneti soronként csak két sort ír az orsóra – egyet az összesítéssel, egyet pedig a részletezéssel.
A Window Spool fizikailag kétféleképpen valósítható meg. Vagy gyors, memórián belüli spoolként, amelyet kifejezetten ablakfunkciókhoz terveztek, vagy lassú, lemezen lévő spoolként, amely lényegében egy ideiglenes tábla a tempdb-ben. Ha az alapjául szolgáló soronként a spoolba írandó sorok száma meghaladhatja a 10 000-et, vagy ha az SQL Server nem tudja megjósolni ezt a számot, akkor a lassabb lemezen lévő spoolt fogja használni. A mi lekérdezési tervünkben pontosan két sort írunk az orsóba mögöttes soronként, ezért az SQL Server a memórián belüli orsót használja. Sajnos a tervből nem lehet megmondani, hogy milyen spoolt kapunk. Kétféleképpen lehet ezt kideríteni. Az egyik a window_spool_ondisk_warning nevű kiterjesztett esemény használata. A másik lehetőség a STATISTICS IO engedélyezése, és a Worktable nevű táblázathoz bejelentett logikai olvasások számának ellenőrzése. A nullánál nagyobb szám azt jelenti, hogy megvan a lemezen lévő spool. A nulla azt jelenti, hogy a memórián belüli spoolt kapta. Íme a lekérdezésünk I/O statisztikái:
Amint láthatjuk, a memórián belüli spoolt használtuk. Ez általában akkor van így, ha a ROWS ablakkeret egységet használjuk UNBOUNDED PRECEDING első elválasztójelként.
Itt vannak a lekérdezésünk időstatisztikái:
Ez a lekérdezés az én gépemen körülbelül 4,5 másodpercig tartott, az eredmények eldobásával együtt.
Most jön a bökkenő. Ha a ROWS helyett a RANGE opciót használja, ugyanazokkal az elhatárolójelekkel, akkor lehet, hogy a jelentésben finom különbség van, de a teljesítményben nagy különbség a row módban. A jelentésbeli különbség csak akkor releváns, ha nincs teljes rendezés, azaz ha valami olyan alapján rendezünk, ami nem egyedi. A ROWS UNBOUNDED PRECEDING opció az aktuális sorral áll meg, így döntetlenek esetén a számítás nem determinisztikus. Ezzel szemben a RANGE UNBOUNDED PRECEDING opció az aktuális sor elé néz, és ha van, akkor a kötöttségeket is figyelembe veszi. Hasonló logikát használ, mint a TOP WITH TIES opció. Ha teljes rendezés van, azaz valami egyedi alapján rendezünk, akkor nincs bevonandó kötés, és ezért a ROWS és a RANGE logikailag egyenértékűvé válik ilyen esetben. A probléma az, hogy a RANGE használatakor az SQL Server mindig a lemezen lévő spoolt használja a row-mode feldolgozás alatt, mivel egy adott sor feldolgozása során nem tudja megjósolni, hogy hány további sort fog bevonni. Ez komoly teljesítménycsökkenéssel járhat.
Nézzük meg a következő lekérdezést (nevezzük 2. lekérdezésnek), amely megegyezik az 1. lekérdezéssel, csak a ROWS helyett a RANGE opciót használja:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
A 2. ábrán látható a lekérdezés terve.
2. ábra: A 2. lekérdezés terve, sor-módú feldolgozás
A 2. lekérdezés logikailag egyenértékű az 1. lekérdezéssel, mivel teljes sorrenddel rendelkezünk; mivel azonban RANGE-t használ, a lemezen lévő spool segítségével optimalizálódik. Figyeljük meg, hogy a 2. lekérdezés tervében a Window Spool ugyanúgy néz ki, mint az 1. lekérdezés tervében, és a becsült költségek is ugyanazok.
Íme a 2. lekérdezés végrehajtásának idő- és I/O-statisztikái:
A ‘Worktable’ táblázat logikai olvasása: 12044701. A ‘Tranzakciók’ táblázat logikai olvasásai:
Figyeljük meg a nagyszámú logikai olvasást a Worktable ellen, ami azt jelzi, hogy megkapta a lemezen lévő spoolt. A futási idő több mint négyszer hosszabb, mint az 1. lekérdezésnél.
Ha arra gondolsz, hogy ha ez a helyzet, akkor egyszerűen elkerülöd a RANGE opció használatát, hacsak nem kell feltétlenül kötelékeket bevonni, akkor ez jó gondolkodás. A probléma az, hogy ha olyan ablakfüggvényt használsz, amely támogatja a keretet (aggregátumok, FIRST_VALUE, LAST_VALUE) explicit ablakrendezési záradékkal, de nem említi az ablakkeret egységét és a hozzá tartozó kiterjedést, akkor alapértelmezés szerint RANGE UNBOUNDED PRECEDING-et kapsz. Ezt az alapértelmezést az SQL szabvány diktálja, és a szabvány azért választotta, mert általában a determinisztikusabb beállításokat részesíti előnyben alapértelmezettként. A következő lekérdezés (nevezzük 3. lekérdezésnek) egy példa, amely ebbe a csapdába esik:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;
Az emberek gyakran írnak így, feltételezve, hogy alapértelmezés szerint ROWS UNBOUNDED PRECEDING-et kapnak, és nem veszik észre, hogy valójában RANGE UNBOUNDED PRECEDING-et kapnak. A helyzet az, hogy mivel a függvény teljes sorrendet használ, ugyanazt az eredményt kapod, mint a ROWS esetén, így az eredményből nem látszik, hogy baj van. De a teljesítményszámok, amelyeket kapni fog, olyanok, mint a 2. lekérdezés esetében. Állandóan látom, hogy az emberek ebbe a csapdába esnek.
A legjobb gyakorlat ennek a problémának az elkerülésére az, hogy azokban az esetekben, amikor ablakfüggvényt használsz egy kerettel, légy explicit az ablakkeret egységéről és annak kiterjedéséről, és általában a ROWS-t részesítsd előnyben. A RANGE használatát csak olyan esetekre tartsa fenn, amikor a rendezés nem egyedi, és szükség van a kötöttségek bevonására.
Tekintsük a következő lekérdezést, amely egy olyan esetet szemléltet, amikor fogalmi különbség van a ROWS és a RANGE között:
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;
Ez a lekérdezés a következő kimenetet generálja:
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 ...
Nézze meg a különbséget az eredményekben azoknál a soroknál, ahol ugyanaz a rendelési dátum többször is szerepel, mint például 2017. július 8-án. Vegyük észre, hogy a ROWS opció nem tartalmazza a kötéseket, és ezért nem determinisztikus, míg a RANGE opció tartalmazza a kötéseket, és ezért mindig determinisztikus.
Ez azonban kérdéses, hogy a gyakorlatban vannak-e olyan esetek, amikor valami olyan alapján rendelünk, ami nem egyedi, és valóban szükség van a kötések bevonására, hogy a számítás determinisztikus legyen. Ami valószínűleg sokkal gyakoribb a gyakorlatban, az a két dolog egyike. Az egyik az, hogy a kötöttségeket úgy törjük meg, hogy az ablakrendezéshez hozzáadunk valamit, ami egyedivé teszi, és így determinisztikus számítást eredményez, például így:
SELECT orderdate, orderid, val, SUM(val) OVER( ORDER BY orderdate, orderid ROWS UNBOUNDED PRECEDING ) AS runningsum FROM Sales.OrderValues ORDER BY orderdate;
Ez a lekérdezés a következő kimenetet generálja:
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 ...
Egy másik lehetőség az előzetes csoportosítás alkalmazása, esetünkben a rendelés dátuma szerint, a következőképpen:
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;
Ez a lekérdezés a következő kimenetet generálja, ahol minden rendelési dátum csak egyszer jelenik meg:
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 ...
Mindenesetre ügyeljen arra, hogy itt ne feledje a legjobb gyakorlatot!
A jó hír az, hogy ha SQL Server 2016 vagy újabb SQL Server 2016-on fut, és az adatokon oszloptároló index van jelen (még akkor is, ha ez egy álszűrt oszloptároló index), vagy ha SQL Server 2019 vagy újabb SQL Server 2019-en vagy Azure SQL Database-en fut, akkor az oszloptároló indexek jelenlététől függetlenül mindhárom fent említett lekérdezés optimalizálható a kötegelt módú Window Aggregate operátorral. Ezzel az operátorral a sormódú feldolgozás számos hatékonysági hiányossága kiküszöbölhető. Ez az operátor egyáltalán nem használ spoolt, így nem merül fel a memórián belüli vagy a lemezen lévő spool kérdése. Kifinomultabb feldolgozást használ, ahol a ROWS és a RANGE esetében is több párhuzamos átfutást tud alkalmazni a memóriában lévő sorok ablakán.
A kötegelt módú optimalizálás használatának bemutatásához győződjön meg róla, hogy az adatbázis kompatibilitási szintje 150-re vagy magasabbra van állítva:
ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 150;
Futtassa le újra az 1. lekérdezést:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
A lekérdezés terve a 3. ábrán látható.
3. ábra: Az 1. lekérdezés terve, kötegelt módú feldolgozás
Itt vannak a teljesítménystatisztikák, amelyeket erre a lekérdezésre kaptam:
A ‘Tranzakciók’ táblázat logikai olvasása:
A futási idő 1 másodpercre csökkent!
Futtassuk le újra a 2. lekérdezést az explicit RANGE opcióval:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
A 4. ábrán látható ennek a lekérdezésnek a terve.
2. ábra: A 2. lekérdezés terve, kötegelt módú feldolgozás
Itt vannak a teljesítménystatisztikák, amelyeket erre a lekérdezésre kaptam:
A ‘Tranzakciók’ táblázat logikai olvasása:
Még egyszer futtassuk le a 3. lekérdezést az implicit RANGE opcióval:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;
A terv és a teljesítményszámok természetesen megegyeznek a 2. lekérdezéssel.
Ha végzett, futtassa a következő kódot a teljesítménystatisztikák kikapcsolásához:
SET STATISTICS TIME, IO OFF;
Azt se felejtse el, hogy az SSMS-ben kapcsolja ki a Discard results after execution opciót.
Implicit frame with FIRST_VALUE and LAST_VALUE
A FIRST_VALUE és LAST_VALUE függvények olyan offset ablakfüggvények, amelyek az ablakkeret első, illetve utolsó sorában lévő kifejezést adják vissza. A trükkös rész velük kapcsolatban az, hogy gyakran, amikor az emberek először használják őket, nem veszik észre, hogy egy keretet támogatnak, inkább azt hiszik, hogy az egész partícióra vonatkoznak.
Gondoljunk a következő kísérletre, amely a megrendelésre vonatkozó információkat, valamint az ügyfél első és utolsó megrendelésének értékeit kívánja visszaadni:
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;
Ha tévesen azt hiszi, hogy ezek a függvények a teljes ablakpartícióra működnek, amit sokan hisznek, akik először használják ezeket a függvényeket, akkor természetesen azt várja, hogy a FIRST_VALUE a vevő első rendelésének rendelési értékét, a LAST_VALUE pedig a vevő utolsó rendelésének értékét adja vissza. A gyakorlatban azonban ezek a funkciók egy keretet támogatnak. Emlékeztetőül: a keretet támogató függvényeknél, ha megadjuk az ablakrendező záradékot, de nem adjuk meg az ablakkeret egységét és a hozzá tartozó kiterjedést, akkor alapértelmezés szerint RANGE UNBOUNDED PRECEDING-et kapunk. A FIRST_VALUE függvénnyel a várt eredményt kapja, de ha a lekérdezés sor-módú operátorokkal optimalizálódik, akkor a lemezen lévő spool használatának büntetését kell megfizetnie. A LAST_VALUE függvénnyel még rosszabb a helyzet. Nemcsak, hogy a lemezen lévő spool büntetését kell megfizetnie, de ahelyett, hogy a partíció utolsó sorának értékét kapná meg, az aktuális sor értékét fogja kapni!
Itt van a fenti lekérdezés kimenete:
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 ...
Gyakran, amikor az emberek először látnak ilyen kimenetet, azt gondolják, hogy az SQL Serverben hiba van. De természetesen nincs; ez egyszerűen az SQL-szabvány alapértelmezett beállítása. A lekérdezésben van egy hiba. Ha felismeri, hogy egy keretről van szó, akkor egyértelműen meg kell határoznia a keret specifikációját, és azt a minimális keretet kell használnia, amely megragadja a keresett sort. Győződjön meg arról is, hogy a ROWS egységet használja. Tehát, ha a partíció első sorát szeretné megkapni, használja a FIRST_VALUE függvényt a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWS kerettel. Ha a partíció utolsó sorát szeretné megkapni, használja a LAST_VALUE függvényt a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING kerettel.
Itt a javított lekérdezésünk a hiba kijavításával:
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;
Ezúttal a helyes eredményt kapjuk:
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 ...
Az ember elgondolkodik azon, mi motiválta a szabványt, hogy egyáltalán támogasson egy keretet ezekkel a függvényekkel. Ha jobban belegondolsz, többnyire arra használod őket, hogy a partíció első vagy utolsó soraiból kapj valamit. Ha mondjuk az aktuális előtt két sorral korábbi értékre van szükséged, ahelyett, hogy a FIRST_VALUE-t használnád egy olyan kerettel, amely 2 PRECEDING-gel kezdődik, nem sokkal egyszerűbb a LAG-ot használni egy explicit 2-es eltolással, például így:
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;
Ez a lekérdezés a következő kimenetet generálja:
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 ...
Nyilvánvalóan szemantikai különbség van a LAG függvény fenti használata és a FIRST_VALUE használata között egy 2 PRECEDING-gel kezdődő kerettel. Az előbbivel, ha egy sor nem létezik a kívánt eltolásban, akkor alapértelmezés szerint NULL-t kapunk. Az utóbbival továbbra is az első olyan sor értékét kapja meg, amely jelen van, azaz a partíció első sorának értékét. Tekintsük a következő lekérdezést:
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;
Ez a lekérdezés a következő kimenetet generálja:
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 ...
Nézzük meg, hogy ezúttal nincs NULL a kimenetben. Tehát van némi értéke a FIRST_VALUE és LAST_VALUE keret támogatásának. Csak arra ügyelj, hogy ne feledkezz meg a legjobb gyakorlatról, hogy ezekkel a függvényekkel mindig explicit legyen a keret specifikációja, és hogy a ROWS opciót azzal a minimális kerettel használd, amely tartalmazza a keresett sort.
Következtetés
Ez a cikk az ablakfüggvényekkel kapcsolatos hibákra, buktatókra és legjobb gyakorlatokra összpontosított. Ne feledje, hogy mind az ablak összesítő függvények, mind a FIRST_VALUE és LAST_VALUE ablakeltolás függvények támogatják a keretet, és hogy ha megadja az ablakrendező záradékot, de nem adja meg az ablakkeret egységét és a hozzá tartozó kiterjedést, akkor alapértelmezés szerint RANGE UNBOUNDED PRECEDING-et kap. Ez teljesítménybeli hátrányt okoz, amikor a lekérdezést sormódú operátorokkal optimalizáljuk. A LAST_VALUE függvénnyel ez azt eredményezi, hogy a partíció utolsó sora helyett az aktuális sorból kapjuk meg az értékeket. Ne feledje, hogy legyen egyértelmű a keret, és általában a ROWS opciót részesítse előnyben a RANGE opcióval szemben. Nagyszerű látni a teljesítményjavulást a kötegelt módú Window Aggregate operátorral. Ha alkalmazható, legalább a teljesítmény buktatója kiküszöbölhető.