Analitičke funkcije

Česte operacije

Zahtevi za multidimenzionim analizama (dimenzije – proizvodi, partije, konta, OJ):

Računanje trenutnog salda
Izračunavanje trenutnog proseka
Računanje ostalih suma (unutar podgrupa) (rollup, cube)
Procenat unutar grupe
Izračunavanje n najvećih
Razni drugi upiti rangiranja

SQL agregacije kao podrška za Data Warehous (skladišta podataka)

Prednosti proširenja:

- Manje koda

- Efikasnije izvršavanje

- Upiti se „keširaju“ u memoriji (Library cache)

Da bi dobili jednu ovakvu tabelu:

Pregled obračunskog prometa po klasama konta i org. jedinicama u određenom vremenskom periodu.

	500100	500105	500106	500107	500109	500110	502019	Total OJ
161	0	335.564,63	132,49				100.000,00	435.697,12
162	123.456.365.980,71	65.045.508,47	688,07	133,61	25,02	4.520,02		123.521.416.855,90
262	5.000,00	0						5.000,00
Total konto	123.456.370.980,71	65.381.073,10	820,56	133,61	25,02	4.520,02		123.521.857.553,02

I način - Preko cube i rollup

select konto_id, org_jed, sum(iznos) promet

from l_stavke

where konto_id like '50%'

and org_jed in('161','162','262')

and datum_obrade between to_date('1-04-2004','DD-MM-YYYY') and to_date('1-05-2004','DD-MM-YYYY')

group by cube(konto_id,org_jed)

II način - unija (više koda, duže izvršavanje)

select konto_id, org_jed, sum(iznos) promet

from l_stavke

where konto_id like '50%'

and org_jed in('161','162','262')

and datum_obrade between to_date('1-04-2004','DD-MM-YYYY') and to_date('1-05-2004','DD-MM-YYYY')

group by konto_id,org_jed

union

select null,org_jed,sum(iznos) promet

from l_stavke

where konto_id like '500100'

and org_jed ='162'

and datum_obrade between to_date('1-04-2004','DD-MM-YYYY') and to_date('1-05-2004','DD-MM-YYYY')

group by org_jed

union .....

Proširenja uslova group by sa rollup( ) i cube( )

Obe funkcije dozvoljavaju da u „group by“ uslovu definišemo tačno grupu podataka koji su nam od interesa.

Rollup( )- Računa različite nivoe subtotala(medjuzbirova) , od najnižeg do ukupne sume, a moguće ga je koristiti i parcijalno po kolonama.

Cube ( ) – Računa sve moguće varijante agregacija koje su inače potrebne za unakrsne izveštaje i to radi samo sa jednim upitom. Veoma je zahtevan i preporučljivo ga je koristiti sa „grouping set“ uslovima, kako bi poboljšali perfomanse.

U rezultatima upita, NULL vrednosti označavaju subtotale.

Rollup (reference lista kolona)

SELECT .... GROUP BY ROLLUP(reference lista kolona); i

SELECT .... GROUP BY expr1, ROLLUP(expr2, expr3);

n - broj kolona

n+1 - subtotala

Za kolone : konto_id, org_jed, sifra_valute

I - zbir za konto_id, org_jed, sifra_valute

II - zbir za konto_id, org_jed

III - zbir za konto_id

IV - ukupan zbir

select konto_id, org_jed, sifra_valute, sum(stanje_din_potrazuje-stanje_din_duguje) as stanje_ukupno

from lstanje

where konto_id like '4%'

and org_jed in('110','161','162')

group by rollup(konto_id, org_jed, sifra_valute)

