Displaying which rows are used in the moving average calculations with two other analytical functions: FIRST_ VALUE and LAST_VALUE. : Window Functions « Analytical Functions « Oracle PL/SQL Tutorial

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>
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>
SQL> -- clean the table
SQL> drop table myTable
  2  /

Table dropped.

SQL>
SQL>

16.29.Window Functions
	16.29.1.	Using the Window Functions
	16.29.2.	Performing a Cumulative Sum
	16.29.3.	Use ROWS UNBOUNDED PRECEDING to implicitly indicate the end of the window is the current row
	16.29.4.	Performing a Moving Average
	16.29.5.	Performing a Centered Average
	16.29.6.	Moving average
	16.29.7.	Row-ordering is done first and then the moving average
	16.29.8.	Use SUM for windowing
	16.29.9.	Use the COUNT aggregate analytical function to show how many rows are included in each window
	16.29.10.	An Expanded Example of a Physical Window
	16.29.11.	Displaying which rows are used in the moving average calculations with two other analytical functions: FIRST_ VALUE and LAST_VALUE.