SELECT – Clauza OVER (Transact-SQL)

• 08/11/2017
• 17 minute de citit
• • V
  • L
  • c
  • j
  • M
  • +11

Se aplică la: SQL Server (toate versiuni acceptate) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Parallel Data Warehouse

Determină partiționarea și ordonarea unui set de rânduri înainte de aplicarea funcției de fereastră asociate. Adică, clauza OVER definește o fereastră sau un set de rânduri specificat de utilizator într-un set de rezultate de interogare. O funcție de fereastră calculează apoi o valoare pentru fiecare rând din fereastră. Puteți utiliza clauza OVER cu funcții pentru a calcula valori cumulate cum ar fi medii mobile, agregate cumulative, totaluri în curs de executare sau un rezultat N pentru fiecare grup.

• Funcții de clasificare

• Funcții agregate

• Funcții analitice

• FUNCȚIA NEXT VALUE FOR

Convenții privind sintaxa Transact-SQL

Sintaxă

Argumente

Funcțiile Window pot avea următoarele argumente în clauza OVER:

• PARTITION BY care împarte setul de rezultate ale interogării în partiții.
• ORDER BY care definește ordinea logică a rândurilor din cadrul fiecărei partiții a setului de rezultate.
• ROWS / RANGE care limitează rândurile din partiție prin specificarea punctelor de început și de sfârșit în cadrul partiției. Necesită argument ORDER BY și valoarea implicită este de la începutul partiției până la elementul curent dacă este specificat argumentul ORDER BY.

Dacă nu specificați niciun argument, funcțiile ferestrei vor fi aplicate pe întregul set de rezultate.

object_id	min	max
3	3	2139154666
5	3	2139154666
…	…	…
2123154609	3	2139154666
2139154666	3	2139154666

PARTITION BY

Împarte rezultatul interogării setat în partiții. Funcția fereastră este aplicată fiecărei partiții separat și calculul repornește pentru fiecare partit ion.

Dacă PARTITION BY nu este specificată, funcția tratează toate rândurile setului de rezultate ale interogării ca o singură partiție. Funcția va fi aplicată pe toate rândurile în partiție dacă nu specificați clauza ORDER BY.

PARTITION BY value_expression

Specifică coloana prin care este partiționat setul de rânduri. value_expression se poate referi numai la coloanele puse la dispoziție prin clauza FROM. value_expression nu se poate referi la expresii sau aliasuri din lista de selectare. value_expression poate fi o expresie de coloană, subinterogare scalară, funcție scalară sau variabilă definită de utilizator.

ORDER BY

Definește ordinea logică a rândurilor din fiecare partiție a setului de rezultate. Adică, specifică ordinea logică în care se efectuează calculul funcției ferestrei.

• Dacă nu este specificat, ordinea implicită este ASC și funcția de fereastră va utiliza toate rândurile din partiție.
• Dacă este specificată și nu este specificat un ROWS / RANGE, atunci implicit pentru fereastră este RANGE UNBOUNDED PRECEDING AND CURRENT ROW cadru după funcțiile care pot accepta specificațiile ROWS / RANGE opționale (de exemplu min sau max).

order_by_expression
Specifică o coloană sau o expresie pe care să se sorteze. order_by_expression se poate referi numai la coloanele puse la dispoziție prin clauza FROM. Un număr întreg nu poate fi specificat pentru a reprezenta un nume de coloană sau un alias.

COLLATE collation_name
Specifică faptul că operația ORDER BY trebuie efectuată în conformitate cu colationarea specificată în collation_name. collation_name poate fi fie un nume de colație Windows, fie un nume de colație SQL. Pentru mai multe informații, consultați Asistență pentru colaționare și Unicode. COLLATE se aplică numai pentru coloanele de tip char, varchar, nchar și nvarchar.

ASC | DESC
Specifică faptul că valorile din coloana specificată trebuie să fie sortate în ordine crescătoare sau descendentă. ASC este ordinea de sortare implicită.Valorile nule sunt tratate ca cele mai mici valori posibile.

ROWS or RANGE

Se aplică la: SQL Server 2012 (11.x) și versiuni ulterioare.

Alte limite rândurile din partiție prin specificarea punctelor de început și de sfârșit din cadrul partiției. Acest lucru se face prin specificarea unui interval de rânduri în raport cu rândul curent, fie prin asociere logică, fie prin asociere fizică. Asocierea fizică se realizează utilizând clauza ROWS.

Clauza ROWS limitează rândurile dintr-o partiție prin specificarea unui număr fix de rânduri care precedă sau urmează rândul curent. Alternativ, clauza RANGE limitează logic rândurile dintr-o partiție prin specificarea unui interval de valori față de valoarea din rândul curent. Rândurile anterioare și următoare sunt definite pe baza ordonării din clauza ORDER BY. Cadrul ferestrei „RANGE … CURRENT ROW …” include toate rândurile care au aceleași valori în expresia ORDER BY ca rândul curent. De exemplu, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW înseamnă că fereastra de rânduri pe care funcționează funcția are trei rânduri ca dimensiune, începând cu 2 rânduri care preced până și includ rândul curent.

UNBOUNDED PRECEDING

Se aplică la: SQL Server 2012 (11.x) și versiunile ulterioare.

Specifică faptul că fereastra pornește de la primul rând al partiției. PRECEDAREA NELIMITATĂ poate fi specificată doar ca punct de pornire a ferestrei.

< specificație de valoare nesemnată > PRECEDING
Specified cu < specificație de valoare nesemnată > pentru a indica numărul de rânduri sau valorile care preced precedentul rând curent. Această specificație nu este permisă pentru RANGE.

RUN ACTUAL

Se aplică la: SQL Server 2012 (11.x) și versiuni ulterioare.

Specifică faptul că fereastra pornește sau se termină la rândul curent atunci când este utilizat cu ROWS sau la valoarea curentă când este utilizat cu RANGE. RÂNDUL ACTUAL poate fi specificat atât ca punct de pornire, cât și ca punct final.

ÎNTRE ȘI

Se aplică la: SQL Server 2012 (11.x) și ulterior.

Folosit fie cu ROWS, fie cu RANGE pentru a specifica punctele limită inferioare (de început) și superioare (finale) ale ferestrei. < legat cadru fereastră > definește punctul de pornire la limită și < legat cadru fereastră > definește punctul final al limitei. Limita superioară nu poate fi mai mică decât limita inferioară.

URMĂRIRE NELIMITATĂ

Se aplică la: SQL Server 2012 (11.x) și versiunile ulterioare.

Specifică faptul că fereastra se termină la ultimul rând al partiției. URMĂTOAREA NELIMITATĂ poate fi specificată numai ca punct final al ferestrei. De exemplu GAMA ÎNTRE RÂNDUL ACTUAL ȘI URMĂTOAREA NELIMITATĂ definește o fereastră care începe cu rândul curent și se termină cu ultimul rând al partiției.

< > URMĂTOARE
Specificat cu < specificație de valoare nesemnată > pentru a indica numărul de rânduri sau valori pentru a urma rândul curent. Când < specificație de valoare nesemnată > URMĂTOARE este specificat ca punct de pornire al ferestrei, punctul final trebuie să fie < specificație de valoare nesemnată > URMĂTOARE. De exemplu, RÂNDURI ÎNTRE 2 URMĂTOARE ȘI 10 URMĂTOARE definește o fereastră care începe cu al doilea rând care urmează rândul curent și se termină cu al zecelea rând care urmează rândul curent. Această specificație nu este permisă pentru RANGE.

literal întreg fără semnătură
Se aplică la: SQL Server 2012 (11.x) și versiuni ulterioare.

Este un literal întreg pozitiv (inclusiv 0 ) care specifică numărul de rânduri sau valori care trebuie să preceadă sau să urmeze rândul sau valoarea curentă. Această specificație este valabilă numai pentru ROWS.

Observații generale

Mai multe funcții de fereastră pot fi utilizate într-o singură interogare cu o singură clauză FROM. Clauza OVER pentru fiecare funcție poate diferi în partiționare și ordonare.

Dacă PARTITION BY nu este specificat, funcția tratează toate rândurile setului de rezultate ale interogării ca un singur grup.

Important !

Dacă este specificat ROWS / RANGE și < cadrul ferestrei care precedă > este utilizat pentru < extinderea cadrului ferestrei > (sintaxă scurtă), atunci această specificație este utilizată pentru punctul de pornire al limitei cadrului ferestrei, iar CURRENT ROW este utilizat pentru punctul final de limită. De exemplu, „ROWS 5 PRECEDING” este egal cu „ROWS BETWEEN 5 PRECEDING AND CURRENT ROW”.

Limitări și restricții

Clauza OVER nu poate fi utilizată cu funcția agregată CHECKSUM.

