Pokročilé agregační funkce – 10. díl

V nadcházejícím článku se zaměřím na windowed funkce. Ty provádějí pokročilé agregace nad specifickým „oknem“ řádků, což rozšiřuje možnosti analýz. Věnuji se funkcím ROW_NUMBER, RANK, LEAD, LAG, FIRST_VALUE a dalším. Ukážu konkrétní příklady využití.

OVER & PARTITION BY

Za klasické agregační funkce jako COUNT, SUM, MIN, MAX, AVG apod. lze dopsat slovíčko OVER. Spolu s PARTITION BY lze specifikovat jiný „GROUP BY“ než v dotazu přes, který se bude agregovat. U některých funkcích lze za PARTITION BY zapsat i ORDER BY – umožňuje inkrementální agregaci. Syntaxe vypadá následovně.

aggfunction() OVER (partition BY <columns1> optional ORDER BY <columns2>)

Ukáži to na příkladu nad kalendářem. Dejme tomu, že chci v měsíci spočítat počet pracovních dnů, ale potřebuji nechat jednotlivé dny na výstupu. SELECT a výstup vypadá následovně.

SELECT cal.DIM_CALENDAR_DATE, cal.FLAG_WORKING_DAY,
	SUM(cal.FLAG_WORKING_DAY) OVER (PARTITION BY cal.FLAG_WORKING_DAY) AS CNT_WORKING_DAYS,
	SUM(cal.FLAG_WORKING_DAY) OVER (PARTITION BY cal.FLAG_WORKING_DAY ORDER BY cal.DIM_CALENDAR_DATE) AS CNT_INC_WORKING_DAYS,
	SUM(CASE WHEN cal.DIM_CALENDAR_DATE <= now()::DATE THEN cal.FLAG_WORKING_DAY ELSE 0 END) OVER (PARTITION BY cal.FLAG_WORKING_DAY) AS CNT_WORKING_DAYS_TO_TODAY
FROM l2.DIM_CALENDAR cal
WHERE cal.DIM_CALENDAR_DATE BETWEEN '2023-11-01' AND '2023-11-30'
ORDER BY cal.DIM_CALENDAR_DATE

CNT_WORKING_DAYS sečte všechny pracovní dny v měsíci. Jako kdyby byl použitý jen sum(cal.FLAG_WORKING_DAY) ovšem zachová dny na výstupu.

CNT_INC_WORKING_DAYS je stejný jako přechozí s tím rozdílem, že jsem přidal navíc ORDER BY, kterým říkám v jakém pořadí má funkce dělat inkrement a ten zobrazí na výstupu.

CNT_WORKING_DAYS_TO_TODAY je jen pro ukázku, že do závorek sum() lze dávat i složitější výpočty než jen konkrétní sloupec. Konkrétně se zde spočítá počet pracovních dní v měsíci do současného dne včetně.

Obdobně fungují ostatní základní agregační funkce. Nad windowed funkcemi se nedá filtrovat v současném dotaze a je nutné dotaz obalit pomocí subquery (Nelze ani v HAVING).

ROW_NUMBER

Funkce ROW_NUMBER čísluje řádky na základě partition (nepovinný) a pořadí. Hodí se například pokud potřebuji z více duplicitních řádků ponechat pouze jeden. Nebo když chci zjistit například 5. pracovní den v měsíci (následující kód).

SELECT *
FROM (
	SELECT cal.DIM_CALENDAR_DATE, ROW_NUMBER() OVER (ORDER BY cal.DIM_CALENDAR_DATE) AS RN
	FROM l2.DIM_CALENDAR cal
	WHERE cal.DIM_CALENDAR_DATE BETWEEN '2023-11-01' AND '2023-11-30'
		AND cal.FLAG_WORKING_DAY = 1
) cal
WHERE cal.RN = 5

Pokud není jednoznačně určeno, který záznam má přednost, funkce se nějak rozhodne. Při opakovaném spuštění se ovšem může rozhodnout jinak, a proto je vždy potřeba určit pořadí jednoznačně.

RANK

Rank funguje podobně jako ROW_NUMBER s tím rozdílem, že pokud od sebe nedokáže odlišit 2 záznamy na základě definovaných kritérií. Tak oběma určí stejné číslo. Následující odlišný záznam nedostane číslo následující, ale číslo o tolik větší, kolik mám nejednoznačných záznamů. Tedy přeskakuje čísla.

Existuje i varianta DENSE_RANK(), která nevynechává čísla v pořadí a může nastat například situace: 1, 1, 1, 2. (RANK by vypadal: 1, 1, 1, 4 a ROW_NUMBER() by vypadal 1, 2, 3, 4).

LEAD & LAG

Funkce LEAD slouží k dohledání hodnoty na následujícím řádku. Opět je zde nepovinný PARTITION BY. LAG je úplně stejný jen řadí obráceně. Nejlépe to je vidět na následující ukázce.

