Spis treści:

Kategoria:Funkcje oknaSQL Server


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:

--przykładowa tabela
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:

SELECT TOP 3 ROW_NUMBER() OVER (ORDER BY Kwota DESC) Pozycja, Kwota FROM Zarobki

Tym razem wynikiem zapytania będą trzy rekordy z dwoma kolumnami:

PozycjaKwota
12000
21800
31500

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:

SELECT TOPROW_NUMBER() OVER (ORDER BY (SELECT 1)) Pozycja, Kwota
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.

PozycjaKwota
12000
21200
31100

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:

SELECT TOP 3 RANK() OVER (ORDER BY Kwota DESC) Pozycja, Kwota FROM Zarobki

Zbiór wynikowy będzie następujący:

PozycjaKwota
12000
21800
31500

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:

INSERT INTO Zarobki VALUES (2, 1800)

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:

SELECT TOP 4
  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:

RANKDENSE_RANKROW_NUMBERKwota
1112000
2221800
2231800
4341500

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

, 2013-12-20

Komentarze:

BVB (2016-10-28 12:12:14)
Bardzo fajnie i przejrzyście opisane. Ja jednak jestem początkujący i nie bardzo wiem jak za pomocą echo""; wyświetlić pobierane dane. Próbowąłem wszelkich znanych mi sposobów np po select -->while ($row = mysql_fetch_array($wynik)) $id= $row['id']; $kwota= $row['kwota']; { echo" $id"; oraz echo" $kwota";<--- ale w najlepszym wypadku nic nie zwraca. Czy mogę prosić o podpowiedź?
Dodaj komentarz
Wyślij
Ostatnie komentarze
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !
a z innej strony - co gdybym ciąg znaków chciał mieć rozbity nie na wiersze a na kolumny? Czyli ciąg ABCD: 1. kolumna: A, 2. kolumna: B, 3. kolumna: C, 4 kolumna: D?
Ciekawy artykuł.
Czy można za pomocą EF wysłać swoje zapytanie?
Czy lepiej do tego użyć ADO.net i DataTable?