W SQL Server w wersji 9-tej (czyli 2005) zostały wprowadzone cztery analityczne funkcje szeregujące. Ich składnia i funkcjonalności zostały zdefiniowane w standardzie ANSI SQL:2003 – spotkasz je również w innych dialektach np. PL/SQL (Oracle).

Stanowią rozszerzenie języka i w większości zastosowań poprawiają również wydajność zapytań w stosunku do „tradycyjnych” metod np. numerowania rekordów.

Zanim przejdziesz do poznawania poszczególnych funkcji – powinieneś poznać ogólne zasady według których działają. Wspólnym mianownikiem i ich nieodłącznym elementem, jest funkcja okna – OVER(). Omówienie poszczególnych funkcji, umieściłem w osobnych, dedykowanych artykułach :

• ROW_NUMBER() – numerowanie rekordów
• RANK() – szereg
• DENSE_RANK() – szereg zwarty
• NTILE() – dzielenie na przedziały

Artykuł ten przedstawia ideę działania i składnię – wspólną dla wszystkich funkcji rankingowych. Opisywane tu funkcjonalności są spójne dla wszystkich wersji SQL Server w których możesz je spotkać (czyli od 2005+).

Składnia funkcji szeregujących – funkcja okna OVER()

Praktyczne zastosowanie funkcji rankingowych, jest wbrew pozorom bardzo łatwe i intuicyjne.

Każda funkcja szeregująca działa w oparciu o funkcję okna – OVER(). Zastanówmy się jakich elementów składni, możemy potrzebować, aby wykonać szeregowanie np. numerowanie rekordów.

Nadawanie jakiejkolwiek numeracji elementów w ramach zbioru, wykonujemy zazwyczaj według określonego porządku (np. wartości zamówień, liczby, daty pojawienia się, czasu etc.). Pierwsza rzecz, która będzie potrzebna, to określenie sposobu sortowania zbioru – klucza, według którego, będziemy numerowali rekordy. Jest to pierwsza (z dwóch możliwch do określenia) właściwość, funkcji okna. Właściwości funkcji okna, zademonstruję na przykładzie funkcji ROW_NUMBER() czyli prostego, kolejnego numerowania wierszy.

Szeregowanie elementów według określonego porządku (sortowanie)

W funkcji okna, określamy przede wszystkim sposób uporządkowania elementów według którego będziemy dokonywać numeracji rekordów. Ten element składni, trzeba zawsze określić – jest on wymagany. Dla przykładu załóżmy, że chcemy ponumerować zamówienia, według ich wartości :

Use AdventureWorks2008
Go

select SalesOrderId,TotalDue , 
                -- funkcja szeregująca ROW_NUMBER() wraz z nierozłączną funkcją okna OVER()
		ROW_NUMBER() OVER( ORDER BY TotalDue desc) as RowNum
		
from Sales.SalesOrderHeader

Jak widać, sposób sortowania elementów, określamy za pomocą dobrze znanej klauzuli ORDER BY, wewnątrz funkcji OVER().

Jeśli nie zależy nam na jakimś konkretnym sortowaniu, lub chcemy aby to było wykonane w sposób pseudolosowy możemy wykorzystać inne możliwości jakie daje ORDER BY, ale zrezygnować z niej nie możemy – jest obowiązkowa.

select SalesOrderId,TotalDue , 
                -- sortowanie w ramach funkcji okna w sposób pseudolosowy
		ROW_NUMBER() OVER( ORDER BY NEWID()) as RowNum
		
from Sales.SalesOrderHeader

W tym przykłądzie nie ma określonych żadnych dodatkowych warunków filtracji w kwerendzie (ani we FROM, ani w WHERE – którego z resztą w ogóle tu nie ma) – operujemy więc funkcją ROW_NUMBER(), na wszystkich elementach ze zbioru Sales.SalesOrderHeader. Nadajemy im kolejne, unikalne numery w kolumnie RowNum.

Ważne jest żeby dostrzec, że ten przykład szereguje elementy, w ramach całego zbioru na którym operuje SELECT. Wynik zwracany przez funkcję jest ściśle określony, według przyjętego porządku – zastosowanego klucza sortowania w funkcji okna.

Szeregowanie elementów z uwzględnieniem podzbiorów (partycje)

Funkcja okna, umożliwia szeregowanie elementów zbioru, również w węższym zakresie, czyli w kontekście jego podzbiorów. Jest to druga właściwość jaką możemy tu określić, czyli partycjonowanie elementów (PARTITION BY) ze względu na wartość atrybutu (lub atrybutów).
Np. ponumerujmy produkty w ramach podkategorii :

SELECT P.Name as Product, P.ListPrice, PSC.Name as Category,
     
  -- funkcja szeregująca, działająca w ramach partycji wartości atrybutu PSC.Name 
        ROW_NUMBER() 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

Jak widać na powyższym przykładzie, możemy szeregować zlecenia, ze względu na wartość kolumny (lub kolumn), według której wyznaczane są podzbiory (partycje) .

Numeracja wykonywana jest w ich zakresie, dla każdego z podzbiorów osobno.

Podzbiory definiowane są przez wartość kolumny (atrybutu) PSC.Name czyli podkategorii produktu. Nadawane numery są unikalne, ale tylko w wąskim zakresie (domenie czy też dziedzinie) – danej kategorii, określonej w atrybucie partycji.

Implementacja funkcji okna OVER(), została znacząco rozszerzona w SQL Server 2012. Jej możliwości opisałem w poprzednim artykule tego kursu

Ogólne zasady działania funkcji szeregujących

Funkcje rankingowe działają zawsze na tabeli wirtualnej, na której operuje (w odpowiednim czasie przetwarzania zapytania) klauzula SELECT. Omawiam to szczegółowo w rozdziale dotyczącym logicznych faz wykonywania zapytań .

Skutkiem tego, funkcje rankingowe możemy używać, tylko w krokach SELECT oraz ORDER BY, danej kwerendy. Jeśli chcesz użyć ich wyniku w warunkach filtracji – nie obejdzie się bez korzystania z podzapytania np. z wykorzystaniem CTE.

Najlepiej zobrazować to za pomocą przykładu. Pobierzmy informacje o trzech najdroższych produktach w ramach kategorii (sortujemy według klucza wartość ListPrice dla każdej kategorii produktów (partycjonujemy według wartości atrybutu ProductCategory.Name)

With CTE as (
SELECT 

	ROW_NUMBER() OVER(PARTITION BY PC.Name ORDER BY ListPrice desc) AS RowNum,
	
	PC.Name Category, P.Name Product, P.ListPrice

FROM 
   Production.Product P JOIN Production.ProductSubCategory PSC
      ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
   JOIN Production.ProductCategory PC
      ON PSC.ProductCategoryID = PC.ProductCategoryID
)
select * from CTE where RowNum<4

Przykład ten pokazuje również pewną specyficzną właściwość funkcji ROW_NUMBER(). Zauważ, że istnieją produkty w ramach kategorii, które mają tą samą cenę. Na jakiej podstawie nadawany jest zatem numer pozycji, jeśli wartości kolumny według której działamy (sortujemy) są równe? Zgodnie z teorią, jeśli zbiór nie jest posortowany (tu mam na myśli X elementów o tej samej wartości), to elementy teoretycznie zwracane są w losowej kolejności.
Funkcja ROW_NUMBER() jest funkcją niedeterministyczną. Wyjaśniam to szczegółowo w artykule na jej temat.