KONTO_ID	ORG_JED	SIFRA_VALUTE	STANJE_UKUPNO
403000	110	891	-851457,22
403000	110		-851457,22
403000			-851457,22
424010	110	891	30935401,48
424010	110		30935401,48
424010	161	891	707292,64
424010	161		707292,64
424010			31642694,12
424020	110	891	30206,31
424020	110		30206,31
424020			30206,31
439001	110	891	6135475776,03
439001	110		6135475776,03
439001			6135475776,03
440000	110	891	-1800000
440000	110		-1800000
440000			-1800000
...........
453000	110	756	-377964,99
453000	110	840	392527,31
453000	110	978	-1220446
453000	110		-1205883,68
453000			-1205883,68
453100	110	840	0
453100	110		0
453100			0
461200	110	840	123786600
461200	110	978	6087210
461200	110		129873810
461200	161	280	0
461200	161	840	60590
461200	161	978	6632314,67
461200	161		6692904,67
461200			136566714,67
........
488506	110	40	0
488506	110	250	0
488506	110	280	0
488506	110	756	921974,38
488506	110	826	3891456
488506	110	840	71177,3
488506	110	978	13359178,4
488506	110		18243786,08
488506	161	840	1211800
488506	161	978	7145523,92
488506	161		8357323,92
488506			26601110
488507	110	756	1390282,76
488507	110	978	39126592
488507	110		40516874,76
488507			40516874,76
			6353611867,02

Rollup ne pravi sve subtotale, a napravljeni zavise od redosleda kolona. Subtotali se računaju s desna na levo. Za ostale je potrebno izmeniti redosled kolona u upitu.

Cube (reference lista kolona)

Cube( )- Računa sve moguće varijante subtotala. Menja nekoliko union all upita.

SELECT … GROUP BY

CUBE (group_reference_lista)

SELECT … GROUP BY expr1,

CUBE (expr2, expr3)

Za n kolona , cube() vraća 2^n kombinacija.

select konto_id, org_jed, sifra_valute, sum(stanje_din_potrazuje-stanje_din_duguje) as stanje_ukupno

from lstanje

where konto_id like '4%'

and org_jed in('161','162')

group by cube(konto_id,org_jed, sifra_valute)

KONTO_ID	ORG_JED	SIFRA_VALUTE	STANJE_UKUPNO
			15757521,23
		280	0
		840	1272390
		891	707292,64
		978	13777838,59
	161		15757521,23
	161	280	0
	161	840	1272390
	161	891	707292,64
	161	978	13777838,59
424010			707292,64
424010		891	707292,64
424010	161		707292,64
424010	161	891	707292,64
451000			0
451000		978	0
451000	161		0
451000	161	978	0
461200			6692904,67
461200		280	0
461200		840	60590
461200		978	6632314,67
461200	161		6692904,67
461200	161	280	0
461200	161	840	60590
461200	161	978	6632314,67
471000			0
471000		978	0
471000	161		0
471000	161	978	0
488506			8357323,92
488506		840	1211800
488506		978	7145523,92
488506	161		8357323,92
488506	161	840	1211800
488506	161	978	7145523,92

Grouping

Pitanje programskog određivanja koje grupa čine subtotal i kog je on nivoa, određuje se „grouping“ funkcijama.

Kako razlikovati null vrednosti od onih koje su rezultat cube i rollup-a ???

SELECT … [GROUPING(dimension_column)…] …

GROUP BY … {CUBE | ROLLUP| GROUPING SETS} (dimension_column)

select decode(grouping(konto_id),1,'Sve podklase 500',konto_id) konto,

decode(grouping(org_jed),1,'Sve OJ',org_jed) org_jed,

decode(grouping(sifra_valute),1,'Sve valute',sifra_valute ) sifra_valute,

sum(stanje_din_potrazuje-stanje_din_duguje) as stanje_ukupno

from l_stanje

where konto_id like '500%'

and org_jed in('161','162')

group by cube(konto_id,org_jed, sifra_valute)

