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
  

Mensagemem Ter, 03 Jun 2008 11:24 am

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.

Código: Selecionar todos
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.

Código: Selecionar todos
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

Código: Selecionar todos
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:

Código: Selecionar todos
================================================================================
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
----------------------------------------
ricardorauber
Localização: Canoas RS

Mensagemem Ter, 03 Jun 2008 4:32 pm

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 ???
gokden
Localização: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Mensagemem Ter, 03 Jun 2008 7:09 pm

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.
rogenaro
Localização: Londrina - PR

Rafael O. Genaro

Mensagemem Sex, 06 Jun 2008 3:14 pm

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.
ricardorauber
Localização: Canoas RS

Mensagemem Ter, 14 Ago 2012 4:36 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
}

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):

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)
Mr.Delaima

Mensagemem Ter, 21 Ago 2012 5:29 pm

Se entendi corretamente, acho que assim possa resolver:
Código: Selecionar todos
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
fsitja
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

Mensagemem Ter, 21 Ago 2012 10:01 pm

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 !!!
schnu
Localização: Dongguan - Guangdong - China

Deus criou os loucos para confundir os sábios

Mensagemem Qua, 22 Ago 2012 9:19 am

schnu escreveu: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 !!!

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:
Código: Selecionar todos
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;
fsitja
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

Mensagemem Qui, 08 Nov 2012 11:16 am

No código abaixo, há alguma coisa que possa subststituir o comando connect_by_isleaf =1 no oracle 9.2 ?
Código: Selecionar todos
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
Localização: Cotia - SP

A Benção de Deus enriquece e não acrescenta dores


  • Veja também
    Respostas
    ExibiÇões
    Última mensagem


    Voltar para SQL

    Quem está online

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