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;
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
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
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: Oracle
Legal!
ResponderExcluir