Redivis Documentation
API DocumentationRedivis Home
Search
⌃K
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