O introducere în funcțiile ferestrei SQL

Funcția foarte puternică pe care îți place să o urăști (dar trebuie să știi)

Funcțiile ferestrei SQL oferă câteva caracteristici extrem de puternice și utile. Dar pentru mulți, deoarece sunt atât de străini de SQL-ul standard, sunt greu de învățat și de înțeles, au o sintaxă ciudată și sunt foarte des evitate.

Funcțiile ferestrei pot fi explicate pur și simplu ca funcții de calcul similare cu agregarea, dar în cazul în care agregarea normală prin clauza GROUP BY se combină, atunci ascunde rândurile individuale care sunt agregate, funcțiile ferestre au acces la rândurile individuale și pot adăuga unele dintre atributele din acele rânduri în setul de rezultate.

În acest tutorial privind funcțiile ferestrei SQL, vă voi face să începeți cu funcțiile ferestrei, vă voi explica beneficiile și când le-ați utiliza și vă voi oferi exemple reale pentru a ajuta la concepte.

O fereastră în datele dvs

Una dintre cele mai utilizate și mai importante caracteristici ale SQL este capacitatea de a agrega sau grupa rânduri de date în anumite moduri. În unele cazuri însă, gruparea poate deveni extrem de complexă, în funcție de ceea ce este necesar.

Ați dorit vreodată să parcurgeți rezultatele interogării dvs. pentru a obține un clasament, o listă de top x sau similar? Ați avut proiecte de analiză în care ați vrut să vă pregătiți datele exact pentru un instrument de vizualizare, dar ați găsit că este aproape imposibil sau atât de complex încât nu a meritat?

Funcțiile ferestrelor pot ușura lucrurile. După ce obțineți rezultatul interogării, adică după clauza WHERE și orice agregare standard, funcțiile ferestrei vor acționa asupra rândurilor rămase ( fereastra de date) și vă vor obține ceea ce doriți.

Unele dintre funcțiile ferestrei pe care le vom analiza includ:

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

Peste Ușor

Clauza OVER este cea care specifică o funcție de fereastră și trebuie întotdeauna inclusă în instrucțiune. Valoarea implicită într-o clauză OVER este întregul set de rânduri. De exemplu, să ne uităm la un tabel de angajați dintr-o bază de date a companiei și să arătăm numărul total de angajați pe fiecare rând, împreună cu informațiile fiecărui angajat, inclusiv când au început cu compania.

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Cele de mai sus, la fel ca multe funcții de fereastră, pot fi, de asemenea, scrise într-un mod mai familiar fără ferestre - ceea ce, în acest exemplu simplu, nu este prea rău:

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Dar acum, să presupunem că dorim să arătăm numărul de angajați care au început în aceeași lună cu angajatul din rând. Va trebui să restrângem sau să restrângem numărul la acea lună pentru fiecare rând. Cum se face asta? Folosim clauza fereastră PARTITION , astfel:

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

Partițiile vă permit să filtrați fereastra în secțiuni după o anumită valoare sau valori. Fiecare secțiune este adesea numită rama ferestrei.

Pentru a merge mai departe, să presupunem că nu am vrut doar să aflăm câți angajați au început în aceeași lună, dar vrem să arătăm în ce ordine au început în luna respectivă. Pentru asta, putem folosi familiara clauză ORDER BY . Cu toate acestea, în cadrul unei funcții de fereastră, ORDER BY acționează puțin diferit decât la sfârșitul unei interogări.

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

În acest caz, ORDER BY modifică fereastra astfel încât să meargă de la începutul partiției (în acest caz luna și anul când a început angajatul) la rândul curent. Astfel, numărul repornește la fiecare partiție.

Clasează-l

Funcțiile ferestrei pot fi foarte utile în scopuri de clasare. Anterior, am văzut că utilizarea funcției de agregare COUNT ne permitea să vedem în ce ordine s-au alăturat angajații companiei. De asemenea, am fi putut folosi funcții de clasificare a ferestrei, cum ar fi ROW_NUMBER() , RANK() și DENSE_RANK() .

Diferențele pot fi văzute după ce adăugăm un nou angajat în luna următoare și eliminăm partiția:

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Puteți vedea diferențele. ROW_NUMBER() oferă un număr secvenţial într-o anumită partiţie (dar în absenţa unei partiţii, trece prin toate rândurile). RANK() oferă rangul fiecărui rând pe baza clauzei ORDER BY . Afișează legături și apoi omite următoarea clasare. DENSE_RANK afișează, de asemenea, egalități, dar apoi continuă cu următoarea valoare consecutivă ca și cum nu ar fi fost egalitate.

Alte funcții de clasare includ:

• CUME_DIST – Calculează rangul relativ al rândului curent dintr-o partiție
• NTILE – Împarte rândurile pentru fiecare partiție de fereastră cât mai egal posibil
• PERCENT_RANK – Clasament procentual al rândului curent

Observați, de asemenea, în acest exemplu că puteți avea mai multe funcții Window într-o singură interogare - și atât partiția, cât și ordinea pot fi diferite în fiecare!

Rânduri și intervale și cadre, Oh My!

Pentru a defini sau a limita în continuare cadrul ferestrei în cadrul clauzei OVER() , puteți utiliza ROWS și RANGE . Cu clauza ROWS , puteți specifica rândurile incluse în partiția dvs. ca cele anterioare sau după rândul curent.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

În acest exemplu, cadrul ferestrei trece de la primul rând la rândul curent minus 1, iar dimensiunea ferestrei continuă să crească pentru fiecare rând.

Gama funcționează puțin diferit și este posibil să obținem un rezultat diferit.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

Intervalul va include acele rânduri din cadrul ferestrei care au aceleași valori ORDER BY ca și rândul curent. Astfel, este posibil să puteți obține duplicate cu RANGE dacă ORDER BY nu este unic.

Unii descriu ROWS ca un operator fizic, în timp ce RANGE este un operator logic. În orice caz, valorile implicite pentru ROWS și RANGE sunt întotdeauna UNBOUNDED PRECEDING AND CURRENT ROW .

Ce altceva?

Majoritatea funcțiilor agregate standard funcționează cu funcții Window. Am văzut deja COUNT în exemple. Altele includ SUM , AVG , MIN , MAX etc.

Cu funcțiile ferestre, puteți accesa atât înregistrările anterioare, cât și înregistrările ulterioare folosind LAG și LEAD și FIRST_VALUE și LAST_VALUE . De exemplu, să presupunem că doriți să afișați pe fiecare rând o cifră de vânzări pentru luna curentă și diferența dintre cifra de vânzare de luna trecută. S-ar putea să faci ceva de genul acesta:

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

Practic, funcțiile ferestrei SQL sunt foarte puternice

Deși aceasta este o introducere rapidă în funcțiile ferestrei SQL, sperăm că vă va stârni interesul pentru a vedea tot ce pot face acestea. Am aflat că funcțiile ferestre efectuează calcule similare cu cele de agregare, dar cu avantajul suplimentar că au acces la date în rândurile individuale, ceea ce le face destul de puternice. Acestea conțin întotdeauna clauza OVER și pot conține PARTITION BY , ORDER BY și o serie de agregare ( SUM , COUNT , etc.) și alte funcții poziționale ( LEAD , LAG ). Am învățat, de asemenea, despre ramele de fereastră și despre modul în care acestea încapsulează secțiuni de date.

Rețineți că diferitele variante de SQL pot implementa diferite funcții de fereastră, iar unele pot să nu fi implementat toate funcțiile sau clauzele ferestrei. Asigurați-vă că verificați documentația pentru platforma pe care o utilizați.

Dacă, în calitate de dezvoltator SQL, sunteți interesat să reglați performanța bazei de date SQL, consultați SQL Database Performance Tuning for Developers .

Fereastră fereastră!

Pentru mai multe informații despre implementări specifice, consultați:

• Documentația pentru funcțiile de fereastră PostgreSQL pentru o implementare PostgreSQL.
• SELECT - OVER Clause (Transact-SQL) documente de la Microsoft.
• Funcții de fereastră în SQL Server pentru o imagine de ansamblu excelentă asupra implementărilor SQL Server și a părții sale 2.