Mega Code Archive
SUM(y) OVER(ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
<![CDATA[
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>
]]>