Centered Average

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));

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>

Home »
Oracle »
Analytical Functions »

Window Functions: