Analitikus függvények

A rangsoroló függvények mindegyike egy sorszámot ad az OVER utasításrészben szereplő kifejezésre, az eredmény halmaz minden egyes sorának egy rendezési relációt alapul véve, melyek az ORDER BY záradékkal kell definiálni.Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

A hagyományos rangsorolás számos esetben nem ad tökéletes megoldást, például duplikációk esetén. Ilyenkor rangsoroló függvények használatával tudjuk megfelelően sorba rendezni az adatokat.Példa: ha a dolgozóinkat bérük szerint szeretnénk sorba állítani, a duplikált elemeket - akik ugyanannyit keresnek - nem tudja megfelelően sorba rendezniFelhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

Különbség a PARTITION BY záradékkal ellátott analitikus függvény és egy GROUP BY záradékkal ellátott lekérdezés között:A legfontosabb különbség talán az, hogy míg a GROUP BY záradékkal ellátott lekérdezés SELECT ágán nem szerepelhet olyan oszlop definíció, amely nem szerepel a GROUP BY ágon, addig az analitikus függvényeknél nincs ilyen megkötés, viszont az aggregáció ugyan úgy elvégezhető. Azaz az analitikus függvények úgy végzik el a csoportosítást és rajtuk az aggregációt, hogy a megjelenő eredményhalmaz ténylegesen nem lesz csoportosítva.Míg a GROUP BY záradékkal ellátott lekérdezés egy értéket ad vissza (csoportonként egy eredményt ad vissza), addig a PARTITION BY minden egyes sorra generál eredményt, tehát a sorok száma ez esetben nem módosul.Felhasznált irodalom:Analitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

lépés: JOIN-ok, WHERE, GROUP BY, HAVING klauzuláklépés: partíciók létrehozása (az analitikus függvények alkalmazása a partíciók minden egyes sorára)lépés: ORDER BYFelhasznált irodalom:órai előadásfóliák

Az analitikus függvények logikai csoportok - PARTITION- sorain operálnakA hagyományos csoportfüggvényekkel ellentétben az analitikus függvények a partíció minden sorára generálnak értéketA partíción belül ablak - WINDOW- definiálható az analitikus utasításrésszel - ANALYTIC CLAUSE -Az ablakot mindig az aktuális sorhoz - CURRENT ROW - relatívan specifikáljuk, intervallumkéntFizikailag - sorok számávalLogikailag - pl idő adatok alapjánFelhasznált irodalom:órai előadásfóliák

Az analitikus függvényeket rendezési elvtől függően számos csoportba tudjuk rendezni.Egy lehetséges csoportosítás (az előadás dia mentén)rangsoroló függvények - példa: RANK(), DENSE_RANK()fix méretű ablak technikát alkalmazó függvények - péda: MIN, MAX, AVG, SUMriport készítő függvények - halmozott összesítések - példa: MIN, MAX, AVG, SUMmás időszakhoz tartozó adatok kezelése - példa: LAG, LEADstatisztikai függvények - példa: WITH_BUCKET, NTILE, CORR(A térkép elkészítése során igyekeztem e fenti felsorolás és a diasor mentén csoportosítani, de ez nem mindenhol volt kivitelezhető.)Felhasznált irodalom:órai előadásfóliák

A RATIO_TO_REPORT függvény egy halmaz minden elemére megmondja, hogy azok hány százaléka a halmaz elemeinek összegének. A halmaz méretét a PARTITION BY záradékkal tudjuk szabályozni, a halmaz értékeit pedig paraméterül kapja a függvény. A függvény nem támogatja az ORDER BY záradékot és az ablak definíciót sem.Szintaxisa:RATIO_TO_REPORT (oszlopnév) OVER(), 'számérték'Példa:SELECT d.*,RATIO_TO_REPORT(d.a2) OVER (PARTITION BY d.a1) as ratio_to_reportFROM test d;Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

A LAG és a LEAD függvények segítségével egy halmaz bármely során állva elkérhetjük egy az általunk definiált rendezési reláció szerinti az adott sort x-el megelőző (LAG) illetve követő (LEAD) sor egy oszlopának értékét.Mindkét függvénynek 3 paramétere van:· sql kifejezés: Egy szabványos sql kifejezés, mely ki lesz értékelve a megelőző vagy a következő soron.· eltolás: Egy egész szám, mely megmondja hány sorral megelőző illetve követő soron értékelődjön ki az első paraméter.· alapértelmezett érték: Egy alapértelmezett érték mely akkor kerül visszaadásra, ha a kiértékelt kifejezés eredménye null.Szintaxisa:LEAD (<sql_kifejezés>, <eltolás>, <alapértelmezett_érték>)OVER ([PARTITION BY <...>] [ORDER BY <....>])LAG (<sql_kifejezés>, <eltolás>, <alapértelmezett_érték>)OVER ([PARTITION BY <...>] [ORDER BY <....>])Példa:SELECT dolgozo.név "Név", dolgozo.fizetés "Fizetés",LAG(dolgozo.fizetés,1,0) OVER (ORDER BY dolgozo.név) "Előző sor fizetése",LEAD(dolgozo.fizetés,1,0) OVER (ORDER BY dolgozo.név) "Következő sor fizetése"FROM aa dolgozo;Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

Néhány analitikus függvény támogatja az ablak definíció használatát, melynek segítségével tovább szűkíthetjük a partíción belüli rekordok számát, oly módon, hogy a partíción belül meghatározzuk az ablak kezdetét és a végét. Ezeket a határokat az aktuális sorhoz képest relatívan tudjuk megadni. Kétféle ablaktípus létezik, a ROW és a RANGE.Az ablakfüggvények lehetővé teszik halmozott, mozgó vagy centrális aggregátumok kiszámítását. Ezek a tábla minden sorára visszaadnak egy értéket, mely érték a sorhoz tartozó ablaktól (soroktól) függnek. Felhasználhatjuk többek közt a MIN, MAX, COUNT, SUM, AVG függvényeket. Csak a SELECT illetve az ORDER BY utasításrészben használhatjuk ezeket. Ide tartozik a FIRST_VALUE illetve a LAST_VALUE függvény, amik egy ablakhoz tartozó első illetve az utolsó értéket adják meg. Ezek a függvények a tábla több sorához biztosítanak hozzáférést self-join nélkül.Szintaxisa:ROW/RANGE ] BETWEEN <kezdőpont> AND <végpont><kezdőpont>(UNBOUNDED PRECEDING/CURRENT ROW | <sql_kifejezés> [PRECEDING/FOLLOWING] )<végpont>(UNBOUNDED FOLLOWING/CURRENT ROW | <sql_kifejezés> [PRECEDING/FOLLOWING] )

Fizikai eltolást a ROWS kulcsszóval adunk meg.Az UNBOUNDED PRECEDING jelentése az aktuális sort megelőző partíción belüli első sor.Az UNBOUNDED FOLLOWING az aktuális sort követő partíción belüli utolsó sort fogja jelenteni.A CURRENT ROW az aktuális sort jelenti.Az <sql_kifejezés> PRECEDING az aktuális sort <sql_kifejezés>-el megelőző sort.Az <sql_kifejezés> FOLLOWING pedig az aktuális sort követő <sql_kifejezés>-dik sort jelenti, ahol az <sql_kifejezés> értékének pozitív egésznek kell lenni.A kezdőpontnak mindig kisebbnek kell lenni a végpontnál.Felhasznált irodalom:Analitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

Logikai eltolást a RANGE kulcsszóval adhatunk meg. A RANGE típus esetén a szintaxis ugyan az mint a ROW típus esetén, csupán értelmezésbeli különbség van, valamit további megkötések, melyek a következők.· Az order by záradék csak egy kifejezést tartalmazhat.· A <kezdőpont> és a <végpont>-ben szereplő <sql_kifejezés> típusánakúgymond kompatibilisek kell lenni az order by záradékban szereplő kifejezés típusával. Ez azt jelenti, hogy ha a kifejezés típusa number, akkor az order by kifejezésnek number vagy date típusúnak kell lenni. Ha a kifejezés egy intervallum típus, akkor az order by kifejezésnek date típusúnak kell lenni.A RANGE típus értelmezésében a kezdő és a végpont nem más, mint az order by által meghatározott oszlop aktuális értékének a kifejezéssel történő eltolása.Felhasznált irodalom:Analitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

Az aktuális sorhoz viszonyít, ehhez képest jeleníti meg a megadott értékkel előtte, illetve utána lévő sorokat.Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

