ANALITI�KE FUNKCIJE

Neboj�a Vidi�, OCP DBA

Antegra Informacioni Sistemiďż˝ d.o.o.

ANALITI�KE FUNKCIJE. 1

Analiti�ke funkcije. 3

�este operacije. 3

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

Pro�irenja uslova group by sa�� rollup( ) i� cube( ) 4

Rollup (reference lista kolona).. 4

Cube (reference lista kolona) 6

Grouping. 7

Korisni�ke agregatne funkcije. 9

Sintaksa� analiti�kih� funkcija. 9

Funkcije. 10

�Partition by� uslov. 10

�Order by� uslov. 10

Windowing uslov. 10

Range window.. 11

Row window.. 11

Pregled funkcija sa primerima: 11

Rangiranje. 11

Windowing. 14

Izve�tavanje. 16

Leg and Lead. 17

Case izrazi 19

Hipoteti�ki rang i distribucija. 19

Statisti�ke � izra�unavaju statisti�ke vrednosti neure�ene particije. 19

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

ďż˝