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,WINDOW
clause.aggregate
can be used as window functions by adding the OVER
clause. The aggregate function is
computed for each row over the rows within the current row’s window
frame.
For example, the following query produces a rolling sum of order prices
by day for each clerk:
rank
, except that tie values do not produce gaps in the sequence.
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, null
is returned. 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, null
is returned. 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.