Cláusulas ROLLUP,CUBE E GROUPING - Parte II

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

Read More

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!

Read More

Olhar Digital - Tudo o que rolou na Campus Party 2011!

Read More

Privilégios de Usuários no Oracle 10g

Olá pessoal hoje irei publicar alguns assuntos do Curso de Oracle na Conhecimento Digital aqui no blog, segue a  Privilégios de Usuários no Oracle Módulo II.

Segue apresentação abaixo:

Usuarios e Permissões - Parte II

Espero que gostem e possam aproveitar o conteúdo!
Abraços.

Read More