První náhled na dataset

Pro elementární prozkoumání datasetu lze využít agregační funkce, nejčastěji COUNT(), MIN(), MAX(). Agregační funkce se aplikují na všechny záznamy, případně na podmnožiny rozlišené dle atributů v GROUP BY části dotazu, je-li uvedena. Hvězdička v argumentu COUNT(*) je specialita pro spočtení všech záznamů, častěji bývá jako argument použit název atributu nebo výpočet.

Identifikace záznamu s extrémní hodnotou

Pomocí agregačních funkcí je sice možné najít konkrétní extrémní hodnotu, ale již není triviální najít záznam, ve kterém se extrémní hodnota nacházela. Pro řešení takové otázky se nabízí dva přístupy – dohledat zpětně hodnotu z výsledku selectu; místo agregační funkce vhodně použít řazení a omezení počtu záznamů ve výsledku.

K omezení počtu vrácených řádek je v Oracle speciální syntaxe FETCH NEXT x ROWS ONLY [SQL:2008] fungující jako ekvivalent klíčového slova LIMIT x [nestandardní], které se běžně vyskytuje v PostgreSQL, SQLite a jiných nejen relačních databázích. K seřazení výsledku se používá klíčové slovo ORDER BY za nímž následuje seznam atributů, podle kterých řadit. V ukázce je vidět 5 dnů s nejvyšším počtem spotřebovaných AG testů.

Seskupování záznamů dle data

K použití agregátních funkcí na určité podmnožiny datasetu slouží GROUP BY část příkazu. Za klíčovým slovem následuje posloupnost atributů či výrazů, podle jejiž hodnot budou záznamy rozčleněny do skupin.

V řešeném příkladu bude hledán průměrný počet nově nakažených za celý měsíc, aby byly eliminovány denní výkyvy a byl poskytnut přehled o delších časových úsecích. Oracle má specifickou syntax pro získání měsíce z datetime hodnoty. EXTRACT(MONTH FROM datum) zpracuje hodnotu v atributu datum a vrátí číslo 1-12 příslušného měsíce. Podobně lze pomocí klíčových slov YEAR, DAY, HOUR, MINUTE, SECOND vrátit i jiné složky data.

Do projekce v dotazu je možné při použití GROUP BY umístit pouze výrazy, podle kterých se záznamy seskupují, a výrazy, které umožní z množiny záznamů spočíst právě jednu hodnotu (typicky agregátní funkce). V příkladu je použita funkce AVG() vracející průměr z příslušných hodnot. Funkce ROUND() spočtenou hodnotu zaokrouhlí na zvolený počet desetinných míst pro lepší čitelnost.

Za zmínku stojí speciální výraz ORDER BY 3, kterým je dán pokyn pro seřazení výsledku dle třetího sloupce. Tímto způsobem se tedy lze vyhnout opakováním zdlouhavého výrazu.

Výpočet přírůstku v čase

Častou úlohou pojící se s časovými řadami bývá určení rozdílu dvou hodnot v následujících obdobích k identifikaci trendu. V SQL lze tohoto docílit v podstatě dvěma způsoby.

První možností je pomocí JOIN spojit datovou tabulku samu se sebou a využít spojující klauzuli k provedení zamýšleného časového posunu. Asi nejčastěji je při spojování k vidění kód a JOIN b ON a.fk_id = b.id pro prosté spojení dle cizího klíče a příslušného primárního klíče v druhé tabulce. Nicméně v spojení je možné použít jednak jiné operátory než shodnost, jednak i složitější výrazy namísto prostého názvu atributu. V příkladu je dosaženo spojení se záznamem přesně jeden den starým pomocí odečtení jednoho dne od datumu.

Alternativou je využití Window funkcí [SQL:2003], což jsou analytické funkce pracující s klíčovým slovem OVER v projekční části dotazu. Za tímto slovem následuje definice pro virtuální seřazení, či seskupení používaného datasetu ve zbytku dotazu. Samotnou použitou funkcí mohou být buď klasické agregační funkce (viz dále) nebo speciální funkce pracující pouze s klíčovým slovem OVER.

V řešeném příkladu lze hodnotu z předcházejícího řádku získat pomocí volání LAG(ag_testu, 1) OVER (ORDER BY datum), které vybere hodnotu ag_testu, která je posunuta o jeden řádek vůči současnému řádku při řazení dle datumu. Za zmínku stojí, že v tomto případě není nutné, aby celý dotaz byl řazen podle datumu. Dokonce je možné, aby řazení celého dotazu bylo jiné, než řazení uvnitř window funkce.

Výpočet klouzavého průměru

Při práci s daty často bývá problém se zohledněním výkyvů v důsledku krátkých cyklů – např. doprava je hustější v ranní a odpolední špičce, či v tomto případě se o víkendech méně testuje než ve všední dny. Jedna ze strategií k potlačení tohoto je sledované hodnoty zprůměrovat za určité vhodně zvolené období definované jako okolí současné zpracovávané hodnoty.

Vytvoření sedmidenního klouzavého průměru by v SQL bez window funkce bylo poměrně krkolomné. Například by bylo možné spojit tabulku 6x se sebou s různým odpočtem dnů podobně jako v předchozím příkladu, a následně sečíst sledované hodnoty z těchto tabulek a vydělit sedmi.

Použití window funkce pro vyčíslení 7denního průměru počtu nakažených je v ukázce. Oproti předchozímu příkladu je zde použita syntaxe ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING určující, že ve výpočtu mají být zohledněny vždy 3 předcházející, současný a 3 následující záznamy při řazení dle datumu.

Alternativou by bylo nevytvářet okno -3 až +3, ale -6 až současná řádka. To by mělo za výhodu, že i pro poslední záznam by bylo možné stanovit hodnotu klouzavého průměru a nebylo by potřeba čekat další 3 dny. Této variantě by odpovídala syntax BETWEEN 6 PRECEDING AND CURRENT ROW nebo též zkratkou jen jako 6 PRECEDING.

V definici intervalu window funkce lze namísto konkrétní hodnoty počtu řádek uvést klíčové slovo UNBOUNDED znamenající "až do začátku/konce". To lze elegantně využít k výpočtu kumulativního součtu atributu, např. počtu nakažených od začátku evidence. Nevím, jak jinak by šlo v SQL bez tohoto kumulativní součet získat.

K procvičení: vytvořte dotaz, který vypočte, ve kterém dni došlo k překročení milníku 1M potvrzených nákaz.

Využití pořadí záznamu v podmnožině

Jednou z užitečných funkcí je RANK(), který vrátí pořadí dané řádky při využití příslušného členění a řazení. V předchozím příkladu byla hledána extrémní hodnota počtu testů. S využitím následující techniky by bylo možné řešit i obecnější otázky jako např. v jakých dnech byla extrémní hodnota v jednotlivých letech.

V ukázce je použit blok OVER(PARTITION BY EXTRACT(year FROM datum) ORDER BY pocet_nakazenych DESC). Dochází tedy k sestavení virtuální tabulky (neboli okna) členěné podle roku a řazené sestupně dle počtu nakažených v daný den. RANK(), jak již bylo zmíněno, vrátí hodnotu 1 - 356 vyjadřující, na kolikátém místě se v daný rok tento počet nakažených umístil.

Jelikož není způsob jak výsledek window funkce použít ve filtraci příslušného dotazu, je tento dotaz obalen pomocným SELECT *, jehož jediný úkol je vybrat řádky, jejichž umístěni odpovídalo hodnotě 1, tedy maximu daného roku.

Tato otázka by šla opět řešit pomocí GROUP BY a zpětného napárování výsledku dotazu.

K procvičení: vytvořte alternativní dotaz bez použití window funkcí, který vyřeší tuto otázku. Je možné bez window funkcí najít pro každý rok 5 dnů s největšími přírůstky? Lze bez window funkce najít v každém roce dny s 10. nejvyšším přírůstkem?

Organizace zanořených dotazů

Při tvorbě složitějších analytických dotazů často dochází k tvorbě poddotazů do několika hierarchických úrovní. Takový dotaz může být obtížné přečíst a pochopit, co vlastně koná. Podobně jako v programování lze místo one-lineru mezivýsledek uložit do proměnné a pokračovat později, i SQL má techniku umožňující něco podobného.

