average 5 before, after : Aggregrate Analytical « Analytical Functions « Oracle PL / SQL

average 5 before, after

 
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> select * from emp;

   EMPNO ENAME      JOB            MGR HIREDATE       SAL     COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
 7369.00 SMITH      CLERK      7902.00 17-DEC-80   800.00              20

 7499.00 ALLEN      SALESMAN   7698.00 20-FEB-81  1600.00   300.00     30
 7521.00 WARD       SALESMAN   7698.00 22-FEB-81  1250.00   500.00

 7566.00 JONES      MANAGER    7839.00 02-APR-81  2975.00              20

 7654.00 MARTIN     SALESMAN   7698.00 28-SEP-81  1250.00  1400.00     30
 7698.00 BLAKE      MANAGER    7839.00 01-MAY-81  2850.00

 7782.00 CLARK      MANAGER    7839.00 09-JUN-81  2450.00              10

   EMPNO ENAME      JOB            MGR HIREDATE       SAL     COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------

 7788.00 SCOTT      ANALYST    7566.00 09-DEC-82  3000.00              20

 7839.00 KING       PRESIDENT          17-NOV-81  5000.00              10

 7844.00 TURNER     SALESMAN   7698.00 08-SEP-81  1500.00      .00     30

 7876.00 ADAMS      CLERK      7788.00 12-JAN-83  1100.00              20

 7900.00 JAMES      CLERK      7698.00 03-DEC-81   950.00              30


   EMPNO ENAME      JOB            MGR HIREDATE       SAL     COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
 7902.00 FORD       ANALYST    7566.00 03-DEC-81  3000.00              20

 7934.00 MILLER     CLERK      7782.00 23-JAN-82  1300.00              10


14 rows selected.

SQL>
SQL> set numformat 9999.99
SQL> select ename, hiredate, sal,
  2         avg(sal)
  3         over ( order by hiredate asc rows 5 preceding ) avg_5_before,
  4             count(*)
  5         over ( order by hiredate asc rows 5 preceding ) obs_before,
  6         avg(sal)
  7         over ( order by hiredate desc rows 5 preceding ) avg_5_after,
  8             count(*)
  9         over ( order by hiredate desc rows 5 preceding ) obs_after
 10    from emp
 11   order by hiredate
 12  /

ENAME      HIREDATE       SAL AVG_5_BEFORE OBS_BEFORE AVG_5_AFTER OBS_AFTER
---------- --------- -------- ------------ ---------- ----------- ---------
SMITH      17-DEC-80   800.00       800.00       1.00     1987.50      6.00
ALLEN      20-FEB-81  1600.00      1200.00       2.00     2104.17      6.00
WARD       22-FEB-81  1250.00      1216.67       3.00     2045.83      6.00
JONES      02-APR-81  2975.00      1656.25       4.00     2670.83      6.00
BLAKE      01-MAY-81  2850.00      1895.00       5.00     2333.33      6.00
CLARK      09-JUN-81  2450.00      1987.50       6.00     2358.33      6.00
TURNER     08-SEP-81  1500.00      2104.17       6.00     2166.67      6.00
MARTIN     28-SEP-81  1250.00      2045.83       6.00     2416.67      6.00
KING       17-NOV-81  5000.00      2670.83       6.00     2391.67      6.00
JAMES      03-DEC-81   950.00      2333.33       6.00     1870.00      5.00
FORD       03-DEC-81  3000.00      2358.33       6.00     2100.00      4.00

ENAME      HIREDATE       SAL AVG_5_BEFORE OBS_BEFORE AVG_5_AFTER OBS_AFTER
---------- --------- -------- ------------ ---------- ----------- ---------
MILLER     23-JAN-82  1300.00      2166.67       6.00     1800.00      3.00
SCOTT      09-DEC-82  3000.00      2416.67       6.00     2050.00      2.00
ADAMS      12-JAN-83  1100.00      2391.67       6.00     1100.00      1.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.	avg over order by range
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