As cláusulas ROLLUP, CUBE e GROUPING

SQL em Oracle > DML > Funções > Funções de grupo > As cláusulas ROLLUP, CUBE e GROUPING

As cláusulas ROLLUP, CUBE e GROUPING

A conjugação das funções de grupo com a cláusula GROUP BY permite obter contagens, somas ou médias sobre os valores dos elementos de um grupo. As cláusulas ROLLUP e CUBE permitem agregar esses resultados, produzindo contagens sobre as contagens, somas das somas ou médias das médias:

ROLLUP
CUBE
GROUPING

ROLLUP

A cláusula ROLLUP é usada juntamente com GROUP BY e permite agregações sobre grupos de linhas. Vamos analisar a sua utilidade seguindo uma sequência de exemplos.

Usando funções de grupo conseguimos determinar a soma de salários por função (JOB) mas, usando um único query, não conseguimos determinar também a soma total. Adicionando a cláusula ROLLUP ao GROUP BY conseguimos obter essa soma, como mostra o primeiro exemplo:

select job, sum(sal)
from emp
group by rollup(job)
order by job;

JOB       SUM(SAL)               
--------- ---------------------- 
ANALYST   6000                   
CLERK     4150                   
MANAGER   8275                   
PRESIDENT 5000                   
SALESMAN  5600                   
          29025                  

6 rows selected

A cláusula ROLLUP(JOB) permite obter a soma agregada de todos os salários;
O uso da cláusula GROUP BY não garante que o resultado vai aparecer ordenado, pelo que forçamos a ordenação com ORDER BY;
Se usássemos uma folha de cálculo o resultado apareceria assim:

O exemplo abaixo determina a soma de salários por departamento e usa a cláusula ROLLUP para a soma total:

select deptno, sum(sal)
from emp
group by rollup(deptno)
order by deptno;

DEPTNO                 SUM(SAL)               
---------------------- ---------------------- 
10                     8750                   
20                     10875                  
30                     9400                   
                       29025                  

4 rows selected

A cláusula ROLLUP(DEPTNO) permite obter a soma agregada de todos os salários;
Se usássemos uma folha de cálculo o resultado apareceria assim:

Vamos complicar o problema inicial; agora pretendemos a soma de salários por função (JOB) e por departamento (DEPTNO) e vamos utilizar GROUP BY ROLLUP:

select job, deptno, sum(sal)
from emp
group by rollup(job,deptno)
order by job, deptno;

JOB       DEPTNO                 SUM(SAL)               
--------- ---------------------- ---------------------- 
ANALYST   20                     6000                   
ANALYST                          6000                   
CLERK     10                     1300                   
CLERK     20                     1900                   
CLERK     30                     950                    
CLERK                            4150                   
MANAGER   10                     2450                   
MANAGER   20                     2975                   
MANAGER   30                     2850                   
MANAGER                          8275                   
PRESIDENT 10                     5000                   
PRESIDENT                        5000                   
SALESMAN  30                     5600                   
SALESMAN                         5600                   
                                 29025                  

15 rows selected

A cláusula ROLLUP(JOB,DEPTNO) permitiu obter agregações por (JOB,DEPTNO) assim como as agregações totais por JOB e a soma total;
Se usássemos uma folha de cálculo o resultado apareceria assim:

No quarto exemplo invertemos a ordem de GROUP BY para fazer um ROLLUP(DEPTNO,JOB):

select deptno, job, sum(sal)
from emp
group by rollup(deptno, job)
order by deptno, job;

DEPTNO                 JOB       SUM(SAL)               
---------------------- --------- ---------------------- 
10                     CLERK     1300                   
10                     MANAGER   2450                   
10                     PRESIDENT 5000                   
10                               8750                   
20                     ANALYST   6000                   
20                     CLERK     1900                   
20                     MANAGER   2975                   
20                               10875                  
30                     CLERK     950                    
30                     MANAGER   2850                   
30                     SALESMAN  5600                   
30                               9400                   
                                 29025                  

13 rows selected

A cláusula ROLLUP(DEPTNO,JOB) permitiu obter agregações por (DEPTNO,JOB) assim como as agregações totais por DEPTNO e a soma total;
Se usássemos uma folha de cálculo o resultado apareceria assim:

No quinto exemplo adicionamos o cálculo da média de salários e a contagem do número de empregados. Vamos fazer um GROUP BY ROLLUP(JOB, DEPTNO):

select job, deptno, sum(sal), round(avg(sal)), count(*)
from emp
group by rollup(job, deptno)
order by job, deptno;