Common Table Expressions (CTE) je v podstatě pojmenovaný dočasný result set, s kterým je možné v rámci dotazu pracovat jako s tabulkou. S touto technikou se lze setkat u hierarchických a rekurzivních dotazů, nicméně i pro v tomto ohledu obyčejný dotaz lze CTE uplatnit.

V příkladu je použit dotaz na výpočet plovoucího 7denního průměru. Pro přehlednost byl pomocí WITH avgs AS vytvořen CTE pojmenovaný jako "avgs" s tímto výpočtem. V následném dotazu, který hledá 5 dnů s největším mezidenním přírůstkem, je CTE odkazováno jako FROM avgs a s výsledkem poddotazu je pracováno jako s klasickou tabulkou.

Samotné vyhodnocení nejvyšších přírůstků využívá koncepty představné již dříve. Za zmínku stojí rozšířené volání LAG("7d_avg", 1, 0), které vrátí hodnotu na předchízejícím řádku a pokud takový řádek nebude existovat, vrátí defaultní hodnotu 0. Tímto způsobem se odstraní problém s NULL hodnotami u prvních dnů v datasetu.

Alternativou k CTE by mohlo být založit databázový pohled obsahující poddotaz nebo dočasnou tabulku obsahující výsledek poddotazu. Nevýhodou těchto řešení oproti CTE je vzniknutí nového objektu v databázi a nutnost provést více příkazů (CTE je součástí dotazu).

Komplexní příklad #1

Otázka: kolik bylo v jednotlivých měsících růstových, resp. klesajících dnů. Růstovým dnem se rozumí situace, kdy se 7denní průměr v daném dnu zvýšil oproti stejnému dni v předcházejícím týdnu.

Analýza: z otázky přímo vyplývají dva koncepty, které se v řešení budou vyskytovat. Prvním z nich je kalkulace 7denního klouzavého průměru, což již bylo řešeno dříve pomocí window funkce za použití definice ROWS BETWEEN a obalení příslušnou aggregační funkcí. Druhým konceptem je srovnávání hodnoty oproti hodnotě stejného významu v časově předchozím záznamu. To bylo v textu výše demonstrováno pomocí window funkce LAG, případně vhodným použitím JOIN.

Ze zadání dále lze vyvodit požadavek na označení řádku dvěma různými popisky - růstový, či klesající. V zadání není explicitně řečeno, jak se zachovat u stagnujícího stavu, např. na začátku sledovaného období, kdy denní přírůstky byly nulové. V praxi to znamená, že bude potřeba spočtenou hodnotu rozdílu převést na hodnotu indikující sledovaný stav. K aplikaci této podmínečné logiky lze použít v projekci výraz CASE WHEN ... THEN ....

Za zvážení stojí 2 přístupy - vytvořit jeden stavový atribut nabývající např. texty "růst", "pokles", "stagnace"; či vytvořit 3 atributy (růst, pokles, stagnace) obsahující logickou hodnotu, zda situace v daný den nastala.

Posledním poznatkem je nutnost vytvořit agregace na úrovni kalendářních měsíců počítající dny, kdy nastala jedna ze sledovaných situací. Na mysl by tedy měla přijít agregační funkce COUNT a funkce pro práci s částmi datumů EXTRACT, která bude využita v GROUP BY klauzuli.

Komentář řešení: jelikož je dotaz rozsáhlý a vyžaduje několik mezivýpočtů, volím pro čitelnost vynesení poddotazů jako CTE. Pokud je potřeba v jednom dotazu uvést vícero CTE, klíčové slovo WITH je použito pouze jednou a jednotlivé CTE jsou oddělené čárkou.

Výpočet avgs je shodný jako v předchozím textu. U nastavení okna pro klouzavý průměr volím ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING, tedy každou hodnotu průměruji pomocí 3 časově předcházejících a 3 časově následujících hodnot k vyhlazení týdenních cyklů. Alternativou by bylo uvažovat předcházejících 6 hodnot, ale to ve výsledku ovlivní jen zařazení hraničních hodnot do příslušných měsíců.

Výpočet states je podobný jako hlavní dotaz v předchozím textu. Původní atribut diff není zde v projekci explicitně zmíněn, ale jeho výpočet je použit v CASE WHEN bloku k přepsání kladných hodnot na text +++, záporných na --- a zbylých (nula) na x.

