472,145 Members | 1,439 Online

Windowing Function - 1

8,127 Expert 4TB
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
------------------------
Expand|Select|Wrap|Line Numbers
1. SELECT HIREDATE,SUM(SAL) SALARY, SUM(SUM(SAL)) OVER(ORDER BY HIREDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TOTALSALARY FROM EMP
2. GROUP BY HIREDATE ORDER BY HIREDATE;
Sample ex#2
------------------------
Expand|Select|Wrap|Line Numbers
1. SELECT HIREDATE,SUM(SAL) SALARY,MAX(SUM(SAL)) OVER(ORDER BY HIREDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) TOTALSALARY
2. FROM EMP
3. WHERE DEPTNO=30
4. GROUP BY HIREDATE ORDER BY HIREDATE;
Sample ex#3
-----------------------
Expand|Select|Wrap|Line Numbers
1. SELECT HIREDATE,SUM(SAL) SALARY,SUM(SUM(SAL)) OVER(ORDER BY HIREDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RUNNINGTOTAL
2. FROM EMP
3. WHERE DEPTNO=30
4. GROUP BY HIREDATE ORDER BY HIREDATE;
Sample ex#4
-----------------------
Expand|Select|Wrap|Line Numbers
1. SELECT HIREDATE,SUM(SAL) SALARY,AVG(SUM(SAL)) OVER(ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ROLLINGAVERAGE
2. FROM EMP
3. WHERE DEPTNO=30
4. GROUP BY HIREDATE ORDER BY HIREDATE;
Sample ex#5
-----------------------
Expand|Select|Wrap|Line Numbers
1. SELECT HIREDATE,SUM(SAL) SALARY,AVG(SUM(SAL)) OVER(ORDER BY HIREDATE RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) ROLLINGAVERAGE
2. FROM EMP
3. GROUP BY HIREDATE ORDER BY HIREDATE;
Sample ex#6
--------------------
Expand|Select|Wrap|Line Numbers
1. SELECT HIREDATE,SUM(SAL) SALARY,AVG(SUM(SAL)) OVER(ORDER BY HIREDATE RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING) ROLLINGAVERAGE
2. FROM EMP
3. GROUP BY HIREDATE ORDER BY HIREDATE;
Sample ex#7
--------------------
Expand|Select|Wrap|Line Numbers
1. SELECT TRUNC(HIREDATE)DAY,SUM (SAL) SALARY,AVG(SUM(SAL)) OVER (ORDER BY TRUNC(HIREDATE)
2. RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND INTERVAL '2' DAY FOLLOWING) AVERAGE
3. FROM EMP
4. GROUP BY TRUNC(HIREDATE);
Also Check Windowing Function - 2
Jul 4 '07 #1
0 3771