JOB       DEPTNO                 SUM(SAL)               ROUND(AVG(SAL))        COUNT(*)               
--------- ---------------------- ---------------------- ---------------------- ---------------------- 
ANALYST   20                     6000                   3000                   2                      
ANALYST                          6000                   3000                   2                      
CLERK     10                     1300                   1300                   1                      
CLERK     20                     1900                   950                    2                      
CLERK     30                     950                    950                    1                      
CLERK                            4150                   1038                   4                      
MANAGER   10                     2450                   2450                   1                      
MANAGER   20                     2975                   2975                   1                      
MANAGER   30                     2850                   2850                   1                      
MANAGER                          8275                   2758                   3                      
PRESIDENT 10                     5000                   5000                   1                      
PRESIDENT                        5000                   5000                   1                      
SALESMAN  30                     5600                   1400                   4                      
SALESMAN                         5600                   1400                   4                      
                                 29025                  2073                   14                     

15 rows selected

A cláusula ROLLUP permitiu as agregações por (JOB, DEPTNO) assim como as agregações totais por JOB e as somas totais;
Se usássemos uma folha de cálculo o resultado apareceria assim:
Repare que a coluna que calcula a média faz arredondamento às unidades. Nesta coluna a cláusula ROLLUP obtem uma média, enquanto que nas outras agregações obtem somas;

O sexto exemplo faz as mesmas selecções que o quinto, mas alteramos a cláusula GROUP BY ROLLUP de (JOB, DEPTNO) para (DEPTNO, JOB):

select deptno, job, sum(sal), round(avg(sal)), count(*)
from emp
group by rollup(deptno, job)
order by deptno, job;

DEPTNO                 JOB       SUM(SAL)               ROUND(AVG(SAL))        COUNT(*)               
---------------------- --------- ---------------------- ---------------------- ---------------------- 
10                     CLERK     1300                   1300                   1                      
10                     MANAGER   2450                   2450                   1                      
10                     PRESIDENT 5000                   5000                   1                      
10                               8750                   2917                   3                      
20                     ANALYST   6000                   3000                   2                      
20                     CLERK     1900                   950                    2                      
20                     MANAGER   2975                   2975                   1                      
20                               10875                  2175                   5                      
30                     CLERK     950                    950                    1                      
30                     MANAGER   2850                   2850                   1                      
30                     SALESMAN  5600                   1400                   4                      
30                               9400                   1567                   6                      
                                 29025                  2073                   14                     

13 rows selected

A cláusula ROLLUP permitiu as agregações por (DEPTNO, JOB) assim como as agregações totais por DEPTNO e as somas totais;
Se usássemos uma folha de cálculo o resultado apareceria assim:
Repare que a coluna que calcula a média faz arredondamento às unidades. Nesta coluna a clausula ROLLUP obtem uma média, enquanto que nas outras agregações obtem somas;

A cláusula ROLLUP(coluna1, coluna2) permite fazer agregações parciais pelo par (coluna1, coluna2), agregações totais por coluna1 e as somas totais.

Topo

CUBE

A clausula CUBE é uma extensão de ROLLUP que permite as agregações totais por todas as colunas envolvidas. Na prática cria um cubo.

O exemplo abaixo determina a soma de salários usando GROUP BY CUBE(JOB, DEPTNO):

select job, deptno, sum(sal)
from emp
group by cube(job, deptno)
order by job, deptno;

JOB       DEPTNO                 SUM(SAL)               
--------- ---------------------- ---------------------- 
ANALYST   20                     6000                   
ANALYST                          6000                   
CLERK     10                     1300                   
CLERK     20                     1900                   
CLERK     30                     950                    
CLERK                            4150                   
MANAGER   10                     2450                   
MANAGER   20                     2975                   
MANAGER   30                     2850                   
MANAGER                          8275                   
PRESIDENT 10                     5000                   
PRESIDENT                        5000                   
SALESMAN  30                     5600                   
SALESMAN                         5600                   
          10                     8750                   
          20                     10875                  
          30                     9400                   
                                 29025                  

18 rows selected

A cláusula CUBE permitiu todas as agregações por (JOB, DEPTNO);
Se usássemos uma folha de cálculo o resultado apareceria assim:

O próximo exemplo adiciona à soma de salários a média de salários e a contagem do número de empregados. Usamos GROUP BY CUBE(JOB, DEPTNO) para obter todas as agregações possíveis:

select job, deptno, sum(sal), round(avg(sal)), count(*)
from emp
group by cube(job, deptno)
order by job, deptno;

