Architektury a techniky DSArchitektury a techniky DS
Cvičení č. 3Cvičení č. 3
RNDr. David Žák, Ph.D.RNDr. David Žák, Ph.D.Fakulta elektrotechniky a informatikyFakulta elektrotechniky a informatiky
Analytické funkceAnalytické funkceStále vyšší využití databázových systémů pro evidování veškerých možných 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 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, zavedení dalších funkcí, které rozšířily kategorie agregačních funkcí SUM, COUNT, AVG, MIN, MAX.COUNT, AVG, MIN, MAX.
Tato nová skupina funkcí překračuje hranice ANSI SQL a je proto závislá na 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 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 platformě Oracle (zhruba od verze 9). Uvedené příklady lze vyzkoušet na schématu schématu A_HR.A_HR.
Analytické funkceAnalytické funkceUvědomíme-li si rozdíl mezi skupinovými funkcemi a analytickými funkcemi, 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ř.pak výsledkem skupinového dotazu jsou pouze seskupené výsledky, např.
SELECT SELECT ODDELENI_IDODDELENI_ID,,COUNT(*) POCET_ZAMCOUNT(*) POCET_ZAMFROM ZAMESTNANCIFROM ZAMESTNANCIWHERE ODDELENI_ID IN (20, 30)WHERE ODDELENI_ID IN (20, 30)GROUP BY ODDELENI_IDGROUP BY ODDELENI_ID;;
U analytických funkcí mohou být ve výsledku i „neseskupené“ sloupce, např.U analytických funkcí mohou být ve výsledku i „neseskupené“ sloupce, např.
SELECT ZAMESTNANEC_ID, ODDELENI_ID, SELECT ZAMESTNANEC_ID, ODDELENI_ID, COUNT(*) OVER (PARTITION BY ODDELENI_ID) POCET_ZAMCOUNT(*) OVER (PARTITION BY ODDELENI_ID) POCET_ZAMFROM ZAMESTNANCIFROM ZAMESTNANCIWHERE ODDELENI_ID IN (20, 30)WHERE ODDELENI_ID IN (20, 30)
Analytické funkce jsou kalkulovány až po provedeníAnalytické funkce jsou kalkulovány až po provedenívšech spojení a omezení, těsné před provedením všech spojení a omezení, těsné před provedením klauzule ORDER BY.klauzule ORDER BY.
ODDELENI_IDODDELENI_ID POCET_ZAMPOCET_ZAM2020 223030 66
ZAMESTNANEC_IDZAMESTNANEC_ID ODDELENI_IDODDELENI_ID POCET_ZAMPOCET_ZAM201201 2020 22202202 2020 22114114 3030 66115115 3030 66116116 3030 66117117 3030 66118118 3030 66119119 3030 66
Analytické funkceAnalytické funkceV předchozím příkladě klauzule V předchozím příkladě klauzule PARTITION BY PARTITION BY je použita pro rozdělení dotazem je použita pro rozdělení dotazem vygenerovaných řádků do skupin (v našem případě např. podle ODDELENI_ID )vygenerovaných řádků do skupin (v našem případě např. podle ODDELENI_ID ). .
Některé analytické funkce podporují vymezení oknaNěkteré analytické funkce podporují vymezení okna uvnitř klauzule PARTITION uvnitř klauzule PARTITION BY pro omezení počtu řádků, na něž se vztahujíBY pro omezení počtu řádků, na něž se vztahují. . Pokud okno není vymezeno, je Pokud okno není vymezeno, je analytická funkce kalkulována ze všech řádků v dané skupině.analytická funkce kalkulována ze všech řádků v dané skupině.
U funkcíU funkcí SUM, COUNT, AVG, MIN, MAX SUM, COUNT, AVG, MIN, MAX je jejich výsledek nezávislý na pořadí je jejich výsledek nezávislý na pořadí řádkůřádků..
Funkce Funkce LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE, LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE, LAST, LAST VALUE LAST, LAST VALUE jsou závislé na pořadí řádků, tedy na jejich řazení klauzulí jsou závislé na pořadí řádků, tedy na jejich řazení klauzulí ORDER BY v syntaxi analytické funkceORDER BY v syntaxi analytické funkce. .
ROW_NUMBER( ) ROW_NUMBER( )
ROW_NUMBER( ) vrací pořadí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žito k očíslování řádku ve skupině dané číslem oddělení pro oddělení s čísly 10 a 20 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) PORADIFROM ZAMESTNANCIWHERE ODDELENI_ID IN (20, 30)ORDER BY ODDELENI_ID, PORADI;
ZAMESTNANEC_ID ODDELENI_ID DATUM_NASTUP PORADI201 20 17.2.1996 1202 20 17.8.1997 2114 30 7.12.1994 1115 30 18.5.1995 2117 30 24.7.1997 3116 30 24.12.1997 4118 30 15.11.1998 5119 30 10.8.1999 6
ROW_NUMBER, RANK a DENSE_RANKROW_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 řádku v závislosti na hodnotě v nějakém sloupci nebo 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 i ODDELENI_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_RANKFROM ZAMESTNANCIWHERE ODDELENI_ID IN (20, 60)ORDER BY 2, RANK;
ZAMESTNANEC_ID ODDELENI_ID MZDA RANK DENSE_RANK201 20 13000 1 1202 20 6000 2 2103 60 9000 1 1104 60 6000 2 2105 60 4800 3 3106 60 4800 3 3107 60 4200 5 4
LEAD, LAGLEAD, LAGFunkce LFunkce LEAD EAD má schopnost kalkulovat výraz na základě hodnot ve sloupcích některého z má schopnost kalkulovat výraz na základě hodnot ve sloupcích některého z následujících řádkůnásledujících řádků ( (zobrazených po daném řádkuzobrazených po daném řádku) a) a vrátit hodnotu do daného řádku vrátit hodnotu do daného řádku. . Obecná syntaxeObecná syntaxe LEAD LEAD je: je:
LEAD (<sql_expr>, <offset>, <default>) OVER (<analytic_clause>)LEAD (<sql_expr>, <offset>, <default>) OVER (<analytic_clause>)<sql_expr> <sql_expr> je výraz kalkulovaný z následujících řádkůje výraz kalkulovaný z následujících řádků<offset> <offset> kladné celé číslo, defaultně kladné celé číslo, defaultně 11 (index následujícího řádku vzhledem k řádku (index následujícího řádku vzhledem k řádku aktuálnímu)aktuálnímu)<default> <default> je hodnota vracená v případě, kdy je hodnota vracená v případě, kdy <offset> <offset> ukazuje na řádek mimo vybranou ukazuje na řádek mimo vybranou skupinuskupinu..
SyntaxeSyntaxe LAG i LAG ie obdobná, jen se OFFSET vztahuje na předchozí řádkye obdobná, jen se OFFSET vztahuje na předchozí řádky..
SELECT ZAMESTNANEC_ID, ODDELENI_ID, MZDA,SELECT ZAMESTNANEC_ID, ODDELENI_ID, MZDA,LEAD(MZDA, 1, 0) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA DESC NULLS LAST) LEAD(MZDA, 1, 0) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA DESC NULLS LAST) NEXT_LOWER_SAL,NEXT_LOWER_SAL,LAG(MZDA, 1, 0) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA DESC NULLS LAST) LAG(MZDA, 1, 0) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA DESC NULLS LAST) PREV_HIGHER_SALPREV_HIGHER_SALFROM ZAMESTNANCIFROM ZAMESTNANCIWHERE ODDELENI_ID IN (20, 30)WHERE ODDELENI_ID IN (20, 30)ORDER BY ODDELENI_ID, MZDA DESC;ORDER BY ODDELENI_ID, MZDA DESC;
ZAMESTNANEC_IDZAMESTNANEC_ID ODDELENI_IDODDELENI_ID MZDAMZDA NEXT_LOWER_SALNEXT_LOWER_SAL PREV_HIGHER_SALPREV_HIGHER_SAL201201 2020 1300013000 60006000 00202202 2020 60006000 00 1300013000114114 3030 1100011000 31003100 00115115 3030 31003100 29002900 1100011000116116 3030 29002900 28002800 31003100117117 3030 28002800 26002600 29002900118118 3030 26002600 25002500 28002800119119 3030 25002500 00 26002600
FIRST VALUEFIRST VALUE, , LAST VALUE LAST VALUE Funkce Funkce FIRST_VALUE FIRST_VALUE vybere první záznamvybere první záznam ze skupiny po provedeníze skupiny po provedení ORDER BY. ORDER BY. Obecná Obecná syntaxesyntaxe jeje::
FIRST_VALUE(<sql_expr>) OVER (<analytic_clause>)FIRST_VALUE(<sql_expr>) OVER (<analytic_clause>)VýrazVýraz <sql_expr> <sql_expr> je vyhodnocen na sloupcích vybraného prvního záznamu a výsledek je je vyhodnocen na sloupcích vybraného prvního záznamu a výsledek je vrácen. vrácen.
Funkce Funkce LAST_VALUE LAST_VALUE pracuje obdobně a vybírá poslední záznam ze skupiny. 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í Příklad zjišťuje, kolik dní po nástupu prvního pracovníka na oddělení nastoupili ostatní pracovníci.pracovníci.
SELECT ZAMESTNANEC_ID, ODDELENI_ID, DATUM_NASTUP,SELECT ZAMESTNANEC_ID, ODDELENI_ID, DATUM_NASTUP,DATUM_NASTUP – DATUM_NASTUP – FIRST_VALUE(DATUM_NASTUP) OVER FIRST_VALUE(DATUM_NASTUP) OVER (PARTITION BY ODDELENI_ID ORDER BY DATUM_NASTUP) (PARTITION BY ODDELENI_ID ORDER BY DATUM_NASTUP) DNU_MEZIDOBIDNU_MEZIDOBIFROM ZAMESTNANCIFROM ZAMESTNANCIWHERE ODDELENI_ID IN (20, 30)WHERE ODDELENI_ID IN (20, 30)ORDER BY ODDELENI_ID, DNU_MEZIDOBI;ORDER BY ODDELENI_ID, DNU_MEZIDOBI;
ZAMESTNANEC_IDZAMESTNANEC_ID ODDELENI_IDODDELENI_ID DATUM_NASTUPDATUM_NASTUP DNU_MEZIDOBIDNU_MEZIDOBI201201 2020 17.2.199617.2.1996 00202202 2020 17.8.199717.8.1997 547547114114 3030 7.12.19947.12.1994 00115115 3030 18.5.199518.5.1995 162162117117 3030 24.7.199724.7.1997 960960116116 3030 24.12.199724.12.1997 11131113118118 3030 15.11.199815.11.1998 14391439119119 3030 10.8.199910.8.1999 17071707
FIRST a LASTFIRST a LASTFunkceFunkce FIRST FIRST je užívána ve velmi speciálních situacíchje užívána ve velmi speciálních situacích. . Předpokládejme, že v rámci dané 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í 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. Funkcefunkce na všechny záznamy s prvním pořadím. Funkce KEEP FIRST KEEP FIRST toto umožnítoto umožní.. Obecná Obecná syntaxe jesyntaxe je::
Function( ) KEEP (DENSE_RANK FIRST ORDER BY <expr>) OVER Function( ) KEEP (DENSE_RANK FIRST ORDER BY <expr>) OVER (<partitioning_clause>)(<partitioning_clause>)
FIRST a LAST FIRST a LAST jsou jediné funkce, které se odchylují od obecné syntaxe analytických jsou jediné funkce, které se odchylují od obecné syntaxe analytických funkcí.funkcí. NemajíNemají klauzuli klauzuli ORDER BY ORDER BY uvnitř klauzuleuvnitř klauzule OVER OVER, ani nepodporují vymezení okna pomocí , ani nepodporují vymezení okna pomocí window klauzulewindow klauzule. . Řazení realizované veŘazení realizované ve FIRST a LAST FIRST a LAST je vždy je vždy DENSE_RANK. DENSE_RANK.
Příklad ukazuje využití funkce Příklad ukazuje využití funkce FIRSTFIRST (funkce LAST se používá obdobně) (funkce LAST se používá obdobně) pro porovnání pro porovnání platu zaměstnance s průměrným platem zaměstnanců přijatých v prvním roce.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,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 TRUNC(AVG(MZDA) KEEP (DENSE_RANK FIRST ORDER BY EXTRACT (YEAR FROM DATUM_NASTUP))OVER (PARTITION BY ODDELENI_ID) ) (PARTITION BY ODDELENI_ID) ) AVG_MZDA_1_ROKAVG_MZDA_1_ROKFROM ZAMESTNANCIFROM ZAMESTNANCIWHERE ODDELENI_ID IN (20, 50)WHERE ODDELENI_ID IN (20, 50)ORDER BY ODDELENI_ID, ROK_NASTUPU, ZAMESTNANEC_ID;ORDER BY ODDELENI_ID, ROK_NASTUPU, ZAMESTNANEC_ID;
ZAMESTNANEC_IDZAMESTNANEC_ID ODDELENI_IDODDELENI_ID ROK_NASTUPUROK_NASTUPU MZDAMZDA AVG_MZDA_1_ROKAVG_MZDA_1_ROK201201 2020 19961996 1300013000 1300013000202202 2020 19971997 60006000 1300013000122122 5050 19951995 79007900 50005000137137 5050 19951995 36003600 50005000141141 5050 19951995 35003500 50005000120120 5050 19961996 80008000 50005000133133 5050 19961996 33003300 50005000
SpecifikaceSpecifikace Window Window klauzuleklauzuleNěkteré analytické funkce (AVG, COUNT, FIRST_VALUE, LAST_VALUE, MAX, MIN, SUM) mohou obsahovat window klauzuli pro 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ích UNBOUNDED 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.
SpecifikaceSpecifikace Window Window klauzuleklauzuleDo okna může nebo nemusí být zahrnut aktuální řádek dle hodnot v <start_expr> či Do okna může nebo nemusí být zahrnut aktuální řádek dle hodnot v <start_expr> či <end_expr>. <end_expr>. SStarttartovní bodovní bod nemůže následovat za koncovým bodem oknanemůže následovat za koncovým bodem okna. . V případě, kdy některý V případě, kdy některý konec okna není definován, je defaultní hodnota konec okna není definován, je defaultní hodnota UNBOUNDED PRECEDING UNBOUNDED PRECEDING propro <start_expr> a UNBOUNDED FOLLOWING <start_expr> a UNBOUNDED FOLLOWING propro <end_expr>. <end_expr>.
Jestliže konečným bodem je aktuální řádekJestliže konečným bodem je aktuální řádek, , může být uvedena pouze syntaxe startovního může být uvedena pouze syntaxe startovního bodubodu[ROW or RANGE] [<start_expr> PRECEDING or UNBOUNDED PRECEDING ][ROW or RANGE] [<start_expr> PRECEDING or UNBOUNDED PRECEDING ]
Pro analytické funkce s oknem typu Pro analytické funkce s oknem typu ROW ROW je obecná syntaxe je obecná syntaxe ::
Function( ) OVER (PARTITIFunction( ) OVER (PARTITIOON BY <expr1> ORDER BY <expr2,..> ROWS N BY <expr1> ORDER BY <expr2,..> ROWS BETWEEN <start_expr> AND <end_expr>)BETWEEN <start_expr> AND <end_expr>)
nebonebo
Function( ) OVER (PARTITON BY <expr1> ORDER BY <expr2,..> ROWS Function( ) OVER (PARTITON BY <expr1> ORDER BY <expr2,..> ROWS [<start_expr> PRECEDING or UNBOUNDED PRECEDING][<start_expr> PRECEDING or UNBOUNDED PRECEDING]
Klauzule Klauzule Window Window typu ROWtypu ROW
Příklad –Příklad – pro každého zaměstnance vrací průměr mzdy 3 zaměstnanců daného oddělení 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 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ů placených zaměstnanců na stejném oddělení jako daný zaměstnanec.na stejném oddělení jako daný zaměstnanec.
SELECT ODDELENI_ID, ZAMESTNANEC_ID, MZDA,SELECT ODDELENI_ID, ZAMESTNANEC_ID, MZDA,trunc(avg(MZDA) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDAtrunc(avg(MZDA) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING))ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)) PRUMER_3_NIZSI, PRUMER_3_NIZSI,trunc(avg(MZDA) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA trunc(avg(MZDA) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING))ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING)) PRUMER_DANY_3_VYSSIPRUMER_DANY_3_VYSSIFROM ZAMESTNANCIFROM ZAMESTNANCIWHERE ODDELENI_ID IN (20, 30) WHERE ODDELENI_ID IN (20, 30) ORDER BY ODDELENI_ID, MZDA;ORDER BY ODDELENI_ID, MZDA;
ODDELENI_IDODDELENI_ID ZAMESTNANEC_IDZAMESTNANEC_ID MZDAMZDA PRUMER_3_NIZSIPRUMER_3_NIZSI PRUMER_DANY_3_VYSSIPRUMER_DANY_3_VYSSI2020 202202 60006000 950095002020 201201 1300013000 60006000 13000130003030 119119 25002500 270027003030 118118 26002600 25002500 285028503030 117117 28002800 25502550 495049503030 116116 29002900 26332633 566656663030 115115 31003100 27662766 705070503030 114114 1100011000 29332933 1100011000
Klauzule Klauzule Window Window typu RANGEtypu RANGEPro okno typu Pro okno typu RANGE RANGE je obecná syntaxe analogická s okny typuje obecná syntaxe analogická s okny typu ROW ROW. Důležité je si . 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 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..kontrolovaná hodnota leží v požadovaném intervalu..
Příklad –Příklad – pro každého zaměstnance (v aktuálním řádku) vrací počet zaměstnanců, kteří pro každého zaměstnance (v aktuálním řádku) vrací počet zaměstnanců, kteří mají mzdu o mají mzdu o 110 a více0 a více procent menší, než je plat aktuálního zaměstnance, a kteří mají o procent menší, než je plat aktuálního zaměstnance, a kteří mají o 1010%% až 50 až 50% % vyšší mzdu.vyšší mzdu.
SELECT ODDELENI_ID, ZAMESTNANEC_ID, MZDA, SELECT ODDELENI_ID, ZAMESTNANEC_ID, MZDA, COUNT(*) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA COUNT(*) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA RANGE BETWEEN UNBOUNDED PRECEDING AND (MZDA*0.1) PRECEDING) RANGE BETWEEN UNBOUNDED PRECEDING AND (MZDA*0.1) PRECEDING) o_o_1010pct_MENE, pct_MENE, COUNT(*) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA COUNT(*) OVER (PARTITION BY ODDELENI_ID ORDER BY MZDA RANGE BETWEEN (MZDA*0.1) FOLLOWING AND (MZDA*0.RANGE BETWEEN (MZDA*0.1) FOLLOWING AND (MZDA*0.55) FOLLOWING)) FOLLOWING) o_o_10_50_10_50_pct_VICEpct_VICEFROM ZAMESTNANCIFROM ZAMESTNANCIWHERE ODDELENI_ID IN (20, 30)WHERE ODDELENI_ID IN (20, 30)ORDER BY ODDELENI_ID, MZDAORDER BY ODDELENI_ID, MZDA
ODDELENI_IDODDELENI_ID ZAMESTNANEC_IDZAMESTNANEC_ID MZDAMZDA O_10PCT_MENEO_10PCT_MENE O_10_50_PCT_VICEO_10_50_PCT_VICE2020 202202 60006000 00 002020 201201 1300013000 11 003030 119119 25002500 00 333030 118118 26002600 00 223030 117117 28002800 11 113030 116116 29002900 22 003030 115115 31003100 22 003030 114114 1100011000 55 00
Cvičení č. 3 - zadáníCvičení č. 3 - zadáníNad tabulkou LIDE ve schématu A_CLOVEK řešte pomocí analytických funkcí:
1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
Doplnění hierarchických dotazůDoplnění hierarchických dotazů• Pro jednotlivé záznamy můžete také získat cestu od nejvyššího záznamu 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í:(jak to znáte třeba ze souborového systému) nebo řadu dalších informací:
• Funkce Funkce SYS_CONNECT_BY_PATHSYS_CONNECT_BY_PATH vrací cestu v hierarchii k aktuálnímu vrací cestu v hierarchii k aktuálnímu záznamu. záznamu.
• Klauzule Klauzule CONNECT_BY_ROOTCONNECT_BY_ROOT vrací hodnotu z příslušného záznamu vrací hodnotu z příslušného záznamu nejvyšší úrovně (tj. například nejvyššího manažera). nejvyšší úrovně (tj. například nejvyššího manažera).
• Pokud byste chtěli výstup z dotazu použít pro zobrazení ve formě 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, rozbalovací hierarchie tak, jak to třeba dělá u souborů Windows Explorer, bude se vám hodit i pseudosloupec bude se vám hodit i pseudosloupec CONNECT_BY_ISLEAFCONNECT_BY_ISLEAF, který určuje, zda , který určuje, zda je aktuální záznam na poslední úrovni hierarchie (CONNECT_BY_ISLEAF=1) je aktuální záznam na poslední úrovni hierarchie (CONNECT_BY_ISLEAF=1) nebo zda má podřízené záznamy (CONNECT_BY_ISLEAF=0).nebo zda má podřízené záznamy (CONNECT_BY_ISLEAF=0).
Příklad hierarchických dotazů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;
NAME PATH TOPMGR ISLEAF LEVEL King Steven /King King 0 1 Cambrault Gerald /King/Cambrault King 0 2 Bates Elizabeth /King/Cambrault/Bates King 1 3 Bloom Harrison /King/Cambrault/Bloom King 1 3 Fox Tayler /King/Cambrault/Fox King 1 3 Kumar Sundita /King/Cambrault/Kumar King 1 3 Ozer Lisa /King/Cambrault/Ozer King 1 3 Smith William /King/Cambrault/Smith King 1 3 De Haan Lex /King/De Haan King 0 2 Hunold Alexander /King/De Haan/Hunold King 0 3 Austin David /King/De Haan/Hunold/Austin King 1 4
OtázkyOtázky
Děkuji za pozornost.
http://www.orafaq.com/node/55
