Partitioned variable methods

Category: Aggregate

Any value

Returns any value from the input or NULL if there are zero input rows –> learn more
1
ANY_VALUE(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
Copied!

Return type

dynamic (input-dependent)

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
true
(variable)
@partition
false
-
@order_by
variable
false
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
false
(partition order direction)
@rows_preceding
literal
any integer
false
(integer, default "unbounded")
@rows_following
literal
any integer
false
(integer, default "unbounded")

Average

Returns the average of non-null values –> learn more
1
AVG(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
Copied!

Return type

float

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
any integer, float
true
(numeric variable)
@partition
false
(partition variable(s))
@order_by
variable
false
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
false
(partition order direction)
@rows_preceding
literal
any integer
false
(integer, default "unbounded")
@rows_following
literal
any integer
false
(integer, default "unbounded")

Count

Returns the count of all non-null values –> learn more
1
COUNT([@DISTINCT ]@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
Copied!

Return type

integer

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
true
(variable)
@partition
false
(partition variable(s))
@DISTINCT
boolean
any boolean
false
-
@order_by
variable
false
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
false
(partition order direction)
@rows_preceding
literal
any integer
false
(integer, default "unbounded")
@rows_following
literal
any integer
false
(integer, default "unbounded")

Logical and

Returns the logical AND of all non-NULL expressions –> learn more
1
LOGICAL_AND(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
Copied!

Return type

boolean

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
any boolean
true
(boolean variable)
@partition
false
(partition variable(s))
@order_by
variable
false
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
false
(partition order direction)
@rows_preceding
literal
any integer
false
(integer, default "unbounded")
@rows_following
literal
any integer
false
(integer, default "unbounded")

Logical or

Returns the logical OR of all non-NULL expressions –> learn more
1
LOGICAL_OR(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
Copied!

Return type

boolean

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
any boolean
true
(boolean variable)
@partition
false
(partition variable(s))
@order_by
variable
false
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
false
(partition order direction)
@rows_preceding
literal
any integer
false
(integer, default "unbounded")
@rows_following
literal
any integer
false
(integer, default "unbounded")

Max

Returns the maximum value of all non-null inputs –> learn more
1
MAX(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
Copied!

Return type

dynamic (input-dependent)

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
true
(variable)
@partition
false
(partition variable(s))
@order_by
variable
false
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
false
(partition order direction)
@rows_preceding
literal
any integer
false
(integer, default "unbounded")
@rows_following
literal
any integer
false
(integer, default "unbounded")

Min

Returns the minimum value of all non-null inputs –> learn more
1
MIN(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
Copied!

Return type

dynamic (input-dependent)

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
true
(variable)
@partition
false
(partition variable(s))
@order_by
variable
false
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
false
(partition order direction)
@rows_preceding
literal
any integer
false
(integer, default "unbounded")
@rows_following
literal
any integer
false
(integer, default "unbounded")

String aggregate

Returns a value obtained by concatenating non-null values –> learn more
1
STRING_AGG(@expression[, @delimiter]) OVER ([PARTITION BY @partition ][ORDER BY @order_by][ @order_direction])
Copied!

Return type

string

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
any string
true
(string variable)
@delimiter
literal
false
(",")
@partition
false
(partition variable(s) (optional))
@order_by
variable
false
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
false
(partition order direction)
@rows_preceding
literal
any integer
false
(integer, default "unbounded")
@rows_following
literal
any integer
false
(integer, default "unbounded")

Sum

Returns the sum of all values, ignoring nulls –> learn more
1
SUM(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
Copied!

Return type

dynamic (input-dependent)

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
any integer, float
true
(numeric variable)
@partition
false
(partition variable(s))
@order_by
variable
false
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
false
(partition order direction)
@rows_preceding
literal
any integer
false
(integer, default "unbounded")
@rows_following
literal
any integer
false
(integer, default "unbounded")

Category: Geography

DBSCAN clustering

Performs DBSCAN clustering on a column of geographies. Returns a 0-based cluster number. –> learn more
1
ST_CLUSTERDBSCAN(@geography, @epsilon, @minimum_geographies) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction])
Copied!

Return type

integer

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@geography
variable
any geography
true
(geography variable)
@epsilon
literal
any integer, float
true
(meters)
@minimum_geographies
literal
any integer
true
(count)
@partition
false
-
@order_by
variable
false
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
false
(partition order direction)

Category: Navigation

First value

Returns the value of a variable for the first row in a given window frame. –> learn more
1
FIRST_VALUE(@expression @ignore_null NULLS) OVER ([PARTITION BY @partition ]ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING)
Copied!

Return type

dynamic (input-dependent)

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
true
(variable)
@ignore_null
enum
any of: IGNORE, RESPECT
true
-
@partition
false
(partition variable(s))
@order_by
variable
true
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
true
(partition order direction)
@rows_preceding
literal
any integer
false
(integer, default "unbounded")
@rows_following
literal
any integer
false
(integer, default "unbounded")

Lag

Returns the value of a variable on a preceding row –> learn more
1
LAG(@expression, @literal[, @default_value]) OVER ([PARTITION BY @partition ]ORDER BY @order_by @order_direction)
Copied!

Return type

dynamic (input-dependent)

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
true
(variable)
@literal
literal
any integer
true
(offset)
@default_value
literal
false
((optional, defaults to NULL))
@partition
false
(partition variable(s))
@order_by
variable
true
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
true
(partition order direction)

Last value

Returns the value of a variable for the last row in a given window frame. –> learn more
1
LAST_VALUE(@expression @ignore_null NULLS) OVER ([PARTITION BY @partition ]ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING)
Copied!

Return type

dynamic (input-dependent)

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
true
(variable)
@ignore_null
enum
any of: IGNORE, RESPECT
true
-
@partition
false
(partition variable(s))
@order_by
variable
true
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
true
(partition order direction)
@rows_preceding
literal
any integer
false
(integer, default "unbounded")
@rows_following
literal
any integer
false
(integer, default "unbounded")

Lead

Returns the value of a variable on a subsequent row –> learn more
1
LEAD(@expression, @literal[, @default_value]) OVER ([PARTITION BY @partition ]ORDER BY @order_by @order_direction)
Copied!

Return type

dynamic (input-dependent)

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
true
(variable)
@literal
literal
any integer
true
(offset)
@default_value
literal
false
((optional, defaults to NULL))
@partition
false
(partition variable(s))
@order_by
variable
true
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
true
(partition order direction)

Nth value

Returns the value at the Nth row of a given window frame. –> learn more
1
NTH_VALUE(@expression, @literal @ignore_null NULLS) OVER ([PARTITION BY @partition ]ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING)
Copied!

Return type

dynamic (input-dependent)

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
true
(variable)
@literal
literal
any integer
true
(integer)
@ignore_null
enum
any of: IGNORE, RESPECT
true
-
@partition
false
(partition variable(s))
@order_by
variable
true
(partition order)
@order_direction
enum
any of: DESC, ASC
true
(partition order direction)
@rows_preceding
literal
any integer
false
(integer, default "unbounded")
@rows_following
literal
any integer
false
(integer, default "unbounded")

Percentile (continuous)

Computes the specified percentile value for a variable within an ordered partition, with linear interpolation. –> learn more
1
PERCENTILE_CONT(@expression, @literal @ignore_null NULLS) OVER ([PARTITION BY @partition ])
Copied!

Return type

float

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
any integer, float
true
(variable)
@literal
literal
any float
true
(percentile, between [0, 1])
@ignore_null
enum
any of: IGNORE, RESPECT
true
-
@partition
false
(partition variable(s))

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
1
PERCENTILE_DISC(@expression, @literal @ignore_null NULLS) OVER ([PARTITION BY @partition ])
Copied!

Return type

float

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
any integer, float
true
(variable)
@literal
literal
any float
true
(percentile, between [0, 1])
@ignore_null
enum
any of: IGNORE, RESPECT
true
-
@partition
false
(partition variable(s))

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
1
CUME_DIST() OVER ([PARTITION BY @partition ]ORDER BY @order_by @order_direction)
Copied!

Return type

float

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@partition
false
(partition variable(s))
@order_by
variable
true
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
true
(partition order direction)

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
1
DENSE_RANK() OVER ([PARTITION BY @partition ]ORDER BY @order_by @order_direction)
Copied!

Return type

integer

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@partition
false
(partition variable(s))
@order_by
variable
true
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
true
(partition order direction)

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
1
NTILE(@literal) OVER ([PARTITION BY @partition ]ORDER BY @order_by @order_direction)
Copied!

Return type

integer

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@literal
literal
any integer
true
(integer)
@partition
false
(partition variable(s))
@order_by
variable
true
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
true
(partition order direction)

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
1
PERCENT_RANK() OVER ([PARTITION BY @partition ]ORDER BY @order_by @order_direction)
Copied!

Return type

float

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@partition
false
(partition variable(s))
@order_by
variable
true
(partition order (optional))
@order_direction
enum
any of: DESC, ASC
true
(partition order direction)

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
1
RANK() OVER ([PARTITION BY @partition ]ORDER BY @order_by @order_direction)
Copied!

Return type

integer

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@partition
false
(partition variable(s))
@order_by
variable
true
(partition order (optional))
@order_direction
enum