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.

Nenhum comentário:

Postar um comentário