Clauza OVER a fost adăugată la SQL Server „înapoi” în SQL Server 2005 și a fost extinsă în SQL Server 2012. Este utilizat în principal cu „Funcțiile ferestrei”; singura excepție fiind funcția de secvență NEXT VALUE FOR. Clauza OVER este utilizată pentru a determina care rânduri din interogare sunt aplicate funcției, în ce ordine sunt evaluate de acea funcție și când calculele funcției ar trebui să repornească. Deoarece este utilizat împreună cu alte funcții, iar acest articol se referă în mod specific la clauza OVER, despre aceste funcții se va vorbi numai în ceea ce privește clauza OVER din exemplele date.

Sintaxa lui clauza OVER este:

<function> OVER ( )

Privind sintaxa, se pare că toate sub-clauzele sunt opționale. De fapt, fiecare funcție care poate utiliza clauza OVER determină care dintre sub-clauze sunt permise și care sunt necesare. În funcție de funcția utilizată, clauza OVER în sine poate fi opțională. Există un grafic la sfârșitul acestui articol care arată ce funcții permit / necesită ce porțiuni din clauza OVER.

Clauza PARTITION BY este utilizată pentru a împărți setul de rezultate din interogare în subseturi de date sau partiții. Dacă clauza PARTITION BY nu este utilizată, întregul set de rezultate din interogare este partiția care va fi utilizată. Funcția de fereastră utilizată se aplică fiecărei partiții separat, iar calculul pe care îl efectuează funcția este repornit pentru fiecare partiție. Definiți un set de valori care determină partiția / partițiile în care se împarte interogarea. Aceste valori pot fi coloane, funcții scalare, subinterogări scalare sau variabile.

De exemplu, să examinăm următoarea interogare:

SELECT COUNT(*)FROM .sys.indexes;

Această interogare returnează următorul set de rezultate:

Acesta este pur și simplu numărul de rânduri returnate de interogare – în acest caz, numărul de indici din baza de date msdb. Acum să adăugăm clauza OVER la această interogare:

SELECT object_id, index_id, COUNT(*) OVER ()FROM .sys.indexes;

Rezultatele abreviate sunt:

Această interogare returnează object_id și index_id pentru fiecare index și numărul total de indici din setul de rezultate. Deoarece nu a fost utilizată o clauză PARTITION BY, întregul set de rezultate a fost tratat ca o singură partiție. Acum este timpul să adăugați clauza PARTITION BY și să vedeți cum modifică rezultatele:

SELECT object_id, index_id, COUNT(*) OVER (PARTITION BY object_id)FROM .sys.indexes;

Rezultatele abreviate sunt:

Această interogare returnează un rând pentru fiecare index, dar acum interogarea specifică o clauză PARTITION BY a coloanei object_id, astfel încât funcția de numărare returnează numărul de indexuri pe acel obiect_id. Clauza ORDER BY controlează ordinea în care rândurile sunt evaluate de funcție. Acest lucru va fi demonstrat în curând. Clauza ROWS sau RANGE determină subsetul de rânduri din cadrul partiției care urmează să fie aplicate funcției. Când utilizați ROWS sau RANGE, specificați punctul de început și sfârșit al ferestrei. Valorile permise sunt:

Există două sintaxi pentru specificarea ferestrei:

BETWEEN <beginning frame> AND <ending frame><beginning frame>

Dacă este specificat doar cadrul de început, cadrul de sfârșit implicit este RÂNDUL ACTUAL.

Cuvântul cheie UNBOUNDED specifică începutul partiției (pentru PRECEDING) sau sfârșitul partiția (pentru URMĂTOARE). RÂNDUL ACTUAL specifică faptul că rândul curent este fie începutul ferestrei, fie sfârșitul ferestrei, în funcție de poziția cadrului ferestrei în care este utilizat. „N” specifică un număr de rânduri fie înainte de rândul curent (pentru ), sau după rândul curent (pentru URMĂTOARE) de utilizat pentru cadrul ferestrei.

Următoarele sunt specificații valide ale ferestrei:

-- specifies the entire result set from the partitionBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- specifies 5 rows, starting 4 rows prior to the current row through the current row from the partitionBETWEEN 4 PRECEDING AND CURRENT ROW-- specifies all of the rows from the current row to the end of the partitionBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING-- specifies all of the rows from the start of the partition through the current rowUNBOUNDED PRECEDING

Pentru a utiliza clauza ROWS sau RANGE, trebuie să specificați și clauza ORDER BY. Dimpotrivă, dacă utilizați clauza ORDER BY și nu specificați o clauză ROWS sau RANGE, atunci GAMA implicită ÎNTRE PRECEDEREA ȘI CURENTUL NELIMITAT ROW este utilizat.

Pentru a demonstra clauzele ORDER BY și ROWS sau RANGE, să creăm câteva date de testare: două conturi, patru date pe cont și o sumă pentru fiecare dată. Interogarea va afișa ambele clauzele fiind utilizate în diferite moduri:

Această interogare returnează următorul set de rezultate:

Coloana RowNbr ”este usin g funcția COUNT pentru a returna câte rânduri sunt în partiție. Partiția este ordonată de TranDate și specificăm un cadru de fereastră pentru toate rândurile de la începutul partiției până la rândul curent. Pentru primul rând, există un singur rând în cadrul ferestrei, astfel încât valoarea „1” este returnată. Pentru al doilea rând, acum sunt două rânduri în cadrul ferestrei, astfel încât valoarea „2” este returnată. Și așa mai departe prin restul rândurilor din acest cont.

Deoarece clauza PARTITION BY specifică contul, atunci când contul se modifică, calculele funcției sunt resetate, ceea ce poate fi văzut examinând rândurile pentru al doilea cont din setul de rezultate. Acesta este un exemplu de agregare „în curs”, în care agregarea se bazează pe calculele anterioare. Un exemplu de când ați utiliza acest lucru ar fi să calculați soldul contului bancar după fiecare tranzacție (altfel cunoscut sub numele de total curent).

Coloana „DateCount” efectuează un număr de câte rânduri, partiționate după dată. În acest exemplu, fiecare dintre conturi are o tranzacție la fiecare din aceleași patru date, deci fiecare dată are două tranzacții (una pentru fiecare cont). Aceasta are ca rezultat returnarea valorii „2” pentru fiecare rând. Acest lucru este similar cu efectuarea unui număr care folosește GROUP BY pentru dată; diferența fiind că totalul este returnat pentru fiecare rând în loc de o singură dată pentru fiecare dată. Un exemplu când ați folosi această metodă ar fi să afișați un „Rând X de Y” sau să calculați un procent din rândul curent până la total.

Coloana „Last2Count” efectuează un număr de rânduri în cadrul partiției, pentru rândul curent și primul rând imediat precedent. Pentru primul rând din fiecare cont, deoarece nu există rânduri care îl precedă, se returnează o valoare „1”. Pentru rândurile rămase din fiecare cont, se returnează o valoare „2”. Acesta este un exemplu de agregare „în mișcare” sau „glisant”. Un exemplu de când ați utiliza această metodă ar fi calcularea unui bonus pe baza vânzările din ultimele două luni.

În acest moment, am arătat doar clauza ROWS. Clauza RANGE funcționează într-un mod similar, dar în loc să trateze rândurile într-un mod pozițional, se ocupă de valorile returnate de acel rând. Pentru că nu este pozițional, clauzele N PRECEDING / URMĂTOARE nu pot fi utilizate. Să aruncăm o privire rapidă asupra diferenței dintre ROWS și RANGE folosindu-le pe ambele în aceeași interogare. Aici avem un listă de persoane (să le numim DBA) și tarifele lor orare. Rețineți că rândurile cu RowIDs 4 & 5 și 12 & 13 au aceeași rată. Interogarea va însuma tarifele de două ori, odată folosind ROWS și cealaltă utilizând RANGE:

Această interogare produce următorul set de rezultate:

În coloanele SumByRows și SumByRange, clauza OVER este identică cu excepția clauzei ROWS / RANGE. Observați, de asemenea, că întrucât intervalul de final nu a fost specificat, implicit este să folosiți CURRENT ROW Întrucât însumăm salariul de la începutul setului de rezultate prin rândul curent, ceea ce calculăm cu adevărat este un total curent al coloanei Salariu. În coloana SumByRows, valoarea este calculată folosind clauza ROWS și putem vedea că suma rândului curent este Salariul rândului curent plus totalul rândului anterior. Cu toate acestea, clauza RANGE funcționează din valoarea coloanei Salariu, deci rezumă toate rândurile cu salariul același sau mai mic. Rezultă ca valoarea SumByRange să fie aceeași valoare pentru toate rândurile cu același salariu.

O notă importantă: clauza ORDER BY din clauza OVER controlează doar ordinea în care rândurile din partiție vor fi utilizate. prin funcția de fereastră. Nu controlează ordinea setului de rezultate finale. Fără o clauză ORDER BY pentru interogarea însăși, ordinea rândurilor nu este garantată. Este posibil să observați că interogarea dvs. poate reveni în ordinea ultimei clauze OVER specificate – acest lucru se datorează modului în care aceasta este implementată în prezent în SQL Server. Dacă echipa SQL Server de la Microsoft schimbă modul în care funcționează, este posibil să nu mai comande rezultatele în modul pe care îl observați în prezent. Dacă aveți nevoie de o comandă specifică pentru setul de rezultate, trebuie să furnizați o clauză ORDER BY împotriva interogării în sine.

În cele din urmă, iată o diagramă a diferitelor funcții care pot utiliza clauza OVER, precum și ce porțiuni din clauză sunt permise / obligatorii / opționale.

R-Obligatoriu, O-Opțional, X-Nu Permis