InfluxQL subqueries

An InfluxQL subquery is a query nested in the FROM clause of an InfluxQL query. The outer query queries results returned by the inner query (subquery).

• Syntax
• Examples
• Notable subquery behaviors

Syntax

SELECT_clause FROM ( SELECT_statement ) [...]

When using subqueries, InfluxQL performs the inner query first, then performs the outer query.

The outer query requires a SELECT clause and a FROM clause. The inner query is enclosed in parentheses in the outer query’s FROM clause.

InfluxQL supports multiple nested subqueries:

SELECT_clause FROM ( SELECT_clause FROM ( SELECT_statement ) [...] ) [...]

Examples

SELECT
  SUM(max)
FROM
  (
    SELECT
      MAX(temp)
    FROM
      home
    GROUP BY
      room
  )

SELECT
  MEAN(difference)
FROM
  (
    SELECT
      a - b AS difference
    FROM
      numbers
  )

SELECT
  co_change
FROM
  (
    SELECT
      SPREAD(co) AS co_change
    FROM
      home
    GROUP BY
      room,
      time(2h)
  )
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'
  AND co_change >= 4

SELECT
  SUM(co_derivative) AS sum_derivative
FROM
  (
    SELECT
      DERIVATIVE(MEAN(co)) AS co_derivative
    FROM
      home
    GROUP BY
      time(12m),
      room
  )
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'
GROUP BY
  room

Notable subquery behaviors

• Apply time bounds to the outer query to improve performance
• Cannot use multiple SELECT statements in a subquery

Apply time bounds to the outer query to improve performance

To improve the performance of InfluxQL queries that use subqueries and a specified time range, apply the WHERE clause with time-based predicates to the outer query rather than the inner query. For example–the following queries return the same results, but the query with time-based predicate on the outer query is more performant than the query with time-based predicate on the inner query:

SELECT
  inner_value AS value
FROM
  (
    SELECT
      raw_value as inner_value
  )
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'

SELECT
  inner_value AS value
FROM
  (
    SELECT
      raw_value as inner_value
    WHERE
      time >= '2022-07-19T08:00:00Z'
      AND time <= '2022-01-01T20:00:00Z'
  )

Cannot use multiple SELECT statements in a subquery

InfluxQL does not support multiple SELECT statements per subquery:

SELECT_clause FROM (SELECT_statement; SELECT_statement) [...]

However, InfluxQL does support multiple nested subqueries per outer query:

SELECT_clause FROM ( SELECT_clause FROM ( SELECT_statement ) [...] ) [...]
                     ------------------   ----------------
                         Subquery 1          Subquery 2