Výpočet by_months by sám o sobě mohl být odpovědí na řešenou otázku, jelikož seskupuje výsledek výpočtu states dle roku-měsíce a počítá výskyty různých stavů. Každý záznam odpovídá kombinaci roku-měsíce a stavu, např. 2021, 1, '+++', 12. I když tento formát není špatný, pro další zpracování se může hodit spíše forma podobné kontingenční tabulky v tabulkovém procesoru, kdy v jednom záznamu by byla jak informace o počtu růstových, tak klesajících dnů.

K transformaci tabulky lze použít Oracle nativní operaci PIVOT [nestandardní]. V samotném příkazu se udává, jaké hodnoty má zavést jako samostatné sloupce výsledku FOR state IN ('+++', '---'), tedy hodnoty růstu a poklesu (nikoliv stagnace) z atributu state. Kromě toho je nutné uvést, jakou hodnotu má do nových sloupců dosadit. Zde MAX(c) je použit kvůli tomu, že hodnoty jsou již předpočítané z předchozí agregace. Zbylé položky projekce, tedy y a m jsou implicitně chápány jako atributy, které PIVOT používá ke grupování výsledných záznamů.

V jiných systémech nemusí existovat ekvivalent PIVOT a je nutné použít jako alternativu CASE nebo FILTER [SQL:2003] na úrovni projekce viz 「alternativy a matice kompatibility」.

Komplexní příklad #2

Otázka: jaká byla nejdelší období růstu, resp. poklesů denních nákaz? Uvažuje se opět rozdíl 7denních průměrů stejného dnu v po sobě následujících týdnech.

Analýza: otázka vychází ze stejného výpočtu jako příklad #1. Lze tedy využít výpočet předcházející seskupení záznamů podle roku-měsíce.

Samotný problém spočívá v určení, kolik předchozích dnů vůči současnému mělo stejný stav, tj. růst, pokles, či stagnace. Zároveň je potřeba najít, kdy momentální období končí. V ideálním světě by bylo možné pomocí syntaxe nastavení velikosti okna window funkce nějakou podmínkou požadovat, aby okno takový interval samo maximalizovalo a následně pak stačilo jen zavolat funkci COUNT. Mám za to, že taková možnost bohužel neexistuje ani v SQL standardu, ani jako nestandardní funkce běžných relačních databází.

Je tedy potřeba přistoupit k řešení trikem. Se stávajícími nástroji je možné identifikovat dny, ve kterých dojde ke změně trendu, a vyfiltrovat je do pomocné tabulky. Následně bude možné pro každý den zlomu najít nejbližší další den zlomu, čímž bude vymezen trendový interval. Se znalostí intervalů je triviální spočíst jejich velikost v počtu dnů a vhodně seřadit.

Komentář řešení: výpočty klouzavých průměrů avgs a trendových stavů v daný den states jsou přejaté z předchozího příkladu.

Výpočet changes slouží k nalezení změny trendu pomocí logické podmínky, že hodnota state na předcházející řádce je odlišná od té, na současném řádku LAG(state, 1) OVER(ORDER BY datum) <> state. Tím, že není uveden ELSE blok, bude většina záznamů obsahovat NULL v tomto sloupci.

Výpočet state_durations nejprve vyfiltruje záznamy, které mají vyplněn odvozený atribut prev z předchozího výpočtu a tedy jsou hraniční při změně trendu. Následně pomocí funkce LEAD nalezne další záznam s vyplněnou hodnotou a spočte jejich časový rozdíl v dnech. Jako konec intervalu datum_to je určen den předcházející nalezené hodnotě. (pozn. místo LEAD by bylo možné použít LAG s zápornou hodnotou -1 v argumentu, případně s obráceným řazením)

Samotný dotaz pouze seřadí předchozí výpočet a vyřadí neukončené intervaly, tedy nejnovější aktivní trend v datech. Zajímavým okem pozorovatelným výstupem je, že se zdá, že může existovat roční cyklus ohledně nejdelších rostoucí a klesající intervalů – dlouhé růsty začínají na konci srpna; dlouhé poklesy začínají v první půlce dubna.