WINDOWING FUNCTION:-
====================
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
------------------------
-
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;
Sample ex#2
------------------------
- 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;
Sample ex#3
-----------------------
- 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;
Sample ex#4
-----------------------
- 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;
Sample ex#5
-----------------------
- 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;
Sample ex#6
--------------------
- 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;
Sample ex#7
--------------------
- 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);
Also Check
Windowing Function - 2 
