全部产品

子查询

更新时间:2020-07-01 09:30:22

子查询是嵌套在另一个查询的FROM子句中的查询。使用子查询将查询作为条件应用在另一个查询中。子查询提供类似嵌套函数和SQL HAVING子句的功能。

语法

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

语法描述

TSDB For InfluxDB®首先执行子查询,然后执行主查询。

主查询包含着子查询,至少需要SELECT子句和FROM子句。主查询支持本文档中列出的所有子句。

子查询在主查询的FROM子句中,需要用括号将子查询括起来。子查询支持本文档中列出的所有子句。

InfluxQL支持在主查询中有多个嵌套的子查询,示例语法如下:

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

示例

计算多个MAX()值的SUM()

  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

该查询返回每个locationwater_level的最大值的总和。

TSDB For InfluxDB®首先执行子查询,计算每个locationwater_level的最大值:

  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

然后,TSDB For InfluxDB®执行主查询,计算这些最大值的总和:9.964 + 7.205 = 17.169。请注意,该主查询指定max(而不是water_level)作为SUM()函数中的field key。

计算两个field的差值的MEAN()

  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

该查询返回measurement pet_daycarecats数量和dogs数量的差异的平均值。

TSDB For InfluxDB®首先执行子查询,计算field cats中的值和field dogs中的值的差异,并将输出列命名为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

然后,TSDB For InfluxDB®执行主查询,计算这些差值的平均值。请注意,该主查询指定difference作为MEAN()函数中的field key。

计算多个MEAN()值并在这些值上加上条件

  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

该查询返回water_level的所有大于5的平均值。

TSDB For InfluxDB®首先执行子查询,计算从2015-08-18T00:00:00Z2015-08-18T00:30:00Z water_level的平均值,并将结果按12分钟的时间间隔进行分组,同时将输出列命名为all_the_means

  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

然后,TSDB For InfluxDB®执行主查询,只返回那些大于5的平均值。请注意,该主查询指定all_the_means作为SELECT子句中的field key。

计算多个DERIVATIVE()值的SUM()

  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

该查询返回每个locationwater_level的平均值的导数之和。

TSDB For InfluxDB®首先执行子查询,计算以12分钟为间隔获取的water_level的平均值的导数,它对每个location都进行了计算,并将输出列命名为water_level_derivative

  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

然后,TSDB For InfluxDB®执行主查询,计算每个locationwater_level_derivative的总和。请注意,该主查询指定water_level_derivative(而不是water_levelderivative)作为SUM()函数中的field key。

子查询的常见问题

在子查询中有多个SELECT语句

InfluxQL支持在主查询中有多个嵌套的子查询:

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

InfluxQL不支持在子查询中有多个SELECT语句:

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

如果在子查询中有多个SELECT语句,那么系统会返回解析错误。


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