Category: aggregate

Any value

Returns any value from the input or NULL if there are zero input rows - learn more >​

• Return type: dynamic

• Fields:

  • expression (variable)

    • required: true

    • input type: variable

    • allowed type(s): any

  • partition

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: false

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

Average

Returns the average of non-null values - learn more >​

• Return type: float

• Fields:

  • expression (numeric variable)

    • required: true

    • input type: variable

    • allowed type(s): integer | float

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: false

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

Count

Returns the count of all non-null values - learn more >​

• Return type: integer

• Fields:

  • expression (variable)

    • required: true

    • input type: variable

    • allowed type(s): any

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • DISTINCT

    • required: false

    • input type: boolean

  • order_by (partition order (optional))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: false

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

Logical and

Returns the logical AND of all non-NULL expressions - learn more >​

• Return type: boolean

• Fields:

  • expression (boolean variable)

    • required: true

    • input type: variable

    • allowed type(s): boolean

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: false

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

Logical or

Returns the logical OR of all non-NULL expressions - learn more >​

• Return type: boolean

• Fields:

  • expression (boolean variable)

    • required: true

    • input type: variable

    • allowed type(s): boolean

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: false

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

Max

Returns the maximum value of all non-null inputs - learn more >​

• Return type: dynamic

• Fields:

  • expression (variable)

    • required: true

    • input type: variable

    • allowed type(s): any

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: false

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

Min

Returns the minimum value of all non-null inputs - learn more >​

• Return type: dynamic

• Fields:

  • expression (variable)

    • required: true

    • input type: variable

    • allowed type(s): any

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: false

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

String aggregate

Returns a value obtained by concatenating non-null values - learn more >​

• Return type: string

• Fields:

  • expression (string variable)

    • required: true

    • input type: variable

    • allowed type(s): string

  • delimiter (",")

    • required: false

    • input type: literal

    • allowed type(s): any

  • partition (partition variable(s) (optional))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: false

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

Sum

Returns the sum of all values, ignoring nulls - learn more >​

• Return type: dynamic

• Fields:

  • expression (numeric variable)

    • required: true

    • input type: variable

    • allowed type(s): integer | float

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: false

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

Category: navigation

First value

Returns the value of a variable for the first row in a given window frame. - learn more >​

• Return type: dynamic

• Fields:

  • expression (variable)

    • required: true

    • input type: variable

    • allowed type(s): any

  • ignore_null

    • required: true

    • input type: enum

    • allowed type(s): any

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: true

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: true

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

Lag

Returns the value of a variable on a preceding row - learn more >​

• Return type: dynamic

• Fields:

  • expression (variable)

    • required: true

    • input type: variable

    • allowed type(s): any

  • literal (offset)

    • required: true

    • input type: literal

    • allowed type(s): integer

  • default_value ((optional, defaults to NULL))

    • required: false

    • input type: literal

    • allowed type(s): any

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: true

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: true

    • input type: enum

    • allowed type(s): any

Last value

Returns the value of a variable for the last row in a given window frame. - learn more >​

• Return type: dynamic

• Fields:

  • expression (variable)

    • required: true

    • input type: variable

    • allowed type(s): any

  • ignore_null

    • required: true

    • input type: enum

    • allowed type(s): any

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: true

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: true

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

Lead

Returns the value of a variable on a subsequent row - learn more >​

• Return type: dynamic

• Fields:

  • expression (variable)

    • required: true

    • input type: variable

    • allowed type(s): any

  • literal (offset)

    • required: true

    • input type: literal

    • allowed type(s): integer

  • default_value ((optional, defaults to NULL))

    • required: false

    • input type: literal

    • allowed type(s): any

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: true

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: true

    • input type: enum

    • allowed type(s): any

Nth value

Returns the value at the Nth row of a given window frame. - learn more >​

• Return type: dynamic

• Fields:

  • expression (variable)

    • required: true

    • input type: variable

    • allowed type(s): any

  • literal (integer)

    • required: true

    • input type: literal

    • allowed type(s): integer

  • ignore_null

    • required: true

    • input type: enum

    • allowed type(s): any

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: true

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: true

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

Percentile (continuous)

Computes the specified percentile value for a variable within an ordered partition, with linear interpolation. - learn more >​

• Return type: float