KONTO	ORG_JED	SIFRA_VALUTE	STANJE_UKUPNO
Sve podklase 500	Sve OJ	Sve valute	126968735,59
Sve podklase 500	Sve OJ	891	126968735,59
Sve podklase 500	161	Sve valute	2451924,63
Sve podklase 500	161	891	2451924,63
Sve podklase 500	162	Sve valute	124516810,96
Sve podklase 500	162	891	124516810,96
500005	Sve OJ	Sve valute	191577,2
500005	Sve OJ	891	191577,2
500005	161	Sve valute	0
500005	161	891	0
500005	162	Sve valute	191577,2
500005	162	891	191577,2
500100	Sve OJ	Sve valute	11482018,33
500100	Sve OJ	891	11482018,33
500100	161	Sve valute	-2236309,74
500100	161	891	-2236309,74
500100	162	Sve valute	13718328,07
500100	162	891	13718328,07
500105	Sve OJ	Sve valute	113025166,59
500105	Sve OJ	891	113025166,59
500105	161	Sve valute	2410682,82
500105	161	891	2410682,82
500105	162	Sve valute	110614483,77
500105	162	891	110614483,77
500107	Sve OJ	Sve valute	2761679
500107	Sve OJ	891	2761679
500107	161	Sve valute	2761679
500107	161	891	2761679
500108	Sve OJ	Sve valute	22108,97
500108	Sve OJ	891	22108,97
500108	161	Sve valute	11845,05
500108	161	891	11845,05
500108	162	Sve valute	10263,92
500108	162	891	10263,92
500109	Sve OJ	Sve valute	-513814,5
500109	Sve OJ	891	-513814,5
500109	161	Sve valute	-495972,5
500109	161	891	-495972,5
500109	162	Sve valute	-17842
500109	162	891	-17842

Takođe, iz grupe zbirova , možemo izdvojiti samo one koji su nama interesantni:

select decode(grouping(org_jed),1,'Sve OJ',org_jed) org_jed,

decode(grouping(konto_id),1,'Sve podklase 500',konto_id) konto,

decode(grouping(sifra_valute),1,'Sve valute',sifra_valute ) sifra_valute,

sum(stanje_din_potrazuje-stanje_din_duguje) as stanje_ukupno

from l_stanje

where konto_id like '500%'

and org_jed in('161','162')

group by cube(org_jed,konto_id,sifra_valute)

having ((grouping(konto_id)=1) AND (grouping(org_jed)=1) AND (grouping(sifra_valute)=1)) OR

((grouping(konto_id)=1) AND (grouping(org_jed)=1)) OR

((grouping(org_jed)=1) AND (grouping(sifra_valute)=1))

order by org_jed,konto,sifra_valute

ORG_JED	KONTO	SIFRA_VALUTE	STANJE_UKUPNO
Sve OJ	500005	Sve valute	191577,2
Sve OJ	500100	Sve valute	11483066,61
Sve OJ	500105	Sve valute	113025166,59
Sve OJ	500107	Sve valute	2761679
Sve OJ	500108	Sve valute	22108,97
Sve OJ	500109	Sve valute	-513814,5
Sve OJ	Sve podklase 500	891	126969783,87
Sve OJ	Sve podklase 500	Sve valute	126969783,87

Korisničke agregatne funkcije

Oracle omogućava da kreirate sopstvene korisničke funkcije. One mogu biti kreirane u PL/SQL-u, Javi ili C –u.

Sintaksa analitičkih funkcija

FUNCTION_NAME (<argument>, <argument>,...)

OVER

(<partition_clause> <order by_clause> <windowing_clause> )

Analitičke funkcije se sastoje iz najviše 4 glavna dela osim samog imena funkcije. To su:

Argumenti
Uslov particionisanja
Uslov poretka
„Windowing“ uslov

„OVER“ - je klauzula koja ukazuje parseru da je u pitanju analitička funkcija

Uslov upita iza ključne reči OVER opisuje deo podataka na koje ćemo primeniti analitičku funkciju.

„PARTITION BY“ - je opcioni uslov. Ako ne postoji, ceo rezultat se tretira kao jedna velika particija. Koristimo ga da bi podelili rezultat u grupe i onda primenili analitičke funkcije na svaku grupu pojedinačno.

„ORDER BY“ - za neke funkcije obavezan (windowing), za neke ne. Služi da izvrši sortiranje unutar grupe podataka kad izračunavate analitičku funkciju.

Funkcije

Oko 26 analitičkih funkcija

Podela funkcija po funkcionalnosti:

Rangiranje
Windowing
Reporting
Leg and Lead
Statistic

„Partition by“ uslov

PARTITION BY <izraz1> <,izraz2> <,izraz3>

Logički razbija rezultat u N grupa, prema kriterijumu u uslovu particionisanja. Analitička funkcija se potom primenjuje na svaku grupu ponaosob.

