java获取日志数据去重汇总,Neohope's Blog

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

Share the post "InfluxDB环境搭建03"