Partitioned variable methods
ANY_VALUE(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
dynamic (input-dependent)
Name | Type | Allowed values | Required | Placeholder (in UI) |
---|---|---|---|---|
@expression | true | (variable) | ||
@partition | false | - | ||
@order_by | false | (partition order (optional)) | ||
@order_direction | any of: DESC , ASC | false | (partition order direction) | |
@rows_preceding | false | (integer, default "unbounded") | ||
@rows_following | false | (integer, default "unbounded") |
AVG(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
float
Name | Type | Allowed values | Required | Placeholder (in UI) |
---|---|---|---|---|
@expression | true | (numeric variable) | ||
@partition | false | (partition variable(s)) | ||
@order_by | false | (partition order (optional)) | ||
@order_direction | any of: DESC , ASC | false | (partition order direction) | |
@rows_preceding | false | (integer, default "unbounded") | ||
@rows_following | false | (integer, default "unbounded") |
COUNT([@DISTINCT ]@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
integer
Name | Type | Allowed values | Required | Placeholder (in UI) |
---|---|---|---|---|
@expression | true | (variable) | ||
@partition | false | (partition variable(s)) | ||
@DISTINCT | false | - | ||
@order_by | false | (partition order (optional)) | ||
@order_direction | any of: DESC , ASC | false | (partition order direction) | |
@rows_preceding | false | (integer, default "unbounded") | ||
@rows_following | false | (integer, default "unbounded") |
LOGICAL_AND(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
boolean
Name | Type | Allowed values | Required | Placeholder (in UI) |
---|---|---|---|---|
@expression | true | (boolean variable) | ||
@partition | false | (partition variable(s)) | ||
@order_by | false | (partition order (optional)) | ||
@order_direction | any of: DESC , ASC | false | (partition order direction) | |
@rows_preceding | false | (integer, default "unbounded") | ||
@rows_following | false | (integer, default "unbounded") |
LOGICAL_OR(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
boolean
Name | Type | Allowed values | Required | Placeholder (in UI) |
---|---|---|---|---|
@expression | true | (boolean variable) | ||
@partition | false | (partition variable(s)) | ||
@order_by | false | (partition order (optional)) | ||
@order_direction | any of: DESC , ASC | false | (partition order direction) | |
@rows_preceding | false | (integer, default "unbounded") | ||
@rows_following | false | (integer, default "unbounded") |
MAX(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
dynamic (input-dependent)
Name | Type | Allowed values | Required | Placeholder (in UI) |
---|---|---|---|---|
@expression | true | (variable) | ||
@partition | false | (partition variable(s)) | ||
@order_by | false | (partition order (optional)) | ||
@order_direction | any of: DESC , ASC | false | (partition order direction) | |
@rows_preceding | false | (integer, default "unbounded") | ||
@rows_following | false | (integer, default "unbounded") |
MIN(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
dynamic (input-dependent)
Name | Type | Allowed values | Required | Placeholder (in UI) |
---|---|---|---|---|
@expression | true | (variable) | ||
@partition | false | (partition variable(s)) | ||
@order_by | false | (partition order (optional)) | ||
@order_direction | any of: DESC , ASC | false | (partition order direction) | |
@rows_preceding | false | (integer, default "unbounded") | ||
@rows_following | false | (integer, default "unbounded") |
STRING_AGG(@expression[, @delimiter]) OVER ([PARTITION BY @partition ][ORDER BY @order_by][ @order_direction])
string
Name | Type | Allowed values | Required | Placeholder (in UI) |
---|---|---|---|---|
@expression | true | (string variable) | ||
@delimiter | false | (",") | ||
@partition | false | (partition variable(s) (optional)) | ||
@order_by | false | (partition order (optional)) | ||
@order_direction | any of: DESC , ASC | false | (partition order direction) | |
@rows_preceding | false | (integer, default "unbounded") | ||
@rows_following | false | (integer, default "unbounded") |
SUM(@expression) OVER ([PARTITION BY @partition][ ORDER BY @order_by @order_direction ROWS BETWEEN @rows_preceding|UNBOUNDED PRECEDING AND @rows_following|UNBOUNDED FOLLOWING])
dynamic (input-dependent)
Name | Type | Allowed values | Required | Placeholder (in UI) |
---|---|---|---|---|
@expression | true | (numeric variable) | ||
@partition | false | (partition variable(s)) | ||
@order_by | false | (partition order (optional)) | ||
@order_direction | any of: DESC , ASC | false | (partition order direction) | |
@rows_preceding | false | (integer, default "unbounded") | ||
@rows_following | false | (integer, default "unbounded") |
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])
integer
Name | Type | Allowed values | Required | Placeholder (in UI) |
---|---|---|---|---|
@geography | true | (geography variable) | ||
@epsilon | true | (meters) | ||
@minimum_geographies | true | (count) | ||
@partition | false | - | ||
@order_by | false | (partition order (optional)) | ||
@order_direction | any of: DESC , ASC | false | (partition order direction) |
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)
dynamic (input-dependent)
Name | Type | Allowed values | Required | Placeholder (in UI) |
---|---|---|---|---|
@expression | true | (variable) | ||
@ignore_null | any of: IGNORE , RESPECT | true | - | |
@partition | false | (partition variable(s)) | ||
@order_by |