Window aggregate functions return a summary value representing a set of rows.
This syntax applies to the aggregate functions SUM, AVG, MIN, MAX, MEDIAN, FIRST_VALUE, LAST_VALUE, STDDEV, VARIANCE, COUNT, VAR_SAMP, VAR_POP, STDDEV_SAMP, STDDEV_POP, COVAR_SAMP, COVAR_POP, REGR_SLOPE, REGR_INTERCEPT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_SXX, REGR_SXY, REGR_SYY.
Each Window aggregate function can have an optional clause defining the size of the window, by default the window is unbounded i.e. the whole table/partition. This provides answers to questions such as "what is the average sale price over all time" or "What is the average sale price over the last 28 days"
Defining the Window size
The window (or the set of rows to be worked on) can be defined as a fixed number of rows, all preceeding or all following rows or it can be calculated based on comparing values (or time periods) in the current row with values in the ordered sequence. This definition is made with the ROWS or RANGE clause
Function([arguments]) OVER ([PARTITION BY value/expr] [ORDER BY expr [ASC|DESC] [ROWS | RANGE windowing_clause]]) windowing_clauses: INTERVAL 'nn' DAY PRECEDING INTERVAL 'nn' SECONDS FOLLOWING INTERVAL 'nn' MONTH PRECEDING BETWEEN x PRECEDING AND y FOLLOWING BETWEEN x PRECEDING AND y PRECEDING BETWEEN CURRENT ROW AND y FOLLOWING BETWEEN x PRECEDING AND CURRENT ROW BETWEEN x PRECEDING AND UNBOUNDED FOLLOWING BETWEEN UNBOUNDED PRECEDING AND y FOLLOWING column BETWEEN current.column +/- n AND current.column +/- m UNBOUNDED PRECEDING | FOLLOWING value/expr PRECEDING | FOLLOWING CURRENT ROW
For time intervals, the ORDER BY clause has to be a DATE column/expression.
If you omit the windowing_clause entirely, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
If ROWS is specified, it's a physical offset (the no. of rows in the window)
If RANGE is specified, it's a logical offset.
UNBOUNDED means the very first/last row of the partition, or if not partitioned, the first/last row of the dataset.
SELECT employee_name, hire_date, salary, AVG(salary) OVER (ORDER BY hire_date) RANGE INTERVAL '28' DAY PREDEEDING FROM employees; SELECT department_id, hire_date, salary, SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date ROWS UNBOUNDED PRECEEDING) As "Salary running sum" FROM employees ORDER BY department_id;
Analytic aggregate functions
This other aggregate functions listed at the top of the page all work in much the same way, FIRST_VALUE and LAST_VALUE are particularly useful when working with a data window.
The functions PERCENTILE_CONT and PERCENTILE_DISC are new in Oracle 9 and calculate reverse percentiles e.g. given the value 0.42 find the item that comes out at 42% in the ordered data set (_DISC = discrete or nearest value, _CONT=continuous using linear interpolation)
"In the long run the aggregate of decisions in individual businessmen, exercising individual judgment in a free economy, even if often mistaken, is less likely to do harm than the centralized decisions of a government" ~ P. J. O'Rourke (Eat the Rich)
Oracle SQL Functions
© Copyright SS64.com 1999-2013
Some rights reserved