Window funkce - k procvičení

V tabulce ski najdete informace o výsledcích závodu v klasickém lyžování z MS 2023 (Muži, 50km hromadný start). Data jsou přejata z FIS oficiální výsledky

Q1: Vytvořte SQL dotaz, který každému závodníkovi spočte jeho pořadí na 25. km a v cíli závodu.

Q2: Vytvořte SQL dotaz, který identifikuje závodníky, kteří si nejvíce zlepšili pořadí mezi 25. kilometrem a cílem.

Q3: Spočtěte ztrátu každého závodníka na závodníka před ním.

Q4: Vyjádřete pořadí U23 účastníků mezi sebou a jejich vzájemný odstup. V dotazu získejte i celkové pořadí.

Q5: Vypište přehled zemí a umístění a jméno nejlepšího závodníka reprezentujícího danou zemi.

Rozšíření Group By

※ Ve zbytku textu používám SQL syntax DBMS PostgreSQL.

Group By je běžný operátor v SQL, který umožňuje agregovat data podle hodnot v jednom nebo více sloupcích. Výsledkem je tabulka, kde každý řádek odpovídá jedinečné kombinaci hodnot v jednotlivých sloupcích.

Pokud je prováděna agregace přes více atributů, mohlo by být užitečné do mezivýsledků zahrnout řádek odpovídající mezivýpočtům za určitou kategorii. Například v tabulce covid lze vyjádřit počty případů dle jednotlivých měsíců v letech jako:


  

SELECT
EXTRACT(year from datum) as y,
EXTRACT(month from datum) as m,
SUM(pocet_nakazenych)

FROM covid_daily c
GROUP BY y, m
ORDER BY 1, 2
y m sum
2020 1 0
2020 2 0
2020 3 3316
2020 4 4385
2020 5 1615
... ... ...

ROLLUP

Pokud bychom chtěli získat i celkový počet případů v daném roce, museli bychom dotaz rozdělit na dvě části a výsledky spojit. V PostgreSQL lze ale použít klíčové slovo ROLLUP, který zavede hierarchii mezi seskupovanými atributy a přidá mezivýpočty za jednotlivé úrovně hierarchie.


  

SELECT
EXTRACT(year from datum) as y,
EXTRACT(month from datum) as m,
SUM(pocet_nakazenych)

FROM covid_daily c
GROUP BY ROLLUP(y, m)
ORDER BY 1, 2
y m sum
... ... ...
2020 11 188223
2020 12 209434
2020 NULL 732814
2021 1 256569
2021 2 253289
... ... ...

Ve výsledné tabulce se objeví několikrát hodnota NULL v atributu m. To je indikace pro to, že tento řádek odpovídá mezivýpočtu za daný rok. Resp. obecně hodnota NULL v seskupovaném sloupci lze chápat jako wildcard pro libovolnou hodnotu.

Zároveň je nutné si uvědomit, že původní záznamy jsou nyní ve výsledku započítány do několika řádek. Informace o stavu v 2020-12-01 bude zahrnuta v řádku (2020, 12) a v řádku (2020, NULL).

CUBE

Dalším modifikátorem GROUP BY operace je klíčové slovo CUBE. To vytvoří výslednou tabulku, kde každý řádek odpovídá jedinečné kombinaci hodnot v jednotlivých sloupcích a zároveň přidává všechny souhrny (NULL kódované řádky) pro možné kombinace.


  

SELECT
EXTRACT(year from datum) as y,
EXTRACT(month from datum) as m,
SUM(pocet_nakazenych)

FROM covid_daily c
GROUP BY CUBE (y, m)
ORDER BY 1, 2
y m sum
... ... ...
2020 12 209434
2020 NULL 732814
2021 1 256569
... ... ...
NULL 11 594570
NULL 12 517353
NULL NULL 3523869

Výsledná tabulka tak obsahuje záznamy jako (2020, NULL) se souhrnem za rok 2020; (NULL, 12) se souhrnem za všechny prosince; a (NULL, NULL) se souhrnem za celé období.

GROUPING SETS

Posledním modifikátorem GROUP BY operace je klíčové slovo GROUPING SETS. To umožňuje explicitně vyjádřit, dle jakých kombinací atributů má být seskupováno.


  

SELECT
EXTRACT(year from datum) as y,
EXTRACT(month from datum) as m,
SUM(pocet_nakazenych)

FROM covid_daily c
GROUP BY GROUPING SETS ((y, m), (y), ())
ORDER BY 1, 2
y m sum
... ... ...
2020 12 209434
2020 NULL 732814
2021 1 256569
... ... ...
2022 2 479282
2022 NULL 1040176
NULL NULL 3523869

V tomto příkladu je použit zápis definující 3 způsoby seskupování:

Výsledek by tedy odpovídal situaci, kdy budou provedeny tři dotazy s příslušnými skupinami v klasické GROUP BY klauzuli a následně budou výsledky spojeny skrz UNION ALL.

Grouping sets jsou zobecněním ROLLUP a CUBE a lze tedy problémy řešené těmito operátory převést. V případě seskupování dle 3 atributů a, b, c vygeneruje CUBE(a, b, c) 8 skupin:

