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
To samo pytanie co wyżej. Mam za zadanie dodać kolumnę do istniejącej tabeli łącząc obie inne kolumny ze sobą, ale nie mam pojęcia jak za to się zabrać
działa :) tylko była literówka :)
Podziękował. Trochę późno, po 8 latach, ale dzięki za testy (rozumiem że dla SQL2012 robione). Tak się właśnie zastanawiałem ile złego czynię stosując czasem __(max).
Super robota, korzystając z innych internetowych kalkulatorow po prostu wątpiłem w ich prawdomówność, w końcu trafiłem tutaj i wynik w końcu jest wiarygodny. 40 km w 2 h 810 kcal, ciekawostka: na fitatu wyliczyło mi 5700 kcal 😊 najlepiej będzie chyba jak kupię zegarek sportowy.
Wielkie dzieki za solidne wyjasnienia tematu.