segunda-feira, 14 de novembro de 2011

Subtotais em Oracle SQL - parte III

A combinação de GROUP BY com ROLLUP, CUBE ou GROUPING_SETS resulta em dois tipos de linhas: agregadas, aquelas com valores em todas as colunas do GROUP BY; e super-agregadas, as linhas de subtotais ou totais.

A função GROUPING permite facilmente dferenciar as duas, pois retorna o valor 0 se a linha representar um valor agregado e o valor 1 caso seja um super-agregado. Veja na consulta abaixo os valroes da primeira e segunda coluna. A última linha, por ser o grande total, tem valor 1 para ambas. 

SELECT grouping(department_name) grp_dept,
      grouping(TO_CHAR(hire_date, 'yyyy')) grp_hd,
       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;

Grp_Dept Grp_HD Department_Name Hire_Year SUM(Salary)
0 0 IT 2005 4800
0 0 Sales 2007 42200
0 0 Sales 2006 59100
0 0 Sales 2005 100300
0 0 Sales 2004 53500
0 0 IT 2007 10200
0 0 IT 2006 13800
0 0 Sales 2008 49400
0 1 Sales
304500
0 1 IT
28800
1 0
2008 49400
1 0
2007 52400
1 0
2006 72900
1 0
2005 105100
1 0
2004 53500
1 1

333300


Obviamente 0 ou 1 podem ser úteis para algum tipo de processamento automático, mas caso queira mostrar um resultado final para usuários, é mais conveniente substituir por mensagens adequadas. Por exemplo, a primeira coluna é resultado da função GROUPING numa combinação com CASE para definir um valor que o usuário final receberá:

SELECT case when grouping(department_name) = 1 AND grouping(TO_CHAR(hire_date, 'yyyy')) = 1 then 'Grande total'
            when grouping(department_name) = 1 then 'Subtotal por ano'
            when grouping(TO_CHAR(hire_date, 'yyyy')) = 1 then  'Subtotal por departamento'
            else null
       end column_type,
       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;

Column_Type Department_Name Hire_Year SUM(Salary)
IT 2005 4800
IT 2006 13800
IT 2007 10200
Subtotal by department IT 28800
Sales 2004 53500
Sales 2005 100300
Sales 2006 59100
Sales 2007 42200
Sales 2008 49400
Subtotal by department Sales
304500
Subtotal by year
2004 53500
Subtotal by year
2005 105100
Subtotal by year
2006 72900
Subtotal by year
2007 52400
Subtotal by year
2008 49400
Grand total

333300

Por fim, uma nota referente a clausula HAVING: ela filtra linhas agregadas que atendam (ou não) o predicado, mas todas linhas são consideradas para calcular os subtotais e totais. Isto pode resultar em uma certa inconsistência nos dados.  Por exemplo, a consulta abaixo retorna duas linhas para os anos 2006 e 2007 em que mais de um funcionário foi contratado. Mas o valor na linha e subtotal (e total) é resultado da soma de três anos!

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

Department_Name Hire_Year SUM(Salary)
IT 2006 13800
IT 2007 10200
IT
28800


28800

Uma possível solução é utilizar subconsultas:

SELECT department_name,
       hire_year,
       SUM(sum_salary)
FROM(
     SELECT department_name,
            TO_CHAR(hire_date, 'yyyy') hire_year,
            SUM(salary) 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')
     GROUP BY department_name, TO_CHAR(hire_date, 'yyyy')
     HAVING COUNT(*) > 1)
GROUP BY ROLLUP(department_name, hire_year)
ORDER BY 1,2;

Department_Name Hire_Year SUM(Salary)
IT 2006 13800
IT 2007 10200
IT
24000


24000

Espero que tenha sido claro nas explicações. Por favor, qualquer dúvida, sugestão, etc...  utilizem os comentários.

Parte 1 Parte 2 Parte 3

Nenhum comentário:

Postar um comentário