Partitioned variable methods

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