查询 #### 语法:: SELECT [,,] FROM [,] 最简单的:: # 查看自定度量的数据, 里面的相关字段，官方建议使用“双引号”标注出来 select * from "CPU" order by time desc # 查看指定的Field和Tag select "load1","role" from "CPU" order by time desc # 只查看Field select *::field from "CPU" # 执行基本的运算 select ("load1" * 2) + 0.5 from "CPU" # 查询指定Tag的数据，注意，Where子句的字符串值要使用“单引号”，字符串值 # 如果没有使用引号或者使用了双引号，都不会有任何值的返回 注: 一定要使用单引号 select * from "CPU" where role = 'FrontServer' # 查询Field中，load1 > 20 的所有数据 select * from "CPU" where "load1" > 20 模糊查询:: 实现查询以给定字段开始的数据 select fieldName from measurementName where fieldName=~/^ 给定字段 /； 实现查询以给定字段结束的数据 select fieldName from measurementName where fieldName=~/ 给定字段 $/ 实现查询包含给定字段数据 select fieldName from measurementName where fieldName=~/ 给定字段 / 示例: SELECT count("value") FROM $table WHERE path=~/^report/ group by path, time($step) 使用非默认RP:: 只要不是使用默认的RP我们就需要指定RP 注:这儿measurement为downsampled_orders的rp名为rp_year > SELECT * FROM "rp_year"."downsampled_orders" LIMIT 5 执行基本计算:: // SELECT语句支持使用基本的数学运算符，例如，+、-、/、*和()等等。 SELECT field_key1 + field_key2 AS "field_key_sum" FROM "measurement_name" WHERE time < now() - 15m SELECT (key1 + key2) - (key3 + key4) AS "some_calculation" FROM "measurement_name" WHERE time < now() - 15m 提供其标识符类型:: > SELECT "level description"::field,"location"::tag,"water_level"::field FROM "h2o_feet" // 查询所有field > SELECT *::field FROM "h2o_feet" 从多个measurement中查询数据:: > SELECT * FROM "h2o_feet","h2o_pH" 指定DB:: > SELECT * FROM "NOAA_water_database"."autogen"."h2o_feet" // 从特定数据库中查询measurement的所有数据 GroupBy ======= 语法:: SELECT_clause FROM_clause [WHERE_clause] GROUP BY [* | [, 3. 对结果数据按多个tag作group by，其中tag key的顺序没所谓 GROUP BY , GroupBy指定时区:: influx -precision rfc3339 -execute=" SELECT count(*) FROM "win_cpu" WHERE (time >= now() - 15d) GROUP BY IP,host,customer TZ('Asia/Beijing')" // 指定东八区 -database=telegraf -format=csv 使用Group计算百分比:: SELECT (sum(field_key1) / sum(field_key2)) * 100 AS "calculated_percentage" FROM "measurement_name" WHERE time < now() - 15m GROUP BY time(1m) SELECT count(value) FROM devices where time > '2019-08-29T00:00:00Z' group by gadget_type_id, time(1d) 时间相关查询:: > SELECT count(value) FROM devices where time > '2019-08-26T00:00:00Z' group by time(1d) > SELECT count(value) FROM devices where time > now() - 10w group by time(1w) 时间偏移offset:: > SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:06:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(18m, 6m) 带offset为6m的主要是下面3个时间段: time >= 00:06:00Z AND time < 00:24:00Z time >= 00:24:00Z AND time < 00:42:00Z time >= 00:42:00Z AND time < 01:00:00Z 不带offset的包括下面4个时间段: time >= 00:06:00Z AND time < 00:18:00Z time >= 00:18:00Z AND time < 00:36:00Z time >= 00:36:00Z AND time < 00:54:00Z time = 00:54:00Z fill() ====== 说明: fill()更改不含数据的时间间隔的返回值:: fill的参数: 1. 任一数值：用这个数字返回没有数据点的时间间隔 2. linear：返回没有数据的时间间隔的线性插值结果。 3. none: 不返回在时间间隔里没有点的数据 4. previous：返回时间隔间的前一个间隔的数据 例一：fill(100):: 1. 不带fill(100): > SELECT MAX("water_level") FROM "h2o_feet" WHERE time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m) 结果: name: h2o_feet -------------- time max 2015-09-18T16:00:00Z 3.599 2015-09-18T16:12:00Z 3.402 2015-09-18T16:24:00Z 3.235 2015-09-18T16:36:00Z 2. 带fill(100): > SELECT MAX("water_level") FROM "h2o_feet" WHERE time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m) fill(100) 结果: name: h2o_feet -------------- time max 2015-09-18T16:00:00Z 3.599 2015-09-18T16:12:00Z 3.402 2015-09-18T16:24:00Z 3.235 2015-09-18T16:36:00Z 100 例二：fill(linear):: 1. 不带fill(linear): > SELECT MEAN("tadpoles") FROM "pond" WHERE time >= '2016-11-11T21:00:00Z' AND time <= '2016-11-11T22:06:00Z' GROUP BY time(12m) 结果: name: pond time mean ---- ---- 2016-11-11T21:00:00Z 1 2016-11-11T21:12:00Z 2016-11-11T21:24:00Z 3 2016-11-11T21:36:00Z 2016-11-11T21:48:00Z 2016-11-11T22:00:00Z 6 2. 带fill(linear): > SELECT MEAN("tadpoles") FROM "pond" WHERE time >= '2016-11-11T21:00:00Z' AND time <= '2016-11-11T22:06:00Z' GROUP BY time(12m) fill(linear) 结果: name: pond time mean ---- ---- 2016-11-11T21:00:00Z 1 2016-11-11T21:12:00Z 2 2016-11-11T21:24:00Z 3 2016-11-11T21:36:00Z 4 2016-11-11T21:48:00Z 5 2016-11-11T22:00:00Z 6 例三：fill(none):: 1. 不带fill(none): > SELECT MAX("water_level") FROM "h2o_feet" WHERE time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m) 结果: name: h2o_feet -------------- time max 2015-09-18T16:00:00Z 3.599 2015-09-18T16:12:00Z 3.402 2015-09-18T16:24:00Z 3.235 2015-09-18T16:36:00Z 2. 带fill(none): > SELECT MAX("water_level") FROM "h2o_feet" WHERE time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m) fill(none) 结果: name: h2o_feet -------------- time max 2015-09-18T16:00:00Z 3.599 2015-09-18T16:12:00Z 3.402 2015-09-18T16:24:00Z 3.235 例四：fill(null):: 1. 不带fill(null): > SELECT MAX("water_level") FROM "h2o_feet" WHERE time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m) 结果: name: h2o_feet -------------- time max 2015-09-18T16:00:00Z 3.599 2015-09-18T16:12:00Z 3.402 2015-09-18T16:24:00Z 3.235 2015-09-18T16:36:00Z 2. 带fill(null): > SELECT MAX("water_level") FROM "h2o_feet" WHERE time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m) fill(null) 结果: name: h2o_feet -------------- time max 2015-09-18T16:00:00Z 3.599 2015-09-18T16:12:00Z 3.402 2015-09-18T16:24:00Z 3.235 2015-09-18T16:36:00Z 例五：fill(previous):: 1. 不带fill(previous): > SELECT MAX("water_level") FROM "h2o_feet" WHERE time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m) 结果: name: h2o_feet -------------- time max 2015-09-18T16:00:00Z 3.599 2015-09-18T16:12:00Z 3.402 2015-09-18T16:24:00Z 3.235 2015-09-18T16:36:00Z 2. 带fill(previous): > SELECT MAX("water_level") FROM "h2o_feet" WHERE time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m) fill(previous) 结果: name: h2o_feet -------------- time max 2015-09-18T16:00:00Z 3.599 2015-09-18T16:12:00Z 3.402 2015-09-18T16:24:00Z 3.235 2015-09-18T16:36:00Z 3.235 复杂查询实例 ============ 其他:: // 多个AND SELECT * from (_INNER_QUERY_HERE_) WHERE count_linux = -1 AND count_linux64 = -1 AND count_mac = -1 AND count_win = -1 AND count_win64 = -1; // 多个OR SELECT * from (_INNER_QUERY_HERE_) WHERE count_linux = -1 OR count_linux64 = -1 OR count_mac = -1 OR count_win = -1 OR count_win64 = -1; // And加Or > SELECT "water_level" FROM "h2o_feet" WHERE "location" <> 'santa_monica' AND (water_level < -0.59 OR water_level > 9.95) ORDER BY TIME DESC ================== 默认情况下，InfluxDB以升序的顺序返回结果; 返回的第一个点具有最早的时间戳, ORDER BY time DESC反转该顺序 实例:: > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' ORDER BY time DESC > SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY time(12m) ORDER BY time DESC Time Zone子句 ============= 例一：返回从UTC偏移到芝加哥时区的数据:: > SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:18:00Z' tz('America/Chicago') name: h2o_feet time water_level ---- ----------- 2015-08-17T19:00:00-05:00 2.064 2015-08-17T19:06:00-05:00 2.116 2015-08-17T19:12:00-05:00 2.028 2015-08-17T19:18:00-05:00 2.126 rfc3399时间字符串:: 'YYYY-MM-DDTHH:MM:SS.nnnnnnnnnZ' // .nnnnnnnnn是可选的，如果没有的话，默认是.00000000 正则表达式 ========== 例一：在SELECT中使用正则表达式指定field key和tag key:: > SELECT /l/ FROM "h2o_feet" LIMIT 1 name: h2o_feet time level description location water_level ---- ----------------- -------- ----------- 2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12 例二：在SELECT中使用正则表达式指定函数里面的field key:: > SELECT DISTINCT(/level/) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00.000000000Z' AND time <= '2015-08-18T00:12:00Z' name: h2o_feet time distinct_level description distinct_water_level ---- -------------------------- -------------------- 2015-08-18T00:00:00Z below 3 feet 2.064 2015-08-18T00:00:00Z 2.116 2015-08-18T00:00:00Z 2.028 例三：在FROM中使用正则表达式指定measurement:: > SELECT MEAN("degrees") FROM /temperature/ name: average_temperature time mean ---- ---- 1970-01-01T00:00:00Z 79.98472932232272 name: h2o_temperature time mean ---- ---- 1970-01-01T00:00:00Z 64.98872722506226 例四：在WHERE中使用正则表达式指定tag value:: // location的tag value包括m并且water_level大于3 > SELECT MEAN(water_level) FROM "h2o_feet" WHERE "location" =~ /[m]/ AND "water_level" > 3 name: h2o_feet time mean ---- ---- 1970-01-01T00:00:00Z 4.47155532049926 例五：在WHERE中使用正则表达式指定无值的tag:: > SELECT * FROM "h2o_feet" WHERE "location" !~ /./ 例六：在WHERE中使用正则表达式指定有值的tag:: > SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location" =~ /./ name: h2o_feet time mean ---- ---- 1970-01-01T00:00:00Z 4.442107025822523 例七：在WHERE中使用正则表达式指定一个field value:: > SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location" = 'santa_monica' AND "level description" =~ /between/ name: h2o_feet time mean ---- ---- 1970-01-01T00:00:00Z 4.47155532049926