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

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

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.

Parte 1

Parte 2

Parte 3

Novas funções do SQL Server Denali

Alguns dos novos recursos encontrados no SQL Server Denali são as funções Lead e Lag, a seguir uma breve apresentação das duas.

LEAD

                Esta função retorna o conteúdo de uma coluna existente no próximo registro da tabela ou de outro registro subsequente.

                Considerando uma tabela com totais de salários de uma empresa mês a mês, conforme exemplo abaixo:

Create Table TotalSalarios

       (MesAno Datetime not null primary key,
        TotalSalario  decimal(11,2));

Insert into TotalSalarios Values('01-jan-2011', 89500),

                                ('01-feb-2011', 91200),
                                ('01-mar-2011', 93200),
                                ('01-apr-2011', 97200),
                                ('01-may-2011', 101250),
                                ('01-jun-2011', 103000),
                                ('01-jul-2011', 103000),
                                ('01-aug-2011', 105020);

Sintaxe:

Lead(Expressão, Offset, default) Over ([partition_by_clause] order_by_clause)

Expressão:   Valor que sera retornado pela função.

Offset:      Número de linhas para frente de onde será retornado o valor.
Default:     Valor que deverá ser retornado quando o a expressão for nula.
Partition_by_Clause: Critério de agrupamento que deverá ser utilizado pelo SQL.
Order_by_clause:     Ordem que deverá ser aplicada antes da geração da função.

                Abaixo exemplo para apresentar o valor do registro atual e do próximo em uma mesma consulta, sem utilizar uma subquery, opção nas versões anteriores do SQL Server:

Select MesAno, TotalSalario,

       Lead(TotalSalario ,1,totalsalario) over (order by mesano) as TotalProxMes
  from TotalSalarios

Resultado

MesAno	TotalSalario	TotalProxMes
2011-01-01	89500.00	91200.00
2011-02-01	91200.00	93200.00
2011-03-01	93200.00	97200.00
2011-04-01	97200.00	101250.00
2011-05-01	101250.00	103000.00
2011-06-01	103000.00	103000.00
2011-07-01	103000.00	105020.00
2011-08-01	105020.00	105020.00

LAG

                Similar à função anterior a LAG retorna valores de linhas anteriores.

Sintaxe:

Lag(Expressão, Offset, default) Over ([partition_by_clause] order_by_clause)

      

Expressão:   Valor que sera retornado pela função.

Offset:      Número de linhas para trás de onde será retornado o valor.
Default:     Valor que deverá ser retornado quando o a expressão for nula.
Partition_by_Clause: Critério de agrupamento que deverá ser utilizado pelo SQL.
Order_by_clause:     Ordem que deverá ser aplicada antes da geração da função.
 
Abaixo exemplo para apresentar o valor do registro atual, o valor do mês anterior e o percentual da variação de valor
 

Select MesAno, TotalSalario,

       Lag(TotalSalario ,1,totalsalario) over (order by mesano) as TotalMesAnt,
       Round(TotalSalario * 100 / Lag(TotalSalario ,1,totalsalario)
             over (order by mesano),2) as IndiceVariacao
  from TotalSalarios
 

Resultado

MesAno	TotalSalario	TotalMesAnt	IndiceVariacao
2011-01-01	89500.00	89500.00	100.00000000000000
2011-02-01	91200.00	89500.00	101.90000000000000
2011-03-01	93200.00	91200.00	102.19000000000000
2011-04-01	97200.00	93200.00	104.29000000000000
2011-05-01	101250.00	97200.00	104.17000000000000
2011-06-01	103000.00	101250.00	101.73000000000000
2011-07-01	103000.00	103000.00	100.00000000000000
2011-08-01	105020.00	103000.00	101.96000000000000