Cláusulas ROLLUP,CUBE E GROUPING - Parte I

Posted by Emerson on 13:23 with 6 comments

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.


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!
Categories: