HAVING
clause but before the ORDER BY
clause.
Invoking a window function requires special syntax using the OVER
clause to specify the window.
For example, the following query ranks orders for each clerk by price:
WINDOW
clause,WINDOW
clause.OVER
clause. The aggregate function is computed for each row over the rows within the
current row’s window frame. Note that ordering during
aggregation is not supported.
For example, the following query produces a rolling sum of order prices
by day for each clerk:
n
buckets ranging
from 1
to at most n
. Bucket values will differ by at most 1
.
If the number of rows in the partition does not divide evenly into the
number of buckets, then the remainder values are distributed one per
bucket, starting with the first bucket.
For example, with 6
rows and 4
buckets, the bucket values would
be as follows: 1
1
2
2
3
4
(r - 1) / (n - 1)
where r
is the rank() of the row and
n
is the total number of rows in the window partition.
IGNORE NULLS
is specified, all rows where
x
is null are excluded from the calculation. If IGNORE NULLS
is specified and x
is null for all rows, the default_value
is returned, or if it is not specified,
null
is returned.
1
. The offset can be any scalar
expression. If the offset is null or greater than the number of values in
the window, null
is returned. It is an error for the offset to be zero or
negative.
offset
rows after the current row in the window partition.
Offsets start at 0
, which is the current row. The
offset can be any scalar expression. The default offset
is 1
. If the
offset is null, an error is raised. If the offset refers to a row that is not
within the partition, the default_value
is returned, or if it is not specified
null
is returned.
The lead
function requires that the window ordering be specified.
Window frame must not be specified.
offset
rows before the current row in the window partition.
Offsets start at 0
, which is the current row. The
offset can be any scalar expression. The default offset
is 1
. If the
offset is null, an error is raised. If the offset refers to a row that is not
within the partition, the default_value
is returned, or if it is not specified
null
is returned.
The lag
function requires that the window ordering be specified.
Window frame must not be specified.