Funkcje rankingowe RANK(), DENSE_RANK(), ROW_NUMER()

4-cze-2011

Co się dzieje na olimpiadzie, kiedy 2 zawodników uzyska dokładnie taki sam wynik? Powiedzmy w skoku wzwyż obaj przeskoczyli poprzeczkę na wysokości 2,20 i był to najwyższy wynik na zawodach. Gdyby, chociaż jeden z nich był kobietą, a jeden mężczyzną, to dalibyśmy im po medalu w osobnych kategoriach. Ale jeżeli nie można zróżnicować ich wyników?

Jedna z propozycji jest taka, aby dać im po „złotym medalu”, ale kolejny zawodnik nie dostanie miejsca drugiego, tylko trzecie. Pozycja numer 2 i srebrny medal zostaną nieprzydzielone.

SQL Serwer ma dwie funkcje pozwalające budować taki ranking: RANK() i DENSE_RANK().

Przyjrzyjmy się najpierw funkcji RANK(). Działa ona „po olimpijsku”. Załóżmy, że chcemy stworzyć zestawienie pokazujące produkty podzielone ze względu na kolory. W ramach każdego koloru chcemy zobaczyć listę produktów od najdroższego do najtańszego. Produkt najdroższy ma otrzymać numer 1, a kolejne produkty coraz to większe wartości.

Składnia funkcji RANK() w tym przypadku będzie wyglądać następująco:

RANK() OVER(PARTITION BY Color ORDER BY ListPrice DESC)

Całe zaś zapytanie może wyglądać tak:

 SELECT      Color, Name, ListPrice,
            RANK() OVER(PARTITION BY Color
                                   ORDER BY ListPrice DESC)
FROM Production.Product
WHERE Color IS NOT NULL

Zauważ, że w ramach czarnego koloru, kilka produktów ma cenę 3374,99. Te kilka produktów sprawiedliwie otrzymało od funkcji RANK pierwsze miejsce na liście najdroższych produktów. Za to kolejne produkty o cenie 2443,35 nie trafią na pozycję drugą. W numeracji powstaje dziura i produkty te klasyfikują się na miejsce 5.

Cóż. Nie podoba Ci się, że funkcja zostawiła „dziury”. Możesz ponumerować rekordy tak, aby nie pozostawały między nimi luki. Użyj w tym przypadku funkcji DENSE_RANK(). Składania tej funkcji jest identyczna, co w przypadku funkcji RANK:

DENSE_RANK() OVER(PARTITION BY Color ORDER BY ListPrice DESC)

Oto całe zapytanie:

SELECT      Color, Name, ListPrice,
            DENSE_RANK() OVER(PARTITION BY Color
                                   ORDER BY ListPrice DESC)
FROM Production.Product
WHERE Color IS NOT NULL

Zmienia się jednak wynik działania. Mimo iż rekordy o tej samej cenie otrzymały numer 1, to jednak rekordy z kolejną ceną trafiły od razu na pozycję 2. Nie powstały żadne luki w numeracji. Funkcja DENSE_RANK() jest przy tym sprawiedliwa i rekordy z tą samą ceną umieszcza w rankingu na tej samej pozycji.

Funkcja ROW_NUMBER() również potrafi ustawić ranking rekordów, w tym jednak przypadku każdy rekord otrzymuje swój własny i niezależny numer. Gdyby zaś dwa rekordy miały taką samą cenę, to jeden z nich otrzyma wyższy numer od drugiego.

 SELECT      Color, Name, ListPrice,
            ROW_NUMBER() OVER(PARTITION BY Color
                                   ORDER BY ListPrice DESC)
FROM Production.Product
WHERE Color IS NOT NULL