arabera Alexandra Paál 5 years ago
796
Analitikus függvények
Az analitikus függvények használata SQL-ben számos előnyt kínál, mivel bizonyos feladatok megoldása jóval egyszerűbbé válik ezek segítségével, mint hagyományos eszközökkel. Az ANSI/
arabera Alexandra Paál 5 years ago
796
Honelako gehiago
Felhasznált irodalom:
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)
(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:
Egyéb, főleg statisztikai függvények
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:
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:
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, amely
nem 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 dolgozo
GROUP BY dolgozo.fizetés;
Felhasznált irodalom:
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.telep
FROM aa dolgozo;
Felhasznált irodalom:
PERCENT_RANK
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:
CUME_DIST
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:
WITHIN GROUP
Mi lenne ha? típusú lekérdezések
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:
RANK(szám) WITHIN GROUP (ORDER BY oszlopnév)
DENSE_RANK(szám) WITHIN GROUP (ORDER BY oszlopnév)
Felhasznált irodalom:
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:
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 10
Szintaxisa:
DENSE_RANK () OVER ([ORDER BY oszlopnév])
Felhasznált irodalom:
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ési
relá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 10
Szintaxisa:
RANK () OVER ([ORDER BY oszlopnév])
Felhasznált irodalom:
A ROW_NUMBER esetén a sorszámok szigorúan monoton
nö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:
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] )
Változó méretű
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:
Halmozott összesítések
Halmozott összesítések - riportkészítő függvények
Pé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:
Partíciónként
Oszlop mentén képezhetünk partíciót is
Pé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)
Fix méretű
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:
Mozgó aggregált függvények
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:
SUM
MAX
MIN
AVG
FIRST_VALUE LAST_VALUE
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_v
FROM aa dolgozo)t;
Felhasznált irodalom:
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:
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:
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:
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_report
FROM test d;
Felhasznált irodalom:
Felhasznált irodalom:
Felhasznált irodalom:
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:
Felhasznált irodalom:
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 rendezni
Felhasznált irodalom:
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:
