OVER clause in Oracle

Question

What is the meaning of the OVER clause in Oracle

User · Answer

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.

User · Answer

It s part of the Oracle analytic functions

User · Answer

Another way to use OVER is to have a result column in your select operate on another  quot partition quot   so to say  This  SELECT      name       ssn       case        when   count    over  partition by ssn     gt  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  quot tags quot  for data for different error reports and such

User · Answer

The OVER clause specifies the partitioning  ordering and window  quot over which quot  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

[sql] OVER clause in Oracle

Examples related to sql

Examples related to oracle

Examples related to window-functions