A FIRST_VALUE és a LAST_VALUE függvények a képzett csoport meghatározott sorrendjének első illetve utolsó rekordjának megfogására szolgál.Szintaxisa:FIRST_VALUE(<sql_kifejezés>) OVER ([PARTITION BY <...>] [ORDER BY <....>[<ablak_definíció>]])LAST_VALUE(<sql_kifejezés>) OVER ([PARTITION BY <...>] [ORDER BY <....>[<ablak_definíció>]])Példa a FIRST_VALUE használatára:SELECT t.név "Név",t.telep "Telephely",t.fizetés "Fizetés",t.first_v "Legkisebb fiz.",t.fizetés - t.first_v "Eltérés"FROM (SELECT dolgozo.név,dolgozo.telep,dolgozo.fizetés,FIRST_VALUE(dolgozo.fizetés) OVER(PARTITION BY dolgozo.telep ORDER BY dolgozo.fizetés) first_vFROM aa dolgozo)t;Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

Példa szintaxis mozgó aggregált függvényre:{SUM | AVG | MAX | MIN | COUNT | ... } OVER ([ORDER BY <....>[<ablak_definíció>]]){SUM | AVG | MAX | MIN | COUNT | ... } OVER (ORDER BY oszlopnév ROWS BETWEEN szám PRECEDING AND szám FOLLOWING)Bármelyik csoportosító függvény után használhatjuk a PARTITION BY záradékot. A PARTITION BY segítségével a csoportosító függvényünket végrehajthatjuk a csoportosítás egy részcsoportján.Példa függvények: MIN, MAX, AVG, SUM, stb...A PARTITION BY szintaxisa a következő:{SUM | AVG | MAX | MIN | COUNT | ... } OVER ( [PARTITION BY sql_kifejezés1[,...]] )Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

Mindig az aktuális sorból indul ki: ahogy módosul az aktuális sor, úgy változik az ablak mérete is.Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

