The OVER clause specifies the partitioning, ordering and window "over which" the analytic function operates.
Example #1: calculate a moving average
AVG(amt) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
date amt avg_amt
===== ==== =======
1-Jan 10.0 10.5
2-Jan 11.0 17.0
3-Jan 30.0 17.0
4-Jan 10.0 18.0
5-Jan 14.0 12.0
It operates over a moving window (3 rows wide) over the rows, ordered by date.
Example #2: calculate a running balance
SUM(amt) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
date amt sum_amt
===== ==== =======
1-Jan 10.0 10.0
2-Jan 11.0 21.0
3-Jan 30.0 51.0
4-Jan 10.0 61.0
5-Jan 14.0 75.0
It operates over a window that includes the current row and all prior rows.
Note: for an aggregate with an OVER clause specifying a sort ORDER, the default window is UNBOUNDED PRECEDING to CURRENT ROW, so the above expression may be simplified to, with the same result:
SUM(amt) OVER (ORDER BY date)
Example #3: calculate the maximum within each group
MAX(amt) OVER (PARTITION BY dept)
dept amt max_amt
==== ==== =======
ACCT 5.0 7.0
ACCT 7.0 7.0
ACCT 6.0 7.0
MRKT 10.0 11.0
MRKT 11.0 11.0
SLES 2.0 2.0
It operates over a window that includes all rows for a particular dept.
SQL Fiddle: http://sqlfiddle.com/#!4/9eecb7d/122