JOB       DEPTNO                 SUM(SAL)               ROUND(AVG(SAL))        COUNT(*)               
--------- ---------------------- ---------------------- ---------------------- ---------------------- 
ANALYST   20                     6000                   3000                   2                      
ANALYST                          6000                   3000                   2                      
CLERK     10                     1300                   1300                   1                      
CLERK     20                     1900                   950                    2                      
CLERK     30                     950                    950                    1                      
CLERK                            4150                   1038                   4                      
MANAGER   10                     2450                   2450                   1                      
MANAGER   20                     2975                   2975                   1                      
MANAGER   30                     2850                   2850                   1                      
MANAGER                          8275                   2758                   3                      
PRESIDENT 10                     5000                   5000                   1                      
PRESIDENT                        5000                   5000                   1                      
SALESMAN  30                     5600                   1400                   4                      
SALESMAN                         5600                   1400                   4                      
          10                     8750                   2917                   3                      
          20                     10875                  2175                   5                      
          30                     9400                   1567                   6                      
                                 29025                  2073                   14                     

18 rows selected

A cláusula CUBE permitiu todas as agregações por (JOB, DEPTNO) e as somas totais;
Se usássemos uma folha de cálculo o resultado apareceria assim:
Repare que a coluna que calcula a média faz arredondamento às unidades. Nesta coluna a clausula CUBE obtem uma média, enquanto que nas outras agregações obtem somas;

Topo

GROUPING

A função GROUPING pode ser usada com ROLLUP ou CUBE e serve para determinar se a linha devolvida pelo query resulta de uma agregação:

select job, deptno, sum(sal), round(avg(sal)), count(*), grouping(job), grouping(deptno)
from emp
group by cube(job, deptno)
order by job, deptno;

JOB       DEPTNO                 SUM(SAL)               ROUND(AVG(SAL))        COUNT(*)               GROUPING(JOB)          GROUPING(DEPTNO)       
--------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
ANALYST   20                     6000                   3000                   2                      0                      0                      
ANALYST                          6000                   3000                   2                      0                      1                      
CLERK     10                     1300                   1300                   1                      0                      0                      
CLERK     20                     1900                   950                    2                      0                      0                      
CLERK     30                     950                    950                    1                      0                      0                      
CLERK                            4150                   1038                   4                      0                      1                      
MANAGER   10                     2450                   2450                   1                      0                      0                      
MANAGER   20                     2975                   2975                   1                      0                      0                      
MANAGER   30                     2850                   2850                   1                      0                      0                      
MANAGER                          8275                   2758                   3                      0                      1                      
PRESIDENT 10                     5000                   5000                   1                      0                      0                      
PRESIDENT                        5000                   5000                   1                      0                      1                      
SALESMAN  30                     5600                   1400                   4                      0                      0                      
SALESMAN                         5600                   1400                   4                      0                      1                      
          10                     8750                   2917                   3                      1                      0                      
          20                     10875                  2175                   5                      1                      0                      
          30                     9400                   1567                   6                      1                      0                      
                                 29025                  2073                   14                     1                      1                      

18 rows selected

A coluna GROUPING(JOB) devolve 1 quando essa linha contem uma agregação por JOB e 0 nos restantes casos;
A coluna GROUPING(DEPTNO) devolve 1 quando essa linha contem uma agregação por DEPTNO e 0 nos restantes casos;
Este comando pode ser muito útil quando estamos a analisar o resultado do query num programa, já que ao percorrer as linhas do cursor conseguimos determinar as que resultam de uma agregação;

A cláusula GROUPING pode beneficiar com da função CASE:

select 
       case when grouping(deptno)=0 then  to_char(deptno,'99')
            when grouping(job)=0    then  'Soma por função '|| job
            else 'Total geral'
       end as deptno,
       case when grouping(job)=0    then job
            when grouping(deptno)=0 then 'Soma por departamento '|| to_char(deptno,'99')
            else 'Total geral'
       end as job,
       sum(sal)
from emp
group by cube(deptno, job)
order by deptno, job;

DEPTNO                    JOB                       SUM(SAL)               
------------------------- ------------------------- ---------------------- 
 10                       CLERK                     1300                   
 10                       MANAGER                   2450                   
 10                       PRESIDENT                 5000                   
 10                       Soma por departamento  10 8750                   
 20                       ANALYST                   6000                   
 20                       CLERK                     1900                   
 20                       MANAGER                   2975                   
 20                       Soma por departamento  20 10875                  
 30                       CLERK                     950                    
 30                       MANAGER                   2850                   
 30                       SALESMAN                  5600                   
 30                       Soma por departamento  30 9400                   
Soma por função ANALYST   ANALYST                   6000                   
Soma por função CLERK     CLERK                     4150                   
Soma por função MANAGER   MANAGER                   8275                   
Soma por função PRESIDENT PRESIDENT                 5000                   
Soma por função SALESMAN  SALESMAN                  5600                   
Total geral               Total geral               29025                  

18 rows selected

Topo

Realizado por Turtle Learning ®. Última alteração em 2011-02-26