group by (The group by can be omitted if the group is the entire selected result). having clause and thus also after the group by clause. | 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 |
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. 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 ) …
ORDER BY clause is specified. ORDER BY clause, it is OVER ( … RANGE BETWEEN UNBOUNDED PRECEDING and CURRENT ROW )
ORDER BY clause, it is OVER ( … ROWS BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING )
where or having clauses, but H2 comes with a qualify clause where they're allowed, see modern SQL. lag and lead (feature T615)
nth_value (feature T618)
first_value and last_value (feature T617) 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.