Topic: SQL*Plus and PL/SQL >> partition
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| mellow |
Posted: Dec 12, 2007 05:24:48 AM |
Total Post: 1
Joined: Dec, 2007
|
Hi
Some one please expalin me how this query working ?
SELECT manager_id, last_name, hire_date, salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
FROM employees;
Thanks in advance,
Sathish |
|
|
 Murtuja |
| Posted: Dec 12, 2007 07:15:15 AM |  |
|
Total Post: 857
Joined: Jan, 2006
|
Your query is example of Analytic Functions - Window Aggregate Functions
http://www.ss64.com/orasyntax/an_agg_window.html
http://www.orafaq.com/node/55
http://www.orafusion.com/art_anlytc.htm
|
|
|
|
|
| chinna |
| Posted: Dec 13, 2007 12:54:36 AM | |
|
Total Post: 28
Joined: May, 2007
|
hi,
consider this case
SELECT MGR, ENAME, hiredate, sal,
AVG(sal) OVER (PARTITION BY MGR ORDER BY hiredate) AS c_mavg ,
AVG(sal) OVER (PARTITION BY MGR ORDER BY hiredate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavgrows
FROM emp where mgr = 7698
EMPNO ENAME HIREDATE SAL c_mAVG c_mAVGROWS
7698 ALLEN 20-FEB-81 1600 1600 1425
7698 WARD 22-FEB-81 1250 1425(1425 IS THE AVG OF 1600 AND 1250) 1450 (AVG OF 1600,1250,1500 Since they mentioned 1 preceding and 1 folowing)
7698 TURNER 08-SEP-81 1500 1450(avg of 1600,1250,1500) 1333.33(avg of 1250,1500,1250)
similarly
7698 MARTIN 28-SEP-81 1250 1400 1233.33
7698 JAMES 03-DEC-81 950 1310 1100
thanks
chinna
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 333 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
