Windowing Function - 1

[debasisdas]

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