Analitikus, statisztikai és szélsőérték fv-k

1. Analitikus, statisztikai és szélsőérték fv-k 2012. 04.17. Hári Veronika harivero@gmail.comhari.veronika@nik.uni-obuda.hu

2. 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!

3. Analitikus fv-k • Ezek a fv-k nincsenek az SQL nyelvben • Új fv család • 3 nagy csoport • RANG • Statisztikai • Hisztogram

4. Általános felépítés Fv_neve( [paraméter] ) OVER ( záradék ) • Mindig a SELECT utasításrészben találhatóak • A paraméter tetszőleges oszlopkifejezés lehet • Az OVER záradék elhagyható

5. A záradék bővebben ([partíció tag] [rendező tag[ablak-tag]]) • Tulajdonképpen a munkaterületet jelöli ki az analitikus záradék • Partíció tag: Egy munkatábla munkaterületén összefüggő táblatartományokat képezhetünk, ahol az oszlopkifejezések értéke azonos • Rendező tag: ORDER BY (partíción belüli rendezés) • Ablak tag: kijelöli minden partíción belül azt az összefüggő táblatartományt, melynek sorain történik az analitikus fg. által kijelölt műveletvégzéstlehet fizikai vagy logikai

6. RANG fv-k • Over opcióval ellátott • RANK • DENSE_RANK • PERCENT_RANK • Nem tartalmaznak ablak tagot, és paraméterlistájuk üres • Rangsorolást végeznek • Hiányzó partíció tag esetén az egész táblán rangsorolnak

7. RANG fv-k • RANK:1-től partíció méretéig sorszámozza (ellátja „rang”-gal) a sorokat • DENSE_RANKu. a., mint a RANK, de ha 2 sornak ugyanazt a rangot adja, a következőnél a következő sorszámot adja (nem hagy ki sorszámot) • PERCENT_RANK0-1 tartományban ossza ki a rangokat, úgy, hogy ha a sor rangja n, a partíció k sorból áll, akkor arang=(n-1)/(k-1)

8. Példa SELECTenameasnev, salas fizu, RANK() OVER (Orderbysal DESC) asnormr, DENSE_RANK() OVER (Orderbysal DESC) astomor, PERECENT_RANK() OVER (Orderbysal DESC) asszazFROM emp;

9. Aggregáló RANG fv-k • OVER helyett WITHIN GROUP opcióval vannak ellátva • Nem is analitikus fv-k (működésük mögött analitikus fv.-k állnak) • Paraméterként megadott konstans kifejezés értékének helyét keresi meg az egyes partíciókban, azt feltételezve, hogy ez a konstans az egyes partíciók eleme • A paraméterlista már nem üres • Mi lenne, ha... típusú feladatok

10. Példa • Felvennénk egy új dolgozót, Jani bátyát. • Írjunk egy szkriptet, mely megmondja, hogy egy adott fizu esetén, hányadik legtöbbet kereső emberke lenne? • Magyarán kérjünk be egy fizetést, és nézzük meg, hogy hányadik lenne a rangsorban…

11. Példa - megoldás ACCEPT fizu PROMPT ’Fizu: ’;SELECT ’rangsora’ as Kérdés, RANK(&fizu) WITHIN GROUP (Orderbysal DESC) asNormrang, DENSE_RANK(&fizu) WITHIN GROUP (Orderbysal DESC) asTomorrang, PERCENT_RANK(&fizu) WITHIN GROUP (Orderbysal DESC) asSzrangFROM emp;

12. Statisztikai analitikus fv-k • Hagyományos statisztikai fv-knek(sum, min, max stb.) létezik analitikus alakja. • Használata:fv_neve (paraméter/ALL/DISTINCT) OVER ( záradék )

13. Példa • Dolgozók fizetésének folyamatosan halmozott összege (kumulált összeg), fizetés szerint növekvően rendezve: SELECT ename, job, SUM(sal) OVER (ORDER BY sal ASC) as ”Kumulalt”FROM emp;

14. RATIO_TO_REPORT • RATIO_TO_REPORT(kifejezés) OVER ( partíció tag) • Kiszámítja az érték és a csoportösszeg közötti arányt • (Részesedés) • Írjuk meg, hogy ki milyen arányban részesedik az összfizetésből

15. Példa • SELECT enameasnév, salasfizetés, RATIO_TO_REPORT(sal) OVER () as részesedésFROM empORDER BY sal DESC;

16. FIRST_VALUE/LAST_VALUE • Hasonló mint a MIN/MAX • De ablaktartományokban is használhatóak • Alakja:FIRST_VALUE/LAST_VALUE(kifejezés) OVER (analitikus utasításrész)

17. Hisztogram fv-k • WIDTH_BUCKET • WIDTH_BUCKET(oszlopkifejezés, alsóhatár, felsőhatár, szeletek száma) • Nem is analitikus függvény • „azonos szélességű” hisztogram készíthető segítségével, azaz meghatározza, hogy azonos méretű értéktartományokban az oszlopkifejezés értékei hogyan oszlanak meg

18. Példa • Csináljunk 4 fizetési kategóriát, és listázzuk ki, hogy ki hova tartozik. • SELECT ename, sal, WIDTH_BUCKET(sal, 800, 5001, 4) askategoriaFROM empORDER BY kategoria;

19. NTILE függvény • Hasonló mint a WIDTH_BUCKET, de itt a hisztogram azonos magasságú, és a szélesség különbözik • Használata:NTILE(kifejezés) OVER ([Partíció] Rendező tag) • Először rendez (Rendező tag szerint), majd a kifejezés értékének megfelelő számú résztartományra bont

20. Példa • Listázzuk fizetés szerint rendezve a dolgozók nevét, fizetését és fizetési kategóriáját, ahol ez utóbbit az összes dolgozó fizetési tartományának 3 részre osztásával úgy kapjuk meg, hogy minden részbe közel azonos számú dolgozó kerüljön. • SELECT ename, sal,NTILE(3) OVER (orderbysal DESC) askategoriaFROM emp;

21. Köszönöm a figyelmeteket!