本节讲解InfluxDB的查询
1、普通查询
influx -database NOAA_water_database Connected to http://localhost:8086 version 1.8.0 InfluxDB shell version: 1.8.0 > SELECT * FROM "h2o_feet" WHERE time>1568750040000000000 ORDER BY time DESC name: h2o_feet time level description location water_level ---- ----------------- -------- ----------- 1568756520000000000 between 3 and 6 feet santa_monica 4.938 1568756160000000000 between 3 and 6 feet santa_monica 5.066 1568755800000000000 between 3 and 6 feet santa_monica 5.01 1568755440000000000 between 3 and 6 feet santa_monica 5.013 1568755080000000000 between 3 and 6 feet santa_monica 5.072 1568754720000000000 between 3 and 6 feet santa_monica 5.213 1568754360000000000 between 3 and 6 feet santa_monica 5.341 1568754000000000000 between 3 and 6 feet santa_monica 5.338 1568753640000000000 between 3 and 6 feet santa_monica 5.322 1568753280000000000 between 3 and 6 feet santa_monica 5.24 1568752920000000000 between 3 and 6 feet santa_monica 5.302 1568752560000000000 between 3 and 6 feet santa_monica 5.62 1568752200000000000 between 3 and 6 feet santa_monica 5.604 1568751840000000000 between 3 and 6 feet santa_monica 5.502 1568751480000000000 between 3 and 6 feet santa_monica 5.551 1568751120000000000 between 3 and 6 feet santa_monica 5.459 1568750760000000000 between 3 and 6 feet santa_monica 5.62 1568750400000000000 between 3 and 6 feet santa_monica 5.627 > SELECT "location","water_level" FROM "h2o_feet" WHERE time>1568750040000000000 name: h2o_feet time location water_level ---- -------- ----------- 1568750400000000000 santa_monica 5.627 1568750760000000000 santa_monica 5.62 1568751120000000000 santa_monica 5.459 1568751480000000000 santa_monica 5.551 1568751840000000000 santa_monica 5.502 1568752200000000000 santa_monica 5.604 1568752560000000000 santa_monica 5.62 1568752920000000000 santa_monica 5.302 1568753280000000000 santa_monica 5.24 1568753640000000000 santa_monica 5.322 1568754000000000000 santa_monica 5.338 1568754360000000000 santa_monica 5.341 1568754720000000000 santa_monica 5.213 1568755080000000000 santa_monica 5.072 1568755440000000000 santa_monica 5.013 1568755800000000000 santa_monica 5.01 1568756160000000000 santa_monica 5.066 1568756520000000000 santa_monica 4.938 > SELECT *::field FROM "h2o_feet" WHERE time>1568750040000000000 name: h2o_feet time level description water_level ---- ----------------- ----------- 1568750400000000000 between 3 and 6 feet 5.627 1568750760000000000 between 3 and 6 feet 5.62 1568751120000000000 between 3 and 6 feet 5.459 1568751480000000000 between 3 and 6 feet 5.551 1568751840000000000 between 3 and 6 feet 5.502 1568752200000000000 between 3 and 6 feet 5.604 1568752560000000000 between 3 and 6 feet 5.62 1568752920000000000 between 3 and 6 feet 5.302 1568753280000000000 between 3 and 6 feet 5.24 1568753640000000000 between 3 and 6 feet 5.322 1568754000000000000 between 3 and 6 feet 5.338 1568754360000000000 between 3 and 6 feet 5.341 1568754720000000000 between 3 and 6 feet 5.213 1568755080000000000 between 3 and 6 feet 5.072 1568755440000000000 between 3 and 6 feet 5.013 1568755800000000000 between 3 and 6 feet 5.01 1568756160000000000 between 3 and 6 feet 5.066 1568756520000000000 between 3 and 6 feet 4.938 > SELECT "water_level"-3 FROM "h2o_feet" WHERE time>1568750040000000000 name: h2o_feet time water_level ---- ----------- 1568750400000000000 2.627 1568750760000000000 2.62 1568751120000000000 2.4589999999999996 1568751480000000000 2.551 1568751840000000000 2.502 1568752200000000000 2.604 1568752560000000000 2.62 1568752920000000000 2.3019999999999996 1568753280000000000 2.24 1568753640000000000 2.322 1568754000000000000 2.338 1568754360000000000 2.341 1568754720000000000 2.213 1568755080000000000 2.072 1568755440000000000 2.013 1568755800000000000 2.01 1568756160000000000 2.066 1568756520000000000 1.9379999999999997 > SELECT * FROM "NOAA_water_database"."autogen"."h2o_feet" WHERE time>1568750040000000000 name: h2o_feet time level description location water_level ---- ----------------- -------- ----------- 1568750400000000000 between 3 and 6 feet santa_monica 5.627 1568750760000000000 between 3 and 6 feet santa_monica 5.62 1568751120000000000 between 3 and 6 feet santa_monica 5.459 1568751480000000000 between 3 and 6 feet santa_monica 5.551 1568751840000000000 between 3 and 6 feet santa_monica 5.502 1568752200000000000 between 3 and 6 feet santa_monica 5.604 1568752560000000000 between 3 and 6 feet santa_monica 5.62 1568752920000000000 between 3 and 6 feet santa_monica 5.302 1568753280000000000 between 3 and 6 feet santa_monica 5.24 1568753640000000000 between 3 and 6 feet santa_monica 5.322 1568754000000000000 between 3 and 6 feet santa_monica 5.338 1568754360000000000 between 3 and 6 feet santa_monica 5.341 1568754720000000000 between 3 and 6 feet santa_monica 5.213 1568755080000000000 between 3 and 6 feet santa_monica 5.072 1568755440000000000 between 3 and 6 feet santa_monica 5.013 1568755800000000000 between 3 and 6 feet santa_monica 5.01 1568756160000000000 between 3 and 6 feet santa_monica 5.066 1568756520000000000 between 3 and 6 feet santa_monica 4.938 > SELECT * FROM "h2o_feet" WHERE "water_level" > 9.9 name: h2o_feet time level description location water_level ---- ----------------- -------- ----------- 1566975960000000000 at or greater than 9 feet coyote_creek 9.902 1566976320000000000 at or greater than 9 feet coyote_creek 9.938 1566976680000000000 at or greater than 9 feet coyote_creek 9.957 1566977040000000000 at or greater than 9 feet coyote_creek 9.964 1566977400000000000 at or greater than 9 feet coyote_creek 9.954 1566977760000000000 at or greater than 9 feet coyote_creek 9.941 1566978120000000000 at or greater than 9 feet coyote_creek 9.925 1566978480000000000 at or greater than 9 feet coyote_creek 9.902 1567380600000000000 at or greater than 9 feet coyote_creek 9.902 > SELECT * FROM "h2o_feet" WHERE "level description" = 'below 3 feet' and "water_level" >= 3 > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' and "water_level" < -0.2 name: h2o_feet time water_level ---- ----------- 1566988560000000000 -0.243 1567077840000000000 -0.21
2、group by查询
> SELECT MEAN("water_level") FROM "h2o_feet" GROUP BY "location"
name: h2o_feet
tags: location=coyote_creek
time mean
---- ----
0 5.3591424203039155
name: h2o_feet
tags: location=santa_monica
time mean
---- ----
0 3.5307120942458807
> SELECT COUNT("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' GROUP BY time(4w)
name: h2o_feet
time count
---- -----
1565222400000000000 4559
1567641600000000000 3045
1570060800000000000 0
1572480000000000000 0
1574899200000000000 0
1577318400000000000 0
1579737600000000000 0
1582156800000000000 0
1584576000000000000 0
1586995200000000000 0
1589414400000000000 0
1591833600000000000 0
> SELECT * FROM "h2o_feet" WHERE time > now() - 7d
> SELECT * FROM "h2o_feet" WHERE time = '2020-07-01T00:00:00Z'
3、分页查询
> SELECT "water_level","location" FROM "h2o_feet" LIMIT 3 name: h2o_feet time water_level location ---- ----------- -------- 1566000000000000000 8.12 coyote_creek 1566000000000000000 2.064 santa_monica 1566000360000000000 8.005 coyote_creek > SELECT "water_level","location" FROM "h2o_feet" LIMIT 3 OFFSET 3 name: h2o_feet time water_level location ---- ----------- -------- 1566000360000000000 2.116 santa_monica 1566000720000000000 7.887 coyote_creek 1566000720000000000 2.028 santa_monica > SELECT "water_level" FROM "h2o_feet" GROUP BY * LIMIT 3 SLIMIT 1 name: h2o_feet tags: location=coyote_creek time water_level ---- ----------- 1566000000000000000 8.12 1566000360000000000 8.005 1566000720000000000 7.887 > SELECT "water_level","location" FROM "h2o_feet" LIMIT 3 SLIMIT 1 SOFFSET 1
4、带正则表达式的查询
> SELECT /l/ FROM "h2o_feet" LIMIT 1
name: h2o_feet
time level description location water_level
---- ----------------- -------- -----------
1566000000000000000 below 3 feet santa_monica 2.064
> SELECT MEAN("degrees") FROM /temperature/
name: average_temperature
time mean
---- ----
0 79.98472932232272
name: h2o_temperature
time mean
---- ----
0 64.98872722506226
> SELECT MEAN(water_level) FROM "h2o_feet" WHERE "location" =~ /[m]/ AND "water_level" > 3
name: h2o_feet
time mean
---- ----
0 4.471366691627881
> SELECT FIRST("index") FROM "h2o_quality" GROUP BY /l/
name: h2o_quality
tags: location=coyote_creek
time first
---- -----
1566000000000000000 41
name: h2o_quality
tags: location=santa_monica
time first
---- -----
1566000000000000000 99
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location" = 'santa_monica' AND "level description" =~ /between/
name: h2o_feet
time mean
---- ----
0 4.471366691627881
5、嵌套查询
> SELECT SUM("max") FROM (SELECT MAX("water_level") AS "max" FROM "h2o_feet" GROUP BY "location")
name: h2o_feet
time sum
---- ---
0 17.169
6、SELECT INTO
> SELECT "water_level" INTO "h2o_feet_coyote_creek" FROM "h2o_feet" WHERE "location" = 'coyote_creek'
name: result
time written
---- -------
0 7604
> SELECT MEAN("water_level") INTO "water_level_averages_4w" FROM "h2o_feet" WHERE "location" = 'coyote_creek' GROUP BY time(4w)
name: result
time written
---- -------
0 2
7、整库复制
> CREATE DATABASE noaa_water_db > SELECT * INTO "NOAA_water_database_copy"."autogen".:MEASUREMENT FROM "NOAA_water_database"."autogen"./.*/ GROUP BY *