A klasszikus SQL-szűrők, mint az ON, WHERE és HAVING predikátumokon alapulnak. A TOP és az OFFSET-FETCH olyan szűrők, amelyek más koncepción alapulnak: megadjuk a sorrendet és azt, hogy hány sort kell szűrni a sorrend alapján. Sok szűrési feladatot a sorrend és a szükséges sorszám alapján határozunk meg. Mindenképpen jó, ha a T-SQL-ben van olyan nyelvi támogatás, amely lehetővé teszi, hogy a kérést úgy fogalmazzuk meg, ahogyan a feladatról gondolkodunk.
Ez a fejezet a szűrők logikai tervezési szempontjaival kezdődik. Ezután egy lapozási forgatókönyv segítségével mutatja be optimalizálásukat. A fejezet kitér a TOP használatára is a módosító utasításokkal. Végül a fejezet bemutatja a TOP és az OFFSET-FETCH használatát olyan gyakorlati problémák megoldásában, mint a top N per csoport és a medián.
A TOP és az OFFSET-FETCH szűrők
A TOP és az OFFSET-FETCH szűrők segítségével intuitív módon valósíthatja meg a szűrési követelményeket a lekérdezésekben. A TOP szűrő a T-SQL saját szolgáltatása, míg az OFFSET-FETCH szűrő egy szabványos szolgáltatás. A T-SQL a Microsoft SQL Server 2012-vel kezdte el támogatni az OFFSET-FETCH szűrőt. Az SQL Server 2014-től kezdve az OFFSET-FETCH megvalósítása a T-SQL-ben még mindig hiányzik néhány szabványos elem – érdekes módon olyanok, amelyek a TOP szűrővel elérhetőek. A jelenlegi implementációval mindegyik szűrő rendelkezik olyan képességekkel, amelyeket a másik nem támogat.
A TOP logikai tervezési szempontjainak ismertetésével kezdem, majd az OFFSET-FETCH szempontjaival foglalkozom.
A TOP szűrő
A TOP szűrő a T-SQL-ben gyakran használt konstrukció. Népszerűsége valószínűleg annak tulajdonítható, hogy felépítése olyan jól illeszkedik számos szűrési követelmény megfogalmazásának módjához – például: “A három legfrissebb rendelés visszaadása”. Ebben a kérésben a szűrés sorrendje a orderdate alapján csökkenő, és az e sorrend alapján szűrni kívánt sorok száma 3.
A SELECT listában a TOP opciót egy BIGINT-ként begépelt bemeneti értékkel adja meg, amely jelzi, hogy hány sort szeretne szűrni. A rendezési specifikációt a klasszikus ORDER BY záradékban adja meg. Például a következő lekérdezéssel a három legfrissebb rendelést kapja meg.
USE TSQLV3;SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
A lekérdezéssel a következő kimenetet kaptam:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
A szűrni kívánt sorok számának megadása helyett a TOP opcióval megadhatja a százalékot (a lekérdezés eredményében lévő összes sor számának százalékában). Ehhez megad egy értéket a 0 és 100 közötti tartományban (FLOAT-ként beírva), és hozzáadja a PERCENT kulcsszót. Például az alábbi lekérdezésben a sorok egy százalékának szűrését kéri:
SELECT TOP (1) PERCENT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
A SQL Server a megadott százalék alapján kiszámított sorok számát felfelé kerekíti. Például a Rendelések táblázat 830 sorára alkalmazott 1 százalék eredménye 8,3 lesz. Ezt a számot felfelé kerekítve 9-et kapunk. Íme a kimenet, amit erre a lekérdezésre kaptam:
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
Megjegyzendő, hogy a bemeneti százalékot sorszámra fordítva az SQL Server-nek először ki kell számolnia a lekérdezés eredményében szereplő sorok számát, és ez általában pluszmunkát igényel.
Érdekes, hogy a TOP szűrő esetében a sorrendmegadás opcionális. Vegyük például a következő lekérdezést:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.Orders;
A következő kimenetet kaptam a lekérdezésből:
orderid orderdate custid empid----------- ---------- ----------- -----------10248 2013-07-04 85 510249 2013-07-05 79 610250 2013-07-08 34 4
Az, hogy melyik három sor kerüljön visszaadásra, nem determinisztikus. Ez azt jelenti, hogy ha a lekérdezést újra lefuttatjuk anélkül, hogy a mögöttes adatok megváltoznának, elméletileg más három sort is kaphatunk. A gyakorlatban a sorok kiválasztása olyan fizikai feltételektől függ, mint az optimalizálási választások, a tárolómotor választása, az adatok elrendezése és egyéb tényezők. Ha ténylegesen többször futtatja a lekérdezést, mindaddig, amíg ezek a fizikai feltételek nem változnak, bizonyos valószínűséggel ugyanazokat az eredményeket fogja kapni. Fontos azonban, hogy megértsük a relációs modellből a “fizikai adatfüggetlenség” elvét, és ne feledjük, hogy logikai szinten nincs garancia az ismétlődő eredményekre. Rendezési specifikáció nélkül a sorrendet önkényesnek kell tekintenünk, ami nemdeterminisztikus sorválasztást eredményez.
Még ha megadjuk is a sorrendezési specifikációt, ez nem jelenti azt, hogy a lekérdezés determinisztikus. Például egy korábbi TOP lekérdezés orderdate, DESC sorrendezési specifikációt használt. A orderdate oszlop nem egyedi, ezért az azonos rendelési dátummal rendelkező sorok közötti kiválasztás nem determinisztikus. Mit tegyünk tehát azokban az esetekben, amikor garantálni kell a determinizmust? Két lehetőség van: a WITH TIES vagy az egyedi rendezés használata.
A WITH TIES opció hatására a kötések bekerülnek az eredménybe. Íme, hogyan alkalmazza a példánkban:
SELECT TOP (3) WITH TIES orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Itt az eredmény, amit ebből a lekérdezésből kaptam:
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
A SQL Server kiszűri a három sort a legfrissebb rendelési dátummal, plusz az összes többi sort is bevonja, amelyek rendelési dátuma megegyezik az utolsó soréval. Ennek eredményeképpen a megadott számnál több sort kaphat. Ebben a lekérdezésben megadta, hogy három sort szeretne szűrni, de végül négyet kapott. Ami itt érdekes, hogy a sorok kiválasztása most determinisztikus, de az azonos rendelési dátumú sorok közötti bemutatási sorrend nem determinisztikus.
A determinisztikus eredmény garantálásának második módszere az, hogy a rendelési specifikációt egyedivé tesszük egy tiebreaker hozzáadásával. Például a példánkban megadhatjuk a orderid, DESC-t, mint tiebreaker-t. Ez azt jelenti, hogy a sorrendi dátumértékek egyenlősége esetén a magasabb sorrendazonosító értékkel rendelkező sort előnyben részesítjük az alacsonyabb sorrendazonosító értékkel rendelkező sorral szemben. Íme a lekérdezésünk a tiebreaker alkalmazásával:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESC;
Ez a lekérdezés a következő kimenetet generálja:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
Az egyedi sorrend alkalmazása determinisztikussá teszi mind a sorok kiválasztását, mind a prezentáció sorrendjét. Az eredményhalmaz, valamint a sorok prezentációs sorrendje garantáltan megismételhető mindaddig, amíg a mögöttes adatok nem változnak.
Ha olyan esetünk van, amikor bizonyos számú sort kell szűrnünk, de valóban nem érdekel a sorrend, akkor jó ötlet lehet az ORDER BY (SELECT NULL) megadása, például így:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL);
Így mindenki tudtára adjuk, hogy az önkényes sorrend kiválasztása szándékos, ami segít elkerülni a félreértéseket és kétségeket.
Az 1. fejezetben, a “Logikai lekérdezés feldolgozása” című fejezetben a TOP és OFFSET-FETCH szűrőkről elmondottakra emlékeztetve, a bemutatási sorrend csak akkor garantált, ha a külső lekérdezésben van ORDER BY záradék. Például a következő lekérdezés bemutatásában a sorrendiség nem garantált:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS D;
A bemutatási sorrendiség garantálásához a külső lekérdezésben meg kell adnia egy ORDER BY záradékot, például így:
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;
Az OFFSET-FETCH szűrő
Az OFFSET-FETCH szűrő a TOP-hoz hasonlóan, de egy extra elemmel tervezett szabványos funkció. Megadhatjuk, hogy hány sort szeretnénk kihagyni, mielőtt megadnánk, hogy hány sort szeretnénk szűrni.
Amint azt már sejthettük, ez a funkció hasznos lehet a lapozási megoldások megvalósításánál – azaz az eredményt kérésre darabonként adja vissza a felhasználónak, amikor a teljes eredményhalmaz túl hosszú ahhoz, hogy egy képernyőre vagy weboldalra beférjen.
Az OFFSET-FETCH szűrőnek szüksége van egy ORDER BY záradék meglétére, és közvetlenül utána kell megadni. Az OFFSET záradékban először azt kell megadni, hogy hány sort kell kihagyni, majd a FETCH záradékban azt, hogy hány sort kell szűrni. Például a megadott sorrend alapján a következő lekérdezés az első 50 sort kihagyja, a következő 25 sort pedig megszűri:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
Más szóval a lekérdezés az 51-től 75-ig terjedő sorokat szűri. A lapozás szempontjából, 25 soros oldalméretet feltételezve, ez a lekérdezés a harmadik oldalt adja vissza.
A természetes kijelentő nyelvezet érdekében a FIRST kulcsszót használhatja a NEXT helyett, ha szeretné, bár a jelentése ugyanaz. A FIRST használata intuitívabb lehet, ha nem hagyunk ki sorokat. Még ha nem is akarunk sorokat kihagyni, a T-SQL akkor is kötelezővé teszi az OFFSET záradék megadását (0 ROWS értékkel), hogy elkerüljük a parsing félreérthetőségét. Hasonlóképpen, a ROWS kulcsszó többes számú alakja helyett a ROW egyes számú alakját használhatja mind az OFFSET, mind a FETCH záradékban. Ez természetesebb, ha csak egy sort kell kihagyni vagy szűrni.
Ha kíváncsi, hogy mi a célja az ONLY kulcsszónak, akkor azt jelenti, hogy ne vegyük fel a kötéseket. A szabványos SQL definiálja a WITH TIES alternatívát; a T-SQL azonban még nem támogatja. Hasonlóképpen a standard SQL definiálja a PERCENT lehetőséget, de a T-SQL ezt sem támogatja még. Ez a két hiányzó opció a TOP szűrővel elérhető.
Amint említettük, az OFFSET-FETCH szűrőhöz ORDER BY záradék szükséges. Ha tetszőleges sorrendet szeretnénk használni, például TOP-ot ORDER BY záradék nélkül, akkor az ORDER BY (SELECT NULL) trükköt használhatjuk, például így:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL)OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
A FETCH záradék opcionális. Ha bizonyos számú sort szeretnénk kihagyni, de nem korlátozzuk, hogy hány sort adjunk vissza, egyszerűen ne adjunk meg FETCH záradékot. Például a következő lekérdezés 50 sort hagy ki, de nem korlátozza a visszaadott sorok számát:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS;
A megjelenítési sorrend tekintetében a viselkedés ugyanaz, mint a TOP szűrő esetében; azaz az OFFSET-FETCH esetén is csak akkor garantált a megjelenítési sorrend, ha a legkülső lekérdezés ORDER BY záradékkal rendelkezik.