• Fields:

  • expression (variable)

    • required: true

    • input type: variable

    • allowed type(s): integer | float

  • literal (percentile, between [0, 1])

    • required: true

    • input type: literal

    • allowed type(s): float

  • ignore_null

    • required: true

    • input type: enum

    • allowed type(s): any

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: true

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: true

    • input type: enum

    • allowed type(s): any

Percentile (discrete)

Computes the specified percentile value for a variable within an ordered partition. Returns the first sorted value with cumulative distribution greater than or equal to the percentile. - learn more >​

• Return type: float

• Fields:

  • expression (variable)

    • required: true

    • input type: variable

    • allowed type(s): integer | float

  • literal (percentile, between [0, 1])

    • required: true

    • input type: literal

    • allowed type(s): float

  • ignore_null

    • required: true

    • input type: enum

    • allowed type(s): any

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: true

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: true

    • input type: enum

    • allowed type(s): any

Category: numbering

Cumulative distribution

Return the relative rank of a row defined as NP/NR. NP is defined to be the number of rows that either precede or are peers with the current row. NR is the number of rows in the partition. - learn more >​

• Return type: float

• Fields:

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: true

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: true

    • input type: enum

    • allowed type(s): any

Dense rank

Returns the ordinal (1-based) rank of each row within the ordered partition. All peer rows receive the same rank value, and the subsequent rank value is incremented by one. - learn more >​

• Return type: integer

• Fields:

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: true

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: true

    • input type: enum

    • allowed type(s): any

N-tile

Divides the rows into a set number of buckets based on row ordering and returns the 1-based bucket number that is assigned to each row. - learn more >​

• Return type: integer

• Fields:

  • literal (integer)

    • required: true

    • input type: literal

    • allowed type(s): integer

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: true

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: true

    • input type: enum

    • allowed type(s): any

Percent rank

Return the percentile rank of a row defined as (RK-1)/(NR-1), where RK is the RANK of the row and NR is the number of rows in the partition. - learn more >​

• Return type: float

• Fields:

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: true

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: true

    • input type: enum

    • allowed type(s): any

Rank

Returns the ordinal (1-based) rank of each row within the ordered partition. All peer rows receive the same rank value. The next row or set of peer rows receives a rank value which increments by the number of peers with the previous rank value - learn more >​

• Return type: integer

• Fields:

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: true

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: true

    • input type: enum

    • allowed type(s): any

Row number

Returns the sequential row ordinal (1-based) of each row for each ordered partition. If the ORDER BY clause is unspecified then the result is non-deterministic. - learn more >​

• Return type: integer

• Fields:

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: false

    • input type: enum

    • allowed type(s): any

Category: statistical

Correlation

Returns the Pearson coefficient of correlation of a set of number pairs - learn more >​

• Return type: float

• Fields:

  • expression (dependent var)

    • required: true

    • input type: variable or literal

    • allowed type(s): float

  • expression2 (independent var)

    • required: true

    • input type: variable or literal

    • allowed type(s): float

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: false

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

Covariance

Returns the covariance of a set of number pairs - learn more >​

• Return type: float

• Fields:

  • sample_type

    • required: true

    • input type: enum

    • allowed type(s): any

  • expression (dependent var)

    • required: true

    • input type: variable or literal

    • allowed type(s): float

  • expression2 (independet var)

    • required: true

    • input type: variable or literal

    • allowed type(s): float

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: false

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

Standard deviation

Returns the standard deviation of all values - learn more >​

• Return type: float

• Fields:

  • sample_type

    • required: true

    • input type: enum

    • allowed type(s): any

  • expression (dependent var)

    • required: true

    • input type: variable or literal

    • allowed type(s): float

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: false

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

Variance

Returns the variance of all values - learn more >​

• Return type: float

• Fields:

  • sample_type

    • required: true

    • input type: enum

    • allowed type(s): any

  • expression (dependent var)

    • required: true

    • input type: variable or literal

    • allowed type(s): float

  • partition (partition variable(s))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_by (partition order (optional))

    • required: false

    • input type: variable

    • allowed type(s): any

  • order_direction (partition order direction)

    • required: false

    • input type: enum

    • allowed type(s): any

  • rows_preceding (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer

  • rows_following (integer, default "unbounded")

    • required: false

    • input type: literal

    • allowed type(s): integer