Window Expressions

SQL provides a method to aggregate over a series of related tuples. They are called window functions and always come with an OVER clause which determines how tuples are split up over the window functions.

The PARTITION BY clause within OVER divides the rows into groups that share the same values of the PARTITION BY expression(s). For each row, the window function is computed over all rows participating in the group. The order within a partition can be used as well.

window_function_spec:
    window_function OVER  { window_name | '(' window_specification ')' }

window_function:
      RANK '(' ')'
    | DENSE_RANK '(' ')'
    | PERCENT_RANK '(' ')'
    | ROW_NUMBER '(' ')'
    | CUME_DIST '(' ')'
    | FIRST_VALUE '(' query_expression ')'
    | LAST_VALUE '(' query_expression ')'
    | NTH_VALUE '(' query_expression ',' numeric_expression ')'
    | NTILE '(' query_expression ')'
    | LEAD '(' query_expression [ ',' query_expression [ ',' query_expression ] ] ')'
    | LAG '(' query_expression [ ',' query_expression [ ',' query_expression ] ] ')'
    | aggregate_function

aggregate_function:
      AVG '(' query_expression ')'
    | COUNT '(' '*' ')'
    | COUNT '(' [ DISTINCT ] query_expression ')'
    | MAX '(' query_expression ')'
    | MIN '(' query_expression ')'
    | PROD '(' [ DISTINCT ] query_expression ')'
    | SUM '(' [ DISTINCT ] query_expression ')'
    | corr '(' numeric_a, numeric_b ')'
    | covar_pop '(' numeric_expr, numeric_expr ')'
    | covar_samp '(' numeric_expr, numeric_expr ')'
    | stddev_pop '(' numeric_expr ')'
    | stddev_samp '(' numeric_expr ')'
    | var_pop '(' numeric_expr ')'
    | var_samp '(' numeric_expr ')'
    | group_concat '(' col_expr clob ')'
    | group_concat '(' col_expr clob, separator clob ')'
    | listagg '(' col_expr varchar ')'
    | listagg '(' col_expr varchar, separator varchar ')'

window_specification:
    [ window_name ]   [ PARTITION BY column_ref   [ ',' ... ] ]   [ ORDER BY sort_spec ]   [ window_frame ]

window_frame:
    { ROWS | RANGE | GROUPS }   { window_frame_start | BETWEEN window_bound AND window_bound }
        [ EXCLUDE { CURRENT ROW | GROUP | TIES | NO OTHERS }   ]

window_frame_start:
     UNBOUNDED PRECEDING
   | value PRECEDING
   | CURRENT ROW

window_bound:
    window_frame_start
   | UNBOUNDED FOLLOWING
   | value FOLLOWING

The supported window frames are:

ROWS   - Frames are calculated on physical offsets of input rows.
RANGE  - Result frames are calculated on value differences from input rows
         (used with a custom PRECEDING or FOLLOWING bound requires an ORDER BY clause).
GROUPS - Groups of equal row values are used to calculate result frames (requires an ORDER BY clause).

See Window Functions for information on each window function.

Most Aggregate Functions can also be used as window function.

See also Extended SQL:2011 Window Functions in MonetDB.

Examples

create table ranktest (id int, k varchar(3));
insert into ranktest values (1, 'a'),(2, 'b'),(3, 'c'),(4, 'd');
insert into ranktest values (1, 'a'),(2, 'b'),(3, 'c'),(4, 'd');

select ROW_NUMBER() OVER () as foo from ranktest;
select ROW_NUMBER() OVER (PARTITION BY id) as foo, id from ranktest;
select ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) as foo, id from ranktest;
select ROW_NUMBER() OVER (ORDER BY id) as foo, id from ranktest;

select RANK() OVER () as foo from ranktest;
select RANK() OVER (PARTITION BY id) as foo, id from ranktest;
select RANK() OVER (PARTITION BY id ORDER BY id) as foo, id from ranktest;
select RANK() OVER (ORDER BY id) as foo, id from ranktest;

select RANK() OVER () as foo, id, k from ranktest;
select RANK() OVER (PARTITION BY id) as foo, id, k from ranktest;
select RANK() OVER (PARTITION BY id ORDER BY id, k) as foo, id, k from ranktest;
select RANK() OVER (ORDER BY id, k) as foo, id, k from ranktest;

select DENSE_RANK() OVER () as foo, id, k from ranktest order by k;
select DENSE_RANK() OVER (PARTITION BY id) as foo, id, k from ranktest order by k;
select DENSE_RANK() OVER (PARTITION BY id ORDER BY id, k) as foo, id, k from ranktest order by k;
select DENSE_RANK() OVER (ORDER BY id, k) as foo, id, k from ranktest order by k;

drop table ranktest;

More examples can be found at Wikipedia Window function.