influxDB-查询操作influxDB-查询操作
1 #----综合使⽤
2书写顺序
3lect distinct*from'表名'where'限制条件'group by'分组依据'having'过滤条件'order by limit '展⽰条数' 4执⾏顺序
5from-- 查询qingchun
6where-- 限制条件
7group by-- 分组
8having-- 过滤条件
9order by-- 排序
10 limit -- 展⽰条数
11distinct-- 去重
12lect-- 查询的结果
1.查询数据表weather 的所有记录:
> lect * from weather
name: weather
time altitude area humidity temperature
---- -------- ---- -------- -----------
1607604432455278300 1001 南 -5 10
1607656595672442800 1000 东 -4 9
1607656662027484500 1001 南 -5 11
1607656706278952000 999 南 -5 11
1607656751612223600 1002 西 -2 11
1607656799728402900 1003 东 -2 11
2.按条件查询
#查询temperature=11的数据
> lect * from weather where temperature=11
name: weather
time altitude area humidity temperature
---- -------- ---- -------- -----------
1607656662027484500 1001 南 -5 11
1607656706278952000 999 南 -5 11
1607656751612223600 1002 西 -2 11
1607656799728402900 1003 东 -2 11
#查询altitude,temperature两列的数据
> lect altitude,temperature from weather
name: weather
time altitude temperature
水瓶座水逆---- -------- -----------
1607604432455278300 1001 10
1607656595672442800 1000 9
1607656662027484500 1001 11
1607656706278952000 999 11
1607656751612223600 1002 11
1607656799728402900 1003 11
3.排序
#按最新时间排序
> lect * from weather order by time desc
name: weather
time altitude area humidity temperature
---- -------- ---- -------- -----------
1607656799728402900 1003 东 -2 11
1607656751612223600 1002 西 -2 11
1607656706278952000 999 南 -5 11
1607656662027484500 1001 南 -5 11
1607656595672442800 1000 东 -4 9
1607604432455278300 1001 南 -5 10
#按最早时间排序
> lect * from weather order by time asc
name: weather
time altitude area humidity temperature
---- -------- ---- -------- -----------
1607604432455278300 1001 南 -5 10
1607656595672442800 1000 东 -4 9
1607656662027484500 1001 南 -5 11
1607656706278952000 999 南 -5 11
1607656751612223600 1002 西 -2 11
1607656799728402900 1003 东 -2 11
4.去重 (distinct)
>lect distinct humidity from weather
name: weather
time distinct
---- --------
0-5
0-4
0-2
lect查询字段1，查询字段2,... from表名
where过滤条件
group by分组依据 # 分组后取出的是每个组的第⼀条数据
>lect*from weather group by area
name: weather
tags: area=东
内支撑time altitude humidity temperature
---- -------- -------- -----------
16076565956724428001000-49
16076567997284029001003-211
name: weather
tags: area=南
time altitude humidity temperature
---- -------- -------- -----------
16076044324552783001001-510
16076566620274845001001-511
1607656706278952000999-511
name: weather
tags: area=西
time altitude humidity temperature
---- -------- -------- -----------
16076567516122236001002-211
6.聚合
①count()函数
返回⼀个（field）字段中的⾮空值的数量。
SELECT COUNT(<field_key>) FROM <measurement_name> [WHERE <stuff>] [GROUP BY <stuff>]
>lect count(humidity) from weather
name: weather
time count
---- -----
贵反义词06
②MEAN() 函数
返回⼀个字段（field）中的值的算术平均值（平均值）。字段类型必须是长整型或float64。
语法格式：SELECT MEAN(<field_key>) FROM <measurement_name> [WHERE <stuff>] [GROUP BY <stuff>]
>SELECT MEAN(humidity) from weather
name: weather
time mean
---- ----
暑假日记0-3.8333333333333335
③MEDIAN()函数
从单个字段（field）中的排序值返回中间值（中位数）。中值是在⼀组数值中居于中间的数值。字段值的类型必须是长整型或float64格式。语法：SELECT MEDIAN(<field_key>) FROM <measurement_name> [WHERE <stuff>] [GROUP BY <stuff>]
>SELECT MEAN(humidity) from weather
name: weather
time mean
---- ----
0-3.8333333333333335
④SPREAD()函数
返回字段的最⼩值和最⼤值之间的差值。数据的类型必须是长整型或float64。
语法：SELECT SPREAD(<field_key>) FROM <measurement_name> [WHERE <stuff>] [GROUP BY <stuff>]
>lect spread(humidity) from weather
name: weather
time spread
---- ------
03
⑤SUM()函数
返回⼀个字段中的所有值的和。字段的类型必须是长整型或float64。
语法：SELECT SUM(<field_key>) FROM <measurement_name> [WHERE <stuff>] [GROUP BY <stuff>
>lect sum(humidity) from weather
name: weather
time sum
---- ---
0-23
⑥INTEGRAL()函数
返回曲线
语法：SELECT INTEGRAL( [ * | <field_key> | /<regular_expression>/ ] [ , <unit> ] ) [INTO_clau] FROM_clau [WHERE_clau] [GROUP_BY_clau] [ORDER_BY_clau] [LIMIT_clau] [OFFSET_clau] [SLIMIT_clau] [SOFFSET_clau]
>lect INTEGRAL(temperature) from weather
name: weather
time integral
---- --------
0497728.82358215
⑦distinc()函数
>lect distinct(temperature) from weather
name: weather
time distinct
---- --------
010
09
011
7.limit限制条数
情绪管理心得体会#显⽰⼀条信息
>lect*from weather limit 1
name: weather
绩效培训time altitude area humidity temperature
---- -------- ---- -------- -----------
16076044324552783001001南-510
#limit 10 offt 15，就是从第15⾏开始之后的10条数据
>lect*from weather limit 2 offt 2
name: weather
time altitude area humidity temperature
---- -------- ---- -------- -----------
16076566620274845001001南-511
1607656706278952000999南-511
<
influxDB中没有in的操作，但是有or。对于习惯了mysql的in来说，⽤or就需要在代码中循环了。
>lect*from weather where altitude=1001or temperature=11
name: weather
time altitude area humidity temperature
---- -------- ---- -------- -----------
16076566620274845001001南-511
1607656706278952000999南-511
16076567516122236001002西-211巨蟹座和什么座最配对
16076567997284029001003东-211
9.模糊查询
>lect*from test
name: test
time app count host monitor_name num ---- --- ----- ---- ------------ ---15858977039202900001127.0.0.1 test 1585897983909417000 ios 2127.0.0.1 test1 3 1585898383503216000 ios 2127.0.0.1 test1 3 1585901694441000000 ios 2127.0.0.1 app1
3 1585901704179677000 ios 2127.0.0.1 ios1 3 ## =~/给定字段/包含指定字段的
>lect*from test where monitor_name =~/app/ name: test
time app count host monitor_name num ---- --- ----- ---- ------------ ---1585901694441000000 ios 2127.0.0.1 app1 3 ##=~/^给定字段/以指定字段开始的
>lect*from test where monitor_name =~/^app/ name: test
time app count host monitor_name num ---- --- ----- ---- ------------ ---1585901694441000000 ios 2127.0.0.1 app1 3 ##=~/给定字段$/以指定字段结尾的
>lect*from test where monitor_name =~/p1$/ name: test
time app count host monitor_name num ---- --- ----- ---- ------------ ---1585901694441000000 ios 2127.0.0.1 app1 3
10.展⽰tag
> show tag keys from weather
name: weather
tagKey
------
altitude
area
#查询单个tag的value值
#查询所以tag为altitude的value的值
> show tag values from weather with key="altitude" name: weather
key value
--- -----
altitude 1000
altitude 1001
altitude 1002
altitude 1003
altitude 999
