Funkcja rankująca ROW_NUMBER – stronicowanie danych

30-maj-2011

Przyjrzymy się funkcji rankującej ROW_NUMBER. Ta funkcja „nadaje” rekordom numery kolejne, które są nadawane wg określonej kolejności.
Zobaczmy następujące zapytanie:

SELECT

ProductID

,ProductNumber

,Color

,ListPrice

FROM SalesLT.Product

ORDER BY Color, ListPrice, ProductID

W wyniku otrzymujemy uporządkowaną listę produktów. Dzięki temu, że sortując określiłeś klucz sortowania z dokładnością do ProductID, każdy rekord ma tu swoje jednoznacznie określone miejsce.

Ale co jeżeli chcielibyśmy, żeby każdy z tych rekordów otrzymał swój numer? Z pomocą przyjdzie funkcja ROW_NUMBER:

SELECT

ROW_NUMBER() OVER (ORDER BY Color,Listprice,ProductID) AS RowNumber

,ProductID

,ProductNumber

,Color

,ListPrice

FROM SalesLT.Product

ORDER BY Color, ListPrice, ProductID

Funkcja ROW_NUMBER() ma nieco dziwną składnię. Właściwie to, co jest najważniejsze znajduje się za nazwą funkcji. Za słowem OVER, w nawiasie określasz wg jakiego klucza zamierzasz sortować rekordy. W naszym przypadku umieszczona tu została klauzula Order by z całego zapytani, ale wcale tak nie musi być:

SELECT
ROW_NUMBER() OVER (ORDER BY Color,Listprice,ProductID) AS RowNumber
,ProductID
,ProductNumber
,Color
,ListPrice
FROM SalesLT.Product
ORDER BY ProductID

Mimo, że rekordy zostały wyświetlone w kolejności ProductID, to o numerze RowNumber decyduje nadal kolejność podczas sortowania wg color, Listprice i ProductID.
Obecnie nadawanie numeru odbywa się w skali wszystkich produktów. Gdyby jednak chcieć ponumerować rekordy w pewnych grupach można poszerzyć składnię polecenia o PARTITION BY. Gdyby np. chcieć ponumerować rekordy odrębnie w zależności od koloru można zmienić zapytanie następująco:

SELECT

ROW_NUMBER() OVER (PARTITION BY Color ORDER BY Listprice,ProductID) AS RowNumberPartitioned

,ProductID

,ProductNumber

,Color

,ListPrice

FROM SalesLT.Product

ORDER BY Color, ListPrice, ProductID

Teraz każdy kolor ma tworzony ranking wg ceny oddzielnie.
W jednym zapytaniu możesz też dokonać rankingu rekordów wg różnych porządków. Poniższe zapytanie wyświetli numer rekordu w całej tabeli produktów, a obok numer produktu w ramach określonego koloru:

SELECT

ROW_NUMBER() OVER (ORDER BY Color,Listprice,ProductID) AS RowNumber

,ROW_NUMBER() OVER (PARTITION BY Color ORDER BY Listprice,ProductID) AS RowNumberPartitioned

,ProductID

,ProductNumber

,Color

,ListPrice

FROM SalesLT.Product

ORDER BY Color, ListPrice, ProductID

No dobrze. Ale do czego można by tego użyć w praktyce? Załóżmy, że na potrzeby strony internetowej trzeba przygotować procedurę wyświetlającą dane porcjami. Będziemy układać rekordy w określonej kolejności za pomocą ROW_NUMBER i filtrować rekordy, pobierając tylko tyle ile mieści się na jednej stronie:
Na początku deklarujemy zmienne na początek i koniec przedziału oraz nadajemy im wartość. Analizę rozpoczniemy od podzapytania (kolor niebieski). Jego działanie powinno być już zrozumiałe. Pobieramy rekordy z tabeli, numerujemy je w określonej kolejności.
Trudno by jednak było na tym etapie określić jakie warunki ma spełnić RowNumber. Dlatego też filtrowaniem zajmuje się podzapytanie zewnętrzne. Jego zadanie polega wyłącznie na pobraniu rekordów z podzapytania wewnętrznego i odfiltrowanie tych właściwych.

DECLARE @StartRow INT

DECLARE @EndRow INT

SET @StartRow = 20

SET @EndRow = 30

SELECT

RowNumber

,ProductID

,ProductNumber

,Color

,ListPrice

FROM (

SELECT