Ranking functions v SQL 2005 - konečně pohodlné stránkování

Verze 2005 přinesla do SQL Serveru řadu užitečných novinek. Jednou z nich jest též implementace tzv. ranking functions - dalo by se to překládat např. jako hodnotící funkce. Takové funkce jsou celkem čtyři a využijete je především, pokud chcete vypisovat z databáze pouze část záznamů. Vyloženě školním příkladem je využití funkce ROW_NUMBER() pro stránkování výpisů. Znamená to, že nebudeme muset nadále závidět uživatelům MySQL jejich SELECT LIMIT a budeme se moci štastně vzdát šílených stránkovacích procedur realizovaných pomocí rozličných kombinací SELECT TOP.

ROW_NUMBER()

Funkce ROW_NUMBER() slouží nepříliš překvapivě k vrácení čísla řádku, k sekvenčnímu číslování. Jednoduchý dotaz (nad tabulkou Person.Contact z ukázkové databáze AdventureWorks) s využitím této funkce může vypadat takto:

SELECT LastName, FirstName, EmailAddress, ROW_NUMBER() OVER (ORDER BY LastName) AS RowNumber FROM Person.Contact ORDER BY LastName

Výsledek bude obsahovat pole RowNumber, ve kterém bude sekvenční číslo, stoupající od jedné. My zpravidla ovšem nechceme toto číslo získat, ale spíše se podle něj zařídit - zkonstruovat odpovídající podmínku. Ranking functions ovšem není možné použít ve WHERE části dotazu, proto si musíme pomoci malým trikem:

WITH X AS (SELECT LastName, FirstName, EmailAddress, ROW_NUMBER() OVER (ORDER BY LastName) AS RowNumber FROM Person.Contact) SELECT * FROM X WHERE X.RowNumber BETWEEN 21 AND 40 ORDER BY X.LastName

Pomocí klíčového slova WITH si vytvoříme poddotaz, na který aplikujeme požadovanou podmínku, v našem případě touhu po záznamech 21-40, tedy hypotetické druhé stránce při stránkování po dvaceti.

RANK() a DENSE_RANK()

Funkce RANK() a DENSE_RANK() jsou o něco složitější. Nevracejí totiž číslo řádku, ale pořadí hodnoty. Provedeme si drobnou demografickou analýzu jmen kontaktů firmy Adventure Works - budeme zjišťovat, jaká křestní jména se mezi nimi nejčastěji vyskytují. Použijeme k tomu následující dotaz, obsahující všechny dosud představené funkce:

SELECT FirstName, COUNT(*) AS PersonCount, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS RowNumber, RANK() OVER (ORDER BY COUNT(*) DESC) AS Rank, DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS DenseRank FROM Person.Contact GROUP BY FirstName ORDER BY PersonCount DESC

Výsledek tohoto dotazu (resp. jeho úvodní část) bude vypadat takto:

• Sloupec PersonCount obsahuje počet osob obdařených daným křestním jménem.
• Sloupec RowNumber obsahuje výsledek nám již známé funkce ROW_NUMBER(), tedy souvislou číselnou řadu stoupající po jedné.
• Sloupec Rank, jako výsledek stejnojmenné funce, obsahuje pořadí sledované hodnoty (počtu osob). Vidíme, že s hodnotou 97 se jména Marcus a James dělí o třetí místo. Čtvrtá příčka zůstává neobsazena a pokračujeme až jménem Jennifer, které obsadilo pátou pozici.
• Posledním je sloupec DenseRank. Funkce DENSE_RANK() vrací stejný typ výsledku jako předchozí, ale nepřeskakuje "nevyužitá" místa -- o třetí místo se jména dělí a Jennifer je na místě čtvrtém.

NTILE()

Poslední ranking function, NTILE(), rozdělí výsledek do určeného počtu skupin (n-tin, jako např. pětin pro n=5) a vrátí číslo oné n-tiny.

PARTITION BY

Ve všech shora uvedených funkcích můžete použít klauzuli PARTITION BY. V takovém případě se pořadí (atd.) počítá pouze v rámci v této klauzuli specifikované podmínky. Jedná se o jakousi obdobu klauzule GROUP BY. Pokud bychom v tabulce Person.Contact měli ještě sloupeček Country (což bohužel nemáme) mohli bychom statistiku rozšířit na jednotlivé země právě podle PARTITION BY Country.