Architektury a techniky DS

1. Architektury a techniky DS Cvičení č. 3 RNDr. David Žák, Ph.D. Fakulta elektrotechniky a informatikydavid.zak@upce.cz

2. Analytické funkce Stále vyšší využití databázových systémů pro evidování veškerých možných aktivit a rostoucí požadavky manažerů na sledování různých kritérií si vyžádaly zavedení dalších funkcí, které rozšířily kategorie agregačních funkcí SUM, COUNT, AVG, MIN, MAX. Tato nová skupina funkcí překračuje hranice ANSI SQL a je proto závislá na konkrétní databázové platformě. V tomto cvičení popsané příklady pracují na platformě Oracle (zhruba od verze 9). Uvedené příklady lze vyzkoušet na schématu A_HR.

3. Analytické funkce Uvědomíme-li si rozdíl mezi skupinovými funkcemi a analytickými funkcemi, pak výsledkem skupinového dotazu jsou pouze seskupené výsledky, např. SELECT ODDELENI_ID, COUNT(*) POCET_ZAM FROM ZAMESTNANCI WHERE ODDELENI_ID IN (20, 30) GROUP BY ODDELENI_ID; U analytických funkcí mohou být ve výsledku i „neseskupené“ sloupce, např. SELECT ZAMESTNANEC_ID, ODDELENI_ID, COUNT(*) OVER (PARTITION BY ODDELENI_ID) POCET_ZAM FROM ZAMESTNANCI WHERE ODDELENI_ID IN (20, 30) Analytické funkce jsou kalkulovány až po provedení všech spojení a omezení, těsné před provedením klauzule ORDER BY.

4. Analytické funkce V předchozím příkladě klauzule PARTITION BY je použita pro rozdělení dotazem vygenerovaných řádků do skupin (v našem případě např. podle ODDELENI_ID ). Některé analytické funkce podporují vymezení oknauvnitř klauzule PARTITION BY pro omezení počtu řádků, na něž se vztahují. Pokud okno není vymezeno, je analytická funkce kalkulována ze všech řádků v dané skupině. U funkcí SUM, COUNT, AVG, MIN, MAX je jejich výsledek nezávislý na pořadí řádků. Funkce LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE, LAST, LAST VALUE jsou závislé na pořadí řádků, tedy na jejich řazení klauzulí ORDER BY v syntaxi analytické funkce.

5. ROW_NUMBER( ) ROW_NUMBER( ) vrací pořadí řádku ve skupině záznamů. To je zvláště užitečné v případě reportů, kde má být každá skupina samostatně očíslována. V příkladu je ROW_NUMBER( ) použitok očíslování řádku ve skupině dané číslem oddělení pro oddělení s čísly 10 a20 v pořadí dle data nástupu. SELECT ZAMESTNANEC_ID, ODDELENI_ID, DATUM_NASTUP, ROW_NUMBER( ) OVER (PARTITION BY ODDELENI_ID ORDER BY DATUM_NASTUP NULLS LAST) PORADI FROM ZAMESTNANCI WHERE ODDELENI_ID IN (20, 30) ORDER BY ODDELENI_ID, PORADI;

6. ROW_NUMBER, RANK a DENSE_RANK Tyto funkce vrací celá čísla v závislosti na pořadí řádků. RANK a DENSE_RANK obě vrací pořadí hodnoty v daném řádkuv závislosti na hodnotě v nějakém sloupcinebo hodnotě výrazu. V případě nerozhodnosti mezi 2 záznamy na pozici N, RANK vyhlásí 2 pořadí N a přeskočípozici N+1, další řádek pak označí pozicí N+2. DENSE_RANK v takovém případě vyhlásí 2 pořadí N, ale nepřeskočí pozici N+1 (tedy další řádek bude s pozicí N+1). Příklad ukazuje využití obou funkcí RANK a DENSE_RANK. Rozdíl je zřejmý na posledním řádku, neboť na předchozí 3. pozici v oddělení s iODDELENI_ID=60 mají 2 zaměstnanci shodný plat. SELECT ZAMESTNANEC_ID, ODDELENI_ID, MZDA, RANK() OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA DESC NULLS LAST) RANK, DENSE_RANK() OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA DESC NULLS LAST) DENSE_RANK FROM ZAMESTNANCI WHERE ODDELENI_ID IN (20, 60) ORDER BY 2, RANK;

7. LEAD, LAG Funkce LEAD má schopnost kalkulovat výraz na základě hodnot ve sloupcích některého z následujících řádků (zobrazených po daném řádku) a vrátit hodnotu do daného řádku. Obecná syntaxe LEAD je: LEAD (<sql_expr>, <offset>, <default>) OVER (<analytic_clause>) <sql_expr> je výraz kalkulovaný z následujících řádků<offset> kladné celé číslo, defaultně 1 (index následujícího řádku vzhledem k řádku aktuálnímu)<default> je hodnota vracená v případě, kdy <offset> ukazuje na řádek mimo vybranou skupinu. Syntaxe LAG ie obdobná, jen se OFFSET vztahuje na předchozí řádky. SELECT ZAMESTNANEC_ID, ODDELENI_ID, MZDA, LEAD(MZDA, 1, 0) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA DESC NULLS LAST) NEXT_LOWER_SAL, LAG(MZDA, 1, 0) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA DESC NULLS LAST) PREV_HIGHER_SAL FROM ZAMESTNANCI WHERE ODDELENI_ID IN (20, 30) ORDER BY ODDELENI_ID, MZDA DESC;

8. FIRST VALUE, LAST VALUE Funkce FIRST_VALUE vybere první záznamze skupiny po provedení ORDER BY. Obecná syntaxeje: FIRST_VALUE(<sql_expr>) OVER (<analytic_clause>) Výraz <sql_expr> je vyhodnocen na sloupcích vybraného prvního záznamu a výsledek je vrácen. Funkce LAST_VALUE pracuje obdobně a vybírá poslední záznam ze skupiny. Příklad zjišťuje, kolik dní po nástupu prvního pracovníka na oddělení nastoupili ostatní pracovníci. SELECT ZAMESTNANEC_ID, ODDELENI_ID, DATUM_NASTUP, DATUM_NASTUP – FIRST_VALUE(DATUM_NASTUP) OVER (PARTITION BY ODDELENI_ID ORDER BY DATUM_NASTUP) DNU_MEZIDOBI FROM ZAMESTNANCI WHERE ODDELENI_ID IN (20, 30) ORDER BY ODDELENI_ID, DNU_MEZIDOBI;

9. FIRST a LAST Funkce FIRST je užívána ve velmi speciálních situacích. Předpokládejme, že v rámci dané skupiny máme několik záznamů s prvním pořadím. Nyní chceme aplikovat agregační funkce na všechny záznamy s prvním pořadím. Funkce KEEP FIRST toto umožní. Obecná syntaxe je: Function( ) KEEP (DENSE_RANK FIRST ORDER BY <expr>) OVER (<partitioning_clause>) FIRST a LAST jsou jediné funkce, které se odchylují od obecné syntaxe analytických funkcí. Nemajíklauzuli ORDER BY uvnitř klauzule OVER, ani nepodporují vymezení okna pomocí window klauzule. Řazení realizované ve FIRST a LAST je vždy DENSE_RANK. Příklad ukazuje využití funkce FIRST (funkce LAST se používá obdobně) pro porovnání platu zaměstnance s průměrným platem zaměstnanců přijatých v prvním roce. SELECT ZAMESTNANEC_ID, ODDELENI_ID, EXTRACT (YEAR FROM DATUM_NASTUP) ROK_NASTUPU, MZDA, TRUNC(AVG(MZDA) KEEP (DENSE_RANK FIRST ORDER BY EXTRACT (YEAR FROM DATUM_NASTUP))OVER (PARTITION BY ODDELENI_ID) ) AVG_MZDA_1_ROK FROM ZAMESTNANCI WHERE ODDELENI_ID IN (20, 50) ORDER BY ODDELENI_ID, ROK_NASTUPU, ZAMESTNANEC_ID;

10. Specifikace Window klauzule Některé analytické funkce (AVG, COUNT, FIRST_VALUE, LAST_VALUE, MAX, MIN, SUM) mohou obsahovat window klauzulipro další vyčlenění záznamů dané PARTITION a následné aplikování analytických funkcí na tyto vyčleněné záznamy. Celé chování window klauzule je dynamické. Obecná syntaxe <window_clause> je:[ROW or RANGE] BETWEEN <start_expr> AND <end_expr> <start_expr> může být jedna z následujícíchUNBOUNDED PRECEDINGCURRENT ROW <sql_expr> PRECEDING nebo FOLLOWING. <end_expr> může být jedna z následujících možností UNBOUNDED FOLLOWING CURRENT ROW <sql_expr> PRECEDING nebo FOLLOWING kde <sql_expr>hovořící o počtu řádků před či za aktuálním řádkem u ROW type okna.

11. Specifikace Window klauzule Do okna může nebo nemusí být zahrnut aktuální řádek dle hodnot v <start_expr> či <end_expr>. Startovní bodnemůže následovat za koncovým bodem okna. V případě, kdy některý konec okna není definován, je defaultní hodnota UNBOUNDED PRECEDING pro <start_expr> a UNBOUNDED FOLLOWING pro <end_expr>. Jestliže konečným bodem je aktuální řádek, může být uvedena pouze syntaxe startovního bodu[ROW or RANGE] [<start_expr> PRECEDING or UNBOUNDED PRECEDING ] Pro analytické funkce s oknem typu ROW je obecná syntaxe : Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2,..> ROWS BETWEEN <start_expr> AND <end_expr>) nebo Function( ) OVER (PARTITON BY <expr1> ORDER BY <expr2,..> ROWS [<start_expr> PRECEDING or UNBOUNDED PRECEDING]

12. Klauzule Window typu ROW Příklad – pro každého zaměstnance vrací průměr mzdy 3 zaměstnanců daného oddělení se mzdou nejblíže nižší danému zaměstnanci a průměr mzdy daného a 3 nejblíže více placených zaměstnanců na stejném oddělení jako daný zaměstnanec. SELECT ODDELENI_ID, ZAMESTNANEC_ID, MZDA, trunc(avg(MZDA) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)) PRUMER_3_NIZSI, trunc(avg(MZDA) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING))PRUMER_DANY_3_VYSSI FROM ZAMESTNANCI WHERE ODDELENI_ID IN (20, 30) ORDER BY ODDELENI_ID, MZDA;

