Window funkce v T-SQL

Základní window funkce v SQL Serveru známe všichni: ROW_NUMBER(),  RANK(), DENSE_RANK() a NTILE(). K nim se v dalších verzích SQL Serveru přidaly ještě funkce LEAD() a LAG(), na ty se ale podíváme někdy příště. Dnešní článek se nebude věnovat detailům jednotlivých funkcí, ale měl se stát spíše rozcestníkem, do něhož se půjdete podívat, pokud nebudete vědět, která ze čtyř základních funkcí se pro Váš úkol hodí nejlépe.

Pojďme si tedy ukázat, jak se jednotlivé funkce chovají.

Nejprve si vytvoříme tabulku a naplníme ji jednoduchým vzorkem testovacích dat:

-- create sample table
CREATE TABLE dbo.SampleTable
(
    Id INT IDENTITY (1,1) PRIMARY KEY,
    Category CHAR(1), 
    Value VARCHAR(10)
)
GO

-- populate table with data
INSERT dbo.SampleTable VALUES ('A', 'Value1')
INSERT dbo.SampleTable VALUES ('B', 'Value2')
INSERT dbo.SampleTable VALUES ('C', 'Value3')
INSERT dbo.SampleTable VALUES ('D', 'Value4')
INSERT dbo.SampleTable VALUES ('E', 'Value5')
INSERT dbo.SampleTable VALUES ('F', 'Value6')
INSERT dbo.SampleTable VALUES ('G', 'Value7')
INSERT dbo.SampleTable VALUES ('H', 'Value8')
INSERT dbo.SampleTable VALUES ('I', 'Value9')
INSERT dbo.SampleTable VALUES ('J', 'Value10')
GO

Nad tabulkou spustíme dotaz s jednoduchou aplikací window funkcí (zatím bez PARTITION BY):

-- test ranking functions on not duplicated rows
SELECT 
    ROW_NUMBER() OVER (ORDER BY Category) [ROW_NUMBER],
    RANK() OVER (ORDER BY Category) [RANK],
    DENSE_RANK() OVER (ORDER BY Category) [DENSE_RANK],
    NTILE(2) OVER (ORDER BY Category) [NTILE],
    *
FROM dbo.SampleTable
GO

Vidíme, že datovou sadu, kde jsou všechny řádky unikátní, nám všechny funkce vrací stejné hodnoty, pouze NTILE() dělá svoji práci nezávisle na povaze zdrojových dat a zkrátka rozdělí data na požadované úseky, v tomto případě 2 část (NTILE(2)).

Nyní zkusme do testovací tabulky přidat nějaké duplicitní řádky:

-- inset duplicated rows
INSERT dbo.SampleTable VALUES ('C', 'Value3')
INSERT dbo.SampleTable VALUES ('C', 'Value3')
INSERT dbo.SampleTable VALUES ('G', 'Value3')
INSERT dbo.SampleTable VALUES ('G', 'Value3')
GO

Opět spustíme window funkce, u kterých si rovnou otestujeme i PARTITION BY klauzuli:

SELECT 
    ROW_NUMBER() OVER (ORDER BY Category) [ROW_NUMBER],
    RANK() OVER (ORDER BY Category) [RANK],
    RANK() OVER (PARTITION BY Value ORDER BY Id) [PART_RANK],
    DENSE_RANK() OVER (ORDER BY Category) [DENSE_RANK],
    DENSE_RANK() OVER (PARTITION BY Value ORDER BY Id) [PART_DENSE_RANK],
    NTILE(2) OVER (ORDER BY Category) [NTILE],
    NTILE(2) OVER (PARTITION BY Value ORDER BY Category) [PART_NTILE],
    *
FROM dbo.SampleTable
ORDER BY ID
GO

Na obrázku krásně vidíme chování jednotlivých funkcí v případě duplicitních hodnot a zároveň i to, jak se chování funkcí změní, použijeme-li klauzuli PARTITION BY.