200 likes | 260 Vues
Részletező csoportosítások. 2012. 04.03. Hári Veronika harivero @ gmail.hu. A diasor ismerete nem helyettesíti a tankönyvet, és a példatárat. A diasor ismerete szükséges, de nem elégséges feltétele a minimum elégséges érdemjegynek!. Részletező csoportosítások.
E N D
Részletező csoportosítások 2012. 04.03. Hári Veronikaharivero@gmail.hu
A diasor ismerete nem helyettesíti a tankönyvet, és a példatárat. A diasor ismerete szükséges, de nem elégséges feltétele a minimum elégséges érdemjegynek!
Részletező csoportosítások • Elemző jellegű feladatokhoz • Részeredmények képzése • csoportfüggvények részértékei • Rugalmas csoportosítás • A GROUP BY hatékonyabb használata!
GROUPING SETS fv • GROUP BY kiegészítése • Segítségével több szempont alapján is csoportosíthatunk
Példa 1 • Képezzen részösszegeket a fizetés (sal) oszopon • az alábbi csoportosításokban: • mgr, deptno, job • deptno, job • mgr • Egyetlen lekérdezésben?!
Megoldás: GROUPING SETS SELECT mgr, deptno, job, SUM(sal) FROM emp GROUP BY GROUPING SETS((mgr, deptno, job), (deptno, job), (mgr));
Példa 2 • Listázzunk átlagfizetéseket • főnök és részleg • részleg és munkakör • szerint.
Megoldás SELECT NVL(mgr, 0), deptno, NVL(job, 'Nincs'), AVG(sal) FROM emp GROUP BY GROUPING SETS ( (mgr, deptno), (deptno, job) )
GROUP BY GROUPING SETShátrányok • Túl bonyolult, túl hosszú • Ritkán van arra szükségünk, hogy teljesen különböző csoportosításokat végezzünk • Általában a csoportosítások között kapcsolat van • Erre rövidítést, ez gyakran használt: ROLLUP
ROLLUP • GROUP BY résznél használjuk • Segítségével egy lekérdezésen belül egyszerűen juthatunk olyan adatokhoz, melyhez minimum két külön lekérdezést kellene írnunk
ROLLUP - példa • Írjuk meg egy szkriptbe a következő lekérdezéseket: • SELECT job, deptno, avg(sal)FROM empGROUP BY job, deptno; • SELECT job, deptno, avg(sal)FROM empGROUP BY ROLLUP (job, deptno); • SELECT job, deptno, avg(sal)FROM empGROUP BY ROLLUP (deptno, job);
Példa 3 • Listázzuk ki részlegenként, és ezen belül foglalkozásonként az összfizetéseket, továbbá a részlegenkénti összfizetéseket, végül a vállalati összfizetést, és ugyanezen csoportosításban a létszámokat is. • Régi megoldás: külön-külön lekérdezésekkel. • Megoldható GROUPING SETS-szel is.
Régi megoldás • SELECT deptno, job, SUM(sal), COUNT(*)FROM emp GROUP BY deptno, job; • SELECT deptno, SUM(sal), COUNT(*)FROM emp GROUP BY deptno; • SELECT SUM(sal), COUNT(*)FROM emp;
Újabb megoldás SELECT deptno, job, SUM(sal), COUNT(*) FROM emp GROUP BY GROUPING SETS((deptno, job), (deptno)); • Nem teljesen jó! A teljes összeget nem adja meg.
Új megoldás: ROLLUP SELECT deptno, job, SUM(sal), COUNT(*) FROM emp GROUP BY ROLLUP (deptno, job); • ROLLUP(job, deptno): A sima kétmezős GROUP BY-on kívül kiszámolja az egyes munkakör-átlagokat, és a teljes átlagot is
Példa 4 • Listázza ki munkakörönként és azon belül részlegenként az összfizetést, az átlagfizetést, valamint a csoportlétszámot.
Megoldás SELECT job AS munkakör, deptno AS részleg, SUM(sal) AS részösszeg, ROUND(AVG(sal),0) AS átlag, COUNT(*) AS létszám FROM emp GROUP BY ROLLUP (job, deptno);
DECODE fvhasználata • Mi van, ha szeretnénk kiírni, hogy a munkakörök átlagfizetése, illetve az összes átlagfizetést? • NVL fv segítségével megoldható lenne…
DECODE fv használata - KÓD selectnvl(job,'Átlag') as Munkakör, DECODE(nvl(job,'x'), 'x', null, nvl(to_char(deptno), 'Munkakörösszes')) as részleg, avg(sal) asatlag fromemp groupbyrollup (job, deptno);