Mega Code Archive
Use SUM for windowing
<![CDATA[
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060
725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860
221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900
315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990
421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980
808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960
104','YYYYMMDD'), 4322.78,'New York', 'Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980
212','YYYYMMDD'), 7897.78,'New York', 'Manager')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020
415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
3 /
1 row created.
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> COLUMN ma FORMAT 999999.9999
SQL> COLUMN sum LIKE ma
SQL> COLUMN "sum/3" LIKE ma
SQL> SELECT id, salary,
2 AVG(salary) OVER(ORDER BY id
3 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ma,
4 SUM(salary) OVER(ORDER BY id
5 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum,
6 (SUM(salary) OVER(ORDER BY id
7 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING))/3 "Sum/3"
8 FROM employee
9 ORDER BY id;
ID SALARY MA SUM Sum/3
---- ---------- ------------ ------------ ------------
01 1234.56 3948.1700 7896.3400 2632.1133
02 6661.78 4813.7067 14441.1200 4813.7067
03 6544.78 5183.7800 15551.3400 5183.7800
04 2344.78 3741.4467 11224.3400 3741.4467
05 2334.78 3000.7800 9002.3400 3000.7800
06 4322.78 4851.7800 14555.3400 4851.7800
07 7897.78 4484.4467 13453.3400 4484.4467
08 1232.78 4565.2800 9130.5600 3043.5200
8 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
]]>