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
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.
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`
> SELECT SUM("max") FROM (SELECT MAX("water_level") FROM "h2o_feet" GROUP BY "location")
name: h2o_feet
time sum
---- ---
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.
> SELECT MAX("water_level") FROM "h2o_feet" GROUP BY "location"
name: h2o_feet
tags: location=coyote_creek
time max
---- ---
2015-08-29T07:24:00Z 9.964
name: h2o_feet
tags: location=santa_monica
time max
---- ---
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
> SELECT MEAN("difference") FROM (SELECT "cats" - "dogs" AS "difference" FROM "pet_daycare")
name: pet_daycare
time mean
---- ----
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
.
> SELECT "cats" - "dogs" AS "difference" FROM "pet_daycare"
name: pet_daycare
time difference
---- ----------
2017-01-20T00:55:56Z -1
2017-01-21T00:55:56Z -49
2017-01-22T00:55:56Z 66
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
> 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
name: h2o_feet
time all_the_means
---- -------------
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.
> 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)
name: h2o_feet
time all_the_means
---- -------------
2015-08-18T00:00:00Z 5.07625
2015-08-18T00:12:00Z 4.950749999999999
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
> 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"
name: h2o_feet
tags: location=coyote_creek
time sum_derivative
---- --------------
1970-01-01T00:00:00Z -0.4950000000000001
name: h2o_feet
tags: location=santa_monica
time sum_derivative
---- --------------
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.
> 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"
name: h2o_feet
tags: location=coyote_creek
time water_level_derivative
---- ----------------------
2015-08-18T00:12:00Z -0.23800000000000043
2015-08-18T00:24:00Z -0.2569999999999997
name: h2o_feet
tags: location=santa_monica
time water_level_derivative
---- ----------------------
2015-08-18T00:12:00Z -0.0129999999999999
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.
SELECT_clause FROM ( SELECT_clause FROM ( SELECT_statement ) [...] ) [...]
------------------ ----------------
Subquery 1 Subquery 2
InfluxQL does not support multiple SELECT
statements in each subquery.
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®.