Agregačné funkcie, zoskupenie, a množinové operácie

Ciele
  1. Agregačné funkcie pre agregovanie kolekcie hodnôt do jednej hodnoty.
  2. Zoskupovanie záznamov do skupín podľa rovnakej hodnoty v zadanom stĺpci.
  3. Množinové operácie.
Úvod
    Pracujeme na databáze Chinook s nasledovnou schémou.
    Obr.: : Relačný model databázy Chinook
Postup
  1. Agregačné funkcie umožňujú agregovať viaceré hodnoty do jednej - napr. count zráta počet záznamov:
    SELECT count(*) FROM album;
    Vďaka agregačným funkciám vieme spočítať počet špecifických záznamov (napr. koľko umelcov nemá žiaden album), vypočítať priemernú hodnotu, maximálnu hodnotu, atď.
    Úloha: Koľko skladieb sa nachádza v albume War? Pre overenie správnosti ich je 10.
    Úloha: Zisti, koľko bolo spolu predaných skladieb z albumu War od U2. Pre overenie vedz, že ich bolo 11.
    Úloha: Zisti čas v sekundách najdlhšej skladby, najkratšej skladby a priemerný čas skladieb albumu War od U2. Pre overenie vedz, že priemerný čas skladieb je 252,99 sekúnd.
  2. Použitie agregačných funkcií je zvlášť zaujímavé v kombinácii so zoskupením výsledkov. Zoskupovanie s GROUP BY klauzulou nám umožňuje získať sumárne hodnoty pre skupiny záznamov podľa hodnoty vybraného stĺpca. Napr. si vieme získať počet skladieb podľa skladateľa:
    
    SELECT    composer, COUNT(*)
    FROM      track
    GROUP BY  composer
    ORDER BY  COUNT(*) desc;
    Úloha: Vypíš krajiny a počet zákazníkov pre každú z nich. Pre overenie správnosti dopytu vedz, že najviac zákaznikov (13) je z USA.
    Úloha: Vypíš sumárne zisky z predaja po jednotlivých rokoch. Pre overenie správnosti dopytu vedz, že celkový zisk z predaja v roku 2009 bol $449.46.
    Poznámka: Pamätaj, že v GROUP BY klauzule nemusí byť len priamo stĺpec, môže tam byť aj nejaký výraz, ktorý z riadku v tabuľke vypočíta hodnotu.
    Úloha: Vypíš sumárne zisky z predaja po jednotlivých rokoch a mesiacoch. Pre overenie správnosti dopytu vedz, že celkový zisk z predaja v apríli roku 2011 bol $51.62.
    Úloha: Uprav SELECT z predchádzajúcej úlohy tak, aby sa vypísali iba tie mesiace, v ktorých bol zisk aspoň $50. Pre overenie vedz, že také mesiace boli iba 3.
    Úloha: Vypíš sumárne zisky z predaja po jednotlivých rokoch a mesiacoch tak, aby vo výsledku boli zároveň sumárne zisky po jednotlivých rokoch, a zároveň sumárny zisk dokopy. Opäť ide o modifikáciu pred-predchádzajúcej úlohy, takže vieš sa odraziť od jej riešenia.
    Poznámka: Nepoužívaj pri tom množinové operácie.
  3. Množinové operácie pracujú s tabuľkami a výsledkami SELECT-ov ako s množinami a umožňujú nám z nich vytvárať zjednotenie, prienik a množinový rozdiel. Prostredníctvom množinových operácií si napr. vieme ľahko zistiť či máme v systéme zákazníka s rovnakým priezviskom ako má niektorí zo zamestnancov:
    
    SELECT  lastname
    FROM    employee
    INTERSECT
    SELECT  lastname
    FROM    customer;
    Úloha: Zisti, ktorí umelci nie sú zároveň skladateľmi. Pre overenie vedz, že ich je 228.
    Úloha: Získaj zoznam umelcov aj skladateľov, ktorých nájdeš v tabuľkách Artist a Track (bez duplikátov). Do výsledku neuveď null hodnotu, ktorá sa nachádza v skladateľovi, ak je pre skladbu neznámy. Výsledok usporiadaj podľa abecedy. Pre overenie vedz, že dokopy je umelcov a skladateľov 1080.
    Úloha: Vypíš zoznam všetkých skladieb (aspoň názvy spolu s názvom skladateľa), ktoré nepatria do playlistu 'Heavy Metal Classic'. Pre overenie správnosti je počet týchto skladieb 3477.
    Poznámka: Daj si pozor na to, že niektoré skladby môžu mať rovnaký názov i skladateľa, ale nie sú tie isté.
Zdroje
  1. Prednáška 11: Transakcie
  2. Prednáška 12: Pohľady
  3. Prednáška 14: Indexy
  4. Use the index, Luke!
  5. Prednáška 15: Riadenie prístupu Oracle (samoštúdium)
Doplňujúce úlohy
    Úloha:

    Chinook

    Vypíš sumárne počet skladieb v jednotlivých playlistoch. Pre overenie správnosti vedz, že v playliste TV Shows sa nachádza 213 skladieb a v playliste Audiobooks sa nenachádza žiadna skladba (počet je 0). Výsledný zoznam usporiadaj vzostupne podľa názvu playlistu.
    Poznámka: Daj si pozor na to, že niektoré playlisty majú rovnaké meno, aj keď nejde o ten istý playlist.
    Poznámka: A nezabudni ani na playlisty, ktoré nemajú žiadne skladby.
    Úloha:

    Chinook

    Vypíš zisky sumárne podľa žánru. Žáner vypíš, aj keď nemá žiadny zisk. Pre overenie vedz, že najlepšie zarábajúci žáner je Rock so ziskom $826,65.
    Úloha:

    Chinook

    Posledná úloha z kroku č. 2 si vyžadovala použitie príkazu ROLLUP. Prepíš riešenie úlohy bez použitia ROLLUP iba s použitím množinových operácií.
    Poznámka: Znenie pôvodnej úlohy pre zopakovanie: Vypíš sumárne zisky z predaja po jednotlivých rokoch a mesiacoch tak, aby vo výsledku boli zároveň sumárne zisky po jednotlivých rokoch, a zároveň sumárny zisk dokopy.
    Úloha:

    Chinook

    Vypíš zoznam všetkých skladieb (aspoň názvy spolu s názvom skladateľa), ktoré nepatria do playlistu 'Heavy Metal Classic'.
    Poznámka: Úloha je rovnaká ako posledná v kroku 3, tentokrát ju však vyrieš iba s použitím spájania tabuliek.
    Úloha:

    Podnik

    Zisti koľko rôznych stavov majú pracovníci z miest Prešov a Košice (názov mesta a počet stavov). Vo výsledku bude napr. záznam Košice - 1, pretože zamestnanci z Košíc sú všetci ženatí (teda v Košiciach je len 1 stav - ženatý).
    Úloha:

    Podnik

    Vypíš počet pracovníkov, ich priemernú mzdu, maximálnu a minimálnu mzdu, z tých miest, v ktorých počet pracovníkov je vyšší ako 2.
    Úloha:

    Podnik

    Vypíš sumárne podľa miest zamestnancov sumu nevyčerpanej dovolenky v roku 2015. Pre overenie vedz, že zamestnanci z Košíc v roku 2015 nevyčerpali 10 dní dovolenky.
comments powered by Disqus