Criei um exemplo de como fazer um relatório completo utilizando as funções analíticas.
1 - Para começar, vamos ver o conteúdo da tabela.
SQL> select * from ricardo_tmp2 order by a, b;
A B
---------- ----------
1 1
1 2
1 5
1 6
1 9
1 10
2 1
2 3
2 4
2 6
2 84
3 4
3 5
3 6
3 14
3 15
3 21
3 68
4 6
4 34
4 78
5 2
5 12
5 16
5 18
25 rows selected
2 - Agora vamos analisar a consulta que será utilizada no cursor do nosso algoritmo.
SQL> SELECT decode (lag(a,1) over (ORDER BY a),a,NULL,a) grupo,
2 b valor,
3 SUM (b) over (PARTITION BY a ORDER BY b) AS parcial_grupo,
4 decode (lead(a,1) over (ORDER BY a),a,NULL,COUNT(b) over (PARTITION BY a ORDER BY b)) AS qtde_grupo,
5 decode (lead(a,1) over (ORDER BY a),a,NULL,SUM (b) over (PARTITION BY a ORDER BY b)) AS soma_grupo,
6 COUNT (b) over (ORDER BY a,b) AS qtde_total,
7 SUM (b) over (ORDER BY a,b) AS soma_total,
8 decode(lead(b,1) over (ORDER BY a),NULL,'F') FINAL
9 FROM ricardo_tmp2
10 ORDER BY a,b
11 /
GRUPO VALOR PARCIAL_GRUPO QTDE_GRUPO SOMA_GRUPO QTDE_TOTAL SOMA_TOTAL FINAL
------ ---------- ------------- ---------- ---------- ---------- ---------- -----
1 1 1 1 1
2 3 2 3
5 8 3 8
6 14 4 14
9 23 5 23
10 33 6 33 6 33
2 1 1 7 34
3 4 8 37
4 8 9 41
6 14 10 47
84 98 5 98 11 131
3 4 4 12 135
5 9 13 140
6 15 14 146
14 29 15 160
15 44 16 175
21 65 17 196
68 133 7 133 18 264
4 6 6 19 270
34 40 20 304
78 118 3 118 21 382
5 2 2 22 384
12 14 23 396
16 30 24 412
18 48 4 48 25 430 F
25 rows selected
- Campo GRUPO: Usei a função analítica LAG para verificar se é o primeiro registro do grupo e assim usá-lo para criar o cabeçalho do grupo no algoritmo.
- Campo VALOR: Esse campo será parte das linhas do relatório.
- Campo PARCIAL_GRUPO: Esse campo será parte das linhas do relatório.
- Campo QTDE_GRUPO: Usei a função analítica LEAD para verificar se é o último registro do grupo e assim usá-lo como totalizador para o rodapé do grupo no algoritmo.
- Campo SOMA_GRUPO: Usei a função analítica LEAD para verificar se é o último registro do grupo e assim usá-lo como totalizador para o rodapé do grupo no algoritmo.
- Campo QTDE_TOTAL: Usei para ir contando os registros para utilizar no resumo final.
- Campo SOMA_TOTAL: Usei para ir somando os registros para utilizar no resumo final.
- Campo FINAL: Usei a função analítica LEAD para verificar se é o último registro da consulta e assim identificar o final do relatório e exibir o resumo no algoritmo.
3 - Desenvolvendo o algoritmo
DECLARE
-- Cursor
CURSOR c_relatorio IS
SELECT decode (lag(a,1) over (ORDER BY a),a,NULL,a) grupo,
b valor,
SUM (b) over (PARTITION BY a ORDER BY b) AS parcial_grupo,
decode (lead(a,1) over (ORDER BY a),a,NULL,COUNT(b) over (PARTITION BY a ORDER BY b)) AS qtde_grupo,
decode (lead(a,1) over (ORDER BY a),a,NULL,SUM (b) over (PARTITION BY a ORDER BY b)) AS soma_grupo,
COUNT (b) over (ORDER BY a,b) AS qtde_total,
SUM (b) over (ORDER BY a,b) AS soma_total,
decode(lead(b,1) over (ORDER BY a),NULL,'F') FINAL
FROM ricardo_tmp2
ORDER BY a,b;
-- Variáveis
r_relatorio c_relatorio%ROWTYPE;
v_sep NUMBER := 80;
BEGIN
FOR r_relatorio IN c_relatorio LOOP
-- Cabeçalho do grupo
IF r_relatorio.grupo IS NOT NULL THEN
dbms_output.put_line(rpad('=',v_sep,'='));
dbms_output.put_line('Grupo: ' || r_relatorio.grupo);
dbms_output.put_line(rpad('=',v_sep,'='));
END IF;
-- Linhas
dbms_output.put_line('Valor: ' || rpad(r_relatorio.valor,30,' ') || 'Parcial: ' || r_relatorio.parcial_grupo);
-- Rodapé do grupo
IF r_relatorio.qtde_grupo IS NOT NULL THEN
dbms_output.put_line(rpad('-',v_sep/1.5,'-'));
dbms_output.put_line('Quantidade total do grupo: ' || r_relatorio.qtde_grupo);
dbms_output.put_line('Valor total do grupo: ' || r_relatorio.soma_grupo);
dbms_output.put_line(rpad('-',v_sep/1.5,'-') || chr(10));
END IF;
-- Se for o último registro, coloca o resumo
IF r_relatorio.FINAL = 'F' THEN
dbms_output.put_line(chr(10) || rpad('=',v_sep/2,'='));
dbms_output.put_line('Resumo');
dbms_output.put_line(rpad('=',v_sep/2,'='));
dbms_output.put_line('Quantidade total: ' || r_relatorio.qtde_total);
dbms_output.put_line('Somatório total: ' || r_relatorio.soma_total);
dbms_output.put_line(rpad('-',v_sep/2,'-'));
END IF;
END LOOP;
END;
Ele utiliza um cursor com a consulta demonstrada anteriormente e alguns testes para verificar em qual parte do relatório ele está.
Resultado:
================================================================================
Grupo: 1
================================================================================
Valor: 1 Parcial: 1
Valor: 2 Parcial: 3
Valor: 5 Parcial: 8
Valor: 6 Parcial: 14
Valor: 9 Parcial: 23
Valor: 10 Parcial: 33
-----------------------------------------------------
Quantidade total do grupo: 6
Valor total do grupo: 33
-----------------------------------------------------
================================================================================
Grupo: 2
================================================================================
Valor: 1 Parcial: 1
Valor: 3 Parcial: 4
Valor: 4 Parcial: 8
Valor: 6 Parcial: 14
Valor: 84 Parcial: 98
-----------------------------------------------------
Quantidade total do grupo: 5
Valor total do grupo: 98
-----------------------------------------------------
================================================================================
Grupo: 3
================================================================================
Valor: 4 Parcial: 4
Valor: 5 Parcial: 9
Valor: 6 Parcial: 15
Valor: 14 Parcial: 29
Valor: 15 Parcial: 44
Valor: 21 Parcial: 65
Valor: 68 Parcial: 133
-----------------------------------------------------
Quantidade total do grupo: 7
Valor total do grupo: 133
-----------------------------------------------------
================================================================================
Grupo: 4
================================================================================
Valor: 6 Parcial: 6
Valor: 34 Parcial: 40
Valor: 78 Parcial: 118
-----------------------------------------------------
Quantidade total do grupo: 3
Valor total do grupo: 118
-----------------------------------------------------
================================================================================
Grupo: 5
================================================================================
Valor: 2 Parcial: 2
Valor: 12 Parcial: 14
Valor: 16 Parcial: 30
Valor: 18 Parcial: 48
-----------------------------------------------------
Quantidade total do grupo: 4
Valor total do grupo: 48
-----------------------------------------------------
========================================
Resumo Final
========================================
Quantidade total: 25
Somatório total: 430
----------------------------------------