You can perform window function with the following functions: SUM(), AVG(), MAX(), MIN(),
COUNT(), VARIANCE(), and STDDEV().
The following query performs a cumulative sum to compute the cumulative salaries amount, starting with each department.
SUM(amount) computes the sum of an amount.
The outer SUM() computes the cumulative amount.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW defines the start and end of the window.
The start is set to UNBOUNDED PRECEDING.
UNBOUNDED PRECEDING means that the start of the window is fixed at the first row in the result set.
CURRENT ROW sets the end of the window.
CURRENT ROW represents the current row in the result set.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- from www .j a v a 2 s .co m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> SELECT deptno, SUM(sal) AS dept_salary,
2 SUM(SUM(sal)) OVER (ORDER BY deptno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
3 AS cumulative_amount
4 FROM emp
5 GROUP BY deptno
6 ORDER BY deptno;
DEPTNO DEPT_SALARY CUMULATIVE_AMOUNT
---------- ----------- -----------------
10 5850 5850
20 8275 14125
30 8450 22575
SQL>
The following query uses a cumulative sum to compute the cumulative salary amount, starting with employee number 2 to 6:
SQL> SELECT deptno, SUM(sal) AS month_amount,
2 SUM(SUM(sal)) OVER-- ww w . j a v a 2s. c o m
3 (ORDER BY deptno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS
4 cumulative_amount
5 FROM emp
6 WHERE empno BETWEEN 2 AND 6
7 GROUP BY deptno
8 ORDER BY deptno;
DEPTNO MONTH_AMOUNT CUMULATIVE_AMOUNT
---------- ------------ -----------------
20 2975 2975
30 6950 9925
SQL>
The following query computes the moving average of the salary amount between the current
department and the previous three departments:
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW defines the start of the window
as including the three rows preceding the current row; the end of the window is the
current row being processed.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- from w w w . j a v a2 s . co m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> SELECT
2 deptno, SUM(sal) AS month_amount,
3 AVG(SUM(sal)) OVER (ORDER BY deptno ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
4 AS moving_average
5 FROM emp
6 GROUP BY deptno
7 ORDER BY deptno;
DEPTNO MONTH_AMOUNT MOVING_AVERAGE
---------- ------------ --------------
10 5850 5850
20 8275 7062.5
30 8450 7525
SQL>
The following query computes the moving average of the salary amount centered between the
previous and next department from the current department:
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING defines the start of the window as
including the row preceding the current row being processed. The end of the window is
the row following the current row.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- from w ww . j a v a 2 s . c o m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> SELECT deptno, SUM(sal) AS dept_amount,
2 AVG(SUM(sal)) OVER (ORDER BY deptno ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
3 AS moving_average
4 FROM emp
5 GROUP BY deptno
6 ORDER BY deptno;
DEPTNO DEPT_AMOUNT MOVING_AVERAGE
---------- ----------- --------------
10 5850 7062.5
20 8275 7525
30 8450 8362.5
SQL>
FIRST_VALUE() gets the first rows in a window.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- from w ww . ja v a 2 s . co m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> SELECT deptno, SUM(sal) AS dept_amount,
2 FIRST_VALUE(SUM(sal)) OVER (ORDER BY deptno ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
3 AS previous_month_amount
4 FROM emp
5 GROUP BY deptno
6 ORDER BY deptno;
DEPTNO DEPT_AMOUNT PREVIOUS_MONTH_AMOUNT
---------- ----------- ---------------------
10 5850 5850
20 8275 5850
30 8450 8275
SQL>
LAST_VALUE() gets the last rows in a window.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- from w w w . j ava 2 s .c o m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> SELECT deptno, SUM(sal) AS dept_amount,
2 LAST_VALUE(SUM(sal)) OVER(ORDER BY deptno ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
3 AS next_month_amount
4 FROM emp
5 GROUP BY deptno
6 ORDER BY deptno;
DEPTNO DEPT_AMOUNT NEXT_MONTH_AMOUNT
---------- ----------- -----------------
10 5850 8275
20 8275 8450
30 8450 8450
SQL>