These two functions tell us which rows are used in the calculation of the window function for each row.
SQL>
SQL> -- create demo table
SQL> create table myTable(
2 id NUMBER(2),
3 value NUMBER(6,2)
4 )
5 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into myTable(ID, value)values (1,9);
1 row created.
SQL> insert into myTable(ID, value)values (2,2.11);
1 row created.
SQL> insert into myTable(ID, value)values (3,3.44);
1 row created.
SQL> insert into myTable(ID, value)values (4,-4.21);
1 row created.
SQL> insert into myTable(ID, value)values (5,10);
1 row created.
SQL> insert into myTable(ID, value)values (6,3);
1 row created.
SQL> insert into myTable(ID, value)values (7,-5.88);
1 row created.
SQL> insert into myTable(ID, value)values (8,123.45);
1 row created.
SQL> insert into myTable(ID, value)values (9,98.23);
1 row created.
SQL> insert into myTable(ID, value)values (10,938.23);
1 row created.
SQL> insert into myTable(ID, value)values (11,984.23);
1 row created.
SQL> insert into myTable(ID, value)values (12,198.23);
1 row created.
SQL> insert into myTable(ID, value)values (13,928.87);
1 row created.
SQL> insert into myTable(ID, value)values (14,25.37);
1 row created.
SQL> insert into myTable(ID, value)values (15,918.3);
1 row created.
SQL> insert into myTable(ID, value)values (16,9.23);
1 row created.
SQL> insert into myTable(ID, value)values (17,8.23);
1 row created.
SQL>
SQL> select * from myTable
2 /
ID VALUE
---------- ----------
1 9
2 2.11
3 3.44
4 -4.21
5 10
6 3
7 -5.88
8 123.45
9 98.23
10 938.23
11 984.23
12 198.23
13 928.87
14 25.37
15 918.3
16 9.23
17 8.23
17 rows selected.
SQL>
SQL> COLUMN first FORMAT 999999.99
SQL> COLUMN last LIKE first
SQL> SELECT id, value,
2 AVG(value) OVER(ORDER BY id
3 ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) ma,
4 FIRST_VALUE(value) OVER(ORDER BY id
5 ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) first,
6 LAST_VALUE(value) OVER(ORDER BY id
7 ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) last
8 FROM myTable
9 ORDER BY id;
ID VALUE MA FIRST LAST
---------- ---------- ---------- ---------- ----------
1 9 5.555 9.00 2.11
2 2.11 4.850 9.00 3.44
3 3.44 2.585 9.00 -4.21
4 -4.21 4.068 9.00 10.00
5 10 2.868 2.11 3.00
6 3 1.270 3.44 -5.88
7 -5.88 25.272 -4.21 123.45
8 123.45 45.760 10.00 98.23
9 98.23 231.406 3.00 938.23
10 938.23 427.652 -5.88 984.23
11 984.23 468.474 123.45 198.23
12 198.23 629.558 98.23 928.87
13 928.87 614.986 938.23 25.37
14 25.37 611.000 984.23 918.30
15 918.3 416.000 198.23 9.23
16 9.23 378.000 928.87 8.23
17 8.23 240.283 25.37 8.23
17 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table myTable
2 /
Table dropped.
SQL>