Operátor ROLLUP(a, b, c) vygeneruje 4 skupiny:

Pivot

Operace pivot, občas označovaná jako kontingenční sloupec/tabulka, je operace, která umožňuje transformovat agregovanou tabulku z řádkového uspořádání do maticového uspořádání, kde jeden z atributů je použit pro vytvoření sady sloupců. Koncepčně lze tedy chápat pivot jako úpravu GROUP BY operace tak, aby distinct hodnoty jednoho atributu tvořily sloupce výsledné tabulky a vypočtená metrika nebyla pouze v jenom sloupci, ale rozložena do několika sloupců.

PostgreSQL nepodporuje přímo klíčové slovo PIVOT, který byl předveden na předchozím cvičení v Oracle. V PostgreSQL je možné doinstalovat funkci crosstab, která by měla sloužit k podobnému účelu. Případně lze této funkcionality dosáhnout pomocí klíčových slov FILTER a CASE v projekci dotazu.


  

SELECT
EXTRACT(year from datum) as y,
SUM(pocet_nakazenych) FILTER (WHERE EXTRACT(month from datum) = 1) AS _1,
SUM(pocet_nakazenych) FILTER (WHERE EXTRACT(month from datum) = 2) AS _2,
SUM(pocet_nakazenych) FILTER (WHERE EXTRACT(month from datum) = 3) AS _3,
SUM(pocet_nakazenych) FILTER (WHERE EXTRACT(month from datum) = 4) AS _4,
SUM(pocet_nakazenych) FILTER (WHERE EXTRACT(month from datum) = 5) AS _5,
SUM(pocet_nakazenych) FILTER (WHERE EXTRACT(month from datum) = 6) AS _6,
SUM(pocet_nakazenych) FILTER (WHERE EXTRACT(month from datum) = 7) AS _7,
SUM(pocet_nakazenych) FILTER (WHERE EXTRACT(month from datum) = 8) AS _8,
SUM(pocet_nakazenych) FILTER (WHERE EXTRACT(month from datum) = 9) AS _9,
SUM(pocet_nakazenych) FILTER (WHERE EXTRACT(month from datum) = 10) AS _10,
SUM(pocet_nakazenych) FILTER (WHERE EXTRACT(month from datum) = 11) AS _11,
SUM(pocet_nakazenych) FILTER (WHERE EXTRACT(month from datum) = 12) AS _12

FROM covid_daily c
GROUP BY y
ORDER BY y
y _1 _2 _3 _4 _5 _6 _7 _8 _9 _10 _11 _12
2020 0 0 3316 4385 1615 2701 4593 8064 46137 264346 188223 209434
2021 256569 253289 300563 93613 29052 5418 6045 5742 13008 73314 406347 307919
2022 560894 479282 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

Výsledná tabulka obsahuje matici s počty případů dle jednotlivých měsíců (sloupce) a roků (řádky).

Unpivot

Operace unpivot je inverzní operací k pivotu. Vstupem je datová matice, kde jsou hodnoty v několika sloupcích. Výstupem je tabulka, kde jsou hodnoty pouze v jednom sloupci a atribut reprezentující původní dělení do sloupců je vytvořen jako nový sloupec obsahující názvy původních sloupců.

V PostgreSQL lze tuto operaci provést pomocí unnest funkce. Pokud bychom chtěli transformovat tabulku s výsledky závodu ski, aby informace byla rozepsaná pomocí seskupujících sloupců jméno a ujeté kilometry, mohli bychom použít následující dotaz:


  

SELECT name,
UNNEST (ARRAY[8, 16, 25, 35, 43, 50]) AS stage,
UNNEST (ARRAY[km8, km16, km25, km35, km43, km50]) AS time
FROM ski 
ORDER BY name, stage
;
name stage time
ALEV Alvar Johannes 8 00:18:28.9
ALEV Alvar Johannes 16 00:39:03.4
ALEV Alvar Johannes 25 01:05:37.3
ALEV Alvar Johannes 35 01:31:17
ALEV Alvar Johannes 43 01:52:28.6
ALEV Alvar Johannes 50 02:09:54.8
BABA Naoto 8 00:18:21.3
... ... ...

Skyline query

Skyline query je dotaz, který vrací množinu záznamů, které jsou v nějakém smyslu lepší než ostatní (dominantní).

Typickým ukázkovým problémem je doporučovací algoritmus, který se snaží vyhledat nejlepší hotel/restauraci/video/... na základě multidimenzionálního srovnání. V případě restaurace by se mohlo jednat o kombinaci vzdálenosti, ceny a hodnocení. Otázku nelze vyřešit jednoduchým seřazením podle některé z dimenzí, protože nejlevější hotel, stejně jako nejbližší, nebo nejlépe hodnocený (z celé databáze) nebude to, co uživatele zajímá.

K řešení takových problémů byl navržen SKYLINE OF operátor jako rozšíření SQL. Ani v současnosti ale není operátor implementovaný v žádném z běžných DBMS.