SELECT cal.DIM_CALENDAR_DATE,
	lead(cal.DIM_CALENDAR_DATE) OVER (ORDER BY cal.DIM_CALENDAR_DATE) AS NEXT_DAY,
	lead(cal.DIM_CALENDAR_DATE, 5) OVER (ORDER BY cal.DIM_CALENDAR_DATE) AS NEXT_5_DAY,
	lead(cal.DIM_CALENDAR_DATE, 5, '3000-01-01') OVER (ORDER BY cal.DIM_CALENDAR_DATE) AS NEXT_5_DAY_NULL_VALUE,
	lag(cal.DIM_CALENDAR_DATE) OVER (ORDER BY cal.DIM_CALENDAR_DATE) AS PREVIOUS_DAY
FROM l2.DIM_CALENDAR cal
WHERE cal.DIM_CALENDAR_DATE BETWEEN '2023-11-20' AND '2023-11-30'
	AND cal.FLAG_WORKING_DAY = 1

NEXT_DAY pomocí LEAD určí následující den. Uvnitř lead() se specifikuje sloupec, ze kterého se má vzít hodnota. A v OVER se už jen nastaví řazení.

NEXT_5_DAY získá 5. následující den. Pomocí druhého parametru LEAD specifikuji o kolik řádků dopředu má funkce jít. Defaultně je hodnota nastavena na 1.

NEXT_5_DAY_NULL_VALUE je stejný jako přechozí jen s tím rozdílem, že specifikuje jak se má nahradit hodnota NULL.

FIRST_VALUE

FIRST_VALUE je takový ROW_NUMBER, který ale nevrací očíslované řádky, ovšem vrátí vždy první hodnotu podle zvoleného řazení ze sloupce který definuji.

SELECT cal.DIM_CALENDAR_DATE, first_value(cal.DIM_CALENDAR_DATE) OVER (ORDER BY cal.DIM_CALENDAR_DATE) AS FV
FROM l2.DIM_CALENDAR cal
WHERE cal.DIM_CALENDAR_DATE BETWEEN '2023-11-01' AND '2023-11-30'
	AND cal.FLAG_WORKING_DAY = 1

Existuje i funkce NTH_VALUE, která umí získat i ntý výskyt.

SELECT cal.DIM_CALENDAR_DATE,
	nth_value(cal.DIM_CALENDAR_DATE, 5) OVER (ORDER BY cal.DIM_CALENDAR_DATE) AS FV,
	nth_value(cal.DIM_CALENDAR_DATE, 5) OVER (ORDER BY cal.DIM_CALENDAR_DATE RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FVF
	FROM l2.DIM_CALENDAR cal
	WHERE cal.DIM_CALENDAR_DATE BETWEEN '2023-11-01' AND '2023-11-30'
		AND cal.FLAG_WORKING_DAY = 1

FV získá 5. pracovní den. Jak jste si jistě všimli tak, ale až od něj včetně. Pokud potřebuji hodnotu nad celou sestavou, musím použít magii v následujícím sloupci.

PERCENTILE_CONT

PERCENTILE_CONT umožňuje spočítat percentil. Nejznámějším percentilem je medián (50%). V Postgres už nelze použít OVER, ale využije se WITHIN GROUP.

percentile_cont(0.5) WITHIN GROUP (ORDER BY VALUE) AS MEADIAN

STRING_AGG

Funkce STRING_AGG umožňuje slučovat řetězce přes řádky.

SELECT string_agg(VALUE, ', ' ORDER BY VALUE DESC)

STRING_SPLIT

Opačná funkce k STRING_AGG je STRING_SPLIT (v MSSQL + CROSS APPLY). V Postgres lze obdobného dosáhnout kombinací funkcí UNNEST a STRING_TO_ARRAY.

SELECT unnest(string_to_array('Ahoj;Dobry den;Nashle', ';'))

Shrnutí

Pokročilé agregační funkce neboli tzv. windowed funkce umožňují agregovat mimo hlavní „okno“ dotazu pomocí OVER, PARTITION BY a ORDER BY.

Velmi užitečná funkce je ROW_NUMBER, která očísluje záznamy dle zadaného pořadí. Obdobně s určitými modifikacemi fungují funkce RANK a DENSE_RANK.

Pro získání následujícího/předchozího záznamu se hodí LEAD a LAG. Lze nastavit o kolik záznamů vpřed/dozadu chci.

Může se hodit také funkce FIRST_VALUE/NTH_VALUE – první/ntá hodnota a PERCENTILE_CONT pro výpočet percentilu.

Pro řetězení textu lze použít STRING_AGG a pro opačný efekt je vhodná funkce STRING_SPLIT.

Dokumentace

Postgres OVER https://www.postgresql.org/docs/current/tutorial-window.html

Postgres Aggregate functions https://www.postgresql.org/docs/current/functions-aggregate.html

MSSQL OVER https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16

MSSQL STRING_AGG https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16

MSSQL STRING_SPLIT https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16

Oracle OVER https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/window-functions-usage.html

Doporučené články

Předchozí

SELECT – 4.díl

SQL funkce a DML – 5. díl

Dimenze – 6. díl

Fakta – 7. díl

Pojmenovávání a dokumentace – 8. díl

Orchestrace – 9.díl

Následující

Surrogate key – 11. díl

Tipy a triky – 12. díl

Příklady

Příklady k článku windowed functions

SELECT dotazování – 2. příklady

User-defined funkce – 3. příklady

Dimenze – 4. příklady

Fakta – 5. příklady