Funcţii analitice

nume_functie ([argumente]) over ([partitie] [ordonare] [zona])

• partitie: partition by (lista_expresii)),
  Pentru fiecare înregistrare din sursa de date (precizată în instrucţiunea Select) este determinată o partiţie (submulţime de înregistrări din sursă). Intr-o astfel de partiţie se vor include toate înregistrările din sursă care au aceleaşi valori pentru o listă de expresii. Fără opţiunea partiţie se va genera o singură partiţie, care cuprinde toată sursa de date.
• ordonare: order by lista - precizează ordonarea înregistrărilor în partiţie.
  Un element din listă este: expresie [{asc | desc}] [{NULLS FIRST | NULLS LAST}]
• Pentru fiecare înregistrare curentă din sursa de date se determină o partiţie, unde această înregistrare ocupă o anumită poziţie.
• Pentru înregistrarea curentă se poate preciza o anumită zonă din partiţie (o succesiune de înregistrări) care este precizată printr-o construcţie de forma:
  {rows | range} domeniu
  unde domeniu este de forma: between A [and B]
  A: {unbounded preceding | current row | expresie preceding}
  B: {unbounded following | current row | expresie following}
  rows: o fereastră precizată prin liniile sursei (poziţii fizice, prin numărul de linii)
  range: o fereastră dată ca referinţe logice (prin valorile existente)
  Zona menţionată pentru înregistrarea curentă are un început şi un sfârşit, care pot fi:
  • unbounded preceding - începutul partiţiei
  • unbounded following - sfârşitul partiţiei
  • current row - înregistrarea curentă
  • expresie preceding, expresie following - un număr de înregistrări în faţa celei curente, respectiv după aceasta, sau aflate la o anumită distanţă de înregistrarea curentă. In această variantă partiţia trebuie ordonată după valorile unei singure expresii de sortare. Expresia din opţiune precizează distanţa dintre valoarea pentru expresia de sortare din înregistrarea curentă şi înregistrarea care precizează începutul sau sfârşitul zonei.

In exemplele următoare se va folosi un view cu anumite date despre studenţi:

INFSTUD[sectia, denumire, anstudiu, grupa, nume, prenume,  media]

Funcţii analitice posibile:

• funcţiile de grupare (agregare): avg, count, max, min, sum, variance, corr
  

  -- 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;
  

• row_number() - numărul liniei curente în partiţie
  

  -- 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;
  

• rank() - poziţia liniei curente în ordonarea valorilor din clauza de ordonare. Dacă există mai multe valori egale, atunci funcţia dă acelaşi rezultat cu poziţia primei valori;
  dense_rank() - poziţia în şirul de valori distincte
  Exemplu pentru următoarele valori ordonate:

  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;
  

• ntile(expresie) - partiţia generată se divide într-un număr de grupuri egal cu valoarea argumentului (aceste grupuri vor avea un număr aproximativ egal de linii). Funcţia va furniza indexul grupului în care se află înregistrarea curentă

  --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;
  

• percent_rank(): dacă i este rangul înregistrării, iar n este numărul de înregistrări, atunci funcţia dă valoarea (i-1)/n-1) din intervalul [0,1]
• first_value(expresie), last_value(expresie)
  

  --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;
  

• {lag | lead}(expresie, distanţă) - furnizează valoarea expresiei cu un anumit număr de înregistrări înapoi (lag) sau înainte (lead).

  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

• alte functii: COVAR_POP, COVAR_SAMP, CUME_DIST, FIRST, LAST, PERCENTILE_CONT, PERCENTILE_DISC, RATIO_TO_REPORT, REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY, STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP

Bibliografie:

• Analytic functions, http://docs.oracle.com/cd/E16655_01/timesten.121/e21642/function.htm#TTSQL489
• SQL for Analysis and Reporting, http://docs.oracle.com/cd/E16655_01/server.121/e17749/analysis.htm#DWHSG021
• Analytic Functions and Their Uses , http://docs.oracle.com/cd/E16655_01/server.121/e17749/analysis.htm#DWHSG8660