Value expressions are used in a variety of contexts, such as in the target list of the SELECT
command, as new column values in INSERT
or UPDATE
, or in search conditions in a number of commands.
aggregate_name
is one of the aggregation functions listed on Aggregate functions, and expression
is a value expression that does not contain an aggregate expression or a window function call.
The DISTINCT
keyword, which is only available in the second form, cannot be used together with an ORDER BY
or WITHIN GROUP
clause. Additionally, it’s important to note that the order_by_clause
is positioned differently in the first and fourth forms.
aggregate_name
can also be in the following form:
AGGREGATE:
prefix converts a builtin array function (e.g. array_sum
) or an user-defined function, to an aggregate function. The function being converted must accept exactly one argument of an array type. After the conversion, a function like foo ( array of T ) -> U
becomes an aggregate function like AGGREGATE:foo ( T ) -> U
.
OVER
clause, which generally consists of three parts:
PARTITION BY
clause): Specifies how to partition rows into smaller sets.ORDER BY
clause): Specifies how the rows are ordered. This part is required for ranking functions.ROWS
, RANGE
or SESSION
clause): Specifies a particular row or the range of the rows over which calculations are performed.WINDOW
clause:
PARTITION BY
clause is required. If you do not want to partition the rows into smaller sets, you can work around by specifying PARTITION BY 1::int
.row_number
, rank
and dense_rank
, ORDER BY
clause is required.
When operating in the Emit on window close mode for a streaming query, ORDER BY
clause is required for all window functions. Please ensure that you specify exactly one column in the ORDER BY
clause. This column, generally a timestamp column, must have a watermark defined for it. It’s important to note that when using the timestamp column from this streaming query in another streaming query, the watermark information associated with the column is not retained.
window_function_name
is one of the window functions listed on Window functions.
frame_clause
can be one of:
ROWS
or RANGE
frame, frame_start
and frame_end
can be:
frame_start
is specified, CURRENT ROW
will be used as the end of the window.
The requirements of offset
vary in different frames. In ROWS
frame, the offset
should be a positive constant integer indicating the number of rows before or after the current row. While RANGE
frame requires the ORDER BY
clause to specify exactly one column, and the offset
expression to be a positive constant of a data type that is determined by the data type of the ordering column. For example, if the ordering column is timestamptz
, the offset
expression should be an positive constant interval
.
For SESSION
frame, the requirements of gap
are very similar to those of offset
for RANGE
frame. The ORDER BY
clause should specify exactly one column and the gap
expression should be a positive constant of a data type that is determined by the data type of the ordering column.
SESSION
frame is only supported in batch mode and Emit-On-Window-Close streaming mode.frame_exclusion
can be either of these:
frame_clause
is optional. Depending on whether the ORDER BY
clause is present, the default value is different. When the ORDER BY
clause is present, the default value is ROWS UNBOUNDED PRECEDING AND CURRENT ROW
. When the ORDER BY
clause is not present, the default value is ROWS UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
. This is different from the behavior in PostgreSQL. We may align the default frame with PostgreSQL in the future.Parameter | Description |
---|---|
expression | The expression of which the data type to be converted. |
type | The data type of the returned value. For the types you can cast the value to, see Casting. |
t
in the form of row values (,)
.
ARRAY
cannot be omitted. The following statement cannot be parsed.