Cláusulas ROLLUP,CUBE E GROUPING - Parte I

Olá!

Nesse feriadão aproveitei para estudar um assunto que é cobrado na prova de Certificação Oracle  IZO-047 SQL Expert.Nesse caso vamos focar mais precisamente as cláusulas  ROLLUP,CUBE E GROUPING.Mais informações sobre a prova no site da Oracle.

1Z0-047 Oracle Database SQL Expert

Além das funções de grupo como AVG,SUM,MIN,GROUP BY,HAVING existentes na linguagem SQL  e que nos auxiliam com somatórios, médias,etc em diversas situações do dia-a-dia.Com ROLLUP,CUBE E GROUPING podemos melhorar mais ainda a performance das consultas de grupo.Vamos então para os exemplos.

ROLLUP

É utilizada juntamente com o GROUP BY para permitir uma agregação sobre os resultados de grupos de linhas.Vejamos então um exemplo que utilizando apenas o GROUP BY.

SQL> SELECT job_id,sum(salary) FROM hr.employees e
  2  GROUP BY (job_id)
  3  ORDER BY job_id;

JOB_ID     SUM(SALARY)
---------- -----------
AC_ACCOUNT        8300
AC_MGR           12008
AD_ASST           4400
AD_PRES          24000
AD_VP            34000
FI_ACCOUNT       39600
FI_MGR           12008
HR_REP            6500
IT_PROG          28800
MK_MAN           13000
MK_REP            6000

JOB_ID     SUM(SALARY)
---------- -----------
PR_REP           10000
PU_CLERK         13900
PU_MAN           11000
SA_MAN           61000
SA_REP          250500
SH_CLERK         64300
ST_CLERK         55700
ST_MAN           36400

19 rows selected.
SQL>

Observe que só podemos obter o resultado  agrupado .

Adicionando a Cláusula ROLLUP junto com GROUP BY conseguimos obter o resultado dessa soma agrupada, o que não conseguimos determinar no exemplo anterior.

SQL> SELECT job_id,sum(salary) FROM hr.employees e

  2  GROUP BY ROLLUP (job_id)

  3  ORDER BY job_id;

JOB_ID     SUM(SALARY)

---------- -----------

AC_ACCOUNT        8300

AC_MGR           12008

AD_ASST           4400

AD_PRES          24000

AD_VP            34000

FI_ACCOUNT       39600

FI_MGR           12008

HR_REP            6500

IT_PROG          28800

MK_MAN           13000

MK_REP            6000

JOB_ID     SUM(SALARY)

---------- -----------

PR_REP           10000

PU_CLERK         13900

PU_MAN           11000

SA_MAN           61000

SA_REP          250500

SH_CLERK         64300

ST_CLERK         55700

ST_MAN           36400

                691416

20 rows selected.

SQL>

Nesse exemplo a cláusula ROLLUP nos permite obter o resultado da soma agregada de todos os salários; 

Vamos complicar um pouco para sabermos até que ponto podemos melhorar o uso de nossas querys com o ROLLUP.

Agora vamos obter a soma de salários por função (JOB_ID) e por departamento (DEPARTMENT_ID), limitamos  apenas a alguns departamentos para que a exibição não ficasse tão grande.

SQL> select job_id, department_id, sum(salary)

  2  from hr.employees

  3  where department_id in(10,20,40,50,100,110)

  4  group by rollup(job_id,department_id)

  5  order by job_id, department_id;

JOB_ID     DEPARTMENT_ID SUM(SALARY)

---------- ------------- -----------

AC_ACCOUNT           110        8300

AC_ACCOUNT                      8300

AC_MGR               110       12008

AC_MGR                         12008

AD_ASST               10        4400

AD_ASST                         4400

FI_ACCOUNT           100       39600

FI_ACCOUNT                     39600

FI_MGR               100       12008

FI_MGR                         12008

HR_REP                40        6500

JOB_ID     DEPARTMENT_ID SUM(SALARY)

---------- ------------- -----------

HR_REP                          6500

MK_MAN                20       13000

MK_MAN                         13000

MK_REP                20        6000

MK_REP                          6000

SH_CLERK              50       64300

SH_CLERK                       64300

ST_CLERK              50       55700

ST_CLERK                       55700

ST_MAN                50       36400

ST_MAN                         36400

JOB_ID     DEPARTMENT_ID SUM(SALARY)

---------- ------------- -----------

                              258216

23 rows selected.

SQL>

Nesse outro exemplo adicionamos outras funções de grupo para obter-mos a média dos salários e contagem do numero de empregados .

SQL> select job_id, department_id, sum(salary), round(avg(salary)), count(*

  2  from hr.employees

  3  group by rollup(job_id, department_id)

  4  order by job_id, department_id;

JOB_ID     DEPARTMENT_ID SUM(SALARY) ROUND(AVG(SALARY))   COUNT(*)

---------- ------------- ----------- ------------------ ----------

AC_ACCOUNT           110        8300               8300          1

AC_ACCOUNT                      8300               8300          1

AC_MGR               110       12008              12008          1

AC_MGR                         12008              12008          1

AD_ASST               10        4400               4400          1

AD_ASST                         4400               4400          1

AD_PRES               90       24000              24000          1

AD_PRES                        24000              24000          1

AD_VP                 90       34000              17000          2

AD_VP                          34000              17000          2

FI_ACCOUNT           100       39600               7920          5

JOB_ID     DEPARTMENT_ID SUM(SALARY) ROUND(AVG(SALARY))   COUNT(*)

---------- ------------- ----------- ------------------ ----------

FI_ACCOUNT                     39600               7920          5

FI_MGR               100       12008              12008          1

FI_MGR                         12008              12008          1

HR_REP                40        6500               6500          1

HR_REP                          6500               6500          1

IT_PROG               60       28800               5760          5

IT_PROG                        28800               5760          5

MK_MAN                20       13000              13000          1

MK_MAN                         13000              13000          1

MK_REP                20        6000               6000          1

MK_REP                          6000               6000          1

JOB_ID     DEPARTMENT_ID SUM(SALARY) ROUND(AVG(SALARY))   COUNT(*)

---------- ------------- ----------- ------------------ ----------

PR_REP                70       10000              10000          1

PR_REP                         10000              10000          1

PU_CLERK              30       13900               2780          5

PU_CLERK                       13900               2780          5

PU_MAN                30       11000              11000          1

PU_MAN                         11000              11000          1

SA_MAN                80       61000              12200          5

SA_MAN                         61000              12200          5

SA_REP                80      243500               8397         29

SA_REP                        250500               8350         30

SA_REP                          7000               7000          1

JOB_ID     DEPARTMENT_ID SUM(SALARY) ROUND(AVG(SALARY))   COUNT(*)

---------- ------------- ----------- ------------------ ----------

SH_CLERK              50       64300               3215         20

SH_CLERK                       64300               3215         20

ST_CLERK              50       55700               2785         20

ST_CLERK                       55700               2785         20

ST_MAN                50       36400               7280          5

ST_MAN                         36400               7280          5

                              691416               6462        107

40 rows selected.

SQL>

A cláusula ROLLUP permitiu as agregações por (JOB_ID, DEPARTMENT_ID) assim como as agregações totais por JOB e as somas totais;

Espero que tenham gostado.

Abraços!