avg over order by range : Aggregrate Analytical « Analytical Functions « Oracle PL / SQL

avg over order by range

 
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));

Table created.

SQL>
SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7521, 'WARD',  'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER',  7839, TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

1 row created.

SQL>
SQL> set numformat 9999.99
SQL> select ename, hiredate, sal,
  2      avg(sal)
  3      over ( order by hiredate asc  range 100 preceding ) avg_sal_100_days_BEFORE,
  4      avg(sal)
  5      over ( order by hiredate desc  range 100 preceding ) avg_sal_100_days_AFTER
  6    from emp
  7   order by hiredate
  8  /

ENAME      HIREDATE       SAL AVG_SAL_100_DAYS_BEFORE AVG_SAL_100_DAYS_AFTER
---------- --------- -------- ----------------------- ----------------------
SMITH      17-DEC-80   800.00                  800.00                1216.67
ALLEN      20-FEB-81  1600.00                 1200.00                2168.75
WARD       22-FEB-81  1250.00                 1216.67                2358.33
JONES      02-APR-81  2975.00                 1941.67                2758.33
BLAKE      01-MAY-81  2850.00                 2168.75                2650.00
CLARK      09-JUN-81  2450.00                 2758.33                1975.00
TURNER     08-SEP-81  1500.00                 1975.00                2340.00
MARTIN     28-SEP-81  1250.00                 1375.00                2550.00
KING       17-NOV-81  5000.00                 2583.33                2562.50
JAMES      03-DEC-81   950.00                 2340.00                1750.00
FORD       03-DEC-81  3000.00                 2340.00                1750.00

ENAME      HIREDATE       SAL AVG_SAL_100_DAYS_BEFORE AVG_SAL_100_DAYS_AFTER
---------- --------- -------- ----------------------- ----------------------
MILLER     23-JAN-82  1300.00                 2562.50                1300.00
SCOTT      09-DEC-82  3000.00                 3000.00                2050.00
ADAMS      12-JAN-83  1100.00                 2050.00                1100.00

14 rows selected.

SQL>
SQL> drop table emp;

Table dropped.

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.	average 5 before, after
11.	Row-ordering is done first and then the moving average
12.	Avg over ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
13.	SUM(y) OVER(ORDER BY x 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