Cláusulas ROLLUP,CUBE E GROUPING - Parte II

Posted by Emerson on 12:05 with 1 comment
Olá!
Continuando a seqüência do post anterior das Cláusulas ROLLUP,CUBE E GROUPING Parte I, iremos abordar neste artigo o uso da Cláusula CUBE que também é uma extensão da cláusula ROLLUP, que agrupa as linhas selecionadas em valores de todas as combinações possíveis para cada linha, e retorna ainda uma linha com o resumo de informações de cada grupo.
Vamos aos exemplos práticos.

CUBE

Vamos determinar a soma de todos os salarios usando o group by e CUBE;

SQL> select job_id , department_id, sum(salary)
  2  from employees
  3  group by cube(job_id, department_id)
  4  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
AD_PRES               90       24000
AD_PRES                        24000
AD_VP                 90       34000
AD_VP                          34000
FI_ACCOUNT           100       39600

JOB_ID     DEPARTMENT_ID SUM(SALARY)
---------- ------------- -----------
FI_ACCOUNT                     39600
FI_MGR               100       12008
FI_MGR                         12008
HR_REP                40        6500
HR_REP                          6500
IT_PROG               60       28800
IT_PROG                        28800
MK_MAN                20       13000
MK_MAN                         13000
MK_REP                20        6000
MK_REP                          6000

JOB_ID     DEPARTMENT_ID SUM(SALARY)
---------- ------------- -----------
PR_REP                70       10000
PR_REP                         10000
PU_CLERK              30       13900
PU_CLERK                       13900
PU_MAN                30       11000
PU_MAN                         11000
SA_MAN                80       61000
SA_MAN                         61000
SA_REP                80      243500
SA_REP                          7000
SA_REP                        250500

JOB_ID     DEPARTMENT_ID SUM(SALARY)
---------- ------------- -----------
SH_CLERK              50       64300
SH_CLERK                       64300
ST_CLERK              50       55700
ST_CLERK                       55700
ST_MAN                50       36400
ST_MAN                         36400
                      10        4400
                      20       19000
                      30       24900
                      40        6500
                      50      156400

JOB_ID     DEPARTMENT_ID SUM(SALARY)
---------- ------------- -----------
                      60       28800
                      70       10000
                      80      304500
                      90       58000
                     100       51608
                     110       20308
                              691416
                                7000

52 rows selected.

SQL>



Observe que através do CUBE foi possível agregações por JOB_ID e DEPARTMENT_ID, ao contrário do ROLLUP que executa o resultado apenas do total dos agrupamentos.

Segue mais um exemplo:


SQL> select job_id as funcao ,department_id departamento,sum(salary) soma_sal,ro
und(avg(salary)) as media ,count(*) quantidade
  2  from employees
  3  group by cube(job_id,department_id)
  4  order by job_id,department_id;


FUNCAO     DEPARTAMENTO   SOMA_SAL      MEDIA QUANTIDADE
---------- ------------ ---------- ---------- ----------
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

FUNCAO     DEPARTAMENTO   SOMA_SAL      MEDIA QUANTIDADE
---------- ------------ ---------- ---------- ----------

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


FUNCAO     DEPARTAMENTO   SOMA_SAL      MEDIA QUANTIDADE
---------- ------------ ---------- ---------- ----------
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                        7000       7000          1
SA_REP                      250500       8350         30


FUNCAO     DEPARTAMENTO   SOMA_SAL      MEDIA QUANTIDADE
---------- ------------ ---------- ---------- ----------
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
                     10       4400       4400          1
                     20      19000       9500          2
                     30      24900       4150          6
                     40       6500       6500          1
                     50     156400       3476         45


FUNCAO     DEPARTAMENTO   SOMA_SAL      MEDIA QUANTIDADE
---------- ------------ ---------- ---------- ----------
                     60      28800       5760          5
                     70      10000      10000          1
                     80     304500       8956         34
                     90      58000      19333          3
                    100      51608       8601          6
                    110      20308      10154          2
                            691416       6462        107
                              7000       7000          1


52 rows selected.


SQL>


Nesse caso incluímos a soma de salários, uma media salarial e a contagem de empregados por departamento, para visualizarmos melhor a performance do CUBE.
Observando a coluna que calcula a média ela fez um arredondamento às unidades,e nesta a clausula CUBE obtem uma média, enquanto que nas outras agregações obtem somas;

Resumo:
A cláusula ROLLUP exibe apenas resultado dos agrupamentos enquanto que o uso do CUBE emite resultados em forma de CUBO, sendo possível através de SELECT calcular subtotais para todas as combinações possíveis de um grupo de dimensões.Calcula também o total geral.

Espero que tenham gostado!
Obrigado aos comentários dos colegas sugestões também serão bem vindas!

Fontes:
http://www.dba-oracle.com/t_cube.htm
Categories: