====================
ORACLE'S WINDOWING FUNCTIONS ALLOW AGGERGATES TO BE CALCULATED FOR EACH ROW IN A RESULT SET BASED ON A SPECIFIED WINDOW.THE AGGREGATION WINDOW CAN BE DEFINED IN ONE OF THREE WAYS.
1.BY SPECIFYING A SET OF ROWS:
2.BY SPECIFYING A TIME INTERVAL:
3.BY SPECIFYING A RANGE OF VALUES:
Sample ex#1
------------------------
Expand|Select|Wrap|Line Numbers
- SELECT HIREDATE,SUM(SAL) SALARY, SUM(SUM(SAL)) OVER(ORDER BY HIREDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TOTALSALARY FROM EMP
- GROUP BY HIREDATE ORDER BY HIREDATE;
------------------------
Expand|Select|Wrap|Line Numbers
- SELECT HIREDATE,SUM(SAL) SALARY,MAX(SUM(SAL)) OVER(ORDER BY HIREDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) TOTALSALARY
- FROM EMP
- WHERE DEPTNO=30
- GROUP BY HIREDATE ORDER BY HIREDATE;
-----------------------
Expand|Select|Wrap|Line Numbers
- SELECT HIREDATE,SUM(SAL) SALARY,SUM(SUM(SAL)) OVER(ORDER BY HIREDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RUNNINGTOTAL
- FROM EMP
- WHERE DEPTNO=30
- GROUP BY HIREDATE ORDER BY HIREDATE;
-----------------------
Expand|Select|Wrap|Line Numbers
- SELECT HIREDATE,SUM(SAL) SALARY,AVG(SUM(SAL)) OVER(ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ROLLINGAVERAGE
- FROM EMP
- WHERE DEPTNO=30
- GROUP BY HIREDATE ORDER BY HIREDATE;
-----------------------
Expand|Select|Wrap|Line Numbers
- SELECT HIREDATE,SUM(SAL) SALARY,AVG(SUM(SAL)) OVER(ORDER BY HIREDATE RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) ROLLINGAVERAGE
- FROM EMP
- GROUP BY HIREDATE ORDER BY HIREDATE;
--------------------
Expand|Select|Wrap|Line Numbers
- SELECT HIREDATE,SUM(SAL) SALARY,AVG(SUM(SAL)) OVER(ORDER BY HIREDATE RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING) ROLLINGAVERAGE
- FROM EMP
- GROUP BY HIREDATE ORDER BY HIREDATE;
--------------------
Expand|Select|Wrap|Line Numbers
- SELECT TRUNC(HIREDATE)DAY,SUM (SAL) SALARY,AVG(SUM(SAL)) OVER (ORDER BY TRUNC(HIREDATE)
- RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND INTERVAL '2' DAY FOLLOWING) AVERAGE
- FROM EMP
- GROUP BY TRUNC(HIREDATE);