Svako pojavljivanje analitičke f. u upitu (ako ih ima više), može imati potpuno različite uslove particionisanja.

Ako izostavite uslov, onda se ceo rezultat smatra grupom.

„Order by“ uslov

ORDER BY izraz <ASC|DESC> <NULLS FIRST|NULLS LAST>,

Precizira kako su podaci sortirani unutar svake grupe (particije). Analitičke funkcije se potpuno različito ponašaju u prisustvu „order by“ i bez njega, tako da ovaj uslov znatno utiče na rezultate.

Npr. kod funkcije avg() , bez uslova „order by“, prosek računamo za celu grupu i ista vredost se nalazi u svakom redu,

Sa uslovomi, prosek se računa za prethodne redove unutar okvira uključujući i tekući red.

Polednja opcija sa nulama definiše da li će se nule naći na početku ili na kraju.

Windowing uslov

Omogućava nam da definišemo niz podataka unutar grupe (sa fiksiranom ili pomerajućom granicom) na koje ćemo primeniti funkcije.

Predefinisana grupa podataka se odnosi na niz od prvog reda do tekućeg reda.

Opseg (window) možemo podesiti na osnovu dva kriterijuma:

- Opsega vrednosti (range windows)

- Row offset-a (row windows)

Prozor ima svoj prvi i poslednji red. Sve analitičke funkcije su zasnovane na tekućem redu kao referentnoj tački.

Range window

Skuplja redove u grupu na osnovu where uslova.

Select count(*) over (order by hiredate range 100 preceding)

Row window

Row window je fizička jedinica.

Zadaje fizički broj redova koje treba uključiti u opseg.

Select count(*) over (order by hiredate rows 5 preceding)

Redosled obrade upita:

Joins, where, group by, having

Partitions created; analytic func. apply to each row in partition

Final ORDER BY

Pregled funkcija sa primerima:

Rangiranje

služe za upite tipa nalaženje najvećih N ( top N).

DENSE_RANK - Računa relativni položaj reda (ceo broj) u odnosu na druge redove unutar grupe, a na osnovu vrednosti dobijene izrazom iz „order by“ uslova.
Vrednosti unutar grupe su sortirane i prvi broj u grupi je 1, a rang se povećava za svaku sledeću veću vrednost unutar grupe.
Redovi sa istom vrednošću imaju isti rang.
RANK - Sličan je DENSE_RANK funkciji. Za razliku od nje, kad imamo više od jedne iste vrednosti u grupi, samo jedna vrednost ima rang, a sledeća vrednost iz grupe nosi veću vrednost (uvećanu za broj prethodnih istih vrenosti)
Default način sortiranja je ascending.
Vrednost za RANK se resetuje po prelasku na drugu grupu.
ROW_NUMBER ( ) – vraca redni broj reda u grupi
NTILE
PERCENT_RANK() -
(rank of row in its partit.-1)/(number of rows in the part. - 1)
CUME_DIST ( ) – Racuna relativan polozaj reda u tabeli. Vraca broj veći od 0 i manji ili jednak 1. U grupi od 3 reda, kumulativa distribucija vraca 1/3, 2/3 ili 3/3.

select partija, sifra_valute,org_jed, stanje_val_duguje-stanje_val_potrazuje stanje,

rank () over (partition by org_jed order by stanje_val_potrazuje-stanje_val_duguje) rang,

cume_dist() over (partition by org_jed order by stanje_val_potrazuje-stanje_val_duguje) distr

from lstanje

where org_jed in('161','162')

and stanje_val_duguje-stanje_val_potrazuje >0

and rownum <10

order by org_jed, rang, partija

PARTIJA	SIFRA_VALUTE	ORG_JED	STANJE	RANG	DISTR
161934315	891	161	666000	1	0,111111111111111
161934374	891	161	50000	2	0,333333333333333
463006200010000079	891	161	50000	2	0,333333333333333
408036010040513347	891	161	20000	4	0,444444444444444
408156200040000586	891	161	10000	5	0,666666666666667
419006210040000037	891	161	10000	5	0,666666666666667
16194926	978	161	3000	7	0,777777777777778
16157000899	978	161	1000	8	1
16199146	891	161	1000	8	1

select partija, sifra_valute,org_jed, stanje_val_duguje-stanje_val_potrazuje stanje,

dense_rank () over (partition by org_jed order by stanje_val_potrazuje-stanje_val_duguje) rang

from lstanje

where org_jed in('161','162')

and stanje_val_duguje-stanje_val_potrazuje >0

and rownum <10

order by org_jed, rang, partija

PARTIJA	SIFRA_VALUTE	ORG_JED	STANJE	RANG
161934315	891	161	666000	1
161934374	891	161	50000	2
463006200010000079	891	161	50000	2
408036010040513347	891	161	20000	3
408156200040000586	891	161	10000	4
419006210040000037	891	161	10000	4
16194926	978	161	3000	5
16157000899	978	161	1000	6
16199146	891	161	1000	6

select partija, sifra_valute,org_jed, stanje_val_duguje-stanje_val_potrazuje stanje,

rank () over (partition by org_jed order by stanje_val_potrazuje-stanje_val_duguje) rang,

row_number () over (partition by org_jed order by stanje_val_potrazuje-stanje_val_duguje) rd_broj

from lstanje

where org_jed in('161','162')

and stanje_val_duguje-stanje_val_potrazuje >0

and rownum <10

order by org_jed, rang, partija

PARTIJA	SIFRA_VALUTE	ORG_JED	STANJE	RANG	RD_BROJ
161934315	891	161	666000	1	1
161934374	891	161	50000	2	2
463006200010000079	891	161	50000	2	3
408036010040513347	891	161	20000	4	4
408156200040000586	891	161	10000	5	5
419006210040000037	891	161	10000	5	6
16194926	978	161	3000	7	7
16157000899	978	161	1000	8	8
16199146	891	161	1000	8	9

Primer: Tri partije sa najvećim stanjem u OJ

select * from (

select partija, sifra_valute,org_jed, stanje_val_duguje-stanje_val_potrazuje stanje,

rank () over (partition by org_jed order by stanje_val_potrazuje-stanje_val_duguje) rang,

cume_dist() over (partition by org_jed order by stanje_val_potrazuje-stanje_val_duguje) distr

from lstanje

where stanje_val_duguje-stanje_val_potrazuje >0

and rownum <10

order by org_jed, rang, partija

) where rang<=3

PARTIJA	SIFRA_VALUTE	ORG_JED	STANJE	RANG	DISTR
110933648	891	110	10000	1	0,25
11094397	978	110	2200	2	0,5
11094397	978	110	440	3	0,75
161934315	891	161	666000	1	0,2
463006200010000079	891	161	50000	2	0,4
408036010040513347	891	161	20000	3	0,6

Windowing

Za izračunanje raznih agregatnih rezultata (sum(), avg(), min, max) unutar okvira podataka.

Granice okvira se utvrđuju u odnosu na tekući red za koji se i računa funkcija.

Granice okvira (prozor) mogu biti nepromenljive za svaki red ili mogu biti promenljive i za svaki tekući red drugačije.

Granica može biti sa fizičkim (rows) i sa logičkim ograničenjem (range)

Mogu se koristiti u select i order by uslovu

{SUM|AVG|MAX|MIN|COUNT|STDDEV|VARIANCE|FIRST_VALUE|LAST_VALUE}

({value expression1 | *}) OVER