Halmozott összesítések - riportkészítő függvényekPélda függvények: MIN, MAX, AVG, SUM, stb...Példa szintaxis:SUM(oszlopnév) OVER (ORDER BY oszlopnév RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

Oszlop mentén képezhetünk partíciót isPélda szintaxis:SUM(oszlopnév) OVER (PARTITION BY oszlopnév ORDER BY oszlopnév RANGE UNBOUNDED PRECEDING)SUM(oszlopnév) OVER (PARTITION BY oszlopnév ORDER BY oszlopnév ROWS UNBOUNDED PRECEDING)

A rangsoroló függvények mindegyike egy sorszámot ad az OVER utasításrészben szereplő kifejezésre, az eredmény halmaz minden egyes sorának egy rendezési relációt alapul véve, melyek az ORDER BY záradékkal kell definiálni.Példa a rangsoroló függvények használatára particionálással:SELECT ROW_NUMBER() OVER(PARTITION BY dolgozo.telep ORDER BY dolgozo.fizetés) row_number,RANK() OVER(PARTITION BY dolgozo.telep ORDER BY dolgozo.fizetés) rank,DENSE_RANK() OVER(PARTITION BY dolgozo.telep ORDER BY dolgozo.fizetés) dense_rank,dolgozo.név,dolgozo.fizetés,dolgozo.telepFROM aa dolgozo;Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

A ROW_NUMBER esetén a sorszámok szigorúan monotonnövekvő sort alkotnak, ahol az N. elem a halmazban az N. sorszámot kapja.Nem összetévesztendő ROWNUM-mal, mely egy pszeudó oszlop.Szintaxisa:ROW_NUMBER() OVER ([ORDER BY oszlopnév])Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

A RANK() függvény esetén a sorszámok egy monoton növekvő sort alkotnak, ha a halmaz tartalmaz azonos elemeket, akkor ők ugyanazt a sorszámot kapják, így néhány szám kimarad. Tehát ha az N. és az N+1. elem a rendezésireláció szerint egyenlő, akkor az N. és az N+1. elem is N. sorszámot kapja,azonban a rendezési reláció szerinti következő N+2. eltérő elem az N+2.sorszámot kapja.Egy példa a számsorrendre: 1 2 2 4 5 6 6 8 9 10Szintaxisa:RANK () OVER ([ORDER BY oszlopnév])Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

A DENSE_RANK() függvény esetén a sorszámok monoton növekvő sort alkotnak. Abban különbözik a RANK() függvénytől, hogy itt a rendezési reláció szerinti következő N+2. eltérő elem az N+1. sorszámot kapja.Példa egy számsorrendre: 1 2 2 3 4 5 6 6 7 8 9 10Szintaxisa:DENSE_RANK () OVER ([ORDER BY oszlopnév])Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

A PARTITION BY használatával az eredmény halmaza csoportosítható, mely csoportokon aggregációk hajthatók végre.Szintaxisa:RANK () OVER ([PARTITION BY oszlopnév] [ORDER BY oszlopnév])DENSE_RANK () OVER ([PARTITION BY oszlopnév] [ORDER BY oszlopnév])Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

Mi lenne ha? típusú lekérdezésekA függvényt a WITHIN GROUP kulcsszavak követik, majd zárójelben egy rendezési relációt kell megadni, mely szerint az oszlop értékei rendezve lesznek a felsoroláson belül.Szintaxisa:RANK(szám) WITHIN GROUP (ORDER BY oszlopnév)DENSE_RANK(szám) WITHIN GROUP (ORDER BY oszlopnév)Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

A CUME_DIST meghatározza a csoport értékeinek kumulatív eloszlását. A visszaadott értékek nulla és egy között helyezkednek el.Szintaxisa:CUME_DIST() OVER (PARTITION BY oszlopnév ORDER BY oszlopnév)Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

A PERCENT_RANK hasonló CUME_DIST függvényhez, ez is 0 és 1 közötti értékeket ad vissza. Minden halmaz első sora mindig 0-t vesz fel.Szintaxisa:PERCENT_RANK() OVER (PARTITION BY oszlopnév ORDER BY oszlopnév)Felhasznált irodalom:órai előadásfóliákAnalitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

Egyéb, főleg statisztikai függvények

A LISTAGG függvény ugyan nem egy hagyományos értelemben vett analitikus függvény, azonban használata nagyban hasonlít azokra és emellett sokszor nagyon hasznos.Alap esetben egy GROUP BY záradékkal rendelkező lekérdezés SELECT ágán nem szerepelhet aggregációs függvény nélkül olyan oszlop, amelynem szerepel a GROUP BY záradékban. A LISTAGG függvény tulajdonképpen ezt oldja fel úgy, hogy a paramétereként kapott oszlop csoportban szereplő értékeit egy elválasztó karaktersorozatot használva összefűzi.Két paramétere van:· sql_kifejezés: ez egy olyan oszlopdefiníció, amely nem szerepel a GROUP BY záradékban.· elválasztó karaktersorozat: ezzel a karaktersorozattal lesz elválasztva a csoport minden eleme az összefűzés során.A függvényt a WITHIN GROUP kulcsszavak követik, majd zárójelben egy rendezési relációt kell megadni, mely szerint az oszlop értékei rendezve lesznek a felsoroláson belül.Szintaxisa:LISTAGG(sql_kifejezés, elválasztó_karakater) WITHIN GROUP (ORDER BYoszlopnév) [OVER (PARTITION BY oszlopnév)]Példa:SELECT dolgozo.fizetés "Fizetés",listagg(dolgozo.név,', ') WITHIN GROUP (ORDER BY dolgozo.név) "Nevek"FROM aa dolgozoGROUP BY dolgozo.fizetés;Felhasznált irodalom:Analitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

Az NTILE függvény a rendezettséget alapul véve a partíció elemeit a paraméterekéntn kapott számú kosárba osztja szét. Amennyiben a partíción belül a sorok száma nem többszöröse a kosarak számának, úgy mindig az alacsonyabb sorszámú kosarak kerülnek először feltöltésre.Szintaxisa:NTILE(kosarak száma) OVER ([PARTITION BY oszlopnév] ORDER BY oszlopnév)Példa:SELECT dolgozo.név "Név",dolgozo.telep "Telephely",NTILE(3) OVER (PARTITION BY dolgozo.telep ORDER BY dolgozo.név) "Kosár"FROM aa dolgozo;Felhasznált irodalom:Analitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft

A WIDTH_BUCKET egy hisztogramm függvény, mely egy kiértékelt kifejezés értékeit szétosztja egy egyenlő részekre felosztott intervallumon.Négy paramétere van:· sql kifejezés: Ez a kifejezés adja az értéket melyet az intervallumon el kell helyezni.· alsó határ: Az intervallum kezdete.· felső határ: Az intervallum vége.· zsákok száma: Hány részre osszuk fel az intervallumot.Szintaxisa:WIDTH_BUCKET(sql_kifejezés, alsó_határ, felső_határ, zsákok_száma)Példa:SELECT dolgozo.név "Név",dolgozo.fizetés "Fizetés",WIDTH_BUCKET(dolgozo.fizetés,60000,130000,4) "Csoport"FROM aa dolgozo;Felhasznált irodalom:Analitikus és egyéb hasznos függvények Oracle 11g alatt, Konstantinusz Consult Tanácsadó Kft