SQL: Analytic functions

This is different from an aggregate function in that the aggregate returns one value per group and that the aggregate function is tied to a group by (The group by can be omitted if the group is the entire selected result).

Types of analytic functions

Analytic functions can roughly be divided into the following types

Ranking	Calulate the rank, percentile or n-tile of a record in respect to its partition,
Windowing	Cumulative and moveing averages (rolling totals). Applies to `sum`, `avg`, `count`, `variance` and `stddev`, `first_value` and `last_value`.
Reporting
lag / lead	Used if one record needs to access values of another record
first / last	First or last value of an ordered partition
Linear regeression	Slope, intercept etc.
Inversive percentile	The value in a partition the belongs to a given percentile
Hypothetical rank and distribution	The rank or percentile that a value would have in a given set of values

(Some) analytic functions

Some analytic functions are:

ntile to partition a result set into n groups.
percentile_disc and percentile_disc to calculate the nth percentile of a value within a group.
lag and lead to allow records of the result set to access values in other records.

Defining Window into which a row belongs

OVER ( … ROWS  BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
OVER ( … ROWS  BETWEEN n         PRECEDING AND m         FOLLOWING )
OVER ( … ROWS  BETWEEN n         PRECEDING AND CURRENT ROW         )
…

OVER ( … RANGE BETWEEN           n        PRECEDING AND           m        FOLLOWING )
OVER ( … RANGE BETWEEN INTERVAL 'n' MONTH PRECEDING AND INTERVAL 'm' MONTH FOLLOWING )
OVER ( … RANGE BETWEEN UNBOUNDED PRECEDING          AND CURRENT ROW                  )
…

Default window

The default window is dependent on if the ORDER BY clause is specified.

With an ORDER BY clause, it is

OVER ( … RANGE BETWEEN UNBOUNDED PRECEDING and CURRENT ROW         )

Without an ORDER BY clause, it is

OVER ( … ROWS  BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING )

QUALIFY clause

Analytic functions are not permitted in where or having clauses, but H2 comes with a qualify clause where they're allowed, see modern SQL.

Navigating within a window

Functions to navigate within a window are

lag and lead (feature T615)
nth_value (feature T618)
first_value and last_value (feature T617)

While lag and lead allow to access a specific row relative to the current row, nth_value(n) provides access to to the nth row relative to the first or last row in a window.

first_value and last_value are really just a special case of nth_value(n) where n is 1.