Numerowanie rekordów zwracanych z SQL Server
Tworząc różnego rodzaju rankingi lub zestawienia najczęściej warto ponumerować rekordy. Wybierając najlepsze trzy wyniki warto obok nich umieścić cyfry 1, 2 i 3. Takie przedstawienie danych jest po prostu bardziej czytelne. Popatrzmy na przykład skryptu SQL, który zwraca pierwsze trzy rekordy z pewnej tabeli reprezentującej zarobki w firmie:
CREATE TABLE Zarobki
(
Stanowisko int,
Kwota decimal
)
INSERT INTO Zarobki VALUES (1, 2000)
INSERT INTO Zarobki VALUES (1, 1200)
INSERT INTO Zarobki VALUES (1, 1100)
INSERT INTO Zarobki VALUES (1, 1500)
INSERT INTO Zarobki VALUES (1, 1050)
--zapytanie właściwe
SELECT TOP 3 Kwota FROM Zarobki ORDER BY Kwota DESC
Wynikiem działania powyższego skryptu będzie następujący zestaw danych:
| Kwota |
| 2000 |
| 1800 |
| 1500 |
Numerowanie wierszy
Pójdźmy teraz dalej i wstawmy dodatkową kolumnę, która będzie określała pozycję w rankingu zarobków. Do numerowania wierszy w SQL Server służy funkcja ROW_NUMBER. Przykładowy fragment skryptu SQL mógłby wyglądać następująco:
Tym razem wynikiem zapytania będą trzy rekordy z dwoma kolumnami:
| Pozycja | Kwota |
| 1 | 2000 |
| 2 | 1800 |
| 3 | 1500 |
A gdy nie zależy mi na sortowaniu?
Funkcja ROW_NUMBER (a także inne funkcje okna) wymagają frazy ORDER BY. Wiadomo też, że operacje sortowania, jeżeli brakuje nam odpowiedniego indeksu, mogą być bardzo kosztowne. Jak zatem tego sortowania uniknąć? Nie da się pominąć słów kluczowych ORDER BY, ale da się je trochę oszukać. Przyjrzyjmy się następującemu listingowi:
FROM Zarobki
Optymalizator zapytań jest na tyle sprytny, że całkowicie pomija operację sortowania. Do zbioru wynikowego brane są trzy rekordy odczytane w pierwszej kolejności i im przydzielane są kolejne numery. Przykładowy rezultat pokazany jest poniżej. Warto wiedzieć, że rezultata takiego zapytania jest niedeterministyczny. Nie ma gwarancji, że zawsze taki otrzymamy.
| Pozycja | Kwota |
| 1 | 2000 |
| 2 | 1200 |
| 3 | 1100 |
A jeżeli numerujemy pozycje w rankingu?
Wtedy można posłużyć się podobną funkcją - RANK. Przykład użycia funckji RANK pokazany jest poniżej:
Zbiór wynikowy będzie następujący:
| Pozycja | Kwota |
| 1 | 2000 |
| 2 | 1800 |
| 3 | 1500 |
Okazuje się, że wynik jest dokładnie taki sam jak ten, który został wygenerowany przez instrukcję ROW_NUMBER. Czy zatem te dwie funkcje są identyczne? Nie są!
Różnice między funkcją ROW_NUMBER, a funkcją RANK
Zanim wyjaśnię na czym polega różnica, posłużę się przykładem. Dodajmy do tabeli utworzonej na początku artykułu jeszcze jeden rekord:
Teraz już łatwa droga do pokazania różnicy między funkcją ROW_NUMBER, a funkcją RANK. Przy okazji rozpracowywania tych dwóch funkcji dorzucę jeszcze jedną - DENSE_RANK. Popatrzmy na następujące zapytanie:
RANK() OVER (ORDER BY Kwota DESC) [RANK],
DENSE_RANK() OVER (ORDER BY Kwota DESC) [DENSE_RANK],
ROW_NUMBER() OVER (ORDER BY Kwota DESC) [ROW_NUMBER],
Kwota
FROM Zarobki
A teraz rezultat:
| RANK | DENSE_RANK | ROW_NUMBER | Kwota |
| 1 | 1 | 1 | 2000 |
| 2 | 2 | 2 | 1800 |
| 2 | 2 | 3 | 1800 |
| 4 | 3 | 4 | 1500 |
Popatrzmy teraz na różnice. ROW_NUMBER zwraca numer rekordu, więc zawsze będą to kolejne liczny naturalne. RANK zwraca pozycję w rankingu. Jeżeli dwa rekordy są identyczne, wtedy pozycja będzie ta sama. Zauważmy, że żaden rekord nie ma numeru 3. To dlatego, że w tym przypadku pozycja trzecia jest na równi z pozycją drugą (ex aequo srebrny medal, brązowego nie przyznano). DANSE_RANK działa podobnie do RANK, z tym, że nie pomija żadnej liczby naturalnej. Mamy zatem pierwsze miejsce, dwa drugie miejsca i jedno trzecie.
Wnioski
Te trzy różne funkcje wystarczają do zrealizowania większości zapytań numerujących dane. To, którą użyjemy, będzie zależało od konkretnego przypadku i konkretnych wymagań. Reasumując warto zaznaczyć, że zastosowanie tych funkcji zdecydowanie wykracza poza przedstawione tutaj przykłady. ROW_NUMBER jest na przykład powszechnie stosowane do stronicowania (paginacji) danych. O tym napiszę jednak w oddzielnym artykule. Zapraszam do dzielenia się spostrzeżeniami i zgłaszaniu niejasności w komentarzach.
Kategoria:Funkcje oknaSQL Server
Komentarze: