469,082 Members | 1,156 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Windowing Function - 1

debasisdas
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 3660

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by domeceo | last post: by
5 posts views Thread by phil_gg04 | last post: by
2 posts views Thread by laredotornado | last post: by
3 posts views Thread by jefftyzzer | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.