Spis treści:

Kategoria:OracleSQL Server


Pierwszy rekord spełniający warunki w SQL

Ułatwiamy pracę komputerom

Jedną z zasad tworzenia dobrego i bezbłędnego oprogramowania jest takie pisanie metod, funkcji, skryptów i procedur, aby były one optymalne. Może się to wydać dziwne, ale nie jest to pozbawione sensu. Źle napisana metoda jest potem wielokrotnie poprawiana, a każda taka zmiana, w większych zespółach najczęściej przez zupełnie inną osobę, może powodować błędy. W dużych systemach zależności pomiędzy poszczególnymi komponentami nie są tak oczywiste i zmiana w jednym miejscu może ujawniać się nieprawidłowościami w zupełnie innym. Lepiej od razu zadbać o dobry i optymalny kod. Jedną z lepszych technik jest ograniczanie liczby rekordów pobieranych z bazy.

Skoro wystarcza nam jeden rekord - po co pobierać wszystkie?

SQL Server i możliwości

SQL Server, jak zwykle, posiada kilka konstrukcji składniowych do realizacji tego samego celu. Przedstawię trzy z nich.

Instrukcja TOP N

Instrukcja stara, ale bardzo wygodna. Przyjrzyjmy się prostemu zapytaniu pokazanemu na poniższym listinu:

--Argument TOP jako stała
SELECT TOP 1 name
FROM sys.types

--Argument TOP jako zmienna
DECLARE @N int = 1;
SELECT TOP (@N) name
FROM sys.types

Składnia TOP umożliwia oczywiście pobranie dowolnej liczby pierwszych rekordów według jakiegoś kryterium.

Instrukcja TOP N bez określenia porządku może zwracać różne wyniki, samo określenie porządku również nie gwarantuje powtarzalności. W praktyce zależy to od indeksu użytego przez SQL Server i fizycznego ułożenia rekordów (kluczy) w wykorzystywanym indeksie. Dopiero uporządkowanie po unikalnej wartości daje nam gwarancję powtarzalności. Jeżeli trzy rekordy mają taką samą wartość jakiegoś pola i zechcemy wybrać tylko jeden rekord dla takiej nazwy to który będzie zwrócony? Nie ma pewności i o tym trzeba pamiętać.

W przypadku widoku systemowego można wybrać pierwsze trzy typy - w kolejności alfabetycznej. Nazwa typu nie może się powtarzać, więc mamy gwarancję powtarzalności.

SELECT TOP 3 name
FROM sys.types
ORDER BY name

Otrzymamy trzy rekordy:

name
bigint
binary
bit

Instrukcja SET ROWCOUNT

Instrukcja SET ROWCOUNT działa podobnie do instrukcji TOP N, ale istnieją pewne drobne różnice. Przyjrzyjmy się najpierw pokazanemu poniżej listingowi:

--Argument SET ROWCOUNT jako stała
SET ROWCOUNT 1
SELECT name
FROM sys.types

--Argument SET ROWCOUNT jako zmienna
DECLARE @N int = 1;
SET ROWCOUNT @N;
SELECT name
FROM sys.types

Warto zwrócić uwagę, że również istnieje mozliwość wykorzystania zmiennej liczby pierwszych rekordów, która może być wyliczana i zmieniana. Jakie są różnice?

  • Instrukcja TOP N jest częścią zapytania i nie ma wpływu na inne zapytania. Instrukcja SET ROWCOUNT działa na poziomie całego wsadu i może mieć wpływ na wiele instrukcji.
  • TOP N jest blokiem funkcyjnym w planie wykonania i może podlegać przekształceniom. SET ROWCOUNT jest realizowany na poziomie silnika bazy danych, brak bloku TOP (i innego alternatywnego) w planie wykonania.
  • TOP N jest ściśle związane z zapytaniem. SET ROWCOUNT jest oddzielną instrukcją, która pozwala ograniczyć liczbę zwracanych rekordów bez ingerencji w treść oryginalnego zapytania.

Uzupełniając informację o zakresie instrukcji SET ROWCOUNT przyjrzyjmy się następującemu wsadowi:

--Instrukcja obejmuje cały wsad
SET ROWCOUNT 1
SELECT name
FROM sys.types
SELECT name
FROM sys.tables

W powyższym przypadku obie instrukcje zwrócą po jednym rekordzie. To właśnie wada lub zaleta, w zależności od przypadku, instrukcji SET ROWCOUNT.

