Klassiset SQL:n suodattimet, kuten ON, WHERE ja HAVING, perustuvat predikaatteihin. TOP ja OFFSET-FETCH ovat suodattimia, jotka perustuvat eri käsitteeseen: ilmoitat järjestyksen ja kuinka monta riviä suodatetaan tämän järjestyksen perusteella. Monet suodatustehtävät määritellään järjestyksen ja vaaditun rivimäärän perusteella. On varmasti hyvä, että T-SQL:ssä on kielituki, jonka avulla voit muotoilla pyynnön tavalla, joka vastaa tapaa, jolla ajattelet tehtävää.
Tässä luvussa aloitetaan suodattimien loogisesta suunnittelusta. Sen jälkeen siinä käytetään sivutusskenaariota niiden optimoinnin havainnollistamiseen. Luvussa käsitellään myös TOPin käyttöä muutoslausekkeiden kanssa. Lopuksi luvussa demonstroidaan TOP- ja OFFSET-FETCH-suodattimien käyttöä käytännön ongelmien, kuten top N per ryhmä ja mediaani, ratkaisemisessa.
Top- ja OFFSET-FETCH-suodattimet
Top- ja OFFSET-FETCH-suodattimien avulla voit toteuttaa suodatusvaatimukset kyselyissäsi intuitiivisella tavalla. TOP-suodatin on T-SQL:n oma ominaisuus, kun taas OFFSET-FETCH-suodatin on vakio-ominaisuus. T-SQL alkoi tukea OFFSET-FETCH-suodatinta Microsoft SQL Server 2012:ssa. SQL Server 2014:stä lähtien T-SQL:n OFFSET-FETCH-toteutuksesta puuttuu edelleen pari vakioelementtiä – mielenkiintoista kyllä, sellaisia, jotka ovat käytettävissä TOP-suodattimella. Nykyisessä toteutuksessa kummallakin suodattimella on ominaisuuksia, joita toinen suodatin ei tue.
Aloitan kuvaamalla TOP:n loogiset suunnittelunäkökohdat ja käsittelen sitten OFFSET-FETCH:n näkökohtia.
TOP-suodatin
TOP-suodatin on yleisesti käytetty konstruktio T-SQL:ssä. Sen suosio johtuu luultavasti siitä, että sen rakenne on niin hyvin linjassa sen kanssa, miten monet suodatusvaatimukset ilmaistaan – esimerkiksi ”Palauta kolme viimeisintä tilausta”. Tässä pyynnössä suodattimen järjestys perustuu järjestyspäivään (orderdate), aleneva, ja niiden rivien määrä, jotka haluat suodattaa tämän järjestyksen perusteella, on 3.
Määrität TOP-vaihtoehdon SELECT-luettelossa syöttöarvolla, joka on kirjoitettu BIGINT-muodossa ja joka osoittaa, kuinka monta riviä haluat suodattaa. Annat järjestysmäärittelyn klassisessa ORDER BY -lausekkeessa. Käytät esimerkiksi seuraavaa kyselyä saadaksesi kolme viimeisintä tilausta.
USE TSQLV3;SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Sain tästä kyselystä seuraavan tulosteen:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
Suodatettavien rivien lukumäärän määrittämisen sijasta voit käyttää TOP-vaihtoehtoa, jolla määritetään prosenttiosuus (kyselyn tuloksen rivien kokonaismäärästä). Tämä tapahtuu antamalla arvo alueella 0-100 (kirjoitetaan muodossa FLOAT) ja lisäämällä PERCENT-avainsana. Esimerkiksi seuraavassa kyselyssä pyydät suodattamaan yhden prosentin riveistä:
SELECT TOP (1) PERCENT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
SQL Server pyöristää syötetyn prosentin perusteella lasketun rivien määrän. Esimerkiksi Tilaukset-taulukon 830 riviin sovelletun yhden prosentin tulos on 8,3. Pyöristämällä tätä lukua ylöspäin saadaan 9. Tässä on tuloste, jonka sain tälle kyselylle:
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
Huomaa, että syöttöprosentin muuntamiseksi rivien lukumääräksi SQL Serverin on ensin selvitettävä kyselyn tuloksen rivien lukumäärä, ja tämä vaatii yleensä ylimääräistä työtä.
Huomionarvoista on, että TOP-suodattimelle järjestysmäärittely on valinnainen. Tarkastellaan esimerkiksi seuraavaa kyselyä:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.Orders;
Sain tästä kyselystä seuraavan tuloksen:
orderid orderdate custid empid----------- ---------- ----------- -----------10248 2013-07-04 85 510249 2013-07-05 79 610250 2013-07-08 34 4
Valinta siitä, mitkä kolme riviä palautetaan, on epämääräinen. Tämä tarkoittaa, että jos suoritat kyselyn uudelleen ilman, että taustalla olevat tiedot muuttuvat, voit teoriassa saada eri kolmen rivin joukon. Käytännössä rivien valinta riippuu fyysisistä olosuhteista, kuten optimointivalinnoista, tallennuskoneen valinnoista, tietojen asettelusta ja muista tekijöistä. Jos suoritat kyselyn useita kertoja, niin kauan kuin nämä fyysiset olosuhteet eivät muutu, on todennäköistä, että saat samat tulokset. On kuitenkin ratkaisevan tärkeää ymmärtää relaatiomallin ”fyysisen datan riippumattomuuden” periaate ja muistaa, että loogisella tasolla sinulla ei ole takeita toistettavista tuloksista. Ilman järjestysmäärittelyä järjestystä on pidettävä mielivaltaisena, mikä johtaa epädeterministiseen rivivalintaan.
Silloinkin kun järjestysmäärittely annetaan, se ei tarkoita, että kysely on deterministinen. Esimerkiksi aiemmassa TOP-kyselyssä käytettiin järjestysmäärittelynä orderdate, DESC. Orderdate-sarake ei ole yksikäsitteinen, joten valinta saman tilauspäivämäärän omaavien rivien välillä on epädeterministinen. Mitä siis tehdään tapauksissa, joissa determinismi on taattava? Vaihtoehtoja on kaksi: käyttää WITH TIES -vaihtoehtoa tai uniikkia järjestystä.
WITH TIES -vaihtoehto aiheuttaa sen, että siteet sisällytetään tulokseen. Näin sitä sovelletaan esimerkissämme:
SELECT TOP (3) WITH TIES orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Tässä on tulos, jonka sain tästä kyselystä:
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-palvelin suodattaa kolme riviä, joilla on viimeisimmät tilauspäivämäärät, ja lisäksi se sisällyttää kaikki muut rivit, joilla on sama tilauspäivämäärä kuin viimeisellä rivillä. Tämän seurauksena voit saada enemmän rivejä kuin määrittelemäsi määrä. Tässä kyselyssä määrittelit, että haluat suodattaa kolme riviä, mutta sait lopulta neljä riviä. Mielenkiintoista tässä on se, että rivivalinta on nyt deterministinen, mutta esitysjärjestys sellaisten rivien välillä, joilla on sama tilauspäivämäärä, ei ole deterministinen.
Toinen tapa taata deterministinen tulos on tehdä järjestysmäärittelystä ainutkertainen lisäämällä tiebreaker. Voit esimerkiksi lisätä esimerkissämme tiebreakeriksi orderid, DESC. Tämä tarkoittaa sitä, että jos järjestyspäivämäärän arvoissa on tasapeli, rivi, jolla on korkeampi järjestystunnuksen arvo, on parempi kuin rivi, jolla on matalampi arvo. Tässä on kyselymme, johon on sovellettu tiebreakeriä:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESC;
Tämä kysely tuottaa seuraavan tulosteen:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
Yksilöllisen järjestyksen käyttö tekee sekä rivien valinnasta että esitysjärjestyksestä determinististä. Tulosjoukko sekä rivien esitysjärjestys ovat taatusti toistettavissa niin kauan kuin taustalla olevat tiedot eivät muutu.
Jos sinulla on tapaus, jossa sinun täytyy suodattaa tietty määrä rivejä, mutta et todellakaan välitä järjestyksestä, voi olla hyvä ajatus määrittää ORDER BY (SELECT NULL), esimerkiksi näin:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL);
Tällöin annat kaikkien tietää, että valintasi mielivaltaisesta järjestyksestä on tarkoituksellinen, mikä auttaa välttämään sekaannuksia ja epäilyjä.
Muistutuksena siitä, mitä selitin luvussa 1, ”Looginen kyselyjen käsittely”, TOP- ja OFFSET-FETCH-suodattimista, esitysjärjestys on taattu vain, jos ulommassa kyselyssä on ORDER BY -lauseke. Esimerkiksi seuraavassa kyselyn esitystavassa järjestystä ei ole taattu:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS D;
Taataksesi esitystavan järjestyksen, sinun on määritettävä ORDER BY -lauseke ulommassa kyselyssä, esimerkiksi näin:
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;
OFFSET-FETCH-suodatin
OFFSET-FETCH-suodatin on vakio-ominaisuus, joka on suunniteltu samankaltaiseksi kuin TOP-suodatin, mutta siinä on yksi lisäelementti. Voit määrittää, kuinka monta riviä haluat ohittaa, ennen kuin määrität, kuinka monta riviä haluat suodattaa.
Kuten olisit voinut arvata, tämä ominaisuus voi olla kätevä sivutusratkaisujen toteuttamisessa – eli tuloksen palauttamisessa käyttäjälle pyydettäessä pala kerrallaan, kun koko tulosjoukko on liian pitkä mahtuakseen yhdelle ruudulle tai verkkosivulle.
OFFSET-FETCH-suodatin edellyttää ORDER BY-lausekkeen olemassaoloa, ja se määritetään heti ORDER BY-lausekkeen jälkeen. Aluksi ilmoitetaan OFFSET-lausekkeessa, kuinka monta riviä ohitetaan, ja sen jälkeen FETCH-lausekkeessa, kuinka monta riviä suodatetaan. Esimerkiksi ilmoitettuun järjestykseen perustuva seuraava kysely ohittaa ensimmäiset 50 riviä ja suodattaa seuraavat 25 riviä:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
Toisin sanoen kysely suodattaa rivit 51-75. Jos sivun koko on 25 riviä, tämä kysely palauttaa kolmannen sivun.
Luonnollisen deklaratiivisen kielen sallimiseksi voit halutessasi käyttää avainsanaa FIRST NEXT sijasta, vaikka merkitys onkin sama. FIRSTin käyttäminen voi olla intuitiivisempaa, jos et ohita yhtään riviä. Vaikka et haluaisikaan ohittaa yhtään riviä, T-SQL tekee silti pakolliseksi OFFSET-lausekkeen (jossa on 0 ROWS) määrittelyn, jotta vältytään moniselitteiseltä jäsennykseltä. Vastaavasti avainsanan ROWS monikkomuodon sijasta voit käyttää yksikkömuotoa ROW sekä OFFSET- että FETCH-lausekkeissa. Tämä on luontevampaa, jos haluat ohittaa tai suodattaa vain yhden rivin.
Jos olet utelias, mikä on avainsanan ONLY tarkoitus, se tarkoittaa, ettei sidoksia oteta mukaan. Standard SQL määrittelee vaihtoehdon WITH TIES; T-SQL ei kuitenkaan vielä tue sitä. Samoin standardi-SQL määrittelee vaihtoehdon PERCENT, mutta T-SQL ei tue sitäkään vielä. Nämä kaksi puuttuvaa vaihtoehtoa ovat käytettävissä TOP-suodattimen kanssa.
Kuten mainittiin, OFFSET-FETCH-suodatin vaatii ORDER BY -lausekkeen. Jos haluat käyttää mielivaltaista järjestystä, kuten TOP ilman ORDER BY -lauseketta, voit käyttää temppua ORDER BY:n kanssa (SELECT NULL), esimerkiksi näin:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL)OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
FETCH-lauseke on valinnainen. Jos haluat ohittaa tietyn määrän rivejä, mutta et rajoita palautettavien rivien määrää, älä yksinkertaisesti ilmoita FETCH-lauseketta. Esimerkiksi seuraava kysely ohittaa 50 riviä, mutta ei rajoita palautettavien rivien määrää:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS;
Käyttäytyminen esitysjärjestyksen suhteen on sama kuin TOP-suodattimella; nimittäin myös OFFSET-FETCH-lausekkeella esitysjärjestys taataan vain, jos uloimmassa kyselyssä on ORDER BY -lauseke.