An Expanded Example of a Physical Window : Window Functions « Analytical Functions « Oracle PL/SQL Tutorial

To expand the moving average window, we can change the clause ROWS BETWEEN x PRECEDING AND y FOLLOWING to have different values for x and y.

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> COLUMN ma FORMAT 99999.999
SQL> SELECT id, value,
  2    AVG(value) OVER(ORDER BY id
  3    ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) ma
  4  FROM myTable
  5  ORDER BY id;

        ID      VALUE         MA
---------- ---------- ----------
         1          9      5.555
         2       2.11      4.850
         3       3.44      2.585
         4      -4.21      4.068
         5         10      2.868
         6          3      1.270
         7      -5.88     25.272
         8     123.45     45.760
         9      98.23    231.406
        10     938.23    427.652
        11     984.23    468.474
        12     198.23    629.558
        13     928.87    614.986
        14      25.37    611.000
        15      918.3    416.000
        16       9.23    378.000
        17       8.23    240.283

17 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table myTable
  2  /

Table dropped.

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.