Instrukcja OFFSET FETCH

Instrukcja OFFSET FETCH nie jest szczególnie prosta, ale należałoby ją tutaj wstawić, bo pełni podobną funkcję. W SQL Server 2012 sekcja ORDER BY została rozszerzona i pojawiły się w niej dodatkowe instrukcje - OFFSET i FETCH. Pierwsza z nich określa, ile rekordów należy pominąć, druga zaś, ile z tych rekordów należy pobrać. Instrukcja znakomicie nadaje się do stronicowania wyników - jeżeli chcemy pierwszą stronę, pobieramy N rezultatów (OFFSET 0, FETCH N), jeżeli chcemy drugą stronę, przeskakujemy N i pobieramy N (OFFSET N, FETCH N), dla kolejnej przeskakujemy 2N i pobieramy N. Bardzo łatwo to zaimplementować, bo i OFFSET, i FETCH mogą przyjąć zmienną. Przyjrzyjmy się, jak one działają:

--Argument OFFSET FETCH jako stała
SELECT name FROM sys.types
ORDER BY name
OFFSETROWS
FETCH FIRSTROW ONLY;

--Argument OFFSET FETCH jako zmienna
DECLARE @N int = 1;
SELECT name FROM sys.types
ORDER BY name
OFFSETROWS
FETCH FIRST @N ROW ONLY;

Instrukcja jest bardziej złożona niż dwie powyższe, ale ma też znacznie większe możliwości. Po pierwsze, wymaga posortowania wyników (co swoją drogą jest słusznym wymogiem - patrz komentarz w sprawie powtarzalności wyników), a po drugie, część FETCH nie może istnieć bez części OFFSET.

SQL Server jest na tyle sprytny, że tłumaczy powyższą instrukcję na plan wykonania identyczny z planem wykonania TOP N. Skoro otrzymujemy to samo, to lepiej skorzystać z TOP N. OFFSET FETCH przedstawiłem jako pewną mało znaną ciekawostkę.

Pierwszy rekord w Oracle

Przedstawione powyżej rozwiązania nie są na tyle ogólne, żeby mogły być stosowane we wszystkich, albo w większości baz danych. W zasadzie każda baza ma swoje rozwiązania. W oracle zamiast TOP N mamy ROWNUM:

SELECT kolumna
FROM tabela
WHERE ROWNUM <= 1;

ROWNUM jest bliski standardowej funkcji okna ROW_NUMBER() (dostępnej w SQL Server, Oracle), bo zwraca numer kolejnego wiersza. ROW_NUMBER() nie może być stosowane w sekcji WHERE - ROWNUM można tam umieścić. To taka drobna różnica, znacznie skracająca zapis. Piszę o funkcji ROW_NUMBER() dlatego, że pozwala ona napisać instrukcję bardziej ogólną. Taką, która będzie działała i na SQL Server i na Oracle.

LIMIT w MySQL

Skoro napisałem o Oracle, napiszę również o bazie MySQL. Tutaj składnia również jest prosta i... znowu inna. Tym razem ograniczenie realizowane jest przez słowo kluczowe LIMIT. Popatrzmy na poniższy listing:

SELECT kolumna
FROM tabela
LIMIT 1;

Instrukcja prosta łatwa i przyjemna. Oprócz ograniczenia liczby wierszy instrukcja LIMIT pozwala na stronicowanie rekordów. Jeżeli podamy dwie wartości, pierwsza z nich będzie oznaczała liczbę wierszy, które należy przeskoczyć, a druga liczbę wierszy, które należy pobrać:

--składnia
SELECT kolumna
FROM tabela
LIMIT przeskocz,pobierz;

--przykład
SELECT kolumna
FROM tabela
LIMIT 0,1;

Kategoria:OracleSQL Server

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
Przydałyby się jeszcze 2 rzeczy do cz. 3 i byłoby superanckie.
1. Na starcie sortuje wg jakiejś kolumny i tam jest już strzałeczka. Widok takiej strzałeczki daje znać użytkownikowi, że taką tabele można sortować, a na razie pojawia się ona tylko po kliknięciu.
2. Uwzględnienie polskich znaków, bo np. przy sortowaniu Nazwisk i Imion jest to bardzo uciążliwe.
Ogólnie bardzo fajnie i prosto.
PS. Jest ten artykuł z jQuery już dostępny.
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!
Dzieki za te informacje. były kluczowe