Links

Partitioned variable methods

Category: Aggregate

Any value

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

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
AVG(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])

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
COUNT([@DISTINCT ]@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])

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
LOGICAL_AND(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])

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
LOGICAL_OR(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])

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
MAX(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])

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
MIN(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])

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
STRING_AGG(@expression[, @delimiter]) OVER ([PARTITION BY @partition ][ORDER BY @order_by][ @order_direction])

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
SUM(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])

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
ST_CLUSTERDBSCAN(@geography, @epsilon, @minimum_geographies) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction])

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
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)

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
LAG(@expression, @literal[, @default_value]) OVER ([PARTITION BY @partition ]ORDER BY @order_by @order_direction)

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
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)

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
LEAD(@expression, @literal[, @default_value]) OVER ([PARTITION BY @partition ]ORDER BY @order_by @order_direction)

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
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)

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

Return type

float

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable
any integer, float
true
(variable)