ROLLUP, CUBE, GROUPING, GROUPING SETS

L'operatore ROLLUP raccogglie i dati ottenuti da una GROUP BY in ulteriori raggruppamenti noti come "superaggregate rows".
Viene utilizzato sempre in combinazione con la clausola GROUP BY.

E s e m p i
Estrarre per ogni dipartimento e professione la somma dei salari.

select department_name , job_title, sum(SALARY)
from employees join departments using( DEPARTMENT_ID)
where department_id not in (10, 40,60,90)
group by department_name , job_title
order by department_name , job_title ;

Se si vuole conoscere anche la somma dei salari di ogni dipartimento utilizzare la parola chiave ROLLUP dopo la parola chiave GROUP BY.

select department_name , job_title , sum(SALARY)
from employees join departments using( DEPARTMENT_ID)
join jobs using(job_id)
where  department_id  not in  (10, 40,60,90,70)
GROUP BY ROLLUP( department_name ,JOB_TITLE )
order by department_name ,job_title;

L'operatore ROLLUP ha prodotto sei"subtotals group" da destra verso sinistra e un "grand total" per l'intero set di dati. Se n è il numero di colonne indicate in ROLLUP allora abbiamo n+1 livelli (o combinazioni) di superaggregati.

L'operatore CUBE restituisci tutti i possibili raggruppamenti delle colonne incluse nella clausola GROUP BY.

E s e m p i o
 select department_name , job_title , sum(SALARY)
from employees join departments using( DEPARTMENT_ID)
join jobs using(job_id)
where  department_id  not in  (10, 40,60,90,70)
GROUP BY CUBE( department_name ,JOB_TITLE )
order by department_name ,job_title;

Restituisce gli stessi risultati della istruzione ROLLUP (vedere righe 3,6,9,12,15,19 e 33) oltre al raggruppamento per la colonna JOB_TITLE (vedere righe 20 - 32).

 Se n è il numero di colonne indicate in CUBE allora abbiamo 2*n livelli (o combinazioni) di super aggregati.

La funzione GROUPING individua le righe contenenti i super gruppi e i gruppi restituiti dagli operatori ROLLUP e CUBE e associa i valori numeri 0 e 1.
Può essere utilizzata solo nelle istruzioni Select...Group by e in assenza di ROLLUP e CUBE restituisce sempre il valore 0.
Il valore 1 è associato a ciascun super raggruppamento e 0 ai singoli gruppi.
E' usato per verificare se il valore a NULL in una espressione è un valore memorizzato a NULL o è creato dalle funzioni Rollup o Cube.

E s e m p i
select GROUPING (job_title) , department_name , job_title , sum(SALARY)
from employees
join departments using( DEPARTMENT_ID)
join jobs using(job_id)
where  department_id  not in  (10, 40,60,90,70)
GROUP BY ROLLUP( department_name ,JOB_TITLE )
order by department_name ,job_title;

Utilizzando la funzione NLV2 e CASE WHEN possiamo visualizzare al posto dei numeri 0 e 1 direttamente la denominazione di gruppi e super gruppi, più il totole di tutti i record.

select 

nvl2( department_name , (case GROUPING (job_title) when  0 then 'GRUPPO' when  1 then 'SUPERGRUPPO' end)  , 'TOTALE' ) as tipo_gruppo

, department_name , job_title , sum(SALARY)

from employees 

join departments using( DEPARTMENT_ID)

join jobs using(job_id)

where  department_id  not in  (10, 40,60,90,70)

GROUP BY ROLLUP( department_name ,JOB_TITLE )

order by department_name ,job_title;

La Clausola GROUPING SETS è utilizzata quando si vogliono estrarre da una stessa Select statament due o tre GROUP BY contemporaneamente. Viene utilizzata sempre in combinazione con la clausola GROUP BY, è posta dopo la GROUP BY ed è seguita da una coppia di parentesi contenenti le colonne da raggruppare.

La clausola è come se eseguisse più istruzioni Select e poi unisse i risultati con l'operatore UNION ALL.

E s e m p i o

select department_name , job_title , sum(SALARY)

from employees join departments using( DEPARTMENT_ID)

join jobs using(job_id)

where  department_id  not in  (10, 40,60,90,70)

GROUP BY GROUPING SETS (( department_name ,JOB_TITLE ),(job_title), NULL)

order by department_name ,job_title;

NULL è utilizzato nel GROUPING SETS per calcolare e visualizza il totale complessivo.