Relatório SQL usando funções analíticas

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
ricardorauber
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 96
Registrado em: Qua, 21 Jun 2006 11:33 pm
Localização: Canoas RS

Relatório SQL usando funções analíticas

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.

Selecionar tudo

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
Como podemos notar, o campo A repete várias vezes enquanto que o campo B varia sem uma função lógica. Por causa disso, vamos usar o campo A como agrupador e o campo B como valores do grupo.

2 - Agora vamos analisar a consulta que será utilizada no cursor do nosso algoritmo.

Selecionar tudo

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
Vamos passar campo a campo:
- 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

Selecionar tudo

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;
Como podem ver, o algoritmo é simples, bem fácil de entender e auto-explicativo.
Ele utiliza um cursor com a consulta demonstrada anteriormente e alguns testes para verificar em qual parte do relatório ele está.

Resultado:

Selecionar tudo

================================================================================
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
----------------------------------------
gokden
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 264
Registrado em: Dom, 19 Ago 2007 8:18 pm
Localização: Ribeirão Preto - SP
Lucas de Souza

OCA Developer
Analista de sistemas

hmm.... bem interessante mesmo essa parte de Funções analiticas....

mais eu sei qui no Forms 6i ele não identificava essas funções analiticas... você sabe se no Forms 10g e no Reports está aceitando esse tipo de função ??

e você tem algum documento relacionado a isso que você possa compartilhar com a comunidade inteira ???
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Sex, 30 Mar 2007 7:26 pm
Localização: Londrina - PR
Rafael O. Genaro

Realmente a versão o parser PL/SQL do forms 6i não identifica funções analíticas (e outras coisinhas mais...) que funcionam sem problemas via SQL.
No reports 6i, no entanto, este problema não ocorre, e este tipo de função podem ser usadas sem problemas.

Este problema ocorre também na versão 8i do banco (é possível usar funções analíticas via SQL, mas não em blocos PL/SQL).

Algumas alternativas para usar estas funções em versões antigas do forms seriam:

1) Colocar a lógica em procedures/functions/packages (versão 9i do banco, ou superior);

2) Usar sql dinâmico (vale também para a versão 8i do banco);

3) Criar uma view com a consulta (também vale para a versão 8i). Neste caso, no entanto, deve-se tomar cuidado para certificar-se de que está ocorrendo predicate pushing com os parâmetros passados ao se consultar a view (cláusulas de partição, etc) para se evitar a geração de planos de execução monstruosos (isso ocorre com freqüência bem maior nas versão 8i, já que seu otimizador não é dos mais inteligentes para este tipo de query, embora possa ocorrer também nas versões mais recentes).


Não posso afirmar com certeza que a versão 10g do Forms suporta estas funções, mas tudo indica que sim, visto que a versão do seu parser de PL/SQL provavelmente é bem mais recente que o da versão 6i.
ricardorauber
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 96
Registrado em: Qua, 21 Jun 2006 11:33 pm
Localização: Canoas RS

O Forms 10g parece que funciona sim! Certa vez me disseram que o Forms 6i tem uma versão antiga do PL/SQL, o que de fato é verdade. Para funcionar funções analíticas e types "matriz" teria que ser feito um upgrade do pl/sql do Forms mas eu sinceramente não sei como fazer isso.
Mr.Delaima
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 1
Registrado em: Ter, 14 Ago 2012 3:22 pm

Pessoal, Aproveitando a assunto de funções analíticas, estou tendo um problema que até o momento não consegui resolver (com um select), fiz uma solução usando loop, mas fica muito lento...
o negócio é o seguinte:
Tenho a seguinte estrutura na tabela:

ColunaA - sequence - chave da tabela
ColunaB - numérico podendo repetir

Selecionar tudo

ColunaA  |  ColunaB
-------------------------
1            |     1
2            |     1            
3            |     1
4            |     45
5            |     45
6            |     45     
7            |    1
8            |    1
9            |    1
10          |    1
11          |    2
12          |    2
13          |    1
14          |    1
15          |    1
No relatório, preciso exibir os registros de da seguinte forma (dado um valor da ColunaB, por exemplo 1):

Selecionar tudo

ColunaB  | Faixa
-------------------------
 1           | de 1 a 3
 1           | de 7 a 10
 1           | de 13 a 15
Alguém sabe me dizer se existe algum função analítica que me ajudaria solucionar essa situação?
Será que é possível resolver isso sem precisar fazer um loop e analisar todos os registros (filtrados pela ColunaB = 1, claro)
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Se entendi corretamente, acho que assim possa resolver:

Selecionar tudo

SQL> create table minha_tab (colunaA number(10) primary key, colunaB number(10));
 
Table created
SQL> insert into minha_tab values (1, 1);
 
1 row inserted
SQL> insert into minha_tab values (2, 1);
 
1 row inserted
SQL> insert into minha_tab values (3, 1);
 
1 row inserted
SQL> insert into minha_tab values (4, 45);
 
1 row inserted
SQL> insert into minha_tab values (5, 45);
 
1 row inserted
SQL> insert into minha_tab values (6, 45);
 
1 row inserted
SQL> insert into minha_tab values (7, 1);
 
1 row inserted
SQL> insert into minha_tab values (8, 1);
 
1 row inserted
SQL> insert into minha_tab values (9, 1);
 
1 row inserted
SQL> insert into minha_tab values (10, 1);
 
1 row inserted
SQL> insert into minha_tab values (11, 2);
 
1 row inserted
SQL> insert into minha_tab values (12, 2);
 
1 row inserted
SQL> insert into minha_tab values (13, 1);
 
1 row inserted
SQL> insert into minha_tab values (14, 1);
 
1 row inserted
SQL> insert into minha_tab values (15, 1);
 
1 row inserted
SQL> commit;
 
Commit complete
SQL> select max(colunab) keep (dense_rank last order by nivel) colunab,
  2         max(inicio_faixa) keep (dense_rank last order by nivel) inicio_faixa,
  3         max(fim_faixa) keep (dense_rank last order by nivel) fim_faixa
  4    from (select colunaa inicio_faixa, connect_by_root(colunaa) fim_faixa, sys_connect_by_path(colunaa, '=>'), level nivel, colunab
  5            from (select colunaa, colunab
  6                    from minha_tab
  7                   where colunab = 1)
  8           where connect_by_isleaf = 1
  9           connect by colunaa = prior (colunaa) - 1)
 10   group by inicio_faixa;
 
   COLUNAB INICIO_FAIXA  FIM_FAIXA
---------- ------------ ----------
         1            1          3
         1            7         10
         1           13         15
schnu
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 110
Registrado em: Ter, 24 Mai 2005 9:16 pm
Localização: Dongguan - Guangdong - China
Deus criou os loucos para confundir os sábios

Muito boa a tua solução fsitja

Parabéns, eu cheguei a quebrar um pouco a cabeça com esse problema mas não havia chegado perto da solução !!!
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Valeu, curti o problema, vamos ver se atende o Mr.Delaima :D

Percebi que ficaram umas "sujeirinhas" dos testes que fiz. Limpando elas fica mais simples, assim:

Selecionar tudo

select max(colunab) keep (dense_rank last order by nivel) colunab,
       inicio_faixa,
       max(fim_faixa) keep (dense_rank last order by nivel) fim_faixa
  from (select colunaa inicio_faixa, connect_by_root(colunaa) fim_faixa, level nivel, colunab
           from (select colunaa, colunab
                   from minha_tab
                  where colunab = 1)
          where connect_by_isleaf = 1
          connect by colunaa = prior (colunaa) - 1)
 group by inicio_faixa;
muttley
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 24
Registrado em: Qua, 15 Nov 2006 10:02 am
Localização: Cotia - SP
A Benção de Deus enriquece e não acrescenta dores

No código abaixo, há alguma coisa que possa subststituir o comando connect_by_isleaf =1 no oracle 9.2 ?

Selecionar tudo

select max(colunab) keep (dense_rank last order by nivel) colunab,
       inicio_faixa,
       max(fim_faixa) keep (dense_rank last order by nivel) fim_faixa
  from (select colunaa inicio_faixa, connect_by_root(colunaa) fim_faixa, level nivel, colunab
           from (select colunaa, colunab
                   from minha_tab
                  where colunab = 1)
          where connect_by_isleaf = 1
          connect by colunaa = prior (colunaa) - 1)
group by inicio_faixa;

Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 18 visitantes