Home » SQL & PL/SQL » SQL & PL/SQL » ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING (Oracle 9.2.0.1)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING [message #430769] Thu, 12 November 2009 05:21 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Quote:

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING


Can anyone give me a simple example to understand what does the
above quoted lines do in Analytical function?


I read the below link but unable to understand how the values are calculated for the 3rd column in the resultset from the below link

http://www.java2s.com/Code/Oracle/Analytical-Functions/AvgoverROWSBETWEEN1PRECEDINGAND1FOLLOWING.htm

Thanks for the help!


Link modified


[Updated on: Thu, 12 November 2009 05:30]

Report message to a moderator

Re: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING [message #430774 is a reply to message #430769] Thu, 12 November 2009 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The link you provided contains such an example so what do you want more?

This means Oracle take the row preceding the current, the current row and the row following the current row and apply the function.

Regards
Michel

[Updated on: Thu, 12 November 2009 05:43]

Report message to a moderator

Re: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING [message #430778 is a reply to message #430769] Thu, 12 November 2009 05:38 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Analytic Example

The following example calculates, for each employee in the employees table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee:

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;

MANAGER_ID LAST_NAME                 HIRE_DATE     SALARY     C_MAVG
---------- ------------------------- --------- ---------- ----------
       100 Kochhar                   21-SEP-89      17000      17000
       100 De Haan                   13-JAN-93      17000      15000
       100 Raphaely                  07-DEC-94      11000 11966.6667
       100 Kaufling                  01-MAY-95       7900 10633.3333
       100 Hartstein                 17-FEB-96      13000 9633.33333
       100 Weiss                     18-JUL-96       8000 11666.6667
       100 Russell                   01-OCT-96      14000 11833.3333

http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/functions010.htm#SQLRF00609
SQL> SELECT x, y,
  2    AVG(y) OVER(ORDER BY x
  3      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ma
  4  FROM TestTable
  5  ORDER BY x;

         X          Y         MA
---------- ---------- ----------
         1          7          4
         2          1 3.33333333
         3          2 2.66666667
         4          5 4.66666667
         5          7 15.3333333
         6         34 24.3333333
         7         32 36.3333333
         8         43         54
         9         87         54
        10         32 43.6666667
        11         12         20
        12         16 30.3333333
        13         63         51
        14         74 57.6666667
        15         36 55.3333333
        16         56 31.3333333
        17          2         29

17 rows selected.


In the example at the link if you look at X=1
then MA=(1+7)/2 = 4
similarily for X=2
MA=(7+1+2)/3=10/3=3.33333
so it means
Michel said
This means Oracle take the row preceding the current, the current row and the row following the current row and apply the function.

[Updated on: Thu, 12 November 2009 05:44]

Report message to a moderator

Re: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING [message #430784 is a reply to message #430778] Thu, 12 November 2009 05:51 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Quote:

In the example at the link if you look at X=1
then MA=(1+7)/2 = 4
similarily for X=2
MA=(7+1+2)/3=10/3=3.33333

Understood but how u are getting 4.66666667,15.3333333


Re: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING [message #430786 is a reply to message #430784] Thu, 12 November 2009 05:53 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
(2+5+7)/3 = 4.66666667,
(5+7+34)/3=15.3333333
Quote:
range just before through just after

[Updated on: Thu, 12 November 2009 05:54]

Report message to a moderator

Previous Topic: Drop table issue
Next Topic: Not a valid month
Goto Forum:
  


Current Time: Sun Dec 04 02:56:31 CST 2016

Total time taken to generate the page: 0.08400 seconds