Funcţii de agregare

funcţiile de grupare (agregare): avg, count, max, min, sum, variance, stddev (deviatia standard = variance^0.5)

CORR(expresie1, expresie2): măsura dependenţei a două şiruri de valori.

Semnificaţia coeficientului de corelaţie (după Visually Evaluating Correlation):

select corr(luna,media)
  from
  (select to_number(substr(cnp,4,2)) as luna,media from infstud where media>=5);

select sectia,count(*) as nr,avg(media),corr(anstudiu,media) as c 
from infstud 
group by sectia order by 1;

LISTAGG: concatenarea valorilor unor expresii pentru înregistrările din grup

LISTAGG(expresie [, delimitator]) WITHIN GROUP (ORDER BY lista)

select sectia,listagg(trim(grupa),',') WITHIN GROUP (ORDER BY grupa) as g 
from infstud 
group by sectia;

Extensia PIVOT
Presupunem că avem sursa de date (tabel, view):
```
stud[sectia,nrmatricol,nume,prenume,cnp,anstudiu,grupa] 
```
Cu extensia group by se pot obţine diverse date utile pentru analiza datelor.
Exemple:
```
select sectia,count(*) nr from stud group by sectia order by 1;
```
```
select sectia,anstudiu,count(*) nr from stud group by sectia,anstudiu order by 1,2;
```
Dacă se doreşte ca rezultatele din ultima interogare să fie furnizate sub forma unui tabel, liniile să corespundă la secţii, iar pentru anii de studiu să apară coloane, atunci putem folosi interogarea următoare:
```
select sectia,SUM(DECODE(anstudiu,1,1,0)) AS an1,SUM(DECODE(anstudiu,2,1,0)) AS an2,SUM(DECODE(anstudiu,3,1,0)) AS an3
from stud 
group by sectia
order by 1;
```
Incepând cu versiunea 11g de la Oracle, instrucţiunea SELECT are extensia PIVOT, care permite generarea de astfel de rezultate. Formatul de utilizare:
```
SELECT lista 
  FROM sursa_de_date
  PIVOT [ XML ]
     (expresie_agregare [[AS] alias ]  [, expresie_agregare [[AS] alias ] ]...
       FOR { coloana  | ( lista_coloane ) }
       clauza_IN
     )  
  [WHERE conditie] [ORDER BY lista] 
```
Explicaţii:
- Sursa de date (tabel, view, expresie join, subinterogare) are anumite coloane, unele dintre ele vor apare în extensia PIVOT (la funcţiile de agregare sau în clauza IN)
- Coloanele din sursa de date care nu apar în PIVOT se folosesc pentru gruparea înregistrărilor (este ca un GROUP BY implicit după aceste coloane). Aceste coloane vor apare în rezultatul furnizat de extensia PIVOT. Pe lângă aceste coloane, se adaugă coloane rezultate din expresiile de agregare (cele care se pot folosi într-o instrucţiune SELECT cu clauza GROUP BY).
- Clauza FOR poate folosi o singură coloană sau mai multe coloane în construcţii de forma:
```
FOR coloana IN ( expr [AS alias] [, expr [AS alias]]... )
```
```
FOR (coloana [,coloana] ...) IN ( (expr [, expr]... )  [AS alias] ... )
```
  unde se precizează efectiv (static) valorile posibile pentru o coloană sau pentru o listă de coloane şi un eventual alias pentru aceste valori.
- Pentru fiecare valoare posibilă a coloanei sau listei de coloane din FOR, prezizată efectiv în clauza IN, se generează un subgrup de înregistrări. Acest subgrup va avea aceleaşi valori pentru coloanele care nu apar în FOR (după care se face gruparea implită amintită) şi o valoare concretă pentru coloanele din FOR. Pentru acest subgrup de înregistrări din sursă se evaluează expresiile de agregare din PIVOT.
- Denumirea coloanei corespunzătoare unei expresii de agregare va fi:
  - valoarea coloanei din FOR - dacă nu apare alias pentru expresia de agregare şi în FOR apare o singură coloană
  - o concatenare a valorilor coloanelor din FOR, iar între valori apare "_" - dacă nu apare alias pentru expresia de agregare şi în IN din FOR apar mai multe coloane
  - dacă expresia de agregare are un alias, atunci între denumirea coloanei generată după cum este precizat mai sus şi alias se face o concatenare, cu caracterul "_" între aceste valori
  - dacă apar mai multe expresii de agregare, atunci sunt necesare aliasuri pentru ele (nu trebuie să apară aceeaşi denumire pentru mai multe coloane)
- In extensia PIVOT poate apare clauza XML, care generează un document XML pentru fiecare grup de înregistrări. In acest caz clauza IN poate conţine o subselecţie (lista de valori poate fi precizată dinamic), sau să fie orice valoare care apare (caz în care lista efectivă se înlocuieşte cu cuvântul ANY.
Exemple:
Instrucţiunea:
```
select * from 
 (select sectia,anstudiu from stud)
 pivot (count(anstudiu)
         for anstudiu in (1,2,3)
       )
 order by 1;
```
are un rezultat de forma:
```
SECTIA 1    2    3
-------------------
  1    7    1    3
  2   60   37   30
 12   22   13   11
```
Instrucţiunea:
```
select * from 
 (select sectia,anstudiu from stud)
 pivot XML (count(anstudiu)
         for anstudiu IN (ANY)
       )
 order by 1;
```
furnizează un rezultat de forma:
```
SECTIA  ANSTUDIU_XML
----------------------
1       <PivotSet>
          <item><column name = "ANSTUDIU">1</column><column name = "COUNT(ANSTUDIU)">7</column></item>
          <item><column name = "ANSTUDIU">2</column><column name = "COUNT(ANSTUDIU)">1</column></item>
          <item><column name = "ANSTUDIU">3</column><column name = "COUNT(ANSTUDIU)">3</column></item>
        </PivotSet>
```
Observaţie: Operaţia de transformare a unor coloane în linii se poate realiza cu extensia UNPIVOT.

Bibliografie:

Funcţii de agregare

Extensia PIVOT