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 9:19 am

Recently I'vê been looking at a few reports that were made several twists of codes for a row of the query could interact with each other. Clearly it was possible to observe that the developer of the query didn't know and probably never should have heard on analytic functions.

These functions are pretty simple to use (mostly) and break a big branch! I am preparing a material on the use of these simple and direct, but for now here are some examples in the code below, it's worth checking the behavior of each one of them in relation to the original values:
Code: Select all
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
Location: Canoas RS

Poston Tue, 03 Jun 2008 9:21 am

Unfortunately it went horribly messy and I can't edit, so I suggest copying the code for an editor such as Notepad and remove the line-wrapping.
ricardorauber
Location: Canoas RS

Poston Tue, 03 Jun 2008 9:28 am

Now just got more interesting:
Code: Select all
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
Location: Canoas RS

Poston Tue, 03 Jun 2008 10:07 am

The functions can also be used to make breaks as in reports. Below is a exemplinho of mini-relatório with totalizadoras columns of the group in the last record of the same (have patience to understand).

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



Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests