• 20.8.2016
  • Ing. Jan Zedníček - Data & Finance
  • 0

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:

  1. Aplikujeme některou z funkcí ROW_NUMBER, RANK, DENSE_RANK, NTILE
  2. Připojíme klauzuli OVER (ORDER BY) a tím definujeme, přes které pole budeme pořadí počítat vzestupně nebo sestupně (Povinné)
  3. 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);

SQL funkce na pořadí
Vstupní data

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;

SQL funkce na pořadí
Příklad použití ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

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

5/5 - (2 votes)

Ing. Jan Zedníček - Data & Finance

Jmenuji se Honza Zedníček a působím jako freelancer. Tuto práci dělám pro různé firmy již přes 10 let. Před tím jsem dlouhou dobu pracoval také jako BI developer, finanční controller a analytik. Vše pro společnosti z oblasti IT, bankovnictví a výroby. Po práci si rád zahraju tenis, volejbal, šachy a zajdu do posilovny. Svoje znalosti a názory se snažím už několik let zapisovat na tento web, aby sloužily i někomu dalšímu - zejména studentům a zájemcům o informace z oblastí Business intelligence, korporátních financí a reportingu.

=> Přihlašte se do naší Excel facebook skupiny (1.4k+ členů), kde si pomáháme Excel CZ/SK diskuse »
=> Pokud vám článek pomohl, sdílejte ho nebo mě uveďte na vašich webových stránkách jako užitečný zdroj informací.

Leave a Reply

Your email address will not be published. Required fields are marked *