SQL funkce na pořadí (ranking functions) nám umožňují přiřazovat v záznamům v tabulce pořadí na základě hodnoty nějakého pole nebo polí. Ranking funkce se vyznačují tím, že klauzule OVER() je povinná.
Funkcí na pořadí řadíme do tzv Window funkcí a do stejné skupiny patří kromě nich také agregační funkce a Offset funkce (na stránkování).
Seznam ranking funkcí na pořadí:
ROW_NUMBER () OVER (PARTITION BY | ORDER BY) – vrátí pořadové číslo řádku s možností rozdělení na části (partitions) a seřazení. Začíná od 1 pro každou skupinu
RANK () OVER (PARTITION BY | ORDER BY) – vrátí pořadí každého záznamu po jednotlivých částech (partitions)
DENSE_RANK () OVER (PARTITION BY | ORDER BY) – vrátí pořadí každého záznamu po jednotlivých částech (partitions) bez mezer mezi pořadími
NTILE (argument) OVER (PARTITION BY | ORDER BY) – Rozdělí řádky do n skupin v závislosti na argumentu
Jak funkci na pořadí použít?
Funkce na pořadí použijeme tak, že:
- Aplikujeme některou z funkcí ROW_NUMBER, RANK, DENSE_RANK, NTILE
- Připojíme klauzuli OVER (ORDER BY) a tím definujeme, přes které pole budeme pořadí počítat vzestupně nebo sestupně (Povinné)
- do klauzule OVER() můžeme ještě připojit PARTITION BY a vytvořit tak skupiny. Pořadí bude potom spočítáno pro každou skupinu zvlášť (Nepovinné)
Praktická aplikace ranking funkcí bude nejlépe vidět na příkladu. Vyrobíme si testovací tabulku s tržbami za produkty a naplníme ji daty:
USE tempdb;
CREATE TABLE Ukazka_ranking
(ID INT IDENTITY(1,1),
Produkt VARCHAR(100),
Produktova_Skupina VARCHAR(100),
Trzby NUMERIC;
INSERT INTO Ukazka_ranking (Produkt, Produktova_Skupina, Trzby)
VALUES ('Židle','Kuchyne',110),
('Stul','Kuchyne',200),
('Skrín','Kuchyne',410),
('Postel','Ložnice',200),
('Vana','Koupelny',100),
('Pracka','Koupelny',400);
Na tuto tabulku budeme postupně aplikovat jednotlivé funkce. Do založené tabulky si doplníme pole s jednotlivými funkcemi, pro zjednodušení nebudeme aplikovat PARTITION BY klauzuli.
SELECT *
,ROW_NUMBER() OVER (ORDER BY Trzby DESC) AS ROW_NUMBER
,RANK() OVER (ORDER BY Trzby DESC) AS RANK
,DENSE_RANK() OVER (ORDER BY Trzby DESC) AS DENSE_RANK
,NTILE(3) OVER(ORDER BY Trzby DESC) AS NTILE
FROM Ukazka_ranking
ORDER BY Trzby DESC;
Vyhodnocení a interpretace :
- ROW_NUMBER() – žádné položky nemají stejné číslo pořadí a nejsou mezi nimi mezery
- RANK() – položky můžou mít stejné pořadí a jsou mezery mezi pořadími
- DENSE_RANK() – položky můžou mít stejné pořadí a nejsou mezery mezi pořadími
- NTILE(3) – Dělí záznamy na 3 stejně početné skupiny
S window funkcemi souvisí článek Agregační funkce, SQL OVER() – Window funkce, OFFSET funkce
