SQL selector functions

SQL selector functions are designed to work with time series data. They behave similarly to aggregate functions in that they take a collection of data and return a single value. However, selectors are unique in that they return a struct that contains a time value in addition to the computed value.

• How do selector functions work?
• Selector functions
  • selector_min
  • selector_max
  • selector_first
  • selector_last

How do selector functions work?

Each selector function returns an Arrow struct (similar to a JSON object) representing a single time and value from the specified column in the each group. What time and value get returned depend on the logic in the selector function. For example, selector_first returns the value of specified column in the first row of the group. selector_max returns the maximum value of the specified column in the group.

Selector struct schema

The struct returned from a selector function has two properties:

• time: time value in the selected row
• expression: value of the specified column in the selected row

{time: 2023-01-01T00:00:00Z, value: 72.1}

Selector functions in use

In your SELECT statement, execute a selector function and use bracket notation to reference properties of the returned struct to populate the column value:

SELECT
  selector_first(temp, time)['time'] AS time,
  selector_first(temp, time)['value'] AS temp,
  room
FROM home
GROUP BY room

Selector functions

• selector_min
• selector_max
• selector_first
• selector_last

selector_min

Returns the smallest value of a selected column and a timestamp.

Arguments:

• expression: Column to operate on or a literal value.
• timestamp: Time column or timestamp literal.

selector_min(expression, timestamp)

SELECT 
  selector_min(water_level, time)['time'] AS time,
  selector_min(water_level, time)['value'] AS water_level
FROM h2o_feet

selector_max

Returns the largest value of a selected column and a timestamp.

Arguments:

• expression: Column to operate on or a literal value.
• timestamp: Time column or timestamp literal.

selector_max(expression, timestamp)

SELECT 
  selector_max(water_level, time)['time'] AS time,
  selector_max(water_level, time)['value'] AS water_level
FROM h2o_feet

selector_first

Returns the first value ordered by time ascending.

Arguments:

• expression: Column to operate on or a literal value.
• timestamp: Time column or timestamp literal.

selector_first(expression, timestamp)

SELECT 
  selector_first(water_level, time)['time'] AS time,
  selector_first(water_level, time)['value'] AS water_level
FROM h2o_feet

selector_last

Returns the last value ordered by time ascending.

Arguments:

• expression: Column to operate on or a value literal.
• timestamp: Time column or timestamp literal.

selector_last(expression, timestamp)

SELECT 
  selector_last(water_level, time)['time'] AS time,
  selector_last(water_level, time)['value'] AS water_level
FROM h2o_feet