Acest articol este al patrulea episod dintr-o serie despre bug-uri T-SQL, capcane și cele mai bune practici. Anterior am abordat determinismul, subinterogările și îmbinările. Articolul din această lună se axează pe bug-uri, capcane și cele mai bune practici legate de funcțiile de fereastră. Mulțumesc Erland Sommarskog, Aaron Bertrand, Alejandro Mesa, Umachandar Jayachandran (UC), Fabiano Neves Amorim, Milos Radivojevic, Simon Sabin, Adam Machanic, Thomas Grohser, Chan Ming Man și Paul White pentru că mi-ați oferit ideile dumneavoastră!
În exemplele mele voi folosi o bază de date de probă numită TSQLV5. Puteți găsi scriptul care creează și populează această bază de date aici, iar diagrama ER a acesteia aici.
Există două capcane comune care implică funcțiile de fereastră, ambele fiind rezultatul unor valori implicite implicite contraintuitive care sunt impuse de standardul SQL. O capcană are legătură cu calculele de totaluri curente în cazul în care se obține un cadru de fereastră cu opțiunea implicită RANGE. O altă capcană este oarecum legată, dar are consecințe mai grave, implicând o definiție implicită a cadrului implicit pentru funcțiile FIRST_VALUE și LAST_VALUE.
Cadru de fereastră cu opțiunea implicită RANGE
Prima noastră capcană implică calculul totalurilor curente folosind o funcție de fereastră agregată, în cazul în care specificați în mod explicit clauza de ordine a ferestrei, dar nu specificați în mod explicit unitatea cadrului de fereastră (ROWS sau RANGE) și extinderea cadrului de fereastră aferent, de ex, ROWS UNBOUNDED PRECEDING. Implicitul implicit este contraintuitiv și consecințele sale ar putea fi surprinzătoare și dureroase.
Pentru a demonstra această capcană, voi folosi un tabel numit Transactions (Tranzacții) care conține două milioane de tranzacții de conturi bancare cu credite (valori pozitive) și debite (valori negative). Rulați următorul cod pentru a crea tabelul Tranzacții și pentru a-l popula cu date de probă:
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;
Problema noastră are atât o latură logică, cu o potențială eroare logică, cât și o latură de performanță, cu o penalizare de performanță. Penalizarea de performanță este relevantă doar atunci când funcția de fereastră este optimizată cu operatori de procesare în modul rând. SQL Server 2016 introduce operatorul Window Aggregate în modul batch, care elimină partea de penalizare de performanță a capcanei, dar înainte de SQL Server 2019, acest operator este utilizat numai dacă aveți un index de tip columnstore prezent pe date. SQL Server 2019 introduce modul batch pe suport rowstore, astfel încât puteți obține procesarea în modul batch chiar dacă nu există indici columnstore prezenți pe date. Pentru a demonstra penalizarea de performanță cu procesarea în modul rânduri, dacă executați exemplele de cod din acest articol pe SQL Server 2019 sau o versiune ulterioară, sau pe Azure SQL Database, utilizați următorul cod pentru a seta nivelul de compatibilitate a bazei de date la 140, astfel încât să nu activați încă modul batch pe stocul de rânduri:
ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 140;
Utilizați următorul cod pentru a activa statisticile de timp și I/O în sesiune:
SET STATISTICS TIME, IO ON;
Pentru a evita să așteptați ca două milioane de rânduri să fie tipărite în SSMS, vă sugerez să rulați exemplele de cod din această secțiune cu opțiunea Discard results after execution activată (mergeți la Query Options, Results, Grid, și bifați Discard results after execution).
Înainte de a ajunge la capcană, luați în considerare următoarea interogare (numiți-o Interogarea 1) care calculează soldul contului bancar după fiecare tranzacție prin aplicarea unui total curent folosind o funcție de agregare de tip fereastră cu o specificație explicită a cadrului:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Planul pentru această interogare, folosind procesarea în modul rând, este prezentat în Figura 1.
Figura 1: Planul pentru interogarea 1, procesare în modul rând
Planul extrage datele preordonate din indexul clusterizat al tabelei. Apoi folosește operatorii Segment și Sequence Project pentru a calcula numerele rândurilor pentru a afla care rânduri aparțin cadrului rândului curent. Apoi, utilizează operatorii Segment, Window Spool și Stream Aggregate pentru a calcula funcția de agregare a ferestrei. Operatorul Window Spool este utilizat pentru a stoca rândurile cadrului care trebuie apoi agregate. Fără o optimizare specială, planul ar fi trebuit să scrie pe fiecare rând toate rândurile de cadre aplicabile în spool și apoi să le agregheze. Acest lucru ar fi dus la o complexitate pătratică, sau N2. Vestea bună este că, atunci când cadrul începe cu UNBOUNDED PRECEDING, SQL Server identifică cazul ca fiind un caz de tip fast track, în care pur și simplu ia totalul curent al rândului anterior și adaugă valoarea rândului curent pentru a calcula totalul curent al rândului curent, rezultând o scalare liniară. În acest mod de urmărire rapidă, planul scrie doar două rânduri în spool pentru fiecare rând de intrare – unul cu agregatul și unul cu detaliul.
Scopul Window Spool poate fi implementat fizic într-unul din două moduri. Fie ca un spool rapid în memorie care a fost conceput special pentru funcțiile de fereastră, fie ca un spool lent pe disc, care este în esență un tabel temporar în tempdb. În cazul în care numărul de rânduri care trebuie scrise în spool pentru fiecare rând de bază ar putea depăși 10 000 sau dacă SQL Server nu poate prevedea acest număr, va utiliza spool mai lent pe disc. În planul nostru de interogare, avem exact două rânduri scrise în spool pentru fiecare rând de bază, astfel că SQL Server utilizează spool în memorie. Din păcate, nu există nicio modalitate de a spune din plan ce tip de spool primiți. Există două modalități de a afla acest lucru. Una este de a utiliza un eveniment extins numit window_spool_ondisk_warning. O altă opțiune este să activați STATISTICA IO și să verificați numărul de citiri logice raportate pentru un tabel numit Worktable. Un număr mai mare decât zero înseamnă că ați primit spool pe disc. Zero înseamnă că ați primit spool în memorie. Iată statisticile I/O pentru interogarea noastră:
După cum puteți vedea, am obținut spool-ul în memorie utilizat. Acesta este, în general, cazul atunci când utilizați unitatea de încadrare a ferestrei ROWS cu UNBOUNDED PRECEDING ca prim delimitator.
Iată statisticile de timp pentru interogarea noastră:
Această interogare a avut nevoie de aproximativ 4,5 secunde pentru a fi finalizată pe mașina mea, cu rezultate eliminate.
Acum pentru captură. Dacă folosiți opțiunea RANGE în loc de ROWS, cu aceiași delimitatori, este posibil să existe o diferență subtilă de semnificație, dar o mare diferență de performanță în modul rând. Diferența de semnificație este relevantă numai dacă nu aveți o ordonare totală, adică dacă ordonați după ceva care nu este unic. Opțiunea ROWS UNBOUNDED PRECEDING se oprește la rândul curent, astfel încât, în caz de egalitate, calculul este nedeterminist. În schimb, opțiunea RANGE UNBOUNDED PRECEDING se uită înainte de rândul curent și include egalitățile, dacă există. Aceasta utilizează o logică similară cu cea a opțiunii TOP WITH TIES. În cazul în care există o ordine totală, adică o ordine în funcție de ceva unic, nu există legături de inclus și, prin urmare, ROWS și RANGE devin echivalente din punct de vedere logic în acest caz. Problema este că, atunci când folosiți RANGE, SQL Server utilizează întotdeauna spool-ul de pe disc în modul de procesare pe rânduri, deoarece, atunci când procesează un anumit rând, nu poate prezice câte rânduri vor mai fi incluse. Acest lucru poate avea o penalizare severă a performanței.
Considerați următoarea interogare (numiți-o interogare 2), care este aceeași cu interogarea 1, doar că folosește opțiunea RANGE în loc de ROWS:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Planul pentru această interogare este prezentat în figura 2.
Figura 2: Planul pentru interogarea 2, procesare în modul rând
Interogarea 2 este echivalentă din punct de vedere logic cu interogarea 1, deoarece avem ordinea totală; cu toate acestea, deoarece utilizează RANGE, aceasta este optimizată cu spool-ul pe disc. Observați că în planul pentru Interogarea 2, Spool-ul de fereastră arată la fel ca în planul pentru Interogarea 1, iar costurile estimate sunt aceleași.
Iată statisticile de timp și I/O pentru execuția Interogării 2:
Tabela ‘Worktable’ citiri logice: 12044701. Tabelul ‘Transactions’ citește logic: 6208.
Observați numărul mare de citiri logice față de „Worktable”, ceea ce indică faptul că ați obținut spool-ul de pe disc. Timpul de execuție este de peste patru ori mai mare decât pentru Query 1.
Dacă vă gândiți că, în acest caz, veți evita pur și simplu să folosiți opțiunea RANGE, cu excepția cazului în care trebuie neapărat să includeți legături, este o gândire bună. Problema este că, dacă utilizați o funcție de fereastră care acceptă un cadru (agregate, FIRST_VALUE, LAST_VALUE) cu o clauză explicită de ordonare a ferestrei, dar fără a menționa unitatea de cadru a ferestrei și întinderea asociată acesteia, veți obține în mod implicit RANGE UNBOUNDED PRECEDING. Această valoare implicită este dictată de standardul SQL, iar standardul a ales-o pentru că, în general, preferă opțiuni mai deterministe ca valori implicite. Următoarea interogare (numiți-o Interogarea 3) este un exemplu care cade în această capcană:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;
De multe ori, oamenii scriu astfel presupunând că primesc implicit ROWS UNBOUNDED PRECEDING, fără să realizeze că de fapt primesc RANGE UNBOUNDED PRECEDING. Problema este că, din moment ce funcția utilizează ordinea totală, obțineți același rezultat ca și în cazul ROWS, astfel încât nu vă puteți da seama că există o problemă din rezultat. Dar cifrele de performanță pe care le veți obține sunt similare cu cele de la Query 2. Văd oameni care cad în această capcană tot timpul.
Cea mai bună practică pentru a evita această problemă este ca, în cazurile în care utilizați o funcție de fereastră cu un cadru, să fiți explicit cu privire la unitatea de cadru a ferestrei și la extinderea acesteia și, în general, să preferați ROWS. Rezervați utilizarea RANGE doar pentru cazurile în care ordonarea nu este unică și trebuie să includeți legături.
Luați în considerare următoarea interogare care ilustrează un caz în care există o diferență conceptuală între ROWS și 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;
Această interogare generează următoarea ieșire:
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 ...
Observați diferența de rezultate pentru rândurile în care aceeași dată a comenzii apare de mai multe ori, așa cum este cazul pentru 8 iulie 2017. Observați cum opțiunea ROWS nu include legăturile și, prin urmare, este nedeterministă, și cum opțiunea RANGE include legăturile și, prin urmare, este întotdeauna deterministă.
Este totuși discutabil dacă, în practică, aveți cazuri în care comandați după ceva care nu este unic și chiar aveți nevoie de includerea legăturilor pentru a face calculul determinist. Ceea ce este probabil mult mai comun în practică este să faci unul din două lucruri. Unul este de a rupe legăturile prin adăugarea unui element la ordonarea ferestrei care să o facă unică și în acest fel să rezulte un calcul determinist, cum ar fi:
SELECT orderdate, orderid, val, SUM(val) OVER( ORDER BY orderdate, orderid ROWS UNBOUNDED PRECEDING ) AS runningsum FROM Sales.OrderValues ORDER BY orderdate;
Această interogare generează următoarea ieșire:
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 ...
O altă opțiune este de a aplica o grupare preliminară, în cazul nostru, în funcție de data comenzii, după cum urmează:
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;
Această interogare generează următoarea ieșire în care fiecare dată a comenzii apare o singură dată:
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 ...
În orice caz, asigurați-vă că vă amintiți cea mai bună practică aici!
Veștile bune sunt că, dacă rulați pe SQL Server 2016 sau o versiune ulterioară și aveți un indice columnstore prezent pe date (chiar dacă este un indice columnstore filtrat fals), sau dacă rulați pe SQL Server 2019 sau o versiune ulterioară, sau pe Azure SQL Database, indiferent de prezența indicilor columnstore, toate cele trei interogări menționate mai sus sunt optimizate cu operatorul Window Aggregate în modul batch. Cu acest operator, multe dintre ineficiențele de procesare în modul rând sunt eliminate. Acest operator nu utilizează deloc un spool, astfel încât nu există nicio problemă de spool în memorie sau pe disc. Folosește o procesare mai sofisticată prin care poate aplica mai multe treceri paralele peste fereastra de rânduri din memorie atât pentru ROWS, cât și pentru RANGE.
Pentru a demonstra utilizarea optimizării în modul batch, asigurați-vă că nivelul de compatibilitate al bazei de date este setat la 150 sau mai mare:
ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 150;
Executați din nou Query 1:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Planul pentru această interogare este prezentat în figura 3.
Figura 3: Planul pentru interogarea 1, procesare în modul batch
Iată statisticile de performanță pe care le-am obținut pentru această interogare:
Tabela ‘Transactions’ logical reads: 6208.
Timpul de execuție a scăzut la 1 secundă!
Executați din nou interogarea 2 cu opțiunea RANGE explicit:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Planul pentru această interogare este prezentat în figura 4.
Figura 2: Planul pentru interogarea 2, procesare în modul batch
Iată statisticile de performanță pe care le-am obținut pentru această interogare:
Tabela ‘Transactions’ logical reads: 6208.
Performanța este aceeași ca și pentru interogarea 1.
Executați din nou interogarea 3, cu opțiunea implicită RANGE:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;
Planul și cifrele de performanță sunt, bineînțeles, aceleași ca pentru interogarea 2.
Când ați terminat, rulați următorul cod pentru a dezactiva statisticile de performanță:
SET STATISTICS TIME, IO OFF;
De asemenea, nu uitați să dezactivați opțiunea Discard results after execution din SSMS.
Cadru implicit cu FIRST_VALUE și LAST_VALUE
Funcțiile FIRST_VALUE și LAST_VALUE sunt funcții de fereastră offset care returnează o expresie din primul, respectiv ultimul rând din cadrul ferestrei. Partea delicată în legătură cu ele este că, adesea, atunci când oamenii le folosesc pentru prima dată, nu își dau seama că ele susțin un cadru, ci mai degrabă cred că se aplică la întreaga partiție.
Considerați următoarea încercare de a returna informații despre comenzi, plus valorile primei și ultimei comenzi a clientului:
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;
Dacă credeți în mod eronat că aceste funcții operează pe întreaga partiție a ferestrei, ceea ce este convingerea multor persoane care utilizează aceste funcții pentru prima dată, vă așteptați în mod natural ca FIRST_VALUE să returneze valoarea comenzii primei comenzi a clientului, iar LAST_VALUE să returneze valoarea comenzii ultimei comenzi a clientului. În practică, însă, aceste funcții susțin un cadru. Pentru a vă reaminti, în cazul funcțiilor care acceptă un cadru, atunci când specificați clauza de ordine a ferestrei, dar nu și unitatea de cadru a ferestrei și extensia asociată acesteia, veți obține în mod implicit RANGE UNBOUNDED PRECEDING. Cu funcția FIRST_VALUE, veți obține rezultatul așteptat, dar dacă interogarea dvs. este optimizată cu operatori în modul rând, veți plăti penalizarea utilizării spoolului de pe disc. Cu funcția LAST_VALUE este chiar mai rău. Nu numai că veți plăti penalizarea spoolului pe disc, dar în loc să obțineți valoarea din ultimul rând din partiție, veți obține valoarea din rândul curent!
Iată rezultatul interogării de mai sus:
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 ...
De multe ori, când oamenii văd un astfel de rezultat pentru prima dată, ei cred că SQL Server are un bug. Dar, desigur, nu este așa; este pur și simplu valoarea implicită a standardului SQL. Există o eroare în interogare. Realizând că există un cadru implicat, doriți să fiți explicit cu privire la specificația cadrului și să folosiți cadrul minim care surprinde rândul pe care îl căutați. De asemenea, asigurați-vă că utilizați unitatea ROWS. Astfel, pentru a obține primul rând din partiție, utilizați funcția FIRST_VALUE cu cadrul ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Pentru a obține ultimul rând din partiție, utilizați funcția LAST_VALUE cu cadrul ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
Iată interogarea noastră revizuită cu bug-ul corectat:
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;
De data aceasta se obține rezultatul corect:
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 ...
Se întreabă care a fost motivația pentru ca standardul să susțină măcar un cadru cu aceste funcții. Dacă vă gândiți bine, le veți folosi de cele mai multe ori pentru a obține ceva din primele sau ultimele rânduri din partiție. Dacă aveți nevoie de valoarea de la, să zicem, două rânduri înaintea rândului curent, în loc să folosiți FIRST_VALUE cu un cadru care începe cu 2 PRECEDING, nu este mult mai simplu să folosiți LAG cu un decalaj explicit de 2, astfel:
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;
Această interogare generează următoarea ieșire:
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 ...
Se pare că există o diferență semantică între utilizarea de mai sus a funcției LAG și FIRST_VALUE cu un cadru care începe cu 2 PRECEDING. Cu prima funcție, dacă nu există un rând în decalajul dorit, se obține în mod implicit un NULL. Cu cea de-a doua, veți obține în continuare valoarea din primul rând care este prezent, adică valoarea din primul rând din partiție. Luați în considerare următoarea interogare:
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;
Această interogare generează următoarea ieșire:
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 ...
Observați că de data aceasta nu există NULL-uri în ieșire. Deci, există o anumită valoare în susținerea unui cadru cu FIRST_VALUE și LAST_VALUE. Asigurați-vă doar că vă amintiți cea mai bună practică de a fi întotdeauna explicit cu privire la specificarea cadrului cu aceste funcții și de a utiliza opțiunea ROWS cu cadrul minim care conține rândul pe care îl căutați.
Concluzie
Acest articol s-a axat pe bug-uri, capcane și cele mai bune practici legate de funcțiile de fereastră. Rețineți că atât funcțiile de agregare a ferestrelor, cât și funcțiile de decalare a ferestrelor FIRST_VALUE și LAST_VALUE acceptă un cadru și că, dacă specificați clauza de ordine a ferestrei, dar nu specificați unitatea de cadru a ferestrei și întinderea asociată acesteia, veți obține în mod implicit RANGE UNBOUNDED PRECEDING. Acest lucru generează o penalizare de performanță atunci când interogarea este optimizată cu operatori în modul rând. Cu funcția LAST_VALUE, acest lucru are ca rezultat obținerea valorilor din rândul curent în loc de ultimul rând din partiție. Nu uitați să fiți explicit în legătură cu cadrul și să preferați, în general, opțiunea ROWS în locul RANGE. Este grozav să vedeți îmbunătățirile de performanță cu operatorul Window Aggregate în modul batch. Atunci când este aplicabil, cel puțin capcana de performanță este eliminată.
.