Partitioned variable methods

Search…
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​
any Redivis type​
true
(variable)
@partition
​variables
any Redivis type​
false
-
@order_by
​variable​
any Redivis type​
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
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
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​
any Redivis type​
true
(variable)
@partition
​variables
any Redivis type​
false
(partition variable(s))
@DISTINCT
​boolean​
any boolean​
false
-
@order_by
​variable​
any Redivis type​
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
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
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
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
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​
any Redivis type​
true
(variable)
@partition
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
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​
any Redivis type​
true
(variable)
@partition
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
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​
any Redivis type​
false
(",")
@partition
​variables
any Redivis type​
false
(partition variable(s) (optional))
@order_by
​variable​
any Redivis type​
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
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
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
​variables
any Redivis type​
false
-
@order_by
​variable​
any Redivis type​
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​
any Redivis type​
true
(variable)
@ignore_null
​enum​
any of: IGNORE, RESPECT
true
-
@partition
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
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​
any Redivis type​
true
(variable)
@literal
​literal​
any integer​
true
(offset)
@default_value
​literal​
any Redivis type​
false
((optional, defaults to NULL))
@partition
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
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​
any Redivis type​
true
(variable)
@ignore_null
​enum​
any of: IGNORE, RESPECT
true
-
@partition
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
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​
any Redivis type​
true
(variable)
@literal
​literal​
any integer​
true
(offset)
@default_value
​literal​
any Redivis type​
false
((optional, defaults to NULL))
@partition
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
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​
any Redivis type​
true
(variable)
@literal
​literal​
any integer​
true
(integer)
@ignore_null
​enum​
any of: IGNORE, RESPECT
true
-
@partition
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
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
​variables
any Redivis type​
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
​variables
any Redivis type​
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
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
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
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
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
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
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
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
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
​variables
any Redivis type​
false
(partition variable(s))
@order_by
​variable​
any Redivis type​
true
(partition order (optional))
@order_direction
​enum