AVG([ DISTINCT | ALL ] expr) [ OVER(analytic_clause) ]
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
WHERE MANAGER_ID BETWEEN 101 AND 108
ORDER BY MANAGER_ID, HIRE_DATE, SALARY;
MANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG
---------- ------------------------- ----------- ---------- ----------
101 Mavris 2002/6/7 6500.00 8250
101 Baer 2002/6/7 10000.00 9502.66666
101 Higgins 2002/6/7 12008.00 11338.6666
101 Greenberg 2002/8/17 12008.00 9472
101 Whalen 2003/9/17 4400.00 8204
102 Hunold 2006/1/3 9000.00 9000
103 Austin 2005/6/25 4800.00 4800
103 Pataballa 2006/2/5 4800.00 4600
103 Lorentz 2007/2/7 4200.00 5000
103 Ernst 2007/5/21 6000.00 5100
108 Faviet 2002/8/16 9000.00 8600
108 Chen 2005/9/28 8200.00 8300
108 Sciarra 2005/9/30 7700.00 7900
108 Urman 2006/3/7 7800.00 7466.66666
108 Popp 2007/12/7 6900.00 7350
15 rows selected