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

Recentemente estive olhando uns reports que foram feitas diversas reviravoltas de códigos para que uma linha da consulta pudesse interagir com a outra. Claramente foi possível observar que o desenvolvedor da consulta não conhecia e provavelmente nunca deve ter ouvido falar nas funções analíticas.

Essas funções são bastante simples de usar (a maioria) e quebram um baita galho! Estou preparando um material sobre a utilização delas bem simples e direto, mas por enquanto seguem alguns exemplos no código abaixo, vale a pena conferir o comportamento de cada uma delas em relação aos valores originais:

Selecionar tudo

SQL> select * from ricardo_tmp;

         A
----------
         1
         2
         3
         4
         5

SQL> SELECT a,
  2         AVG(a) over(ORDER BY a) AS AVG,
  3         COUNT(a) over(ORDER BY a) AS COUNT,
  4         SUM(a) over(ORDER BY a) AS SUM,
  5         first_value(a) over(ORDER BY a) AS first_value,
  6         first_value(a) over(ORDER BY a DESC) AS first_value_desc,
  7         MAX(a) over(ORDER BY a DESC) AS MAX,
  8         MIN(a) over(ORDER BY a) AS MIN,
  9         lag(a,1) over(ORDER BY a) AS lag,
 10         lead(a,1) over(ORDER BY a) AS lead,
 11         row_number() over(ORDER BY a) AS row_number,
 12         VARIANCE(a) over(ORDER BY a) AS VARIANCE
 13    FROM ricardo_tmp
 14  ORDER BY a;

         A        AVG      COUNT        SUM FIRST_VALUE FIRST_VALUE_DESC        MAX        MIN        LAG       LEAD ROW_NUMBER   VARIANCE
---------- ---------- ---------- ---------- ----------- ---------------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1           1                5          5          1                     2          1          0
         2        1,5          2          3           1                5          5          1          1          3          2        0,5
         3          2          3          6           1                5          5          1          2          4          3          1
         4        2,5          4         10           1                5          5          1          3          5          4 1,66666666
         5          3          5         15           1                5          5          1          4                     5        2,5
ricardorauber
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 96
Registrado em: Qua, 21 Jun 2006 11:33 pm
Localização: Canoas RS

Infelizmente saiu tudo bagunçado e não consigo editar, então sugiro copiar o código para um editor como o Bloco de Notas e tirar a quebra automática de linha.
ricardorauber
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 96
Registrado em: Qua, 21 Jun 2006 11:33 pm
Localização: Canoas RS

Agora ficou mais interessante:

Selecionar tudo

SQL> SELECT a
  2    FROM ricardo_tmp
  3  ORDER BY a;

         A
----------
         1
         2
         3
         4
         5

SQL> SELECT a,
  2         AVG(a) over(ORDER BY a) AS AVG
  3    FROM ricardo_tmp
  4  ORDER BY a;

         A        AVG
---------- ----------
         1          1
         2        1,5
         3          2
         4        2,5
         5          3

SQL> SELECT a,
  2         COUNT(a) over(ORDER BY a) AS COUNT
  3    FROM ricardo_tmp
  4  ORDER BY a;

         A      COUNT
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5

SQL> SELECT a,
  2         SUM(a) over(ORDER BY a) AS SUM
  3    FROM ricardo_tmp
  4  ORDER BY a;

         A        SUM
---------- ----------
         1          1
         2          3
         3          6
         4         10
         5         15

SQL> SELECT a,
  2         first_value(a) over(ORDER BY a) AS first_value
  3    FROM ricardo_tmp
  4  ORDER BY a;

         A FIRST_VALUE
---------- -----------
         1           1
         2           1
         3           1
         4           1
         5           1

SQL> SELECT a,
  2         first_value(a) over(ORDER BY a DESC) AS first_value_desc
  3    FROM ricardo_tmp
  4  ORDER BY a;

         A FIRST_VALUE_DESC
---------- ----------------
         1                5
         2                5
         3                5
         4                5
         5                5

SQL> SELECT a,
  2         MAX(a) over(ORDER BY a DESC) AS MAX
  3    FROM ricardo_tmp
  4  ORDER BY a;

         A        MAX
---------- ----------
         1          5
         2          5
         3          5
         4          5
         5          5

SQL> SELECT a,
  2         MIN(a) over(ORDER BY a) AS MIN
  3    FROM ricardo_tmp
  4  ORDER BY a;

         A        MIN
---------- ----------
         1          1
         2          1
         3          1
         4          1
         5          1

SQL> SELECT a,
  2         lag(a,1) over(ORDER BY a) AS lag
  3    FROM ricardo_tmp
  4  ORDER BY a;

         A        LAG
---------- ----------
         1 
         2          1
         3          2
         4          3
         5          4

SQL> SELECT a,
  2         lead(a,1) over(ORDER BY a) AS lead
  3    FROM ricardo_tmp
  4  ORDER BY a;

         A       LEAD
---------- ----------
         1          2
         2          3
         3          4
         4          5
         5 

SQL> SELECT a,
  2         row_number() over(ORDER BY a) AS row_number
  3    FROM ricardo_tmp
  4  ORDER BY a;

         A ROW_NUMBER
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5

SQL> SELECT a,
  2         VARIANCE(a) over(ORDER BY a) AS VARIANCE
  3    FROM ricardo_tmp
  4  ORDER BY a;

         A   VARIANCE
---------- ----------
         1          0
         2        0,5
         3          1
         4 1,66666666
         5        2,5
ricardorauber
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 96
Registrado em: Qua, 21 Jun 2006 11:33 pm
Localização: Canoas RS

As funções também poder ser usadas para fazer quebras como no reports. Abaixo segue um exemplinho de mini-relatório com colunas totalizadoras do grupo no último registro do mesmo (tenha paciência para entender).

Selecionar tudo

SQL> select * from ricardo_tmp2;

         A          B
---------- ----------
         1          1
         1          2
         2          3
         2          4
         3          5

SQL> 
SQL> SELECT a grupo,
  2         b valor,
  3         SUM (b) over (PARTITION BY a ORDER BY b) AS soma_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 total_grupo,
  6         SUM (b) over (ORDER BY b) AS soma_total
  7    FROM ricardo_tmp2
  8  /

     GRUPO      VALOR SOMA_GRUPO QTDE_GRUPO TOTAL_GRUPO SOMA_TOTAL
---------- ---------- ---------- ---------- ----------- ----------
         1          1          1                                 1
         1          2          3 2          3                    3
         2          3          3                                 6
         2          4          7 2          7                   10
         3          5          5 1          5                   15
Responder
  • Informação
  • Quem está online

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