([PARTITION BY value expression2[,...])

ORDER BY value expression3 [collate clause>]

[ASC| DESC] [NULLS FIRST | NULLS LAST] [,...]

{ ROWS | RANGE }

{ BETWEEN

{ UNBOUNDED PRECEDING

| CURRENT ROW

| value_expr { PRECEDING | FOLLOWING }

}

AND

{ UNBOUNDED FOLLOWING

| CURRENT ROW

| value_expr { PRECEDING | FOLLOWING }

}

| { UNBOUNDED PRECEDING

| CURRENT ROW

| value_expr PRECEDING

}

unbounded preceding - od početka particije

ubounded following - do kraja particije

FIRST_VALUE ( ) - vraća prvu vrednost iz definisanog okvira (window)
LAST_VALUE ( ) - vraća poslednju vrednost iz okvira
AVG(), SUM(), MIN(), MAX(), COUNT()

Primer: pregled uplata i 3-mesečni prosek uplata „od ... do“ za OJ_ID <=120 na račune PP

select org_jed_prijema oj_prijema, sifra_valute, iznos_placanja, datum_valute, datum_valute-45 pre, datum_valute+45 posle,

avg(iznos_placanja) over (partition by org_jed_prijema, sifra_valute
order by datum_valute asc
range between 45 preceding and 45 following ) prosek,

first_value(datum_valute)over(partition by org_jed_prijema, sifra_valute
order by datum_valute asc
range between 45 preceding and 45 following ) prvi

from pp3nalozipp

where org_jed_prijema<=120

order by org_jed_prijema, sifra_valute, datum_valute

OJ_PRIJEMA	SIFRA_VALUTE	IZNOS_PLACANJA	DATUM_VALUTE	PRE	POSLE	PROSEK	PRVI
30	891	100	23.11.2003	09.10.2003	07.01.2004	469.00	23.11.2003
30	891	95	23.11.2003	09.10.2003	07.01.2004	469.00	23.11.2003
30	891	500	24.11.2003	10.10.2003	08.01.2004	469.00	23.11.2003
30	891	400	24.11.2003	10.10.2003	08.01.2004	469.00	23.11.2003
30	891	1250	22.12.2003	07.11.2003	05.02.2004	469.00	23.11.2003
30	977	1200	17.04.2003	03.03.2003	01.06.2003	9,316.14	17.04.2003
30	977	1000	17.04.2003	03.03.2003	01.06.2003	9,316.14	17.04.2003
30	977	1000	17.04.2003	03.03.2003	01.06.2003	9,316.14	17.04.2003
30	977	3000	17.04.2003	03.03.2003	01.06.2003	9,316.14	17.04.2003
30	977	3000	17.04.2003	03.03.2003	01.06.2003	9,316.14	17.04.2003
30	977	13	17.04.2003	03.03.2003	01.06.2003	9,316.14	17.04.2003
30	977	56000	17.04.2003	03.03.2003	01.06.2003	9,316.14	17.04.2003
100	891	1	05.05.2003	21.03.2003	19.06.2003	3,942.86	05.05.2003
100	891	3	05.05.2003	21.03.2003	19.06.2003	3,942.86	05.05.2003
100	891	25410	05.05.2003	21.03.2003	19.06.2003	3,942.86	05.05.2003
100	891	183,87	05.05.2003	21.03.2003	19.06.2003	3,942.86	05.05.2003
100	891	1,13	05.05.2003	21.03.2003	19.06.2003	3,942.86	05.05.2003
100	891	2000	05.05.2003	21.03.2003	19.06.2003	3,942.86	05.05.2003
100	891	1	05.05.2003	21.03.2003	19.06.2003	3,942.86	05.05.2003
100	891	2	17.11.2003	03.10.2003	01.01.2004	2.00	17.11.2003
110	891	4440,54	07.08.2003	23.06.2003	21.09.2003	4,440.54	07.08.2003
110	891	1000	26.11.2003	12.10.2003	10.01.2004	977,192.63	26.11.2003
110	891	565577,9	26.11.2003	12.10.2003	10.01.2004	977,192.63	26.11.2003
110	891	2365000	26.11.2003	12.10.2003	10.01.2004	977,192.63	26.11.2003
110	891	6944290	07.05.2004	23.03.2004	21.06.2004	6,944,290.00	07.05.2004
120	891	1368134	25.03.2004	09.02.2004	09.05.2004	1,368,134.00	25.03.2004

Primer za sumu:

select org_jed_prijema oj_prijema, sifra_valute, iznos_placanja, datum_valute,

to_char(sum(iznos_placanja) over (partition by org_jed_prijema, sifra_valute

order by iznos_placanja),'999,999,999.99') suma

from pp3nalozipp

where org_jed_prijema<=120

order by org_jed_prijema, sifra_valute, datum_valute

OJ_PRIJEMA	SIFRA_VALUTE	IZNOS_PLACANJA	DATUM_VALUTE	SUMA
30	891	95	23.11.2003	95.00
30	891	100	23.11.2003	195.00
30	891	400	24.11.2003	595.00
30	891	500	24.11.2003	1,095.00
30	891	1250	22.12.2003	2,345.00
30	977	13	17.04.2003	13.00
30	977	1000	17.04.2003	2,013.00
30	977	1000	17.04.2003	2,013.00
30	977	1200	17.04.2003	3,213.00
30	977	3000	17.04.2003	9,213.00
30	977	3000	17.04.2003	9,213.00
30	977	56000	17.04.2003	65,213.00
100	891	1	05.05.2003	2.00
100	891	183,87	05.05.2003	192.00
100	891	25410	05.05.2003	27,602.00
100	891	2000	05.05.2003	2,192.00
100	891	3	05.05.2003	8.13
100	891	1	05.05.2003	2.00
100	891	1,13	05.05.2003	3.13
100	891	2	17.11.2003	5.13
110	891	4440,54	07.08.2003	5,440.54
110	891	1000	26.11.2003	1,000.00
110	891	565577,9	26.11.2003	571,018.44
110	891	2365000	26.11.2003	2,936,018.44
110	891	6944290	07.05.2004	9,880,308.44
120	891	1368134	25.03.2004	1,368,134.00

Izveštavanje

Za razliku od window funkcija koje rade nad okvirom podataka, ove funkcije rade na svim podacima unutar particije.

Ista agregatna vrednot se vraća za svaki red unutar particije.

Praktično - Isto kao i windowing funkcija samo nema okvir i order by uslov

Mogu se koristiti u select i order by uslovu.

{SUM | AVG | MAX | MIN | COUNT | STDDEV | VARIANCE}

([ALL | DISTINCT] {value expression1 | *})

OVER ([PARTITION BY value expression2[,...]])

AVG ( ) -
COUNT ( ) -
MIN ( ) -
MAX ( ) -
SUM ( ) -
RATIO_TO_REPORT - Unutar grupe vraća vrednost izraz/(suma(izraza)). Daje procenat koliki udeo red ima u ukupnoj sumi.

RATIO_TO_REPORT ( expr ) OVER ( [query_partition_clause] )

select org_jed_prijema oj, sifra_valute s_val, iznos_placanja, datum_valute,

to_char(sum(iznos_placanja) over (partition by org_jed_prijema, sifra_valute),'999,999,999.99') suma,

to_char(ratio_to_report(sum(iznos_placanja)) over (partition by org_jed_prijema, sifra_valute),'999,999,999.99') udeo

from pp3nalozipp

where org_jed_prijema<=120

group by org_jed_prijema,sifra_valute,iznos_placanja, datum_valute

order by org_jed_prijema, sifra_valute, datum_valute, iznos_placanja

OJ	S_VAL	IZNOS_PLACANJA	DATUM_VALUTE	SUMA	UDEO
30	891	95	23.11.2003	2,345.00	.04
30	891	100	23.11.2003	2,345.00	.04
30	891	400	24.11.2003	2,345.00	.17
30	891	500	24.11.2003	2,345.00	.21
30	891	1250	22.12.2003	2,345.00	.53
30	977	13	17.04.2003	61,213.00	.00
30	977	1000	17.04.2003	61,213.00	.03
30	977	1200	17.04.2003	61,213.00	.02
30	977	3000	17.04.2003	61,213.00	.09
30	977	56000	17.04.2003	61,213.00	.86
100	891	1	05.05.2003	27,601.00	.00
100	891	1,13	05.05.2003	27,601.00	.00
100	891	3	05.05.2003	27,601.00	.00
100	891	183,87	05.05.2003	27,601.00	.01
100	891	2000	05.05.2003	27,601.00	.07
100	891	25410	05.05.2003	27,601.00	.92
100	891	2	17.11.2003	27,601.00	.00
110	891	4440,54	07.08.2003	9,880,308.44	.00
110	891	1000	26.11.2003	9,880,308.44	.00
110	891	565577,9	26.11.2003	9,880,308.44	.06
110	891	2365000	26.11.2003	9,880,308.44	.24
110	891	6944290	07.05.2004	9,880,308.44	.70
120	891	1368134	25.03.2004	1,368,134.00	1.00

Leg and Lead

Omogućavaju vam da se krećete po setu rezultata kako bi dobili podatke i istovremeno da izbegnete self-join povezivanja.

{LAG | LEAD} ( value_expr [, offset] [, default] )

OVER ( [query_partition_clause] order_by_clause )

LAG (izraz, ofsset, default) - Daje mogucnost da izaberete prethodni red iz grupe rezultata.
Ukoliko je index van opsega prozora , vraca default vrednost.
Offset je pozitivan, ceo broj , po default-u je 1. Može biti i veći.
LEAD (izraz, ofsset, default) - Vraća sledeći red iz grupe.

select org_jed_prijema oj, sifra_valute s_val, iznos_placanja, datum_valute,

lag(iznos_placanja,1) over (partition by org_jed_prijema, sifra_valute order by datum_valute) pret_suma,

lead(iznos_placanja,1) over (partition by org_jed_prijema, sifra_valute order by datum_valute) sled_suma

from pp3nalozipp

where org_jed_prijema<=120

group by org_jed_prijema,sifra_valute,iznos_placanja, datum_valute

order by org_jed_prijema, sifra_valute, datum_valute, iznos_placanja

OJ	S_VAL	IZNOS_PLACANJA	DATUM_VALUTE	PRET_SUMA	SLED_SUMA
30	891	95	23.11.2003		100
30	891	100	23.11.2003	95	400
30	891	400	24.11.2003	100	500
30	891	500	24.11.2003	400	1250
30	891	1250	22.12.2003	500
30	977	13	17.04.2003		1000
30	977	1000	17.04.2003	13	1200
30	977	1200	17.04.2003	1000	3000
30	977	3000	17.04.2003	1200	56000
30	977	56000	17.04.2003	3000
100	891	1	05.05.2003		1,13
100	891	1,13	05.05.2003	1	3
100	891	3	05.05.2003	1,13	183,87
100	891	183,87	05.05.2003	3	2000
100	891	2000	05.05.2003	183,87	25410
100	891	25410	05.05.2003	2000	2
100	891	2	17.11.2003	25410
110	891	4440,54	07.08.2003		1000
110	891	1000	26.11.2003	4440,54	565577,9
110	891	565577,9	26.11.2003	1000	2365000
110	891	2365000	26.11.2003	565577,9	6944290
110	891	6944290	07.05.2004	2365000
120	891	1368134	25.03.2004

Case izrazi

Case expr WHEN comparison_expr THEN return_expr

[, WHEN comparison_expr THEN return_expr]... end

Primer:

select partija, sifra_valute, org_jed,

(case when stanje_val_duguje-stanje_val_potrazuje>0
then stanje_val_duguje-stanje_val_potrazuje

else stanje_val_potrazuje-stanje_val_duguje end) stanje
from lstanje;

Hipotetički rang i distribucija

Ove funkcije omogućavaju funkcionalnosti „šta ako“ analize. Kao primer može biti da se nađe novi rang prometa partije, ako se u medjuvremenu doda novi zapis za uplatu i sl.

Funkcija ima jedan ili više hipotetičkih redova za argumente, a vraća rang.

Primer:

Ako uplatimo na neku partiju sumu od 25.000,00 din, kako će se rangirati takva uplata unutar svake OJ ponaosob.

[RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST]( constant expression [, ...] )

WITHIN GROUP ( ORDER BY order by expression [ASC|DESC] [NULLS FIRST|NULLS

LAST][, ...] )

select org_jed_prijema oj,

--rank() over (order by iznos_placanja desc) rang,

rank(25000) within group (order by iznos_placanja desc) hiprang

from pp3nalozipp

group by org_jed_prijema

Statističke – izračunavaju statističke vrednosti neuređene particije.

Oracle ima i set statističih funkcija koje omogućavaju izračunavanje kovarijansi, korelacije, linearne regresije itd.

VAR_POP
VAR_SAMP
STDEV_POP
STDEV_SAMP
COVAR_POP
COVAR_SAMP
REGR_COUNT
REGR_AVGX
REGR_AVGY
REGR_SLOPE
REGR_INTERCEPT
REGR_R2
REGR_SXX
REGR_SYY
REGR_SXY