All Products
Search

This Product

    Time Series Database:Subqueries

    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")
    3. name: h2o_feet
    4. time                   sum
    5. ----                   ---
    6. 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
    4. tags: location=coyote_creek
    5. time                   max
    6. ----                   ---
    7. 2015-08-29T07:24:00Z   9.964
    9. name: h2o_feet
    10. tags: location=santa_monica
    11. time                   max
    12. ----                   ---
    13. 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")
    3. name: pet_daycare
    4. time                   mean
    5. ----                   ----
    6. 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"
    3. name: pet_daycare
    4. time                   difference
    5. ----                   ----------
    6. 2017-01-20T00:55:56Z   -1
    7. 2017-01-21T00:55:56Z   -49
    8. 2017-01-22T00:55:56Z   66
    9. 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
    3. name: h2o_feet
    4. time                   all_the_means
    5. ----                   -------------
    6. 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)
    3. name: h2o_feet
    4. time                   all_the_means
    5. ----                   -------------
    6. 2015-08-18T00:00:00Z   5.07625
    7. 2015-08-18T00:12:00Z   4.950749999999999
    8. 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"
    3. name: h2o_feet
    4. tags: location=coyote_creek
    5. time                   sum_derivative
    6. ----                   --------------
    7. 1970-01-01T00:00:00Z   -0.4950000000000001
    9. name: h2o_feet
    10. tags: location=santa_monica
    11. time                   sum_derivative
    12. ----                   --------------
    13. 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"
    3. name: h2o_feet
    4. tags: location=coyote_creek
    5. time                   water_level_derivative
    6. ----                   ----------------------
    7. 2015-08-18T00:12:00Z   -0.23800000000000043
    8. 2015-08-18T00:24:00Z   -0.2569999999999997
    10. name: h2o_feet
    11. tags: location=santa_monica
    12. time                   water_level_derivative
    13. ----                   ----------------------
    14. 2015-08-18T00:12:00Z   -0.0129999999999999
    15. 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®.