Row-ordering is done first and then the moving average



SQL> create table TestTable (
  2    x    number primary key,
  3    y   number
  4  );

Table created.

SQL>
SQL> insert into TestTable values (1, 7 );

1 row created.

SQL> insert into TestTable values (2, 1 );

1 row created.

SQL> insert into TestTable values (3, 2 );

1 row created.

SQL> insert into TestTable values (4, 5 );

1 row created.

SQL> insert into TestTable values (5, 7 );

1 row created.

SQL> insert into TestTable values (6, 34 );

1 row created.

SQL> insert into TestTable values (7, 32 );

1 row created.

SQL> insert into TestTable values (8, 43 );

1 row created.

SQL> insert into TestTable values (9, 87 );

1 row created.

SQL> insert into TestTable values (10, 32 );

1 row created.

SQL> insert into TestTable values (11, 12 );

1 row created.

SQL> insert into TestTable values (12, 16 );

1 row created.

SQL> insert into TestTable values (13, 63 );

1 row created.

SQL> insert into TestTable values (14, 74 );

1 row created.

SQL> insert into TestTable values (15, 36 );

1 row created.

SQL> insert into TestTable values (16, 56 );

1 row created.

SQL> insert into TestTable values (17, 2 );

1 row created.

SQL>
SQL> select * from TestTable;

         X          Y
---------- ----------
         1          7
         2          1
         3          2
         4          5
         5          7
         6         34
         7         32
         8         43
         9         87
        10         32
        11         12
        12         16
        13         63
        14         74
        15         36
        16         56
        17          2

17 rows selected.

SQL>
SQL>
SQL> -- Row-ordering is done first and then the moving average:
SQL>
SQL> SELECT x, y,
  2    AVG(y) OVER(ORDER BY y
  3      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ma
  4  FROM TestTable
  5  ORDER BY y;

         X          Y         MA
---------- ---------- ----------
         2          1        1.5
         3          2 1.66666667
        17          2          3
         4          5 4.66666667
         1          7 6.33333333
         5          7 8.66666667
        11         12 11.6666667
        12         16         20
         7         32 26.6666667
        10         32 32.6666667
         6         34         34
        15         36 37.6666667
         8         43         45
        16         56         54
        13         63 64.3333333
        14         74 74.6666667
         9         87       80.5

17 rows selected.

SQL>
SQL>
SQL>
SQL> drop table TestTable;

Table dropped.

SQL>
SQL>

Related examples in the same category

1.	count(*) over partition, order by, range unbounded preceding
2.	count(*) over partition by, order by, range unbounded preceding
3.	Employee salary report with avg salary for the previous 12 months
4.	avg over range between
5.	Is our average total_order_price increasing or decreasing?
6.	analytic order-by clause
7.	avg over and avg over order by
8.	Sum over order by
9.	Sum over partition by and order by
10.	avg over order by range
11.	average 5 before, after
12.	Avg over ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
13.	SUM(y) OVER(ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
14.	Use the COUNT aggregate analytical function to show how many rows are included in each window
15.	To see how the moving average window can expand
16.	Uses dates and logical offset of seven days preceding
17.	A seven-day MAX and MIN on Tuesdays
18.	A seven-day MAX and MIN on Tuesdays: using TO_CHAR function
19.	Displaying a Running Total Using SUM as an Analytical Function
20.	Reporting on a Sum

Row-ordering is done first and then the moving average : Aggregrate Analytical « Analytical Functions « Oracle PL / SQL

Related examples in the same category