SQL report using analytic functions

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Tue, 03 Jun 2008 11:24 am

SQL report using analytic functions created an example of how to make a full report using the analytic functions.

1-first of all, let's see the contents of the table.

Code: Select all
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
As we note, the field to repeat several times while the B-field varies without a logical function. Because of this, we will use the camp as Collator and the B-field as values of the group.

2-Now let's examine the query that will be used in the cursor of our algorithm.

Code: Select all
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
Let's go field by field:-GROUP Field: used the LAG analytic function to verify that is the first record of the group, and then use it to create the group header in the algorithm.
-Value field: this field is part of the lines of the report.
-PARCIAL_GRUPO Field: this field is part of the lines of the report.
-QTDE_GRUPO Field: used the analytic function LEAD to verify if it is the last record in the Group and so use it as gambling for the group footer in the algorithm.
-SOMA_GRUPO Field: used the analytic function LEAD to verify if it is the last record in the Group and so use it as gambling for the group footer in the algorithm.
-QTDE_TOTAL Field: Used to counting the records for use in the final summary.
-SOMA_TOTAL Field: Used for adding records to use in the final summary.
-FINAL field: used the analytic function LEAD to verify if it is the last record of the query and thus identify the end of the report and display the summary on the algorithm.

3-Developing the algorithm
Code: Select all
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;
as you can see, the algorithm is simple, easy to understand and self-explanatory.
He uses a cursor with the query shown earlier, and some tests to verify in which part of the report.

Result:
Code: Select all
================================================================================
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
Location: Canoas RS

Poston Tue, 03 Jun 2008 4:32 pm

hmm .... very interesting even this part of Analytic Functions .... plus I know Thurs in Forms 6i he not identified these analytic functions ... you know if the Forms 10 g and in the Reports is accepting this kind of function?

and you have any document related to this that you can share with the whole community???
gokden
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Poston Tue, 03 Jun 2008 7:09 pm

Actually the version the PL/SQL parser of forms 6i does not identify analytic functions (and other things more ...) that work without problems via SQL.
In reports 6i, however, this problem does not occur, and this type of function can be used without problems.

This problem also occurs in version 8i of the Bank (you can use analytic functions via SQL, but not in PL/SQL blocks).

Some alternatives to use these functions in older versions of forms would be: 1) Put the logic in procedures/functions/packages (version 9i of the Bank, or higher);

2) Use dynamic sql (also applies to version 8i of the Bank);

3) Create a view with the query (also applies to version 8i). In this case, however, care must be taken to make sure that is occurring predicate pushing with the parameters passed to query the view (partition clauses, etc.) to prevent the generation of monstrous execution plans (this is often greater in the 8i version, since his is not the most intelligent optimizer for this type of query, although it may also occur in the latest versions).


I can't say for sure that the version 10 g Forms supports these functions, but everything indicates that Yes, since the version of your PL/SQL parser is probably far more recent than the version 6i.
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Fri, 06 Jun 2008 3:14 pm

Forms 10 g it seems to work! Once I was told that the Forms 6i has an older version of PL/SQL, what actually is true. To run analytic functions and types \"array\" would have to be upgraded from pl/sql Forms but I honestly do not know how to do this.
ricardorauber
Location: Canoas RS

Poston Tue, 14 Aug 2012 4:36 pm

Guys, taking advantage of the subject of analytic functions, I am having a problem that so far haven't been able to solve (with a select), did a solution using loop, but is very slow ... here's the deal: I have the following structure in the table: {ColunaA-sequence-ColunaB-numeric table key and repeat} 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 in the report, I need to display the records as follows (given a value of 1, for example ColunaB): ColunaB | Faixa
-------------------------
1 | from 1 to 3 1 | from 7 to 10 1 | of 13 to 15 can anyone tell me if there is any analytic function that would help me solve this situation?
Is it possible to fix this without having to do a loop and parse all records (filtered by ColunaB = 1 of course)
Mr.Delaima

Poston Tue, 21 Aug 2012 5:29 pm

If I understand correctly, I think that can solve:
Code: Select all
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
Location: 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

Poston Tue, 21 Aug 2012 10:01 pm

Very good your solution fsitja Congratulations, I arrived to break your head a bit with this problem but no solution close!!!
schnu
Location: Dongguan - Guangdong - China

Deus criou os loucos para confundir os sábios

Poston Wed, 22 Aug 2012 9:19 am

[quote = " schnu "]Very good your solution fsitja Congratulations, I arrived to break your head a bit with this problem but no solution close!!![/quote]
Thanks, I enjoyed the problem, let's see if meets Mr. Delaima:D

I realized that they were some " droppings of " tests I did. Cleaning them is more simple, like this:
Code: Select all
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
Location: 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

Poston Thu, 08 Nov 2012 11:16 am

In the code below, is there anything I can subststituir the command connect_by_isleaf = 1 in oracle 9.2?
Code: Select all
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
Location: Cotia - SP

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


  • See also
    Replies
    Views
    Last Post


    Return to SQL

    Who is online

    Users browsing this forum: No registered users and 4 guests