SUM(y) OVER(ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)



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

Table created.

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>
SQL> COLUMN ma FORMAT 99.999
SQL> COLUMN sum LIKE ma
SQL> COLUMN "sum/3" LIKE ma
SQL>
SQL> SELECT x, y,
  2    AVG(y) OVER(ORDER BY x
  3      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ma,
  4    SUM(y) OVER(ORDER BY x
  5      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum,
  6    (SUM(y) OVER(ORDER BY y
  7      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING))/3 "Sum/3"
  8  FROM TestTable
  9  ORDER BY x;

         X          Y      MA     SUM   Sum/3
---------- ---------- ------- ------- -------
         1          7   4.000   8.000   6.333
         2          1   3.333  10.000   1.000
         3          2   2.667   8.000   1.667
         4          5   4.667  14.000   4.667
         5          7  15.333  46.000   8.667
         6         34  24.333  73.000  34.000
         7         32  36.333 #######  26.667
         8         43  54.000 #######  45.000
         9         87  54.000 #######  53.667
        10         32  43.667 #######  32.667
        11         12  20.000  60.000  11.667
        12         16  30.333  91.000  20.000
        13         63  51.000 #######  64.333
        14         74  57.667 #######  74.667
        15         36  55.333 #######  37.667
        16         56  31.333  94.000  54.000
        17          2  29.000  58.000   3.000

17 rows selected.

SQL>
SQL> COLUMN ma FORMAT 999999.999
SQL> COLUMN sum LIKE ma
SQL> COLUMN "sum/3" LIKE ma
SQL>
SQL>
SQL> SELECT x, y,
  2    AVG(y) OVER(ORDER BY x
  3      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ma,
  4    SUM(y) OVER(ORDER BY x
  5      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum,
  6    (SUM(y) OVER(ORDER BY y
  7      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING))/3 "Sum/3"
  8  FROM TestTable
  9  ORDER BY x;

         X          Y          MA         SUM       Sum/3
---------- ---------- ----------- ----------- -----------
         1          7       4.000       8.000       6.333
         2          1       3.333      10.000       1.000
         3          2       2.667       8.000       1.667
         4          5       4.667      14.000       4.667
         5          7      15.333      46.000       8.667
         6         34      24.333      73.000      34.000
         7         32      36.333     109.000      26.667
         8         43      54.000     162.000      45.000
         9         87      54.000     162.000      53.667
        10         32      43.667     131.000      32.667
        11         12      20.000      60.000      11.667
        12         16      30.333      91.000      20.000
        13         63      51.000     153.000      64.333
        14         74      57.667     173.000      74.667
        15         36      55.333     166.000      37.667
        16         56      31.333      94.000      54.000
        17          2      29.000      58.000       3.000

17 rows selected.

SQL>
SQL> drop table TestTable;

Table dropped.

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.	Row-ordering is done first and then the moving average
13.	Avg over 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

SUM(y) OVER(ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) : Aggregrate Analytical « Analytical Functions « Oracle PL / SQL

Related examples in the same category