Extensii ale funcţiilor de agregare

Fie o sursă de date (un tabel sau un view):
infstud[sectia,anstudiu,grupa,nume,prenume,cnp,media]

Există posibilitatea de a face mai multe agregări într-o singură interogare, deci un timp mai mic de execuţie, cu ajutorul unor extensii ale opţiunii group by.

  1. GROUPING SETS:
    GROUP BY GROUPING SETS (lista1 [,lista2]...)

    Dacă una din liste este formată dintr-o singură coloană (care poate fi şi expresie), atunci ea poate să nu fie inclusă între paranteze. Dacă în listă sunt mai multe expresii, atunci ele se includ între paranteze.
    Va rezulta un singur tabel final care va avea coloanele cerute în select (aceste coloane trebuie să apară în cel puţin una din listele precizate de GROUPING SETS, sau să fie coloane cu expresii de agregare).
    O înregistrare din tabelul rezultat va avea valori pentru coloanele care apar în una din liste (o listă curentă), valori pentru coloanele de agregare, iar coloanele care nu apar în lista curentă vor avea valori null.

    Exemplu: 

    select sectia,substr(cnp,4,2),round(media),count(*)
from infstud
group by grouping sets (sectia,substr(cnp,4,2),round(media),(sectia,round(media)));

    va avea ca rezultate, printre altele, şi înregistrările: 

    SECTIA  SUBSTR(CNP,4,2) ROUND(MEDIA)  COUNT(*) 
    1                    7            633  
    2                    9            490  
           01                         27  
           02                         7  
           11                         27  
           12                         7  
                         7            223  
                         9            398  
                         10           489  
2                                     232                    
21                                    40                      
46                                    10
    Observaţie: 
    select 'a' as a,'b' as b,count(*) from dual group by grouping sets ('a','b');
    este echivalent cu: 
    select 'a' as a,null as b,count(*) from dual
union
select null,'b',count(*) from dual;
  2. ROLLUP:
    GROUP BY ROLLUP(lista1 [,lista2]...)

    va genera grupări pentru următoarele mulţimi:

    (lista1, lista2, ..., listan)
...
(lista1, lista2)
(lista1)
()

    Din modul în care se generează datele, se observă că de la mai multe nivele de detaliu se ajunge la un total general.

    Exemplu: 

    select sectia,round(media),count(*) from infstud group by rollup (sectia,round(media));

  3. CUBE
    GROUP BY CUBE(lista1 [,lista2]...)

    care va face gruparea după toate submulţimile mulţimii date de liste (dacă sunt n liste, atunci vor exista 2n submulţimi).

    Exemplu: 

    select sectia,round(media),count(*) from infstud group by cube (sectia,round(media));


    with v(sectia,media,nr) as
(select sectia,round(media),count(*) from infstud group by cube (sectia,round(media)))
select media,nr from v where sectia=2

    select sectia,anstudiu,round(media),count(*) from infstud group by cube (sectia,anstudiu,round(media));

Observaţie: Cu cele trei extensii se generează diferite liste de coloane pentru grupare. Aceste extensii se pot folosi în diferite combinaţii, între ele sau cu liste efectiv precizate. 

select ... from ... group by lista [, lista] ...

unde lista poate fi:

  1. expresie
  2. grouping sets (lista_expresii)
  3. rollup (lista_expresii)
  4. cube (lista_expresii)
select sectia,anstudiu,round(media),count(*)
from infstud
group by sectia,grouping sets (anstudiu,round(media))
order by 1,2,3;

select sectia,anstudiu,round(media),count(*)
from infstud
group by sectia,rollup(anstudiu,round(media))
order by 1;

select sectia,anstudiu,round(media),count(*)
from infstud
group by sectia,cube(anstudiu,round(media))
order by 1;

Prin folosirea extensiilor amintite mai sus, în tabelul rezultat apar multe valori null. Cu functia: 

grouping(expresie)
unde expresie este folosită într-o grupare cu extensiile amintite, se obţine valoarea: = 1 dacă expresia (folosită pentru grupare) este null, = 0 altfel
Cu această funcţie se poate verifica dacă o linie conţine o valoare pentru expresie, sau este o linie de totaluri.
Funcţia grouping se poate utiliza în lista de expresii din select, sau în filtrarea liniilor generate prin having.

Exemplu: 

select sectia,anstudiu,round(media),count(*) 
from infstud 
group by cube (sectia,anstudiu,round(media))
having grouping(sectia)=0;

Pentru o coloană ce apare într-o grupare se poate asocia cu funcţia grouping o valoare binară (0 sau 1). Dacă se iau aceste valori binare pentru o mulţime de astfel de coloane, incluse in listele din extensiile amintite, se poate construi un număr întreg. Acest număr se poate genera cu funcţia: 

grouping_id(lista_expresii)
Funcţia se poate folosi pentru o determina o ierarhie a agregărilor.
select sectia,anstudiu,round(media),count(*),grouping_id(sectia,anstudiu,round(media))
from infstud 
group by cube (sectia,anstudiu,round(media));

Cu instrucţiunea:

select sectia,anstudiu,count(*)
from infstud 
group by grouping sets(sectia, rollup (sectia,anstudiu))
order by 1;

se obţin duplicări de linii. Funcţia GROUP_ID() furnizează valoarea 1 dacă linia curentă este o duplicare a unei linii precedente.

select sectia,anstudiu,count(*),grouping_id(sectia,anstudiu),GROUP_ID()
from infstud 
group by grouping sets(sectia, rollup (sectia,anstudiu))
order by 1;

Bibliografie: