Partitioned variables

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