glufke.net

Enviado: **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.

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

Enviado: **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 ???

Enviado: **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.

Enviado: **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.

Enviado: **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

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)

Enviado: **Ter, 21 Ago 2012 5:29 pm**

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

Enviado: **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 !!!

Enviado: **Qua, 22 Ago 2012 9:19 am**

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

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;

Enviado: **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 ?

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;

glufke.net

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

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

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

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

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

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

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