Statisztikai függvények, Szélsőérték függvények

1. Statisztikai függvények,Szélsőérték függvények

2. Statisztikai függvények • Mik voltak a statisztikai függvényeink? • SUM • AVG • MIN • MAX • COUNT • STDDEV - szórás • VARIANCE – variancia (Az átlagos négyzetes eltérés az átlagtól.)

3. Statisztikai függvények • A hagyományos statisztikai függvényeknek is létezik analitikus alakja. • Általános alakjuk: függvénynév( [ DISTINCT l ALL l paraméter) OVER ( analitikus_záradék) • Itt már gyakran használunk ablaktagot is az analitikus záradékban. • Könnyedén készíthető vele például kumulált összeg.

4. Statisztikai függvények • Ismétlés: • Ablaktag: • Lehet logikai - rangebetween vagy range • UNBOUNDED PRECEDING a partíció első sorától kezdődően • UNBOUNDED FOLLOWINGa partícióutolsósoráig • CURRENT ROWa partíció aktuális sorától, illetve soráig • Pl.: rangebetweenunboundedpreceding and currentrow

5. Statisztikai függvények • Ismétlés: • Ablaktag: • Lehet fizikai – rowsbetween • Pl.: rowsbetween 2 preceding and 1 following

6. Példa I. • Határozza meg a fizetésük szerint növekvően rendezett dolgozók fizetésének folyamatos halmozott összegét, az első sortól az utolsóig. (Ezt kumulált összegnek is nevezzük.)

7. Példa I. - megoldás SELECT empno, ename, sal, SUM(sal) OVER (ORDER BY sal RANGE UNBOUNDED PRECEDING) AS "Kumulált", SUM(sal) OVER (ORDER BY salRANGE BETWEEN UNBOUNDEDPRECEDING AND CURRENT ROW) AS "Ugyanaz" FROM emp;

8. Példa II. • Határozza meg a névsor szerint növekvően rendezett dolgozók fizetéseinek részlegükön belüli halmozott (kumulált) összegét. • A partíciókon belül rendezünk névsor szerint.

9. Példa II. - megoldás SELECT deptno, ename, sal, SUM(sal) OVER (PARTITION BY deptno ORDER BY ename RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Kumuláltösszeg" FROM emp;

10. Példa III. • Adjuk össze mindig az aktuális sor előtt lévő két sort és az utána következő sort (mozgó ablak).

11. Példa III. - megoldás SELECT ename, sal, SUM(sal) OVER (ORDER BY sal ASC ROWS BETWEEN 2 PRECEDING AND 1FOLLOWING) AS "AblakÖsszeg" FROM emp;

12. Statisztikai függvények – RATIO_TO_REPORT • Kiszámítja az érték a és a csoportösszeg közötti arányt • Általános alakja: • RATIO_TO_REPORT(kifejezés) OVER ( partíció tag) Csak partíciós tag szerepelhet!!!

13. Példa I. • Írjuk meg, hogy ki milyen arányban részesedik az összfizetésből. • Legyenek kiírva a következők: • EMPNO • ENAME • SAL • RÉSZESEDÉS • LOC

14. Példa I. - megoldás SELECT empnoas azonosító, enameasnév, salas fizu, RATIO_TO_REPORT(sal) OVER () as részesedés, locas telephelyFROM emp, dept WHERE emp.deptno = dept.deptno ORDER BY sal DESC;

15. Példa I. • Írjuk meg, hogy ki milyen arányban részesedik a telephelyenkénti összfizetésből. • Legyenek kiírva a következők: • EMPNO • ENAME • SAL • RÉSZESEDÉS • LOC

16. Példa I. - megoldás SELECT empnoas azonosító, enameasnév, salas fizu, RATIO_TO_REPORT(sal) OVER (partitionbyemp.deptno) asrészesedés, locas telephelyFROM emp, dept WHERE emp.deptno = dept.deptno ORDER BY locdesc, saldesc;

17. Speciális szélsőérték függvények • A FIRST _VALUE, illetve LAST _VALUE • szélsőértékek meghatározására alkalmas analitikus függvények • Hasonlóak a MIN() és MAX() függvényekhez • A különbség, hogy ezek ablaktartományon is alkalmazhatóak, míg a MIN(), MAX() nem

18. Speciális szélsőérték függvények • Általános alakjuk: • függvénynév(kifejezés) OVER (analitikus utasításrész) • Ez a két függvény nem ágyazható be más analitikus függvények kifejezés részébe.

19. Példa I. • Listázza ki dolgozónként a nevüket, a fizetésüket, a részlegazonosítójukat, valamint részlegük legkisebb és legnagyobb fizetését, elsődlegesen a részlegazonosító, másodlagosan a fizetés szerint rendezve.

20. Példa I. – megoldás régi módon SELECT ename, sal, al.* FROM emp, (SELECT deptno, MIN(sal) AS legkisebb, MAX(sal) AS legnagyobb FROM emp GROUP BY deptno) al WHERE emp.deptno= al.deptno ORDER BY al.deptno, sal;

21. Példa I. – szélsőérték fv-el SELECT ename, sal, deptno, FIRST_VALUE(sal) OVER (PARTITION BY deptnoORDER BY salASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS legkisebb, LAST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY salASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS legnagyobb FROM emp;

22. Példa II. • Listázza ki dolgozónként a nevüket, a fizetésüket, a munkakörüket, valamint munkakörönként a legkisebb és legnagyobb fizetését, elsődlegesen a munkakör, másodlagosan a fizetés szerint rendezve.

23. Példa II. SELECT ename, sal, job, FIRST_VALUE(sal) OVER (PARTITION BY jobORDER BY salASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS legkisebb, LAST_VALUE(sal) OVER (PARTITION BY job ORDER BY sal ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS legnagyobb FROM emp;

24. Példa III. Listázza ki dolgozónként a nevüket, a fizetésüket, a részlegazonosítójukat, valamint részlegüknek az ábécé szerinti első és utolsó dolgozója nevét elsődlegesen a részlegazonosító, másodlagosan a név szerint rendezve.

25. Példa III. Listázza ki dolgozónként a nevüket, a fizetésüket, a részlegazonosítójukat, valamint részlegüknek az ábécé szerinti első és utolsó dolgozója nevét elsődlegesen a részlegazonosító, másodlagosan a név szerint rendezve.

26. Példa III - megoldás. SELECT ename, sal, deptno, FIRST_VALUE(ename) OVER (PARTITION BY deptno ORDER BY ename ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Első, LAST_VALUE(ename) OVER (PARTITION BY deptno ORDER BY ename ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Utolsó FROM emp;