All Products
Search
Document Center

Time Series Database:Subqueries

Last Updated:Aug 05, 2020

Subqueries are queries that are nested in the FROM clauses of other queries. You can use subqueries as filter conditions in your main queries. The subqueries serve a similar purpose to the nested functions and the SQL ‘HAVING’clauses).

Syntax

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

Description

TSDB for InfluxDB® first runs a subquery and then runs the main query where the subquery is nested.

The main query that includes the subquery must contain at least the SELECT and FROM clauses. The main query supports all the clauses that are described in this topic.

The subquery is included in the FROM clause of the main query. You must use parentheses () to enclose the subquery. The subquery supports all the clauses that are described in this topic.

InfluxQL allows you to nest multiple subqueries in each main query. The following example shows the sample syntax.

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

Examples

Use the SUM() function to calculate the sum of the maximum field values returned by the MAX() function`

  1. > SELECT SUM("max") FROM (SELECT MAX("water_level") FROM "h2o_feet" GROUP BY "location")
  2. name: h2o_feet
  3. time sum
  4. ---- ---
  5. 1970-01-01T00:00:00Z 17.169

The query returns the sum of the maximum field values of the water_level field key for all the tag values of the location tag key.

TSDB for InfluxDB® first runs the subquery to calculate the maximum field value of the water_level field key for each tag value of the location tag key.

  1. > SELECT MAX("water_level") FROM "h2o_feet" GROUP BY "location"
  2. name: h2o_feet
  3. tags: location=coyote_creek
  4. time max
  5. ---- ---
  6. 2015-08-29T07:24:00Z 9.964
  7. name: h2o_feet
  8. tags: location=santa_monica
  9. time max
  10. ---- ---
  11. 2015-08-29T03:54:00Z 7.205

Then, TSDB for InfluxDB® runs the main query to calculate the sum of the obtained maximum values: 9.964 + 7.205 = 17.169. In the main query, max instead of water_level is specified as the field key in the SUM() function.

Use the MEAN() function to calculate the average value of the differences between the field values of two field keys

  1. > SELECT MEAN("difference") FROM (SELECT "cats" - "dogs" AS "difference" FROM "pet_daycare")
  2. name: pet_daycare
  3. time mean
  4. ---- ----
  5. 1970-01-01T00:00:00Z 1.75

The query returns the average value of the differences between the field values of the cats field key and the field values of the dogs field key in the pet_daycare measurement.

TSDB for InfluxDB® first runs the subquery to calculate the differences between the field values of the cats field key and the field values of the dogs field key. The subquery names the output column as difference.

  1. > SELECT "cats" - "dogs" AS "difference" FROM "pet_daycare"
  2. name: pet_daycare
  3. time difference
  4. ---- ----------
  5. 2017-01-20T00:55:56Z -1
  6. 2017-01-21T00:55:56Z -49
  7. 2017-01-22T00:55:56Z 66
  8. 2017-01-23T00:55:56Z -9

Then, TSDB for InfluxDB® runs the main query to calculate the average value of the differences. In the main query, difference is specified as the field key in the MEAN() function.

Calculate average values by using the MEAN() function and obtain the average values that meet the specified condition
  1. > SELECT "all_the_means" FROM (SELECT MEAN("water_level") AS "all_the_means" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m) ) WHERE "all_the_means" > 5
  2. name: h2o_feet
  3. time all_the_means
  4. ---- -------------
  5. 2015-08-18T00:00:00Z 5.07625

The query calculates the average field values of the water_level field key and returns the average values that are greater than five.

TSDB for InfluxDB® first runs the subquery to calculate the average field values of the water_level field key. The timestamps of the field values for the field key must fall in the range from 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The subquery also groups the results based on a 12-minute interval and stores the results in the all_the_means column.

  1. > SELECT MEAN("water_level") AS "all_the_means" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m)
  2. name: h2o_feet
  3. time all_the_means
  4. ---- -------------
  5. 2015-08-18T00:00:00Z 5.07625
  6. 2015-08-18T00:12:00Z 4.950749999999999
  7. 2015-08-18T00:24:00Z 4.80675

Then, TSDB for InfluxDB® runs the main query and returns the average values that are greater than five. In the main query, all_the_means is specified as the field key in the SELECT clause.

Use the SUM() function to calculate the sum of the multiple derivatives returned by the DERIVATIVE() function

  1. > SELECT SUM("water_level_derivative") AS "sum_derivative" FROM (SELECT DERIVATIVE(MEAN("water_level")) AS "water_level_derivative" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m),"location") GROUP BY "location"
  2. name: h2o_feet
  3. tags: location=coyote_creek
  4. time sum_derivative
  5. ---- --------------
  6. 1970-01-01T00:00:00Z -0.4950000000000001
  7. name: h2o_feet
  8. tags: location=santa_monica
  9. time sum_derivative
  10. ---- --------------
  11. 1970-01-01T00:00:00Z -0.043999999999999595

The query returns the sum of the derivatives of the average field values of the water_level field key for each tag value of the location tag key.

TSDB for InfluxDB® first runs the subquery to calculate the derivative of each average field value of the water_level field key. The average field value is obtained based on a 12-minute interval. The subquery performs derivative calculation for each tag value of the location tag key and stores the results in the water_level_derivative column.

  1. > SELECT DERIVATIVE(MEAN("water_level")) AS "water_level_derivative" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m),"location"
  2. name: h2o_feet
  3. tags: location=coyote_creek
  4. time water_level_derivative
  5. ---- ----------------------
  6. 2015-08-18T00:12:00Z -0.23800000000000043
  7. 2015-08-18T00:24:00Z -0.2569999999999997
  8. name: h2o_feet
  9. tags: location=santa_monica
  10. time water_level_derivative
  11. ---- ----------------------
  12. 2015-08-18T00:12:00Z -0.0129999999999999
  13. 2015-08-18T00:24:00Z -0.030999999999999694

Then, TSDB for InfluxDB® runs the main query to calculate the sum of the values in the water_level_derivative column for each tag value of the location tag key. In the main query, water_level_derivative instead of water_level or derivative is specified as the field key in the SUM() function.

FAQ about subqueries

What are the impacts if a subquery includes multiple SELECT statements?

InfluxQL allows you to nest multiple subqueries in each main query.

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

InfluxQL does not support multiple SELECT statements in each subquery.

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

If a subquery includes multiple SELECT statements, the system returns a parsing error.


InfluxDB® is a trademark registered by InfluxData, which is not affiliated with, and does not endorse, TSDB for InfluxDB®.