nume_functie ([argumente]) over ([partitie] [ordonare] [zona])
In exemplele următoare se va folosi
un view cu anumite date despre studenţi:
INFSTUD[sectia, denumire, anstudiu, grupa, nume, prenume, cnp, media]
Funcţii analitice posibile:
-- pentru fiecare student se cere: nume, prenume, sectia, media, media sectiei din care face parte select nume, prenume, sectia, media, avg(media) over(partition by sectia) as ms from infstud order by nume,prenume;
--studentii promovati care au media mai mare decat media sectiei din care fac parte select * from (select sectia,nume,prenume,media, avg(media) over(partition by sectia) as media_s from infstud ) where media>media_s order by sectia,nume,prenume;
--pentru fiecare sectie se cere grupa (grupele) in care mediile studentilor sunt cel mai -- uniform repartizate in jurul mediei generale a grupei (grupa cea mai omogena) select * from (select sectia,grupa,nr_stud_grupa,media_grupa,dev_std_grupa, min(dev_std_grupa) over (partition by sectia) min_dev_std_grupa from (select sectia,grupa,count(*) nr_stud_grupa,avg(media) media_grupa,stddev(media) dev_std_grupa from infstud group by sectia,grupa) ) where dev_std_grupa=min_dev_std_grupa;
Exemplu pentru sume cumulative:
create table exemplu ( data date, x number ); insert into exemplu values (to_date('27.01.2015','DD.MM.YYYY'),4); insert into exemplu values (to_date('02.02.2015','DD.MM.YYYY'),1); insert into exemplu values (to_date('09.02.2015','DD.MM.YYYY'),5); insert into exemplu values (to_date('26.01.2015','DD.MM.YYYY'),3); insert into exemplu values (to_date('28.01.2015','DD.MM.YYYY'),4); insert into exemplu values (to_date('26.01.2015','DD.MM.YYYY'),6); insert into exemplu values (to_date('29.01.2015','DD.MM.YYYY'),9); insert into exemplu values (to_date('30.01.2015','DD.MM.YYYY'),2); insert into exemplu values (to_date('12.02.2015','DD.MM.YYYY'),7); insert into exemplu values (to_date('23.01.2015','DD.MM.YYYY'),2); insert into exemplu values (to_date('27.01.2015','DD.MM.YYYY'),5); insert into exemplu values (to_date('09.02.2015','DD.MM.YYYY'),9); insert into exemplu values (to_date('01.02.2015','DD.MM.YYYY'),3); insert into exemplu values (to_date('07.02.2015','DD.MM.YYYY'),1); insert into exemplu values (to_date('12.02.2015','DD.MM.YYYY'),4); insert into exemplu values (to_date('03.02.2015','DD.MM.YYYY'),5); insert into exemplu values (to_date('03.02.2015','DD.MM.YYYY'),8); insert into exemplu values (to_date('07.02.2015','DD.MM.YYYY'),7); insert into exemplu values (to_date('04.02.2015','DD.MM.YYYY'),8); insert into exemplu values (to_date('09.02.2015','DD.MM.YYYY'),1); insert into exemplu values (to_date('29.01.2015','DD.MM.YYYY'),3); insert into exemplu values (to_date('30.01.2015','DD.MM.YYYY'),7); insert into exemplu values (to_date('24.01.2015','DD.MM.YYYY'),7); insert into exemplu values (to_date('07.02.2015','DD.MM.YYYY'),9); insert into exemplu values (to_date('26.01.2015','DD.MM.YYYY'),2); insert into exemplu values (to_date('09.02.2015','DD.MM.YYYY'),8); select data,sum(x) over (partition by data order by data rows between unbounded preceding and current row) suma, x from exemplu; -- sume cumulative pentru o zi select data,sum(x) over (order by data rows between unbounded preceding and current row) suma, x from exemplu; -- sume cumulative pentru intreaga perioada drop table exemplu;
Se consideră tabelul următor (temp-scotia.sql):
--http://www.adp-gmbh.ch/ora/sql/analytical/edinburgh_temp.html -- --Average temperature in Edinburgh 1764-1820 for each month --(in degrees Fahrenheit) -- Tc = (5/9)*(Tf-32); Tc = temperature in degrees Celsius, Tf = temperature in degrees Fahrenheit -- create table scottish_weather ( -- month number(2), -- year number(4), -- avg_temp number(4,2) -- ); -- insert into scottish_weather values ( 1, 1764, 36.3 ); -- insert into scottish_weather values ( 2, 1764, 38.0 ); -- ...
--pentru fiecare luna se cer temperaturile medii pe ultimele 12 luni select month, year, avg_temp avg_F,round(5/9*(avg_temp-32)*10)/10 avg_C, round(5/9*(avg(avg_temp) over (order by year,month rows between 11 preceding and current row)-32)*10)/10 as avg_year_C from scottish_weather;
select distinct year,corr(month,avg_temp) over (partition by year) from scottish_weather order by year;
select distinct month,corr(year,avg_temp) over (partition by month) from scottish_weather order by 2;
-- pentru fiecare student se cere: nume, prenume, sectia, media, pozitia studentului -- in facultate si sectie (intr-o ordonare decrescatoare dupa medie) si nr. de stud. din sectia de care apartine select nume,prenume,sectia,media, row_number() over (order by media desc) as poz_f, row_number() over (partition by sectia order by media desc) as poz_s, count(*) over (partition by sectia) as nr_stud_sectie from infstud order by nume,prenume;
--pentru fiecare sectie se cere: -- grupa cu media generala maxima -- grupa cu dispersia pentru medie minima (grupa cea mai omogena) -- grupa cu dispersia pentru medie maxima (grupa cea mai neomogena) with infgrupe as (select sectia,grupa,count(*) nrstud, avg(media) media, stddev(media) stddev from infstud group by sectia,grupa) select s1.sectia,s1.grupa g1,s2.grupa g2,s3.grupa g3 from (select * from (select sectia,grupa,row_number() over (partition by sectia order by media desc) nr from infgrupe) where nr=1) s1 inner join (select * from (select sectia,grupa,row_number() over (partition by sectia order by stddev) nr from infgrupe) where nr=1) s2 on s1.sectia=s2.sectia inner join (select * from (select sectia,grupa,row_number() over (partition by sectia order by stddev desc) nr from infgrupe) where nr=1) s3 on s1.sectia=s3.sectia;
Valori: | v1 | v2 | v2 | v2 | v3 | v4 |
rank(): | 1 | 2 | 2 | 2 | 5 | 6 |
dense_rank(): | 1 | 2 | 2 | 2 | 3 | 4 |
--se cer primii trei studenti cu media cea mai mare din fiecare sectie -- cu ultima medie se iau toti select * from (select nume,prenume,sectia,media, rank() over (partition by sectia order by media desc) as pozitia from infstud ) where pozitia<=3 order by sectia,pozitia;
--pentru fiecare sectie se cer grupele cu cele mai mari doua numere de studenti -- si pozitia pe care le ocupa aceste grupe in ordonarea descrescatoare dupa media generala in cadrul sectiilor select * from (select sectia,grupa,avg(media) as medgr, count(*) as nr, dense_rank() over(partition by sectia order by avg(media) desc) as pozitia1, dense_rank() over(partition by sectia order by count(*) desc) as pozitia2 from infstud group by sectia,grupa) where pozitia2<=2;
--pentru fiecare student se cere procentul in care se incadreaza, pe sectia/an si facultate -- la o ordonare decrescatoare dupa medie select sectia,anstudiu,grupa,nume,prenume,media, ntile(100) over (partition by sectia order by media desc,anstudiu) as pozitia1, ntile(100) over (order by media desc,nume,prenume) as pozitia2 from infstud order by 1,2,4,5;
--pentru fiecare student se cere media, media maxima si media minima a studentilor din grupa select nume,prenume,grupa,media, first_value(media) over (partition by grupa order by media desc NULLS LAST) as med_max_grupa, first_value(media) over (partition by grupa order by media NULLS LAST) as med_min_grupa from infstud order by grupa,nume,prenume;
Evoluţia temperaturilor medii lunare:
select year,month,avg_temp,avg_temp-lag(avg_temp,1) over (order by year,month) from scottish_weather order by 1,2
select year,month,avg_temp,avg_temp-lag(avg_temp,12) over (order by year,month) from scottish_weather order by 1,2
Considerăm tabelul care memorează ratele de schimb medii lunare pentru euro, definit astfel:
create table euro(an number(4),luna number(2),euromediu number(6,4));
Se cer diferenţele ratelor de schimb între valorile din două luni consecutive
Bibliografie: