Klasyczne filtry w SQL, takie jak ON, WHERE i HAVING, są oparte na predykatach. TOP i OFFSET-FETCH są filtrami, które opierają się na innej koncepcji: wskazujesz kolejność i ile wierszy ma być filtrowanych w oparciu o tę kolejność. Wiele zadań filtrowania jest definiowanych w oparciu o kolejność i wymaganą liczbę wierszy. Z pewnością dobrze jest mieć wsparcie językowe w T-SQL, które pozwala sformułować żądanie w sposób, który jest podobny do sposobu, w jaki myślisz o zadaniu.
Ten rozdział rozpoczyna się od logicznych aspektów projektowania filtrów. Następnie wykorzystuje scenariusz stronicowania do zademonstrowania ich optymalizacji. W rozdziale omówiono również użycie TOP z deklaracjami modyfikującymi. Wreszcie, rozdział demonstruje użycie TOP i OFFSET-FETCH w rozwiązywaniu praktycznych problemów, takich jak top N per group i mediana.
Filtry TOP i OFFSET-FETCH
Filtrów TOP i OFFSET-FETCH używasz do implementacji wymagań filtrowania w zapytaniach w sposób intuicyjny. Filtr TOP jest zastrzeżoną funkcją w T-SQL, podczas gdy filtr OFFSET-FETCH jest funkcją standardową. T-SQL zaczął wspierać OFFSET-FETCH wraz z Microsoft SQL Server 2012. Począwszy od SQL Server 2014, implementacja OFFSET-FETCH w T-SQL nadal nie posiada kilku standardowych elementów – co ciekawe, takich, które są dostępne w TOP. Przy obecnej implementacji każdy z filtrów ma możliwości, które nie są obsługiwane przez drugi.
Zacznę od opisania logicznych aspektów konstrukcyjnych TOP, a następnie omówię aspekty OFFSET-FETCH.
Filtr TOP
Filtr TOP jest powszechnie stosowaną konstrukcją w T-SQL. Jego popularność można prawdopodobnie przypisać temu, że jego konstrukcja jest tak dobrze dopasowana do sposobu wyrażania wielu wymagań dotyczących filtrowania – na przykład „Zwróć trzy ostatnie zamówienia”. W tym zapytaniu, kolejność dla filtru jest oparta na orderdate, malejąco, a liczba wierszy, które chcesz filtrować w oparciu o tę kolejność wynosi 3.
Precyzujesz opcję TOP w liście SELECT z wartością wejściową wpisaną jako BIGINT wskazującą, ile wierszy chcesz filtrować. Podajesz specyfikację porządkowania w klasycznej klauzuli ORDER BY. Na przykład, używasz następującego zapytania, aby uzyskać trzy najnowsze zamówienia.
USE TSQLV3;SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Otrzymałem następujące dane wyjściowe z tego zapytania:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
Zamiast określać liczbę wierszy, które chcesz filtrować, możesz użyć TOP, aby określić procent (całkowitej liczby wierszy w wyniku zapytania). W tym celu należy podać wartość z zakresu od 0 do 100 (wpisaną jako FLOAT) i dodać słowo kluczowe PERCENT. Na przykład w poniższym zapytaniu żądasz przefiltrowania jednego procenta wierszy:
SELECT TOP (1) PERCENT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
SerwerSQL zaokrągla liczbę wierszy obliczoną na podstawie wprowadzonego procentu. Na przykład, wynik 1 procenta zastosowanego do 830 wierszy w tabeli Zamówienia to 8,3. Zaokrąglając tę liczbę w górę, otrzymujemy 9. Oto wynik, jaki otrzymałem dla tego zapytania:
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
Zauważ, że aby przetłumaczyć procent wejściowy na liczbę wierszy, SQL Server musi najpierw obliczyć liczbę wierszy w wyniku zapytania, a to zazwyczaj wymaga dodatkowej pracy.
Interesujące jest to, że specyfikacja zamawiania jest opcjonalna dla filtra TOP. Na przykład, rozważ następujące zapytanie:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.Orders;
Otrzymałem następujące dane wyjściowe z tego zapytania:
orderid orderdate custid empid----------- ---------- ----------- -----------10248 2013-07-04 85 510249 2013-07-05 79 610250 2013-07-08 34 4
Wybór, które trzy wiersze mają zostać zwrócone jest niedeterministyczny. Oznacza to, że jeśli uruchomisz zapytanie ponownie, bez zmiany danych bazowych, teoretycznie możesz otrzymać inny zestaw trzech wierszy. W praktyce, wybór wiersza zależy od warunków fizycznych, takich jak wybór optymalizacji, wybór silnika przechowywania danych, układ danych i inne czynniki. Jeśli faktycznie uruchomisz zapytanie wiele razy, tak długo jak te fizyczne warunki nie ulegną zmianie, istnieje pewne prawdopodobieństwo, że będziesz otrzymywał te same wyniki. Jednak krytycznym jest, aby zrozumieć zasadę „fizycznej niezależności danych” z modelu relacyjnego i pamiętać, że na poziomie logicznym nie masz gwarancji powtarzalności wyników. Bez określenia kolejności, powinieneś uznać, że kolejność jest arbitralna, co skutkuje niedeterministycznym wyborem wiersza.
Nawet gdy podasz specyfikację kolejności, nie oznacza to, że zapytanie jest deterministyczne. Na przykład, wcześniejsze zapytanie TOP użyło orderdate, DESC jako specyfikacji porządkującej. Kolumna orderdate nie jest unikalna, dlatego wybór pomiędzy wierszami z tą samą datą zamówienia jest niedeterministyczny. Więc co zrobić w przypadkach, gdy musisz zagwarantować determinizm? Istnieją dwie opcje: użycie opcji WITH TIES lub unique ordering.
Opcja WITH TIES powoduje, że więzy są uwzględniane w wyniku. Oto jak zastosować ją w naszym przykładzie:
SELECT TOP (3) WITH TIES orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Oto wynik, jaki otrzymałem z tego zapytania:
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
SerwerSQL filtruje trzy wiersze z najnowszymi datami zamówienia, a także uwzględnia wszystkie inne wiersze, które mają taką samą datę zamówienia jak w ostatnim wierszu. W wyniku tego możesz otrzymać więcej wierszy niż podana liczba. W tym zapytaniu, określiłeś, że chcesz filtrować trzy wiersze, ale ostatecznie otrzymałeś cztery. Interesujące jest to, że wybór wierszy jest teraz deterministyczny, ale kolejność prezentacji pomiędzy wierszami z tą samą datą zamówienia jest niedeterministyczna.
Drugą metodą zagwarantowania deterministycznego wyniku jest sprawienie, że specyfikacja zamówienia jest unikalna poprzez dodanie tiebreakera. Na przykład, możesz dodać orderid, DESC jako tiebreaker w naszym przykładzie. Oznacza to, że w przypadku remisów w wartościach daty zamówienia, wiersz z wyższą wartością ID zamówienia jest preferowany w stosunku do wiersza z niższą wartością. Oto nasze zapytanie z zastosowanym tiebreakerem:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESC;
Zapytanie to generuje następujące dane wyjściowe:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
Użycie unikalnego porządkowania sprawia, że zarówno wybór wierszy jak i porządkowanie prezentacji są deterministyczne. Zestaw wyników, jak również kolejność prezentacji wierszy są gwarantowane jako powtarzalne tak długo, jak długo dane bazowe się nie zmieniają.
Jeśli masz przypadek, w którym musisz przefiltrować pewną liczbę wierszy, ale naprawdę nie dbasz o kolejność, dobrym pomysłem może być określenie ORDER BY (SELECT NULL), jak poniżej:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL);
W ten sposób dajesz wszystkim znać, że twój wybór arbitralnej kolejności jest zamierzony, co pomaga uniknąć zamieszania i wątpliwości.
Przypominając to, co wyjaśniłem w rozdziale 1, „Logiczne przetwarzanie zapytań”, na temat filtrów TOP i OFFSET-FETCH, kolejność prezentacji jest gwarantowana tylko wtedy, gdy zapytanie zewnętrzne ma klauzulę ORDER BY. Na przykład, w następującej prezentacji zapytania, porządek nie jest gwarantowany:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS D;
Aby zapewnić gwarancję porządku prezentacji, musisz określić klauzulę ORDER BY w zapytaniu zewnętrznym, w taki sposób:
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;
Filtr OFFSET-FETCH
Filtr OFFSET-FETCH jest standardową funkcją zaprojektowaną podobnie do TOP, ale z dodatkowym elementem. Możesz określić, ile wierszy chcesz pominąć przed określeniem, ile wierszy chcesz przefiltrować.
Jak można się domyślić, ta funkcja może być przydatna w implementacji rozwiązań stronicowania – czyli zwracania wyniku użytkownikowi po jednym kawałku na żądanie, gdy pełny zestaw wyników jest zbyt długi, aby zmieścić się na jednym ekranie lub stronie internetowej.
Filtr OFFSET-FETCH wymaga istnienia klauzuli ORDER BY i jest określany zaraz po niej. Zaczynasz od określenia liczby wierszy do pominięcia w klauzuli OFFSET, a następnie liczby wierszy do przefiltrowania w klauzuli FETCH. Na przykład, w oparciu o wskazaną kolejność, następujące zapytanie pomija pierwsze 50 wierszy i filtruje następne 25 wierszy:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
Innymi słowy, zapytanie filtruje wiersze od 51 do 75. W kategoriach stronicowania, zakładając rozmiar strony 25 wierszy, zapytanie to zwraca trzecią stronę.
Aby umożliwić naturalny język deklaratywny, możesz użyć słowa kluczowego FIRST zamiast NEXT, jeśli chcesz, choć znaczenie jest takie samo. Użycie FIRST może być bardziej intuicyjne, jeśli nie pomijasz żadnych wierszy. Nawet jeśli nie chcesz pomijać żadnych wierszy, T-SQL nadal wymaga podania klauzuli OFFSET (z 0 ROWS), aby uniknąć niejednoznaczności w parsowaniu. Podobnie, zamiast używać liczby mnogiej słowa kluczowego ROWS, można użyć liczby pojedynczej ROW zarówno w klauzuli OFFSET, jak i FETCH. Jest to bardziej naturalne, jeśli musisz pominąć lub przefiltrować tylko jeden wiersz.
Jeśli jesteś ciekawy, jaki jest cel słowa kluczowego ONLY, oznacza ono, aby nie zawierać więzów. Standardowy SQL definiuje alternatywę WITH TIES; jednak T-SQL jeszcze jej nie obsługuje. Podobnie, standardowy SQL definiuje opcję PERCENT, ale T-SQL również jej jeszcze nie obsługuje. Te dwie brakujące opcje są dostępne z filtrem TOP.
Jak już wspomniano, filtr OFFSET-FETCH wymaga klauzuli ORDER BY. Jeśli chcesz użyć arbitralnej kolejności, jak TOP bez klauzuli ORDER BY, możesz użyć sztuczki z ORDER BY (SELECT NULL), jak poniżej:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL)OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
Klauzula FETCH jest opcjonalna. Jeśli chcesz pominąć określoną liczbę wierszy, ale nie chcesz ograniczać liczby wierszy do zwrócenia, po prostu nie wskazuj klauzuli FETCH. Na przykład, poniższe zapytanie pomija 50 wierszy, ale nie ogranicza liczby zwracanych wierszy:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS;
W odniesieniu do porządkowania prezentacji, zachowanie jest takie samo jak w przypadku filtru TOP; mianowicie, w przypadku OFFSET-FETCH również, porządkowanie prezentacji jest gwarantowane tylko wtedy, gdy zapytanie zewnętrzne posiada klauzulę ORDER BY.
.