InfluxDB环境搭建03

本节讲解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 *

Leave a Reply

Your email address will not be published. Required fields are marked *

*