13. Klauzule Window typu RANGE Pro okno typu RANGE je obecná syntaxe analogická s okny typu ROW. Důležité je si uvědomit, že velikost okna z hlediska počtu řádků se může měnit, neboť důležité je, zda kontrolovaná hodnota leží v požadovaném intervalu.. Příklad – pro každého zaměstnance (v aktuálním řádku) vrací počet zaměstnanců, kteří mají mzdu o 10 a víceprocent menší, než je plat aktuálního zaměstnance, a kteří mají o 10% až 50% vyšší mzdu. SELECT ODDELENI_ID, ZAMESTNANEC_ID, MZDA, COUNT(*) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA RANGE BETWEEN UNBOUNDED PRECEDING AND (MZDA*0.1) PRECEDING) o_10pct_MENE, COUNT(*) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA RANGE BETWEEN (MZDA*0.1) FOLLOWING AND (MZDA*0.5) FOLLOWING) o_10_50_pct_VICE FROM ZAMESTNANCI WHERE ODDELENI_ID IN (20, 30) ORDER BY ODDELENI_ID, MZDA

14. Cvičení č. 3 - zadání • Nad tabulkou LIDE ve schématu A_CLOVEK řešte pomocí analytických funkcí: • Vytvořte pohled jmena_pocet se sloupci (id, jmeno, prijmeni, pocet_jmen) pro zjištění počtu osob, které mají stejné jméno jako daná osoba. • Vytvořte pohled sourozenci_odstup se sloupci (id, jmeno, prijmeni, id_otce, id_matky, narozen, odstup, poradi) pro zjištění rozdílů mezi daty narození po sobě jdoucích vlastních sourozenců v kalendářních dnech a pořadí, jak byli sourozenci narozeni. • Vytvořte pohled bratri_pocet_nejstarsi se sloupci (id, jmeno, prijmeni, id_otce, id_matky, narozen, bratru, starsi_jmeno, nejstarsi_jmeno) pro zjištění počtu vlastních bratrů pro všechny muže a jména nejblíže staršího a nejstaršího z nich. • Vytvořte pohled vnoucata_pocet_starsich se sloupci (id, jmeno, prijmeni, id_babicky, narozen, vnoucata_3r_starsi_pocet, vnoucata_3r_starsi_vek) pro zjištění počtu ostatních vnoučat Boženy Malé, která jsou o 3 roky starší než dané vnouče a průměrný věk s přesností na jedno desetinné místo těchto o 3 roky starších vnoučat. • Vybrané 2 příklady řešte také klasicky, bez použití analytických funkcí. Tyto pohledy označte příponou _klas za původním názvem pohledu.

15. Doplnění hierarchických dotazů • Pro jednotlivé záznamy můžete také získat cestu od nejvyššího záznamu (jak to znáte třeba ze souborového systému) nebo řadu dalších informací: • Funkce SYS_CONNECT_BY_PATH vrací cestu v hierarchii k aktuálnímu záznamu. • Klauzule CONNECT_BY_ROOT vrací hodnotu z příslušného záznamu nejvyšší úrovně (tj. například nejvyššího manažera). • Pokud byste chtěli výstup z dotazu použít pro zobrazení ve formě rozbalovací hierarchie tak, jak to třeba dělá u souborů Windows Explorer, bude se vám hodit i pseudosloupec CONNECT_BY_ISLEAF, který určuje, zda je aktuální záznam na poslední úrovni hierarchie (CONNECT_BY_ISLEAF=1) nebo zda má podřízené záznamy (CONNECT_BY_ISLEAF=0).

16. Příklad hierarchických dotazů • SELECT lpad(' ',level*3)||PRIJMENI||' '||JMENO name, • SYS_CONNECT_BY_PATH(PRIJMENI, '/') path, • CONNECT_BY_ROOT PRIJMENI topmgr, • CONNECT_BY_ISLEAF isleaf, • level • FROM A_HR.ZAMESTNANCI • CONNECT BY MANAZER_ID = PRIOR ZAMESTNANEC_ID • START WITH MANAZER_ID is null • ORDER SIBLINGS BY PRIJMENI;

17. Otázky Děkuji za pozornost. http://www.orafaq.com/node/55