Influxdata
/
docs-v2
mirror of https://github.com/influxdata/docs-v2.git
1
0
Fork 0
Code Issues Projects Releases Wiki Activity
docs-v2/content/shared/sql-reference/functions/selector.md

4.7 KiB
Raw Permalink Blame History

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?

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
  • value: 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

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

selector_min(expression, timestamp)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of string or arithmetic operators.
  • timestamp: Time expression. Can be a constant, column, or function.

{{< expand-wrapper >}} {{% expand "View selector_min query example" %}}

SELECT 
  selector_min(water_level, time)['time'] AS time,
  selector_min(water_level, time)['value'] AS water_level
FROM h2o_feet
time water_level
2019-08-28T14:30:00Z -0.61

{{% /expand %}} {{< /expand-wrapper >}}

selector_max

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

selector_max(expression, timestamp)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of string or arithmetic operators.
  • timestamp: Time expression. Can be a constant, column, or function.

{{< expand-wrapper >}} {{% expand "View selector_max query example" %}}

SELECT 
  selector_max(water_level, time)['time'] AS time,
  selector_max(water_level, time)['value'] AS water_level
FROM h2o_feet
time water_level
2019-08-28T07:24:00Z 9.964

{{% /expand %}} {{< /expand-wrapper >}}

selector_first

Returns the first value ordered by time ascending.

selector_first(expression, timestamp)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of string or arithmetic operators.
  • timestamp: Time expression. Can be a constant, column, or function.

{{< expand-wrapper >}} {{% expand "View selector_first query example" %}}

SELECT 
  selector_first(water_level, time)['time'] AS time,
  selector_first(water_level, time)['value'] AS water_level
FROM h2o_feet
time water_level
2019-08-28T07:24:00Z 9.964

{{% /expand %}} {{< /expand-wrapper >}}

selector_last

Returns the last value ordered by time ascending.

selector_last(expression, timestamp)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of string or arithmetic operators.
  • timestamp: Time expression. Can be a constant, column, or function.

{{< expand-wrapper >}} {{% expand "View selector_last query example" %}}

SELECT 
  selector_last(water_level, time)['time'] AS time,
  selector_last(water_level, time)['value'] AS water_level
FROM h2o_feet
time water_level
2019-09-17T21:42:00Z 4.938

{{% /expand %}} {{< /expand-wrapper >}}