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