Krokodýlovy databáze | SQL / Agregace pokračování

< Agregace | SQL postupně | Poddotazy >

Doposud jsme ve všech příkladech měli nějaký kód označující skupinu, pro kterou se agregace vypočetla. Pokud bychom do výsledku chtěli více informace o takové skupině, potřebume ji v normalizované databázi přečíst z další tabulky:

Příklad: Pro zboží, které bylo někdy prodáno, vypište jeho kód, název a celkový počet objednaných kusů.

        select KOD,NAZ,sum(MNOZ) as POCET_PRODANYCH
        from ZBOZ join POLOZ using (KOD)
        group by KOD,NAZ;

Když do výsledku chceme další sloupce, ve kterých není výpočet agregace, musí být všechny takové sloupce uvedeny v klauzuli GROUP BY – tak je tomu v jazyce Oracle, narozdíl od některých jiných jazyků.

Povšimněte si formulace "zboží, které bylo někdy prodáno". Co když chceme do výsledku všechna zboží, i ta, co nikdy prodána nebyla? Pak potřebujeme vnější spojení:

Příklad: Pro všechna zboží vypište jejich kód, název a počet celkový počet objednaných kusů.

        select KOD,NAZ,sum(MNOZ) as POCET_PRODANYCH
        from ZBOZ left join POLOZ using (KOD)
        group by KOD,NAZ;

V tomto výsledku se objevila Fanta divoká malina, ta v předchozím nebyla.

V takovýchto příkladech dejte pozor na počítání řádků:

Příklad: Pro všechna zboží vypište jejich kód, název a počet objednávek.

        select KOD,NAZ,count(*)
        from ZBOZ left join POLOZ using (KOD)
        group by KOD,NAZ;

– toto je špatně, u Fanty divoká malina máme počet 1. Správně je následující:

        select KOD,NAZ,count(CISO) as POCET_OBJEDNAVEK
        from ZBOZ left join POLOZ using (KOD)
        group by KOD,NAZ;

Máme-li agregační dotaz s vnějším spojením, počítat počet řádků count(*) je nesmysl. Místo toho musíme vybrat nějaký sloupec z tabulky, jejíž řádky chceme počítat, a počítat počet neprázdných hodnot v tomto sloupci: count(<sloupec>).

Co když chceme vypočítat pouze počet objednávek za nějaké období, například za rok 2008?

Příklad: Pro všechna zboží vypište jejich kód, název a počet objednávek za rok 2008.

        select KOD,NAZ,count(CISO) as POCET_OBJEDNAVEK
        from ZBOZ left join POLOZ using (KOD) left join OBJ using(CISO)
        where extract(year from DAT)=2008
        group by KOD,NAZ
        order by 1;

– toto je špatně, dostáváme velmi málo zboží. Správně je následující:

        select KOD,NAZ,count(OBJ.CISO) as POCET_OBJEDNAVEK
        from ZBOZ left join POLOZ using (KOD) left join OBJ on (POLOZ.CISO=OBJ.CISO and extract(year from DAT)=2008)
        group by KOD,NAZ
        order by 1;

Příklad: Pro každého zákazníka spočítejte počet objednávek k osobnímu převzetí (DOPR=1).

        select LOG, count(CISO) as POCET_OBJEDNAVEK_OSOBNE
        from ZAK left join OBJ on (ZAK.LOG=OBJ.ZAK and DOPR=1)
        group by LOG
        order by 1;

Pokud chceme počítat agregaci atributů jen takových objektů, které splňují nějakou podmínku, a přitom chceme do výsledku dostat i skupiny, v nichž žádný takový objekt není, musíme požadovanou podmínku dát do podmínky propojení.

Také je potřeba dát pozor, ze které tabulky počítáme agregaci:

Příklad: Pro každého zákazníka spočítejte součet peněz za objednávky placené převodem (PLAT=1).

        select LOG, sum(VCEN) as SUMA_PREVODEM
        from ZAK left join OBJ on (ZAK.LOG=OBJ.ZAK and PLAT=1) left join POLOZ using (CISO)
        group by LOG
        order by 1;

Příklad: Pro každého zákazníka spočítejte součet peněz za objednávky placené převodem (PLAT=1) a počet takových objednávek.

        select LOG, sum(VCEN) as SUMA_PREVODEM, count(CISO)
        from ZAK left join OBJ on (ZAK.LOG=OBJ.ZAK and PLAT=1) left join POLOZ using (CISO)
        group by LOG
        order by 1;

– toto je špatně, porovnejte výsledky:

        select LOG, count(CISO)
        from ZAK left join OBJ on (ZAK.LOG=OBJ.ZAK and PLAT=1)
        group by LOG
        order by 1;

Vypočtené počty jsou jiné. To proto, že v příkazu předtím jsme započítali každou objednávku tolikrát, kolik měla položek. Správný kód pro naši úlohu:

        select LOG, sum(VCEN) as SUMA_PREVODEM, count(distinct CISO) as POCET_OBJEDNAVEK
        from ZAK left join OBJ on (ZAK.LOG=OBJ.ZAK and PLAT=1) left join POLOZ using (CISO)
        group by LOG
        order by 1;

Pokud počítáme více agregací z různých tabulek najednou v jednom dotazu, musíme dát pozor, abychom nezapočítávali něco vícekrát. Bez problémů je pouze agregace z té z tabulek propojených v klauzuli FROM, jež je z nich nejvíce podřízená (tj. všechny šipky představující vazby cizích klíčů směřují od ní).
Bezpečné je v takových případech počítat každou agregaci zvlášť v poddotazech, a ty pak propojit.

< Agregace | SQL postupně | Poddotazy >

Obsah: