Mostrando postagens com marcador GROUP BY. Mostrar todas as postagens
Mostrando postagens com marcador GROUP BY. Mostrar todas as postagens

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

quarta-feira, 9 de novembro de 2011

Subtotais em Oracle SQL - parte I

A clausula GROUP BY é bastante conhecida para obter dados agregados. Por exemplo, a consulta abaixo obtem a soma dos salários por departamento e ano de contratação:

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 department_name,
         TO_CHAR(hire_date, 'yyyy')
ORDER BY 1,2;

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

Mas se estiver preparando um report, é provável que o requisito inclua linhas de subtotais. Neste ponto entra uma opção bem menos conhecida, o GROUP BY ROLLUP. Veja a mesma consulta utilizando esta opção. As linhas destacadas em verde são novas e representam subtotais e o total geral.

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 department_name, ROLLUP(TO_CHAR(hire_date, 'yyyy'))
ORDER BY 1,2;


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
333300

O ROLLUP não precisa incluir todas as colunas. Neste caso, a consulta retorna subtotais apenas para as colunas não citadas no ROLLUP. Ou, em outros termos, os totais deconsideram os valores individuais das colunas incluídas no ROLLUP. Veja o exemplo abaixo:

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 ROLLUP(department_name, TO_CHAR(hire_date, 'yyyy'))
ORDER BY 1,2;


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

Para finalizar, todos os exemplos utilizaram a função SUM, mas é possível utilizar qualquer outra função, como MIN, MAX, AVG e COUNT.

Na parte II falarei sobre o CUBE, outra variação de GROUP BY que pode ser útil para obter subtotais e totais.