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

Syntax:

Function([arguments]) OVER ([PARTITION BY value/expr] [ORDER BY expr [ASC|DESC] [ROWS | RANGEwindowing_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.

Examples:

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)

Related

Analytic features

Oracle SQL Functions

Copyright © SS64.com 1999-2019

Some rights reserved

Some rights reserved