Szereg – czyli numer miejsca na liście
Funkcje RANK() oraz DENSE_RANK(), służą do określania pozycji w szeregu.
Działają one zawsze na zbiorach, określonych za pomocą funkcji okna OVER(), według określonego w niej porządku.
Uwzględniają powtórzenia w wyznaczaniu numerów rekordów, posiadających te same wartości klucza, według którego sortujemy. Dla przypomnienia, ROW_NUMBER() nadawała zawsze kolejny numer, bez względu na to czy wartość sortowana się różniła czy nie.
Podobnie jak dla pozostałych funkcji rankingowych, możemy wykorzystać tutaj pełen zakres możliwości składni funkcji okna OVER() (np. dzielenie zbioru na partycje), a także zastosowanie mają tu ogólne reguły działania dla funkcji szeregujących.
Funkcja RANK()
Nadaje kolejne wartości wierszom w ramach zbioru, pod warunkiem, że są one różne ze względu na sposób sortowania w klauzuli OVER().
Jej działanie najlepiej pokazać na przykładzie :
USE AdventureWorks2008
GO
SELECT P.Name Product, P.ListPrice, PSC.Name Category,
RANK() OVER( ORDER BY P.ListPrice DESC) AS PriceRank
FROM Production.Product P JOIN Production.ProductSubCategory PSC
ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
WHERE PSC.Name = 'Handlebars'
Wyznaczany jest kolejny numer pozycji elementu w zbiorze, pod warunkiem, że wartość kolumny po której sortujemy, jest unikalna (lub wystąpiła po raz pierwszy). Jeśli wartość atrybutu sortującego, pojawiła się już wcześniej w rekordzie poprzedzającym, zwróci ona ten sam numer. W naszym przykładzie, pierwsze dwie pozycje mają ten sam numer określony przez RANK(), bo wartość w kolumnie ListPrice jest dla nich równa.
Warto zauważyć, że dla wartości inkrementowanych – będzie to zawsze numer bezwzględnej pozycji rekordu w tabeli wynikowej. W naszym przykładzie będą to wiersze numer 1, 3, 4, 6 i 7. Reszta to miejsca ex aequo (ze względu na wartość sortowania) stąd wyznaczone przeez RANK() wartości są takie same, jak dla wierszy poprzedzających je (rekordy 5 i 8).
Funkcja ta, z założenia może być nieciągła – czyli może zostawiać „dziury” w numeracji (w naszym przykładzie nie zwróciła np. wartości 2).
Żeby dobrze zapamiętać jej sposób działania – można odnieść ją do analogii określania listy wyników w zawodach sportowych. Nadaje bowiem te same miejsca dla wyników ex aequo. Jednak funkcja RANK(), bywa niesprawiedliwa i w wyniku działania możliwe jest określenie zwycięzców, ale czasem bez srebrnych lub brązowych medali (przy jednoczesnej możliwości nadania x medali złotych) :
SELECT P.Name Product, P.ListPrice, PSC.Name Category,
RANK() OVER( ORDER BY P.ListPrice DESC) AS PriceRank
FROM Production.Product P JOIN Production.ProductSubCategory PSC
ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
Funkcja DENSE_RANK() – szereg zwarty
Działa analogicznie do RANK() z jedną drobną różnicą – jest funkcją ciągłą, więc jeśli inkrementuje wartości, robi to bez względu na faktyczną pozycję w rankingu, nadaje dokładnie kolejny numer pozycji.
Nawiązując znów do analogii wyników zawodów sportowych, DENSE_RANK() jest „sprawiedliwa” i zawsze wyznaczy drugie (srebrne) i trzecie (brązowe) miejsce w szeregu :
SELECT P.Name Product, P.ListPrice, PSC.Name Category,
DENSE_RANK() OVER( ORDER BY P.ListPrice DESC) AS PriceRank
FROM Production.Product P JOIN Production.ProductSubCategory PSC
ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
WHERE PSC.Name = 'Handlebars'
Poza tą drobną, choć bardzo istotną różnicą, działa identycznie do RANK() czyli dla tego samego argumentu (wartość atrybutu sortującego) zwraca zawsze tą samą wartość.
RANK() i DENSE_RANK() w praktyce
W odróżnieniu do ROW_NUMBER, funkcje RANK() oraz DENSE_RANK() są deterministyczne. Oznacza to, że dla danych argumentów zwracaję zawsze te same wartości.
Podobnie jak dla pozostałych funkcji szeregujących, aby wykorzystać wartości przez nie wyznaczane, np. w warunkach filtracji, musimy użyć podzapytań (lub CTE). Możemy stosować je tylko w SELECT lub ORDER BY zapytania głównego. Związane jest to z logiczną kolejnością przetwarzania zapytań.
-- pobieranie tylko najdroższych (również ex aequo) produktów z podkategorii
with RANKED_CTE(Product, ListPrice , Category, PriceRank )
as (
SELECT P.Name Product, P.ListPrice, PSC.Name Category,
RANK() OVER(PARTITION BY PSC.Name ORDER BY P.ListPrice DESC) AS PriceRank
FROM Production.Product P JOIN Production.ProductSubCategory PSC
ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
)
select * from RANKED_CTE
where PriceRank = 1
Porównanie wyników funkcji RANK i DENSE_RANK()
W poniższym przykładzie, pokazana jest różnica pomiędzy wyznaczanymi wartościami funkcji RANK i DENSE_RANK.
SELECT P.Name Product, P.ListPrice, PSC.Name Category,
RANK() OVER( ORDER BY P.ListPrice DESC) AS RANK_result,
DENSE_RANK() OVER( ORDER BY P.ListPrice DESC) AS DENSE_RANK_result
FROM Production.Product P JOIN Production.ProductSubCategory PSC
ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
WHERE PSC.Name = 'Handlebars'