1 / 17

Architektury a techniky DS

Architektury a techniky DS. Cvičení č. 3 RNDr. David Žák, Ph.D . Fakulta elektrotechniky a informatiky david.zak @ upce.cz. Analytické funkce.

cadee
Download Presentation

Architektury a techniky DS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related