Datová analýza v SQL databázích

Spousta datových analýz začíná exportem dat z relační databáze. Data lze ale často analyzovat přímo v SQL a není potřeba vždy sahat po knihovnách typu pandas. Ve výsledku tak analýza přímo v databázi může být snazší a v závislosti na použité databázi i rychlejší.

Na příkladech z praxe si ukážeme GROUP BY a agregace, WINDOW funkce, několik triků a hlavně jak to celé efektivně zkombinovat a jak přemýšlet i nad složitými dotazy. Doporučím také několik databází, které jsou na tyto operace vhodné (a to i z hlediska ceny).

Komu je přednáška určena?
Pro všechny, kteří mají v SQL databázi nějaká data, která je potřeba alespoň jednou za čas nějakým způsobem analyzovat. Je vyžadována základní znalost konceptů relačních databází a základy dotazování.

Přednáška zazněla v rámci Barcamp 2.0 v Plzni: https://barcamp20.cz

Transcript

1. infrastruktura webová řešení marketing Datová analýza v SQL databázích Jan Smitka https://lynt.cz/
2. Proč analyzovat data v databázi? • Pokud už data jsou v databázi, není nutné je exportovat. • Některé operace jsou často snazší, než u jiných nástrojů. • Rychlost (podle použité databáze). 2 Pokud chci provést datovou analýzu nad operačními daty nějaké aplikace, tak její data pravděpodobně budou uložená v nějaké databázi a mohu data analyzovat přímo tam. Může to být rychlejší, než data stahovat a používat nějaký jiný nástroj či knihovnu, např. pandas v Pythonu.
3. databáze = databázový so ft ware 3 V průběhu přednášky budu často používat pojem „databáze“. Nebudu tím však myslet žádnou konkrétní sadu dat, ale databázový so ft ware (někdy také nazývaný engine).
4. Standard SQL každá databáze implementuje „po svém“ 4 Databázových so ft warů existuje mnoho. Vycházejí ze stejného standardu, ale vždy mají své speciality, ať již z důvodů historických, nebo osobních důvodů autorů. Postupy, které zde budu ukazovat, vycházejí ze standardu a měly by tedy fungovat ve všech relačních SQL databázích. Je dobré ale vždy nahlédnout do dokumentace na konkrétní syntaxi či omezení.
5. Agregační funkce a GROUP BY 5
6. Agregační funkce 6 SUM SUM Agregace redukují počet řádků ve výsledku. Seskupují řádky podle určitého klíče (např. stejná hodnota v uvedeném sloupci) a aplikují na ně nějakou agregační funkci, například výpočet sumy.
7. COUNT(<expr>) 7 Syntaxe je poměrně jednoduchá. Je dobré si uvědomit, že v závorce nemusí být jen název sloupce, ale i libovolný výraz.
8. region_sales 8 month region order_count order_value 2021-01 Praha 97 32 742,00 Kč 2021-01 Středočeský 63 90 946,00 Kč 2021-01 Jihočeský 77 34 785,00 Kč 2021-01 Plzeňský 51 37 316,00 Kč 2021-01 Karlovarský 50 44 470,00 Kč … … … … 2021-02 Praha 9 16 990,00 Kč 2021-02 Středočeský 23 16 921,00 Kč 2021-02 Jihočeský 15 22 209,00 Kč 2021-02 Plzeňský 17 24 473,00 Kč 2021-02 Karlovarský 24 15 768,00 Kč … … … … Pro demonstraci SQL dotazů budeme potřebovat vzorová data. V tabulce máme uložené fiktivní prodeje po měsících a jednotlivých krajích. Pro každý kraj/měsíc máme uložený počet objednávek a celkovou hodnotu objednávek.
9. SELECT COUNT(order_count) FROM region_sales 9 count 336 Nejjednodušší agregací je použití funkce bez GROUP BY. Ve výsledku tak bude jen jeden řádek s agregovanou hodnotou. V tomto případě je použitá funkce COUNT, která vrací počet nenulových (IS NOT NULL) hodnot v uvedeném sloupci. Agregační funkce ignorují hodnoty NULL, není-li to u nich uvedeno jinak. To je důležité například pro výpočet průměru, proto nepoužívejte pro chybějící hodnoty 0, -1 či podobné hodnoty, jinak by tyto hodnoty zkreslovaly výsledek. Bylo by také možné v závorce uvést * - v takovém případě by funkce vrátila celkový počet řádků bez ohledu na hodnoty NULL.
10. COUNT(CASE WHEN order_count > 100 THEN order_count ELSE NULL END) 10 count 192 Funkci ale také můžeme předat i výraz - v tomto případě podmínku, která pro hodnoty vyšší než 100 vrátí počet objednávek, jinak NULL. Tím spočteme počet řádků, kde je počet objednávek vyšší než 100 (NULL se nezapočítávají). Tato filtrace vypadá možná zbytečně složitě, protože by podmínka šla zapsat do WHERE…
11. COUNT(order_count) AS total_orders, COUNT(CASE WHEN order_count > 100 THEN order_count ELSE NULL END) AS high_value_orders 11 total_orders high_value_orders 336 192 … tento zápis nám ale umožňuje podmínky kombinovat. Jedním dotazem tak můžeme zjistit celkový počet řádků s nenulovou objednávkou, tak počet řádků, kdy byl počet objednávek vyšší než 100. Touto technikou můžeme v SQL vytvářet kontingenční tabulky. Pokud bychom například chtěli v řádcích kraje a ve sloupcích měsíce, použili bychom dotaz s 12 agregačními funkcemi - každá pro jeden měsíc, jako argument by byla zadána podmínka, která vrátí hodnotu jen v případě, že jde o řádek pro měsíc odpovídající danému sloupci. V opačném případě by vrátila NULL.
12. SELECT month, SUM(order_count) AS orders FROM region_sales GROUP BY month 12 month orders 2021-04 1114 2022-03 920 2022-09 2267 2021-12 2875 2021-06 2245 … … Dotaz spočte celkový počet objednávek ve všech krajích podle měsíců. Povšimněte si, že řádky nejsou ve výsledku seřazeny - GROUP BY ve většině databází neřadí řádky výsledku a negarantuje jejich pořadí na výstupu. Je to proto, že databáze může dotaz zpracovávat paralelně.
13. SELECT month, SUM(order_count) AS orders FROM region_sales GROUP BY 1 ORDER BY 1 13 month orders 2021-01 869 2021-02 227 2021-03 852 2021-04 1114 2021-05 1731 … … Chceme-li výsledky seřadit, musíme přidat ORDER BY. Nemusíme zde psát názvy sloupců, ale můžeme se odkázat číslem na výraz uvedený v SELECT. V tomto případě 1 odpovídá sloupci month, 2 odpovídá výrazu SUM(order_count).
14. Fáze dotazu 1. WHERE 2. GROUP BY 3. HAVING - umožňuje filtrovat dle agregovaných hodnot 4. ORDER BY 5. LIMIT + OFFSET 14 Pokud bychom chtěli i výsledek filtrovat a zjistit tak, v jakých měsících jsme měli určitý počet objednávek, nemůžeme na to použít WHERE. Podmínky v něm uvedené se totiž aplikují před samotnou agregací a omezují tak, jaké řádky se budou agregovat. Pro agregaci nad výsledkem agregace slouží klauzule HAVING. Pozor na to, že se aplikují skutečně až nad výsledkem agregací a databáze typicky nemohou použít indexy (nemá-li optimalizaci, která takové podmínky přesune do WHERE). Proto bychom jej měli používat skutečně jen v souvislosti s agregacemi, i když u dotazů bez agregací jsou výsledky stejné, jako kdybychom podmínku zapsali do WHERE.
15. SELECT month, SUM(order_count) AS orders FROM region_sales GROUP BY 1 HAVING SUM(order_count) > 2500 15 month orders 2022-11 3683 2022-12 3636 Dotazem s HAVING tedy snadno získáme měsíce, ve kterých byl celkový počet objednávek ve všech krajích > 2 500. Některé databáze umožňují odkázat se na agregaci v SELECT jejím aliasem - v tomto případě orders.
16. SELECT month, region, SUM(order_count) FROM region_sales GROUP BY ROLLUP( month, region ) 16 month region orders 2021-01 Praha 97 2021-01 Jihomoravský 44 … … … 2021-01 NULL 869 … … … 2021-02 Praha 9 2021-02 Jihomoravský 14 … … … 2021-02 NULL 227 … … … NULL NULL 44893 V GROUP BY lze navíc využít další výrazy, které ovlivňují seskupování. Funkce ROLLUP slouží pro výpočet dílčích výsledků a celkového výsledku. V tomto případě provede seskupení podle dvojice month, region, poté jen podle month (region je NULL), a nakonec celkový součet (month i region jsou NULL).
17. SELECT month, region, SUM(order_count) FROM region_sales GROUP BY CUBE( month, region ) 17 month region orders 2021-01 Praha 97 2021-01 Jihomoravský 44 … … … 2021-01 NULL 869 … … … 2021-02 Praha 9 2021-02 Jihomoravský 14 … … … 2021-02 NULL 227 NULL Praha 3374 NULL Jihomoravský 3194 … … … NULL NULL 44893 Výraz CUBE funguje obdobně, ale seskupuje dle kombinací: •(month, region) •(month) •(region) • () - celkový součet jako bez GROUP BY
18. Další agregační funkce • COUNT(DISTINCT …) • AVG(…) • SUM(…) • MIN(…) • MAX(…) • Statistické funkce: STDDEV(…), CORR(X, Y), … 18 Další funkce lze dohledat v dokumentaci konkrétní databáze. Některé databáze mají například funkce, které hodnoty určují jen přibližně. COUNT má i variantu s DISTINCT, která spočítá počet unikátních hodnot. Databáze také typicky podporují různé statistické funkce. PostgreSQL například nabízí i funkce pro lineární regresi metodou nejmenších čtverců. Dokumentace je v tomto případě nejlepší zdroj.
19. WINDOW funkce 19
20. WINDOW funkce 20 SUM SUM SUM SUM WINDOW funkce neredukují počet řádků výsledku. Pro každý řádek vytvoří „okno“ s dalšími řádky dle zadaných kritérií (může být i seřazené) a provedou určitou operaci. U každého řádku se pak použije výsledek operace. Operací může být suma, průměr, první/poslední hodnota z okna či pozice řádku v rámci okna.
21. SUM(<expr>) OVER ( PARTITION BY <col1>, <col2> ORDER BY <col1>, <col2> {ROWS|RANGE} BETWEEN <start> AND <end> ) 21 (žádná část není povinná) Specifikace okna se udává v klauzuli OVER za názvem funkce. PARTITION BY je obdobou GROUP BY - do okna budou vybrány řádky se stejnými hodnotami uvedených sloupců či výrazů. ORDER BY umožňuje řádky v okně seřadit podle určitých sloupců či výrazů. Některé WINDOW funkce řazení vyžadují. ROWS/RANGE BETWEEN pak umožňují určit velikost okna - jaké řádky před aktuálním řádkem a po aktuálním řádku
22. SELECT month, region, order_count, SUM(order_count) OVER() AS total_orders FROM region_sales 22 month region order_count total_orders 2021-01 Praha 97 44893 2021-01 Jihomoravský 44 44893 2021-01 Jihočeský 77 44893 2021-01 Karlovarský 50 44893 2021-01 Vysočina 80 44893 … … … … Nejjednodušší variantou je OVER bez další specifikace. U každého řádku bude v tomto případě stejná hodnota - suma order_count ze všech řádků.
23. SELECT month, region, order_count, SUM(order_count) OVER( PARTITION BY month ) AS month_orders FROM region_sales 23 month region order_count month_orders 2021-01 Praha 97 869 2021-01 Jihomoravský 44 869 … … … … 2021-02 Praha 9 227 2021-02 Jihomoravský 14 227 … … … … Zvolíme-li PARTITION BY month, bude u každého řádku suma order_count všech řádků ze stejného měsíce. To se hodí například v případě, kdy bychom chtěli spočítat, kolik % objednávek v daném měsíci bylo v jakém kraji.
24. SELECT month, region, order_count, ROW_NUMBER() OVER( PARTITION BY region ORDER BY month ) AS row_num FROM region_sales 24 month region order_count row_num 2021-01 Praha 97 1 2021-01 Jihomoravský 44 1 … … … … 2021-02 Praha 9 2 2021-02 Jihomoravský 14 2 … … … … 2021-03 Praha 75 3 2021-03 Jihomoravský 34 3 … … … … Některé funkce vyžadují řazení. Takovou je například i ROW_NUMBER, která vrací pořadí řádku v rámci daného okna. V tomto případě bude u každého řádku uvedeno, o kolikátý měsíc pro daný kraj jde. Tato funkce má význam především v kombinaci s filtrováním: lze například vyfiltrovat první řádek z každé skupiny, nebo ten poslední - stačí k řazení ještě přidat DESC a hledat hodnotu 1.
25. SELECT month, region, order_count, LAG(order_count, 1) OVER( PARTITION BY region ORDER BY month ) AS prev FROM region_sales 25 month region order_count prev 2021-01 Praha 97 NULL 2021-01 Jihomoravský 44 NULL … … … … 2021-02 Praha 9 97 2021-02 Jihomoravský 14 44 … … … … 2021-03 Praha 75 9 2021-03 Jihomoravský 34 14 … … … … Funkce LAG také vyžaduje řazení a vrátí hodnotu z předchozího řádku v rámci okna. První řádek má NULL, protože před ním žádný řádek není. Druhý parametr udává, o kolik řádků zpět se má funkce vracet, výchozí je 1.
26. temperatures 26 measured_at temp_c 2022-01-01 00:00 20.28 2022-01-01 00:30 19.36 2022-01-01 01:00 21.91 2022-01-01 01:30 18.19 2022-01-01 02:00 20.58 2022-01-01 02:30 21.69 2022-01-01 03:00 20.23 2022-01-01 03:30 19.39 2022-01-01 04:00 21.15 2022-01-01 04:30 21.49 … … Pro demonstraci ROWS BETWEEN použijeme další tabulku - tentokrát s teplotou naměřenou v půlhodinových intervalech.
27. SELECT *, AVG(temp_c) OVER ( ORDER BY measured_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avg_temp_c FROM temperatures 27 measured_at temp_c avg_temp_c 2022-01-01 00:00 20.28 20.28 2022-01-01 00:30 19.36 19.82 2022-01-01 01:00 21.91 20.517 2022-01-01 01:30 18.19 19.82 2022-01-01 02:00 20.58 20.227 2022-01-01 02:30 21.69 20.153 2022-01-01 03:00 20.23 20.833 2022-01-01 03:30 19.39 20.437 … … … Typickým využitím je klouzavý průměr. V tomto případě se vždy vypočte z hodnot dvou předcházejících řádků a aktuálního řádku. ROWS BETWEEN se vždy naviguje podle pořadí řádků. Pokud by v nějaký čas měření chybělo, stejně by se použily dva předcházející řádky, nezávisle na čase měření.
28. AVG(temp_c) OVER ( ORDER BY measured_at RANGE BETWEEN '1 hour' PRECEDING AND '1 hour' FOLLOWING ) AS avg_temp_c 28 measured_at temp_c avg_temp_c 2022-01-01 00:00 20.28 20.517 2022-01-01 00:30 19.36 19.935 2022-01-01 01:00 21.91 20.064 2022-01-01 01:30 18.19 20.346 2022-01-01 02:00 20.58 20.52 2022-01-01 02:30 21.69 20.016 2022-01-01 03:00 20.23 20.608 2022-01-01 03:30 19.39 20.79 … … … RANGE BETWEEN oproti tomu pracuje s hodnotami ve sloupci, který je v ORDER BY. Sloupec pro řazení musí být právě jeden. Funkce pracuje typicky s číselnými hodnotami, nebo s časovými údaji. V tomto případě se vyberou řádky v jedné předchozí a v jedné následující hodině. Pokud by v takovém intervalu byl jen 1 řádek, „vypočte“ se průměr jen z něj.
29. SELECT *, AVG(temp_c) OVER hour_around AS avg_temp_c, MIN(temp_c) OVER hour_around AS min_temp_c, MAX(temp_c) OVER hour_around AS max_temp_c FROM temperatures WINDOW hour_around AS ( ORDER BY measured_at RANGE BETWEEN '1 hour' PRECEDING AND '1 hour' FOLLOWING ) ORDER BY measured_at 29 measured_at temp_c avg_temp_c min_temp_c max_temp_c 2022-01-01 00:00 20.28 20.517 19.36 21.91 2022-01-01 00:30 19.36 19.935 18.19 21.91 2022-01-01 01:00 21.91 20.064 18.19 21.91 2022-01-01 01:30 18.19 20.346 18.19 21.91 … … … … … Pokud chceme v jednom dotaze použít více WINDOW funkcí se stejnou specifikací okna, nemusíme je opakovat. V klauzuli WINDOW si můžeme připravit pojmenovanou definici okna a tu použít. V některých databázích může být potřeba kolem názvu okna uvést závorky.
30. Příklady BETWEEN 1. UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 2. UNBOUNDED PRECEDING AND CURRENT ROW 3. 5 PRECEDING AND 1 PRECEDING 4. 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW 30 Ve specifikaci okna nemusí být jen uzavřený interval z obou stran: 1. Interval může zahrnovat všechny řádky. To je i výchozí hodnota, pokud specifikace chybí. 2. Může také obsahovat všechny řádky od začátku až do aktuálního řádku - to je vhodné například pro výpočet kumulativní sumy. 3. Nemusí jít také o specifikaci předchozí + následující, ale lze dvakrát použít PRECEDING či FOLLOWING. 4. Aktuální řádek může být také z okna vyloučen a počítat tak například průměr jen z předchozího a následujícího řádku.
31. Další WINDOW fukce • Všechny běžné agregační funkce. • LEAD(…, <OFFSET>, <DEFAULT>) • FIRST_VALUE(…), LAST_VALUE(…), NTH_VALUE(…, <N>) • ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK() 31 Jako WINDOW funkce lze použít všechny běžné agregační funkce - nelze například použít funkce, které vracejí jen přibližné hodnoty. Opakem funkce LAG je funkce LEAD, která vrací hodnotu z následujících řádků. FIRST_VALUE/LAST_VALUE vrací hodnotu v prvním/posledním řádku okna, NTH_VALUE může vrátit například hodnotu ve 4. řádku. Podobně jako ROW_NUMBER funguje i funkce RANK. Funkce se liší v situaci, pokud mají dva řádky stejnou hodnotu. RANK vrátí pro oba stejné číslo, ROW_NUMBER vždy unikátní. DENSE_RANK pak pokračuje v číslování tam, kde přestal, RANK v sekvenci vynechá tolik čísel, kolik bylo řádků se stejnou hodnotou. PERCENT_RANK pak vrátí pořadí v % - hodnota 0 až 1.
32. Využití WINDOW funkcí • Klouzavý průměr. • Výpočet meziměsíčních a meziročních srovnání. • Kumulativní suma: např. z tabulky se skladovými pohyby lze vypočítat time-series s aktuálním počtem produktů na skladě. • Číslování řádků lze využít k hledání duplicit… 32
33. users 33 id email registered_at 1 raymond.boyce.1@ibm.com 2021-06-30 2 raymond.boyce.2@ibm.com 2021-03-18 3 raymond.boyce.3@ibm.com 2022-04-09 4 raymond.boyce.4@ibm.com 2021-04-14 5 raymond.boyce.5@ibm.com 2021-12-01 6 raymond.boyce.6@ibm.com 2022-10-17 7 raymond.boyce.7@ibm.com 2021-07-30 8 raymond.boyce.8@ibm.com 2021-05-25 9 raymond.boyce.9@ibm.com 2022-07-30 10 raymond.boyce.10@ibm.com 2021-11-17 … … … Ukážeme si hledání duplicit. Uvažujme tabulku uživatelů, u které jsme zapomněli nastavit unikátní klíč nad sloupcem pro e-mail a někteří uživatelů se nám zaregistrovali dvakrát.
34. SELECT *, ROW_NUMBER() OVER( PARTITION BY email ORDER BY registered_at ) AS row_num FROM users WHERE row_num > 1 34 Výsledek WINDOW funkce však nelze použít v podmínce WHERE, ani v podmínce HAVING. Dotaz do databáze skončí chybou.
35. FROM a JOIN lze zadat nejen z tabulek, ale i z funkce či poddotazu 35 Databáze mají často funkce pro generování sekvencí čísel či hodnot v časovém intervalu. Ty můžeme využít například v situacích, kdy chceme doplnit chybějící hodnoty v časových řadách - např. pokud chceme získat počet objednávek v jednotlivých dnech z tabulky objednávek, ale v některé dny nebyla provedena žádná objednávka. Pokud k takové sekvenci provedeme LEFT JOIN našich dat, nebudou nám při agregaci data za takové dny chybět.
36. SELECT . . . FROM ( SELECT . . . FROM . . . ) AS alias 36 V případě poddotazů můžeme místo názvu tabulky uvést vnořený dotaz v závorkách. Dotazu musíme vždy nastavit alias, pod kterým se na něj budeme odkazovat.
37. SELECT . . . FROM ( SELECT . . . FROM ( SELECT . . . FROM . . . ) AS result_1 ) AS result_2 37 Poddotazy můžeme i zanořovat do sebe.
38. SELECT t.* FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY registered_at ) AS row_num FROM users ) AS t WHERE t.row_num > 1 38 Pro hledání duplicitních registrací tedy v poddotazu nejprve vypočteme pořadí registrace se stejným e-mailem, poté vyfiltrujeme 2. a další registrace.
39. 39 id email registered_at row_num 1001 raymond.boyce.406@ibm.com 2022-08-12 2 501 raymond.boyce.501@ibm.com 2021-09-14 2 1002 raymond.boyce.501@ibm.com 2022-05-16 3 1003 raymond.boyce.503@ibm.com 2022-04-20 2 1004 raymond.boyce.582@ibm.com 2022-12-13 2 1005 raymond.boyce.610@ibm.com 2022-05-09 2 1009 raymond.boyce.715@ibm.com 2021-03-09 2 1006 raymond.boyce.715@ibm.com 2022-08-21 3 1007 raymond.boyce.752@ibm.com 2021-08-25 2 752 raymond.boyce.752@ibm.com 2021-08-28 3 772 raymond.boyce.772@ibm.com 2022-11-05 2
40. Common Table Expressions (CTEs) 40
41. WITH result_1 AS ( SELECT . . . FROM . . . ), result_2 AS ( SELECT . . . FROM result_1 ), result_3 AS ( SELECT . . . FROM result_2 ) SELECT . . . FROM result_3 41 Common Table Expressions umožňují v klauzuli WITH poddotazy dopředu nadefinovat a pojmenovat. V samotném dotazu se na CTE můžeme odkázat názvem stejně, jako by to byla tabulka či pohled v databázi. V CTE se můžeme také odkazovat i na jiné CTE a postupně tak výsledek dotazu transformovat do podoby, kterou potřebujeme. CTE mohou být také rekurzivní a odkazovat se na sebe sama. To umožňuje například pracovat se stromovými strukturami v databázi. Rekurzivní forma však není předmětem této přednášky. Srozumitelný popis najdete například v dokumentaci k PostgreSQL: Recursive Queries V ostatních databázích je jejich funkce i syntaxe podobná.
42. Každá tabulka ve WITH je mezivýsledek fáze dotazu 42 U analytických dotazů je dobré ke každé CTE přistupovat jako k jedné fázi dotazu. Lze je také mezi sebou spojovat pomocí JOIN. Kombinací WINDOW funkcí, agregací, filtrací a řazení můžeme výsledek postupně transformovat do podoby, kterou potřebujeme. Jde o podobný princip, jako kompozice funkcí ve funkcionálním programování.
43. WITH users_numbered AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY registered_at ) AS row_num FROM users_duplicate ) SELECT * FROM users_numbered WHERE row_num > 1 43 Takto vypadá dotaz na hledání duplicitních uživatelů s využitím CTE. Výsledek bude stejný.
44. 44 id email registered_at row_num 1001 raymond.boyce.406@ibm.com 2022-08-12 2 501 raymond.boyce.501@ibm.com 2021-09-14 2 1002 raymond.boyce.501@ibm.com 2022-05-16 3 1003 raymond.boyce.503@ibm.com 2022-04-20 2 1004 raymond.boyce.582@ibm.com 2022-12-13 2 1005 raymond.boyce.610@ibm.com 2022-05-09 2 1009 raymond.boyce.715@ibm.com 2021-03-09 2 1006 raymond.boyce.715@ibm.com 2022-08-21 3 1007 raymond.boyce.752@ibm.com 2021-08-25 2 752 raymond.boyce.752@ibm.com 2021-08-28 3 772 raymond.boyce.772@ibm.com 2022-11-05 2
45. WITH sales_grouped AS ( SELECT month, SUM(order_count) AS order_count FROM region_sales GROUP BY month ) SELECT month, order_count, 100 * order_count / SUM(order_count) OVER( PARTITION BY date_part('year', month) ) AS order_pct FROM sales_grouped ORDER BY month 45 1 2 Tímto dotazem lze vypočítat, kolik % objednávek jsme obdrželi v jakém měsíci. 1) Provedeme agregaci po měsících za všechny kraje. 2) Počet objednávek v měsíci vydělíme celkovým počtem objednávek v daném roce. Ten získáme pomocí SUM s PARTITION BY s výrazem, který z data vyextrahuje rok.
46. 46 month order_count order_pct 2021-01 869 4 2021-02 227 1.04 2021-03 852 3.92 2021-04 1114 5.13 2021-05 1731 7.97 2021-06 2245 10.33 2021-07 1845 8.49 2021-08 1539 7.08 2021-09 2198 10.12 2021-10 2460 11.32 2021-11 3768 17.35 2021-12 2875 13.23 2022-01 831 3.59 2022-02 236 1.02 … … … Dohromady 100%. Pozn.: zde vyjde 99.98, protože hodnoty jsou zaokrouhleny.
47. Ukládání výsledků v databázi 47 Výsledky dotazů můžeme i uložit v databázi pro pozdější vizualizaci či další zpracování. Máme několik možností, jak je uložit.
48. Materializované pohledy 48 CREATE [MATERIALIZED] VIEW <view_name> AS SELECT . . . REFRESH MATERIALIZED VIEW <view_name> Nad materializovaným pohledem lze vytvářet indexy. Pokud chceme uložit jen dotaz, ale nepotřebujeme mít uložená samotná data, můžeme v databázi vytvořit pohled - view. View se chová jako tabulka, která je jen ke čtení, a při výběru dat databáze vykoná uložený dotaz a vrátí aktuální výsledek - podobné poddotazu. Speciální variantou je materialized view. Ten spolu s dotazem uloží výsledek tak, jak vypadal v době vytvoření view. Uložená data pak můžeme dalším příkazem aktualizovat - uložený dotaz se provede znovu a původně uložená data se kompletně přepíší. Data jsou uložená stejně, jako kdyby to byla běžná tabulka, jen jsou opět jen ke čtení.
49. Vytvoření nové tabulky 49 CREATE TABLE new_result AS SELECT . . . Výsledná tabulka má stejné sloupce, jako výsledek dotazu. Z výsledku dotazu můžeme i vytvořit novou tabulku. Tabulka bude opět obsahovat data výsledku v době vytvoření tabulky, ale můžeme je pak i libovolně aktualizovat.
50. Vložení do existující tabulky 50 INSERT INTO existing_table(<col1>, <col2>, . . . ) SELECT . . . Pozice sloupců výsledků dotazů musejí odpovídat sloupcům uvedeným v INSERT. Tabulku také nemusíme vytvářet, ale můžeme výsledek dotazu vložit do jiné tabulky. To je efektivnější, než kdybychom si výsledek načetli do vlastní aplikace a pak posílali zpět, protože přenos dat proběhne kompletně na straně databázového serveru.
51. Vložení/update existující tabulky 51 MERGE INTO <table> AS <target_alias> USING (SELECT . . . ) AS <source_alias> ON <join condition> WHEN MATCHED AND <condition> THEN UPDATE SET <col_1> = <value>, . . . WHEN MATCHED AND <condition> THEN DELETE WHEN NOT MATCHED THEN INSERT VALUES(<values>) Pokud chceme nejen vkládat, ale i aktualizovat či mazat, můžeme použít příkaz MERGE. Ten v podstatě provede JOIN dvou tabulek (resp. tabulky a poddotazu) na základě uvedené podmínky. Pokud v cílové tabulce není odpovídající řádek, můžeme provést INSERT. Pokud řádky odpovídají, můžeme provést UPDATE či DELETE původního řádku. Klauzulí WHEN může být i více a u každé lze nadefinovat dodatečnou podmínku. Přesný popis opět najdete v dokumentaci použité databáze.
52. Jakou databázi použít? 52 Probrali jsme celou řadu možností, jak data v databázi zpracovávat. Logicky to vede k dotazu: jaká databáze je na tyto operace vhodná? Bude mi stačit MySQL, kterou už v projektu máme? Dobrou zprávou je, že všechny uvedené techniky podporují všechny v současnosti používané relační databáze. Mohou mít však různá omezení, kdy lze daná funkce použít. Může se také lišit rychlost vykonání dotazů. Ve výsledku jsou tak některé databáze vhodnější, než jiné.
53. Řádkové… 53 2021-01 14 2021-02 97 2021-03 105 2021-04 221 2021-05 151 ⋯ 2021-01 14 2021-02 97 2021-03 105 2021-04 221 2021-05 151 ₀ ₀ … či sloupcové? Základní rozdělení databází je na řádkové či sloupcové. Liší se formou uložení dat v úložišti: řádkové mají celé řádky za sebou v jednom datovém souboru, sloupcové mají data jednotlivých sloupců rozdělená do samostatných bloků. Analytické dotazy často zpracovávají všechny řádky (nebo celý jejich segment), ale typicky vyžadují jen podmnožinu sloupců. Díky tomu mohou sloupcové databáze načítat méně dat, než databáze relační. Zároveň jsou data uložená ve formě vektorů, takže na načtená data lze rovnou aplikovat vektorové instrukce, což vede k rychlejšímu zpracování. Sloupcové databáze jsou tedy obvykle výkonnější. Pokud bychom se vrátili k otázce vhodnosti MySQL: ano, lze jí použít, ale nebude tak efektivní, jako jiné databáze. Má totiž jen základní plánovač dotazů, který složité analytické dotazy nedokáže efektivně naplánovat a databáze je tak vykoná stylem „načti vše a dotaz spočti hrubou silou“. To se snaží napravit fork MariaDB, který ve verzi 11 kompletně přepracovala plánovač dotazů a vykonávají se tak rychleji. Jednotlivé verze také přidávají různé dílčí optimalizace, takže čím novější verze, tím rychlejší by mělo být zpracování dotazů.
54. 54 Pokud chcete databázi, ve které můžete mít transakční data a zároveň provádět analytické dotazy, zvolte PostgreSQL. Jde v současnosti o nejpokročilejší open-source řádkovou databázi s dlouhou historií a dobrým výhledem do budoucna. Má velmi dobrý plánovač dotazů, který si poradí i se složitými případy, implementuje mnoho užitečných funkcí (např. funkce pro práci s JSON, XML, fulltext, …) a dotazy umí vykonávat paralelně (pokud to dává smysl). Pokud jí zvolíte pro svůj projekt, vydrží vám pravděpodobně velmi dlouho. Díky svým kvalitám se také stala de-facto standardem: mnoho společností na ní staví svá řešení a deriváty (např. TimescaleDB, AlloyDB od Googlu apod.), často také implementují její dialekt či protokol pro komunikaci.
55. 55 ClickHouse je relativně nová sloupcová databáze. Je zaměřená primárně na analytické dotazy nad velkými daty. Data řadí na disku podle primárního klíče, podle kterého je i segmentuje do vhodných bloků. To vyžaduje trochu přemýšlení nad tím, jaký primární klíč bude vhodný, a jak data obecně strukturovat. V současnosti má ale pro analytické dotazy asi nejvyšší výkon ze všech dostupných relačních databází, které lze provozovat na vlastním hardware. Aktuální benchmarky: https://benchmark.clickhouse.com/ https://duckdblabs.github.io/db-benchmark/
56. 56 Zatím jsme probírali plnohodnotné databázové servery. Pokud byste chtěli něco menšího, můžete zvolit DuckDB. Jde o in-process sloupcovou databázi, kterou použijete jako knihovnu ve vlastním projektu. Data mohou být dočasně v paměti, nebo uložená na disku, podobně jako u SQLite. Klade velký důraz na paralelismus, efektivitu algoritmů a vektorové zpracování. Velkou výhodou je i to, že umí pracovat s externími daty - ať už CSV/JSON soubor na disku či v AWS S3, nebo se umí připojit třeba k PostgreSQL a přímo načítat data. Je ale nutné počítat s tím, že projekt je v poměrně rané fázi vývoje a zatím nevyšla stabilní verze. Kompatibilita datových souborů mezi verzemi je například garantována až od verze 0.10., která vyšla v únoru 2024.
57. 57 Pokud byste chtěli naopak něco většího a rovnou v cloudu, můžete sáhnout po Google BigQuery. Jde o sloupcovou databázi, která je poskytována jako serverless služba: neřešíte servery, alokace disků apod., jen nahrajete data a spouštíte dotazy. Služba je pak účtována podle objemu uložených dat a podle objemu dat, které musely načítat vaše dotazy. Velkou výhodou je, že pro databáze škáluje prostředky pro dotaz podle objemu dat. I když se tedy zdvojnásobí objem dat, bude dotaz trvat přibližně stejnou dobu, protože BigQuery alokuje více prostředků pro běh dotazu. Ani dotazy nad terabajty dat nejsou problém, BigQuery škáluje až do řádu petabajtů. Prvních 10 GB uložených dat a 1 TB dotazů je zdarma, pak se ceník pohybuje kolem 0.02 - 0.025 USD/měsíc za uložený GB a 6.25 - 10 USD za 1 TB dotazů. Máte-li desítky až menší stovky GB dat, je BigQuery poměrně levné.
58. 58 Amazon Athena Amazon Athena je obdoba BigQuery, akorát v AWS. Od BigQuery se liší především tím, že nemá vlastní úložiště - data načítá z externího zdroje, např. S3, či existující databáze. Jinak je princip fungování i ceník podobný jako u BigQuery. Díky vlastnímu optimalizovanému úložišti bývá BigQuery typicky rychlejší. I MS Azure má svou alternativu: Azure Synapse Analytics. Osobně s ní však nemám zkušenosti.
59. Závěr • Datové analýzy lze provést přímo v databázi pomocí agregací a WINDOW funkcí. • Pomocí subdotazů a CTEs je lze snadno kombinovat. • Výsledky lze snadno uložit přímo v databázi, lze je i slučovat s existujícími daty. 59
60. Díky za pozornost! 60 @jansmitka @jansmitka@fosstodon.org www.linkedin.com/in/jansmitka https://lynt.cz/