84

What is the meaning of the OVER clause in Oracle?

paweloque
  • 18,466
  • 26
  • 80
  • 136
  • If in doubt, read the manual: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174 –  Sep 07 '15 at 07:33
  • oh god no one likes the Oracle manual. Stackoverflow for the ELI5 explanation everytime! – dangel Dec 13 '19 at 15:42

4 Answers4

95

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

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • 1
    Could you explain how OVER() works when used without argument? – Yasen Apr 08 '14 at 11:09
  • 3
    No argument means it ranges over the entire result set. – Jeffrey Kemp Apr 08 '14 at 11:15
  • Why in your first example does it calculate the previous from the first row as zero? Is that something you can change? – k9b Jun 30 '15 at 00:15
  • Hi k9b, it says "1 PRECEDING AND 1 FOLLOWING" which is a window spanning up to 3 records - but the window never implies that there MUST be 3 records. It finds only two records, and calculates the average over those two records. Note: there was a copy-and-paste error which I'll fix. – Jeffrey Kemp Jun 30 '15 at 03:47
34

You can use it to transform some aggregate functions into analytic:

SELECT  MAX(date)
FROM    mytable

will return 1 row with a single maximum,

SELECT  MAX(date) OVER (ORDER BY id)
FROM    mytable

will return all rows with a running maximum.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 2
    You make a very good point about transforming aggregate functions into analytic ones. That's one way I never thought about it. – user5670895 Mar 18 '16 at 12:53
20

It's part of the Oracle analytic functions.

cletus
  • 616,129
  • 168
  • 910
  • 942
0

Another way to use OVER is to have a result column in your select operate on another "partition", so to say.

This:

SELECT 
    name, 
    ssn, 
    case 
      when ( count(*) over (partition by ssn) ) > 1      
      then 1
      else 0
    end AS hasDuplicateSsn
FROM table;

returns 1 in hasDuplicateSsn for each row whose ssn is shared by another row. Great for making "tags" for data for different error reports and such.

stobix
  • 112
  • 2