quinta-feira, 10 de novembro de 2011

SUBTOTAIS EM ORACLE SQL - PARTE II

Outra clausula que pode ser utilizada é o GROUP BY CUBE. A principal diferença é que ela retorna subtotais individuais para cada coluna listada, enquanto o ROLLUP retorna subtotais agregando apenas a última coluna. Ou seja, em geral, CUBE retornará mais linahs que ROLLUP. Por exemplo:

SELECT department_name,
       TO_CHAR(hire_date, 'yyyy') hire_year,
       SUM(salary)
FROM hr.employees emp
JOIN HR.departments dept USING (department_id)
JOIN HR.jobs j USING (job_id)
WHERE department_name IN ( 'IT', 'Sales')
GROUP BY CUBE(department_name, TO_CHAR(hire_date, 'yyyy'))
ORDER BY 1,2;

Resposta - linhas verdes mostram subtotais por Department_Name; azuis, por Hire_Year; e amarela, o grande total.:

Department_Name Hire_Year SUM(Salary)
IT 2005 4800
IT 2006 13800
IT 2007 10200
IT
28800
Sales 2004 53500
Sales 2005 100300
Sales 2006 59100
Sales 2007 42200
Sales 2008 49400
Sales
304500

2004 53500

2005 105100

2006 72900

2007 52400

2008 49400


333300

Digamos agora que outras colunas relativas a departamento são necessárias, como endereço e cidade. Obviamente, cada departamento tem apenas um endereço e cidade e talvez não seja necessário calcular subtotais separados para cada uma destas colunas. Para resolver esta situação, existe o GROUP BY GROUPING SETS. 

A consulta abaixo define dois grupos para calcular subtotais, cada grupo definido por parenteses. O primeiro inclui três colunas: Department_Name, Street_Address e City. O segundo apenas uma coluna: Hire_Year.

SELECT department_name, 
       street_address, 
       city,
       TO_CHAR(hire_date, 'yyyy') hire_year,
       SUM(salary)
FROM hr.employees emp
JOIN HR.departments dept USING (department_id)
JOIN HR.jobs j USING (job_id)
JOIN HR.locations loc USING (location_id)
WHERE department_name IN ( 'IT', 'Sales')
GROUP BY GROUPING SETS((department_name, street_address, city) , 
                       (TO_CHAR(hire_date, 'yyyy'))
                       )
ORDER BY 1,4;

A consulta retorna apenas linhas de subtotais para os grupos indicados.

Department_Name Street_Address City Hire_Year SUM(Salary)
IT 2014 Jabberwocky Rd Southlake
28800
Sales Magdalen Centre, The Oxford Science Park Oxford
304500



2004 53500



2005 105100



2006 72900



2007 52400



2008 49400

E como obter as linhas de detalhe, que foram somadas? Simples, basta combinar com uma consulta baseada apenas em GROUP BY através de UNION ALL:

SELECT department_name, 
       street_address, 
       city,
       TO_CHAR(hire_date, 'yyyy') hire_year,
       SUM(salary)
FROM hr.employees emp
JOIN HR.departments dept USING (department_id)
JOIN HR.jobs j USING (job_id)
WHERE department_name IN ( 'IT', 'Sales')
GROUP BY department_name, street_address, city, TO_CHAR(hire_date, 'yyyy')
ORDER BY 1,4;
UNION ALL
SELECT department_name, 
       street_address, 
       city,
       TO_CHAR(hire_date, 'yyyy') hire_year,
       SUM(salary)
FROM hr.employees emp
JOIN HR.departments dept USING (department_id)
JOIN HR.jobs j USING (job_id)
WHERE department_name IN ( 'IT', 'Sales')
GROUP BY GROUPING SETS((department_name, street_address, city) , 
                       (TO_CHAR(hire_date, 'yyyy'))
                       )
ORDER BY 1,4;


Na terceira parte mostrarei a função GROUPING, muito útil para formatar resultados incluindo linhas de detalhe e linhas de subtotal.

Parte 1 Parte 2 Parte 3

Nenhum comentário:

Postar um comentário