Klassiske filtre i SQL som ON, WHERE og HAVING er baseret på prædikater. TOP og OFFSET-FETCH er filtre, der er baseret på et andet koncept: Du angiver rækkefølge og hvor mange rækker, der skal filtreres på baggrund af denne rækkefølge. Mange filtreringsopgaver er defineret på grundlag af rækkefølge og et krævet antal rækker. Det er bestemt godt at have sprogstøtte i T-SQL, der giver dig mulighed for at formulere anmodningen på en måde, der svarer til den måde, du tænker på opgaven.
Dette kapitel starter med de logiske designaspekter af filtrene. Derefter bruger det et scenarie med personsøgning til at demonstrere deres optimering. Kapitlet dækker også brugen af TOP med ændringsangivelser. Endelig demonstrerer kapitlet brugen af TOP og OFFSET-FETCH i løsningen af praktiske problemer som top N pr. gruppe og median.
TOP- og OFFSET-FETCH-filtre
Du bruger TOP- og OFFSET-FETCH-filtre til at implementere filtreringskrav i dine forespørgsler på en intuitiv måde. TOP-filteret er en proprietær funktion i T-SQL, mens OFFSET-FETCH-filteret er en standardfunktion. T-SQL begyndte at understøtte OFFSET-FETCH med Microsoft SQL Server 2012. Fra og med SQL Server 2014 mangler implementeringen af OFFSET-FETCH i T-SQL stadig et par standardelementer – interessant nok nogle, som er tilgængelige med TOP. Med den nuværende implementering har hvert af filtrene funktioner, som ikke understøttes af det andet.
Jeg vil starte med at beskrive de logiske konstruktionsaspekter af TOP og derefter dække dem af OFFSET-FETCH.
TOP-filteret
TOP-filteret er en almindeligt anvendt konstruktion i T-SQL. Dets popularitet kan sandsynligvis tilskrives det faktum, at dets design er så godt tilpasset den måde, hvorpå mange filtreringskrav udtrykkes – f.eks. “Returner de tre seneste ordrer”. I denne anmodning er rækkefølgen for filteret baseret på orderdate, faldende, og antallet af rækker, du ønsker at filtrere baseret på denne rækkefølge, er 3.
Du angiver TOP-indstillingen i SELECT-listen med en indtastningsværdi indtastet som BIGINT, der angiver, hvor mange rækker du ønsker at filtrere. Du angiver rækkefølgespecifikationen i den klassiske ORDER BY-klausul. Du bruger f.eks. følgende forespørgsel for at få de tre seneste ordrer:
USE TSQLV3;SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Jeg fik følgende output fra denne forespørgsel:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
I stedet for at angive antallet af rækker, du vil filtrere, kan du bruge TOP til at angive procentdelen (af det samlede antal rækker i forespørgselsresultatet). Det gør du ved at angive en værdi i intervallet 0 til 100 (indtastet som FLOAT) og tilføje nøgleordet PERCENT. I følgende forespørgsel anmoder du f.eks. om at filtrere en procent af rækkerne:
SELECT TOP (1) PERCENT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
SQL Server runder antallet af rækker opad, der er beregnet på grundlag af den indtastede procentdel. F.eks. er resultatet af 1 procent anvendt på 830 rækker i tabellen Ordrer 8,3. Hvis du runder dette tal opad, får du 9. Her er det output, jeg fik for denne forespørgsel:
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
Bemærk, at for at oversætte inputprocenten til et antal rækker skal SQL Server først finde ud af antallet af rækker i forespørgselsresultatet, og det kræver normalt ekstra arbejde.
Interessant nok er specifikation af rækkefølge valgfri for TOP-filteret. Overvej f.eks. følgende forespørgsel:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.Orders;
Jeg fik følgende output fra denne forespørgsel:
orderid orderdate custid empid----------- ---------- ----------- -----------10248 2013-07-04 85 510249 2013-07-05 79 610250 2013-07-08 34 4
Vælgelsen af, hvilke tre rækker der skal returneres, er ikke-deterministisk. Det betyder, at hvis du kører forespørgslen igen, uden at de underliggende data ændres, kan du teoretisk set få et andet sæt af tre rækker. I praksis vil rækkevalget afhænge af fysiske forhold som f.eks. optimeringsvalg, valg af lagringsmotor, datalayout og andre faktorer. Hvis du rent faktisk kører forespørgslen flere gange, er der en vis sandsynlighed for, at du vil få de samme resultater, så længe disse fysiske betingelser ikke ændres. Men det er afgørende at forstå princippet om “fysisk datauafhængighed” fra den relationelle model og huske på, at du på det logiske niveau ikke har en garanti for gentagelige resultater. Uden specifikation af rækkefølgen skal du betragte rækkefølgen som værende arbitrær, hvilket resulterer i et ikke-deterministisk rækkevalg.
Selv når du giver specifikation af rækkefølgen, betyder det ikke, at forespørgslen er deterministisk. I en tidligere TOP-forespørgsel blev der f.eks. anvendt orderdate, DESC som rækkefølgespecifikation. Kolonnen orderdate er ikke entydig; derfor er udvælgelsen mellem rækker med samme ordredato ikke deterministisk. Hvad gør man så i de tilfælde, hvor man skal garantere determinisme? Der er to muligheder: man kan bruge WITH TIES eller unique ordering.
Med WITH TIES-indstillingen medtages bindinger i resultatet. Sådan anvender du den på vores eksempel:
SELECT TOP (3) WITH TIES orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Her er det resultat, jeg fik fra denne forespørgsel:
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 filtrerer de tre rækker med de seneste bestillingsdatoer, plus at den medtager alle andre rækker, der har samme bestillingsdato som i den sidste række. Som følge heraf kan du få flere rækker end det antal, du har angivet. I denne forespørgsel angav du, at du ønskede at filtrere tre rækker, men endte med at få fire. Det interessante her er, at rækkevalget nu er deterministisk, men at præsentationsrækkefølgen mellem rækker med samme ordredato er ikke-deterministisk.
Den anden metode til at garantere et deterministisk resultat er at gøre rækkefølgespecifikationen entydig ved at tilføje en tiebreaker. Du kan f.eks. tilføje orderid, DESC som tiebreaker i vores eksempel. Det betyder, at i tilfælde af uafgjorthed i ordredatoværdierne foretrækkes en række med en højere ordredid-værdi frem for en række med en lavere værdi. Her er vores forespørgsel med tiebreaker anvendt:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESC;
Denne forespørgsel genererer følgende output:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
Brug af unik rækkefølge gør både rækkevalg og præsentationsrækkefølge deterministisk. Resultatmængden samt præsentationsrækkefølgen for rækkerne kan garanteret gentages, så længe de underliggende data ikke ændres.
Hvis du har et tilfælde, hvor du har brug for at filtrere et bestemt antal rækker, men virkelig er ligeglad med rækkefølgen, kan det være en god idé at angive ORDER BY (SELECT NULL) på følgende måde:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL);
På denne måde lader du alle vide, at dit valg af vilkårlig rækkefølge er tilsigtet, hvilket er med til at undgå forvirring og tvivl.
Som en påmindelse om det, jeg forklarede i kapitel 1, “Logisk forespørgselsbehandling”, om TOP- og OFFSET-FETCH-filtrene, er præsentationsrækkefølgen kun garanteret, hvis den ydre forespørgsel har en ORDER BY-klausul. I følgende forespørgselspræsentation er rækkefølgen f.eks. ikke garanteret:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS D;
For at give en garanti for præsentationsrækkefølge skal du angive en ORDER BY-klausul i den ydre forespørgsel, således:
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;
Filtret OFFSET-FETCH
Filtret OFFSET-FETCH er en standardfunktion, der er udformet på samme måde som TOP, men med et ekstra element. Du kan angive, hvor mange rækker du vil springe over, før du angiver, hvor mange rækker du vil filtrere.
Som du måske har gættet, kan denne funktion være praktisk ved implementering af paging-løsninger – dvs. ved at returnere et resultat til brugeren én del ad gangen efter anmodning, når det fulde resultatsæt er for langt til at passe på én skærm eller webside.
Filtret OFFSET-FETCH kræver, at der findes en ORDER BY-klausul, og det angives lige efter den. Du starter med at angive, hvor mange rækker der skal springes over i en OFFSET-klausul, efterfulgt af, hvor mange rækker der skal filtreres i en FETCH-klausul. Baseret på den angivne rækkefølge springer følgende forespørgsel f.eks. de første 50 rækker over og filtrerer de næste 25 rækker:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
Med andre ord filtrerer forespørgslen rækker 51 til 75. Hvis man antager en sidestørrelse på 25 rækker, returnerer denne forespørgsel den tredje side.
For at muliggøre et naturligt deklarativt sprog kan du bruge nøgleordet FIRST i stedet for NEXT, hvis du ønsker det, selv om betydningen er den samme. Det kan være mere intuitivt at bruge FIRST, hvis du ikke springer nogen rækker over. Selv hvis du ikke ønsker at springe nogen rækker over, gør T-SQL det stadig obligatorisk at angive OFFSET-klausulen (med 0 ROWS) for at undgå tvetydighed i analysen. På samme måde kan du i stedet for at bruge flertalsformen af nøgleordet ROWS bruge entalsformen ROW i både OFFSET- og FETCH-klausulerne. Dette er mere naturligt, hvis du kun skal springe over eller filtrere én række.
Hvis du er nysgerrig efter at vide, hvad formålet med nøgleordet ONLY er, betyder det, at der ikke skal medtages bindinger. Standard SQL definerer alternativet WITH TIES; T-SQL understøtter det dog ikke endnu. På samme måde definerer standard SQL alternativet PERCENT, men T-SQL understøtter det heller ikke endnu. Disse to manglende muligheder er tilgængelige med TOP-filteret.
Som nævnt kræver OFFSET-FETCH-filteret en ORDER BY-klausul. Hvis du ønsker at bruge vilkårlig rækkefølge, som TOP uden en ORDER BY-klausul, kan du bruge tricket med ORDER BY (SELECT NULL), på følgende måde:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL)OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
FETCH-klausulen er valgfri. Hvis du ønsker at springe et bestemt antal rækker over, men ikke begrænse, hvor mange rækker der skal returneres, skal du blot ikke angive en FETCH-klausul. Følgende forespørgsel springer f.eks. 50 rækker over, men begrænser ikke antallet af returnerede rækker:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS;
Med hensyn til præsentationsrækkefølge er opførslen den samme som med TOP-filteret; med OFFSET-FETCH er præsentationsrækkefølgen nemlig også kun garanteret, hvis den yderste forespørgsel har en ORDER BY-klausul.