Filtrele clasice din SQL, cum ar fi ON, WHERE și HAVING, se bazează pe predicate. TOP și OFFSET-FETCH sunt filtre care se bazează pe un concept diferit: indicați ordinea și câte rânduri să filtrați pe baza acelei ordini. Multe sarcini de filtrare sunt definite pe baza ordinii și a unui număr necesar de rânduri. Cu siguranță este bine să aveți un suport lingvistic în T-SQL care vă permite să formulați cererea într-un mod similar cu modul în care vă gândiți la sarcină.
Acest capitol începe cu aspectele de proiectare logică a filtrelor. Apoi folosește un scenariu de paginare pentru a demonstra optimizarea acestora. Capitolul acoperă, de asemenea, utilizarea TOP cu declarații de modificare. În cele din urmă, capitolul demonstrează utilizarea TOP și OFFSET-FETCH în rezolvarea unor probleme practice, cum ar fi top N per grup și mediană.
Filtrele TOP și OFFSET-FETCH
Utilizați filtrele TOP și OFFSET-FETCH pentru a implementa cerințele de filtrare în interogările dumneavoastră într-un mod intuitiv. Filtrul TOP este o caracteristică proprie în T-SQL, în timp ce filtrul OFFSET-FETCH este o caracteristică standard. T-SQL a început să suporte OFFSET-FETCH odată cu Microsoft SQL Server 2012. Începând cu SQL Server 2014, implementării OFFSET-FETCH în T-SQL îi lipsesc încă câteva elemente standard – interesant, unele care sunt disponibile cu TOP. Cu implementarea actuală, fiecare dintre filtre are capacități care nu sunt suportate de celălalt.
Voi începe prin a descrie aspectele de proiectare logică ale TOP și apoi le voi acoperi pe cele ale OFFSET-FETCH.
Filtrul TOP
Filtrul TOP este un construct utilizat în mod obișnuit în T-SQL. Popularitatea sa poate fi probabil atribuită faptului că designul său este atât de bine aliniat cu modul în care sunt exprimate multe cerințe de filtrare – de exemplu, „Return the three most recent orders”. În această cerere, ordinea pentru filtru se bazează pe orderdate, descrescător, iar numărul de rânduri pe care doriți să le filtrați pe baza acestei ordini este 3.
Specificați opțiunea TOP în lista SELECT cu o valoare de intrare tastată ca BIGINT care indică numărul de rânduri pe care doriți să le filtrați. Furnizați specificația de ordonare în clauza clasică ORDER BY. De exemplu, utilizați următoarea interogare pentru a obține cele mai recente trei comenzi.
USE TSQLV3;SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Am obținut următoarea ieșire din această interogare:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
În loc să specificați numărul de rânduri pe care doriți să le filtrați, puteți utiliza TOP pentru a specifica procentul (din numărul total de rânduri din rezultatul interogării). Faceți acest lucru furnizând o valoare în intervalul de la 0 la 100 (tastată ca FLOAT) și adăugați cuvântul cheie PERCENT. De exemplu, în următoarea interogare solicitați să filtrați un procent din rânduri:
SELECT TOP (1) PERCENT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
SQL Server rotunjește numărul de rânduri calculat pe baza procentului introdus. De exemplu, rezultatul de 1 la sută aplicat la 830 de rânduri din tabelul Comenzi este 8,3. Prin rotunjirea acestui număr, se obține 9. Iată rezultatul pe care l-am obținut pentru această interogare:
orderid orderdate custid empid----------- ---------- ----------- -----------11074 2015-05-06 73 711075 2015-05-06 68 811076 2015-05-06 9 411077 2015-05-06 65 111070 2015-05-05 44 211071 2015-05-05 46 111072 2015-05-05 20 411073 2015-05-05 58 211067 2015-05-04 17 1
Rețineți că, pentru a traduce procentul de intrare într-un număr de rânduri, SQL Server trebuie să calculeze mai întâi numărul de rânduri din rezultatul interogării, iar acest lucru necesită, de obicei, muncă suplimentară.
Interesant este faptul că specificarea ordonării este opțională pentru filtrul TOP. De exemplu, luați în considerare următoarea interogare:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.Orders;
Am obținut următorul rezultat din această interogare:
orderid orderdate custid empid----------- ---------- ----------- -----------10248 2013-07-04 85 510249 2013-07-05 79 610250 2013-07-08 34 4
Selecția celor trei rânduri care trebuie returnate este nedeterministă. Acest lucru înseamnă că, dacă executați din nou interogarea, fără ca datele de bază să se schimbe, teoretic ați putea obține un set diferit de trei rânduri. În practică, selecția rândurilor va depinde de condițiile fizice, cum ar fi opțiunile de optimizare, alegerile motorului de stocare, dispunerea datelor și alți factori. Dacă executați interogarea de mai multe ori, atâta timp cât aceste condiții fizice nu se schimbă, există o anumită probabilitate să obțineți în continuare aceleași rezultate. Dar este esențial să înțelegeți principiul „independenței fizice a datelor” din modelul relațional și să vă amintiți că, la nivel logic, nu aveți o garanție pentru rezultate repetabile. Fără o specificare a ordonării, ar trebui să considerați că ordinea este arbitrară, ceea ce duce la o selecție nedeterministă a rândurilor.
Inclusiv atunci când furnizați o specificare a ordonării, aceasta nu înseamnă că interogarea este deterministă. De exemplu, o interogare TOP anterioară a folosit orderdate, DESC ca specificație de ordonare. Coloana orderdate nu este unică; prin urmare, selecția între rândurile cu aceeași dată a comenzii este nedeterministă. Așadar, ce trebuie să faceți în cazurile în care trebuie să garantați determinismul? Există două opțiuni: utilizarea WITH TIES sau ordonarea unică.
Opțiunea WITH TIES face ca legăturile să fie incluse în rezultat. Iată cum se aplică în exemplul nostru:
SELECT TOP (3) WITH TIES orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Iată rezultatul pe care l-am obținut din această interogare:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 811074 2015-05-06 73 7
SQL Server filtrează cele trei rânduri cu cele mai recente date de comandă, plus că include toate celelalte rânduri care au aceeași dată de comandă ca în ultimul rând. Ca urmare, puteți obține mai multe rânduri decât numărul pe care l-ați specificat. În această interogare, ați specificat că doriți să filtrați trei rânduri, dar ați ajuns să obțineți patru. Ceea ce este interesant de observat aici este că selecția rândurilor este acum deterministă, dar ordinea de prezentare între rândurile cu aceeași dată a comenzii este nedeterministă.
A doua metodă pentru a garanta un rezultat determinist este de a face ca specificația de ordonare să fie unică prin adăugarea unui element de departajare. De exemplu, ați putea adăuga orderid, DESC ca element de departajare în exemplul nostru. Acest lucru înseamnă că, în caz de egalitate în ceea ce privește valorile datei de comandă, un rând cu o valoare mai mare a ID-ului de comandă este preferat unui rând cu o valoare mai mică. Iată interogarea noastră cu aplicarea criteriului de departajare:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESC;
Această interogare generează următorul rezultat:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
Utilizarea ordonării unice face ca atât selecția rândurilor, cât și ordonarea prezentării să fie deterministe. Atât setul de rezultate, cât și ordinea de prezentare a rândurilor sunt garantate a fi repetabile atât timp cât datele de bază nu se schimbă.
Dacă aveți un caz în care trebuie să filtrați un anumit număr de rânduri, dar nu vă interesează cu adevărat ordinea, ar putea fi o idee bună să specificați ORDER BY (SELECT NULL), astfel:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL);
În acest fel, lăsați pe toată lumea să știe că alegerea dvs. de ordine arbitrară este intenționată, ceea ce ajută la evitarea confuziei și a îndoielilor.
Ca o reamintire a ceea ce am explicat în Capitolul 1, „Prelucrarea logică a interogărilor”, despre filtrele TOP și OFFSET-FETCH, ordinea de prezentare este garantată numai dacă interogarea exterioară are o clauză ORDER BY. De exemplu, în următoarea interogare de prezentare, ordinea nu este garantată:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS D;
Pentru a oferi o garanție a ordinii de prezentare, trebuie să specificați o clauză ORDER BY în interogarea exterioară, astfel:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS DORDER BY orderdate DESC, orderid DESC;
Filtrul OFFSET-FETCH
Filtrul OFFSET-FETCH este o caracteristică standard concepută similar cu TOP, dar cu un element în plus. Puteți specifica câte rânduri doriți să săriți înainte de a specifica câte rânduri doriți să filtrați.
Așa cum ați fi putut ghici, această caracteristică poate fi utilă în implementarea soluțiilor de paginare – adică returnarea unui rezultat către utilizator, bucată cu bucată, la cerere, atunci când setul complet de rezultate este prea lung pentru a încăpea pe un ecran sau pe o pagină web.
Filtrul OFFSET-FETCH necesită existența unei clauze ORDER BY și este specificat imediat după aceasta. Începeți prin a indica câte rânduri să săriți într-o clauză OFFSET, urmată de câte rânduri să filtrați într-o clauză FETCH. De exemplu, pe baza ordinii indicate, următoarea interogare sare peste primele 50 de rânduri și filtrează următoarele 25 de rânduri:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
Cu alte cuvinte, interogarea filtrează rândurile de la 51 la 75. În termeni de paginare, presupunând o dimensiune a paginii de 25 de rânduri, această interogare returnează a treia pagină.
Pentru a permite un limbaj declarativ natural, puteți utiliza cuvântul cheie FIRST în loc de NEXT, dacă doriți, deși semnificația este aceeași. Utilizarea FIRST ar putea fi mai intuitivă dacă nu săriți niciun rând. Chiar dacă nu doriți să săriți niciun rând, T-SQL face totuși obligatorie specificarea clauzei OFFSET (cu 0 ROWS) pentru a evita ambiguitatea parsării. În mod similar, în loc să utilizați forma plurală a cuvântului cheie ROWS, puteți utiliza forma singulară ROW atât în clauza OFFSET, cât și în clauza FETCH. Acest lucru este mai natural dacă trebuie să săriți sau să filtrați un singur rând.
Dacă sunteți curioși care este scopul cuvântului cheie ONLY, acesta înseamnă să nu includeți legături. Standard SQL definește alternativa WITH TIES; cu toate acestea, T-SQL nu o suportă încă. În mod similar, SQL standard definește opțiunea PERCENT, dar nici T-SQL nu o suportă încă. Aceste două opțiuni lipsă sunt disponibile cu filtrul TOP.
După cum am menționat, filtrul OFFSET-FETCH necesită o clauză ORDER BY. Dacă doriți să folosiți o ordine arbitrară, precum TOP fără o clauză ORDER BY, puteți folosi trucul cu ORDER BY (SELECT NULL), astfel:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL)OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
Clauza FETCH este opțională. Dacă doriți să săriți peste un anumit număr de rânduri, dar să nu limitați numărul de rânduri de returnat, pur și simplu nu indicați o clauză FETCH. De exemplu, următoarea interogare omite 50 de rânduri, dar nu limitează numărul de rânduri returnate:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS;
În ceea ce privește ordonarea prezentării, comportamentul este același ca și în cazul filtrului TOP; și anume, și în cazul OFFSET-FETCH, ordonarea prezentării este garantată numai dacă interogarea cea mai exterioară are o clauză ORDER BY.
.