Subqueries-Time Series Database(TSDB)-阿里云帮助中心

A subquery is a query nested in the FROM clause of another query. Use subqueries to apply a query as a condition in the enclosing query. Subqueries offer functionality similar to nested functions and SQL HAVING clauses).

Syntax

SELECT_clause FROM ( SELECT_statement ) [...]

TSDB for InfluxDB® runs the subquery first, then runs the main query. The main query must include at least the SELECT and FROM clauses. Both the main query and subqueries support all InfluxQL clauses.

Enclose subqueries in parentheses (). InfluxQL supports multiple nested subqueries per main query:

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

Examples

Calculate the SUM() of several MAX() values

> 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

This query returns the sum of the maximum water_level values across all tag values of the location tag key.

First, the subquery calculates the maximum water_level value for each tag value of location:

> 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, the main query calculates the sum of those maximum values: 9.964 + 7.205 = 17.169. Notice that the main query specifies max, not water_level, as the field key in SUM().

Calculate the MEAN() difference between 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

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

First, the subquery calculates the difference between cats and dogs field values and stores the results in a column named 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, the main query calculates the mean of the difference column. Notice that the main query specifies difference as the field key in MEAN().

Calculate several MEAN() values and filter on those values

> 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

This query returns average water_level values greater than 5.

First, the subquery calculates the mean water_level value for each 12-minute interval within the specified time range 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, the main query returns only the rows where all_the_means is greater than 5.

Calculate the SUM() of several DERIVATIVE() values

> 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

This query returns the sum of the derivatives of the mean water_level values for each tag value of location.

First, the subquery calculates the derivative of the mean water_level value per 12-minute interval for each tag value of location 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, the main query calculates the sum of the water_level_derivative values for each tag value of location. Notice that the main query specifies water_level_derivative, not water_level or derivative, as the field key in SUM().

Multiple SELECT statements in a subquery

InfluxQL supports multiple nested subqueries per main query:

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

InfluxQL does not support multiple SELECT statements within a single 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®.