RANGE nu poate fi utilizată cu < specificație de valoare nesemnată > PRECEDING sau < specificație de valoare nesemnată > URMĂTOARE.

În funcție de clasament, agregat sau analitic funcție utilizată cu clauza OVER, < clauza ORDER BY > și / sau < Clauza ROWS și RANGE > nu poate fi acceptată.

Exemple

A. Utilizarea clauzei OVER cu funcția ROW_NUMBER

Următorul exemplu arată utilizarea clauzei OVER cu funcția ROW_NUMBER pentru a afișa un număr de rând pentru fiecare rând dintr-o partiție. Clauza ORDER BY specificată în clauza OVER ordonează rândurile din fiecare partiție de coloana SalesYTD. Clauza ORDER BY din instrucțiunea SELECT determină ordinea în care este returnat întregul set de rezultate ale interogării.

Iată setul de rezultate.

B. Utilizarea clauzei OVER cu funcții agregate

Următorul exemplu folosește clauza OVER cu funcții agregate pe toate rândurile returnate de interogare. În acest exemplu, utilizarea clauzei OVER este mai eficientă decât utilizarea subinterogărilor pentru a obține valorile agregate.

Iată setul de rezultate.

Următorul exemplu arată utilizarea clauzei OVER cu o funcție agregată într-o valoare calculată.

Iată setul de rezultate. Observați că agregatele sunt calculate de SalesOrderID și Percent by ProductID este calculat pentru fiecare linie din fiecare SalesOrderID.

C. Producerea unei medii mobile și a unui total cumulativ

Următorul exemplu folosește funcțiile AVG și SUM împreună cu clauza OVER pentru a furniza o medie mobilă și un total cumulat al vânzărilor anuale pentru fiecare teritoriu din Sales.SalesPerson tabel. Datele sunt partiționate de TerritoryID și ordonate logic de SalesYTD. Aceasta înseamnă că funcția AVG este calculată pentru fiecare teritoriu pe baza anului de vânzare. Observați că pentru TerritoryID 1, există două rânduri pentru anul de vânzări 2005 care reprezintă cei doi oameni de vânzări cu vânzări din acel an. Vânzările medii pentru aceste două rânduri sunt calculate și apoi al treilea rând care reprezintă vânzările pentru anul 2006 este inclus în calcul.

Iată setul de rezultate.

În acest exemplu, clauza OVER nu include PARTITION BY. Aceasta înseamnă că funcția va fi aplicată tuturor rândurilor returnate de interogare. Clauza ORDER BY specificată în clauza OVER determină ordinea logică la care se aplică funcția AVG. Interogarea returnează o medie mobilă a vânzărilor pe an pentru toate teritoriile de vânzare specificate în clauza WHERE. Clauza ORDER BY specificată în instrucțiunea SELECT determină ordinea în care sunt afișate rândurile interogării.

Iată setul de rezultate.

D. Specificarea clauzei ROWS

Se aplică: SQL Server 2012 (11.x) și versiuni ulterioare.

Următorul exemplu folosește clauza ROWS pentru a defini o fereastră peste care rândurile sunt calculate ca rândul curent și numărul N de rânduri care urmează (1 rând în acest exemplu).

Iată setul de rezultate.

În exemplul următor, clauza ROWS este specificată cu UNBOUNDED PRECEDING. Rezultatul este că fereastra pornește de la primul rând al partiției.

Iată setul de rezultate.

Exemple: Depozit de date paralele

E. Utilizarea clauzei OVER cu funcția ROW_NUMBER

Următorul exemplu returnează ROW_NUMBER pentru reprezentanții de vânzări pe baza cotei lor de vânzări alocate.

Iată un set de rezultate parțiale.

F. Utilizarea clauzei OVER cu funcții agregate

Următoarele exemple arată utilizarea clauzei OVER cu funcții agregate. În acest exemplu, utilizarea clauzei OVER este mai eficientă decât utilizarea subinterogărilor.

Iată setul de rezultate.

Următorul exemplu arată folosind clauza OVER cu o funcție agregată într-o valoare calculată. Observați că agregatele sunt calculate de SalesOrderNumber și procentul din totalul comenzii de vânzare este calculat pentru fiecare linie din fiecare SalesOrderNumber.

Primul început al acestui set de rezultate este:

Vezi și

Funcții agregate (Transact-SQL)
Funcții analitice (Transact-SQL)
Postare excelentă pe blog despre funcțiile ferestrei și peste, pe sqlmag.com, de Itzik Ben-Gan