glufke.net

Enviado: **Seg, 24 Jan 2005 6:19 am**

Bom dia Pessoal

alguém poderia me Explicar qual a diferença de cube e rollup e para que serve ambos e quando devo utilizar , Se não pedir muito poderiam me mandar exemplo.Obrigado David

Enviado: **Seg, 24 Jan 2005 9:51 am**

CUBE
é uma extensão da cláusula group by,
que agrupa as linhas selecionadas baseado em valores
de todas as combinações possíveis para cada linha,
retornando uma linha com o resumo de informações para
cada grupo. Pode usar para gerar os chamados

CROSS-TABULATION VALUES

Selecionar tudo

SELECT DECODE(GROUPING(dname), 1, 'All Departments',
dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal"
FROM emp, dept
WHERE dept.deptno = emp.deptno
SELECT and subquery
11-106 SQL Reference
GROUP BY CUBE (dname, job);


DNAME JOB Total Empl Average Sa
--------------- --------- ---------- ----------
ACCOUNTING CLERK 1 15600
ACCOUNTING MANAGER 1 29400
ACCOUNTING PRESIDENT 1 60000
ACCOUNTING All Jobs 3 35000
RESEARCH ANALYST 2 36000
RESEARCH CLERK 2 11400
RESEARCH MANAGER 1 35700
RESEARCH All Jobs 5 26100
SALES CLERK 1 11400
SALES MANAGER 1 34200
SALES SALESMAN 4 16800
SALES All Jobs 6 18800
All Departments ANALYST 2 36000
All Departments CLERK 4 12450
All Departments MANAGER 3 33100
All Departments PRESIDENT 1 60000
All Departments SALESMAN 4 16800
All Departments All Jobs 14 24878.5714

ROLL UP
Usado por exemplo para gerar subtotais

Selecionar tudo

SELECT DECODE(GROUPING(dname), 1, 'All Departments',
dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal"
FROM emp, dept
WHERE dept.deptno = emp.deptno
GROUP BY ROLLUP (dname, job);
DNAME JOB Total Empl Average Sa
--------------- --------- ---------- ----------
ACCOUNTING CLERK 1 15600
ACCOUNTING MANAGER 1 29400
ACCOUNTING PRESIDENT 1 60000
ACCOUNTING All Jobs 3 35000
RESEARCH ANALYST 2 36000
RESEARCH CLERK 2 11400
RESEARCH MANAGER 1 35700
RESEARCH All Jobs 5 26100
SALES CLERK 1 11400
SALES MANAGER 1 34200
SALES SALESMAN 4 16800
SALES All Jobs 6 18800
All Departments All Jobs 14 24878.5714

+/- isso aí

glufke.net

Diferença entre cube e rollup

Diferença entre cube e rollup