SRE文档

深浅模式

一、query数据查询 ​

使用influx客户端命令时，建议加上"-precision rfc3339"参数，这样交互界面返回的时间将不再是timestamp，而是符合rfc339时间格式的时间戳"YYYY-MM-DDTHH:MM:SS.nnnnnnnnnZ)"

sql
[root@appman ~]# influx -precision rfc3339
Connected to http://localhost:8086 version 1.7.0
InfluxDB shell version: 1.7.0
Enter an InfluxQL query

> select * from k_LINKUSDT_5m;
name: k_LINKUSDT_5m
time                 close   high    low     open    times         val              vol
----                 -----   ----    ---     ----    -----         ---              ---
2022-04-28T09:05:00Z 12.9504 12.9505 12.9104 12.9104 1651136700000 1823533.27466635 141016.29297
2022-04-28T09:10:00Z 12.9604 12.9703 12.9504 12.9504 1651137000000 2389227.76983544 184418.367635


#或者，进入终端之后
[root@appman ~]# influx
Connected to http://localhost:8086 version 1.7.0
InfluxDB shell version: 1.7.0
Enter an InfluxQL query

> precision rfc3339
> select * from k_LINKUSDT_5m;
name: k_LINKUSDT_5m
time                 close   high    low     open    times         val              vol
----                 -----   ----    ---     ----    -----         ---              ---
2022-04-28T09:05:00Z 12.9504 12.9505 12.9104 12.9104 1651136700000 1823533.27466635 141016.29297
2022-04-28T09:10:00Z 12.9604 12.9703 12.9504 12.9504 1651137000000 2389227.76983544 184418.367635
[root@appman ~]# influx -precision rfc3339
Connected to http://localhost:8086 version 1.7.0
InfluxDB shell version: 1.7.0
Enter an InfluxQL query

> select * from k_LINKUSDT_5m;
name: k_LINKUSDT_5m
time                 close   high    low     open    times         val              vol
----                 -----   ----    ---     ----    -----         ---              ---
2022-04-28T09:05:00Z 12.9504 12.9505 12.9104 12.9104 1651136700000 1823533.27466635 141016.29297
2022-04-28T09:10:00Z 12.9604 12.9703 12.9504 12.9504 1651137000000 2389227.76983544 184418.367635


#或者，进入终端之后
[root@appman ~]# influx
Connected to http://localhost:8086 version 1.7.0
InfluxDB shell version: 1.7.0
Enter an InfluxQL query

> precision rfc3339
> select * from k_LINKUSDT_5m;
name: k_LINKUSDT_5m
time                 close   high    low     open    times         val              vol
----                 -----   ----    ---     ----    -----         ---              ---
2022-04-28T09:05:00Z 12.9504 12.9505 12.9104 12.9104 1651136700000 1823533.27466635 141016.29297
2022-04-28T09:10:00Z 12.9604 12.9703 12.9504 12.9504 1651137000000 2389227.76983544 184418.367635

1.1 基本查询 ​

  • 基本查询语法如下
SELECT <field_key>[,<field_key>,<tag_key>] FROM <measurement_name>[,<measurement_name>]
SELECT <field_key>[,<field_key>,<tag_key>] FROM <measurement_name>[,<measurement_name>]
  • 注解：
select语句

select * : 表示查询所有的field和tag对应的值
select field_key: 表示查询特定的field对应的值
select tag_key: 表示查询的特定的tag对应的值
SELECT "<field_key>"::field,"<tag_key>"::tag: 注意::field和::tag用来限定这个数据的类型为tag或者是field
from语句

from后面需要接上measurement，表示从这个mesaurement中查询数据

FROM <measurement_name> 从指定的measurement中获取数据
FROM <measurement_name>,<measurement_name> 从多个measurement中获取数据
FROM <database_name>.<retention_policy_name>.<measurement_name> 从某个数据库中某个保留策略中查询measurement中的数据
select语句

select * : 表示查询所有的field和tag对应的值
select field_key: 表示查询特定的field对应的值
select tag_key: 表示查询的特定的tag对应的值
SELECT "<field_key>"::field,"<tag_key>"::tag: 注意::field和::tag用来限定这个数据的类型为tag或者是field
from语句

from后面需要接上measurement，表示从这个mesaurement中查询数据

FROM <measurement_name> 从指定的measurement中获取数据
FROM <measurement_name>,<measurement_name> 从多个measurement中获取数据
FROM <database_name>.<retention_policy_name>.<measurement_name> 从某个数据库中某个保留策略中查询measurement中的数据
  • 列子
> show measurements;
name: measurements
name
----
add_test
userBaseInfo

> select * from add_test
name: add_test
time                email        name  phone user_id
----                -----        ----  ----- -------
1568603201523971883 test@126.com YiHui 110   20
1568603207102083370 test@126.com YiHui 110   21
1568603211019641828 test@126.com YiHui 110   22

> show tag keys from add_test
name: add_test
tagKey
------
name
phone
> show measurements;
name: measurements
name
----
add_test
userBaseInfo

> select * from add_test
name: add_test
time                email        name  phone user_id
----                -----        ----  ----- -------
1568603201523971883 test@126.com YiHui 110   20
1568603207102083370 test@126.com YiHui 110   21
1568603211019641828 test@126.com YiHui 110   22

> show tag keys from add_test
name: add_test
tagKey
------
name
phone

1.2 查询指定的field/tag的方式 ​

> select email::field ,"name"::tag from add_test
name: add_test
time                email        name
----                -----        ----
1568603201523971883 test@126.com YiHui
1568603207102083370 test@126.com YiHui
1568603211019641828 test@126.com YiHui
> select email::field ,"name"::tag from add_test
name: add_test
time                email        name
----                -----        ----
1568603201523971883 test@126.com YiHui
1568603207102083370 test@126.com YiHui
1568603211019641828 test@126.com YiHui

1.3 保留策略数据查询 ​

# 创建保留策略
> create retention policy "1D" duration 1d on test

# 插入一条数据
> insert into "1D" yhh,name=二灰,phone=119 email="test@126.com",blog="http://sss.hhui.top",id=27

# 查询
> select * from "1D".yhh
name: yhh
time                blog                   email            id name phone
----                ----                   -----            -- ---- -----
1565693045801509796 http://sss.top test@126.com 27 二灰   119
>
# 创建保留策略
> create retention policy "1D" duration 1d on test

# 插入一条数据
> insert into "1D" yhh,name=二灰,phone=119 email="test@126.com",blog="http://sss.hhui.top",id=27

# 查询
> select * from "1D".yhh
name: yhh
time                blog                   email            id name phone
----                ----                   -----            -- ---- -----
1565693045801509796 http://sss.top test@126.com 27 二灰   119
>

唯一需要注意的是measurement前面需要加上保留策略

二、Where语句 ​

  • 语法：
SELECT_clause FROM_clause WHERE <conditional_expression> [(AND|OR) <conditional_expression> [...]]
SELECT_clause FROM_clause WHERE <conditional_expression> [(AND|OR) <conditional_expression> [...]]

where后面的条件表达式，因为influxdb中的数据可以划分为两类，这两种不同的类型，在构建查询语句的时候，会有一些区别

2.1 field查询条件 ​

  • 我们已知field的类型有四种：string|int|boolean|float，所以它支持的操作符有
操作符说明
=相等
<>, !=不相同
>, >=大于,大于等于
<, <=小于,小于等于

2.2 tag查询条件 ​

  • 在influxdb中tag都是string类型，会建立索引，所以基于tag的查询效率一般来讲是优于field查询的，它支持的操作符为
操作符说明
=相等
<>, !=不相同

在influxdb中没有in查询，不同的查询条件可以使用and/or来连接，表示同时满足or一个满足即可

# 根据field进行查询
> select * from yhh where age=26

# 根据tag进行查询
> select * from yhh where phone!=''

# 简单的运算查询
> select * from yhh where age + 2>30
# 根据field进行查询
> select * from yhh where age=26

# 根据tag进行查询
> select * from yhh where phone!=''

# 简单的运算查询
> select * from yhh where age + 2>30
  • 查询语句中，推荐的写法是

tag key或field key请使用双引号括起来 如果类型为string，请用单引号把过滤条件括起来

2.3 or ​

> select * from weather where altitude=1001 or 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
> select * from weather where altitude=1001 or 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

2.4 模糊查询 ​

bash
> select * from test
name: test
time                app count host      monitor_name num
----                --- ----- ----      ------------ ---
1585897703920290000     1     127.0.0.1 test         
1585897983909417000 ios 2     127.0.0.1 test1        3
1585898383503216000 ios 2     127.0.0.1 test1        3
1585901694441000000 ios 2     127.0.0.1 app1         3
1585901704179677000 ios 2     127.0.0.1 ios1         3

## =~/给定字段/ 包含指定字段的
> select * from test where monitor_name =~/app/
name: test
time                app count host      monitor_name num
----                --- ----- ----      ------------ ---
1585901694441000000 ios 2     127.0.0.1 app1         3


#=~/^给定字段/ 以指定字段开始的
> select * from test where monitor_name =~/^app/
name: test
time                app count host      monitor_name num
----                --- ----- ----      ------------ ---
1585901694441000000 ios 2     127.0.0.1 app1         3

#=~/给定字段$/ 以指定字段结尾的
> select * from test where monitor_name =~/p1$/
name: test
time                app count host      monitor_name num
----                --- ----- ----      ------------ ---
1585901694441000000 ios 2     127.0.0.1 app1         3
> select * from test
name: test
time                app count host      monitor_name num
----                --- ----- ----      ------------ ---
1585897703920290000     1     127.0.0.1 test         
1585897983909417000 ios 2     127.0.0.1 test1        3
1585898383503216000 ios 2     127.0.0.1 test1        3
1585901694441000000 ios 2     127.0.0.1 app1         3
1585901704179677000 ios 2     127.0.0.1 ios1         3

## =~/给定字段/ 包含指定字段的
> select * from test where monitor_name =~/app/
name: test
time                app count host      monitor_name num
----                --- ----- ----      ------------ ---
1585901694441000000 ios 2     127.0.0.1 app1         3


#=~/^给定字段/ 以指定字段开始的
> select * from test where monitor_name =~/^app/
name: test
time                app count host      monitor_name num
----                --- ----- ----      ------------ ---
1585901694441000000 ios 2     127.0.0.1 app1         3

#=~/给定字段$/ 以指定字段结尾的
> select * from test where monitor_name =~/p1$/
name: test
time                app count host      monitor_name num
----                --- ----- ----      ------------ ---
1585901694441000000 ios 2     127.0.0.1 app1         3

三、分组查询 ​

  • 语法：nfluxdb sql的分组也是使用group by语句
SELECT_clause FROM_clause [WHERE_clause] GROUP BY [* | <tag_key>[,<tag_key]]
SELECT_clause FROM_clause [WHERE_clause] GROUP BY [* | <tag_key>[,<tag_key]]

3.1 group by tag ​

  • 用来分组的必须是tag，也就是说对于influxdb而言，不支持根据field进行分组
#查看tag
> show tag keys from add_test
name: add_test
tagKey
------
name
phone


> select * from add_test group by phone
name: add_test
tags: phone=110
time                email        name  user_id
----                -----        ----  -------
1568603201523971883 test@126.com YiHui 20
1568603207102083370 test@126.com YiHui 21
1568603211019641828 test@126.com YiHui 22
#查看tag
> show tag keys from add_test
name: add_test
tagKey
------
name
phone


> select * from add_test group by phone
name: add_test
tags: phone=110
time                email        name  user_id
----                -----        ----  -------
1568603201523971883 test@126.com YiHui 20
1568603207102083370 test@126.com YiHui 21
1568603211019641828 test@126.com YiHui 22

3.2 group by * ​

  • 另外一个与一般SQL语法不一样的是group by 后面可以跟上*，表示根据所有的tag进行分组
> select * from add_test group by *
name: add_test
tags: name=YiHui, phone=110
time                email        user_id
----                -----        -------
1568603201523971883 test@126.com 20
1568603207102083370 test@126.com 21
1568603211019641828 test@126.com 22
> select * from add_test group by *
name: add_test
tags: name=YiHui, phone=110
time                email        user_id
----                -----        -------
1568603201523971883 test@126.com 20
1568603207102083370 test@126.com 21
1568603211019641828 test@126.com 22

3.3 group by time ​

  • 语法：
SELECT <function>(<field_key>) FROM_clause WHERE <time_range> GROUP BY time(<time_interval>),[tag_key] [fill(<fill_option>)]
SELECT <function>(<field_key>) FROM_clause WHERE <time_range> GROUP BY time(<time_interval>),[tag_key] [fill(<fill_option>)]
# 为了显示方便，将数据的时间戳改成日期方式展示
> precision rfc3339

> select * from add_test;
name: add_test
time                email        name  phone user_id
----                -----        ----  ----- -------
1568603201523971883 test@126.com YiHui 110   20
1568603207102083370 test@126.com YiHui 110   21
1568603211019641828 test@126.com YiHui 110   22

#只是临时修改
> precision rfc3339
> 
> select * from add_test;
name: add_test
time                           email        name  phone user_id
----                           -----        ----  ----- -------
2019-09-16T03:06:41.523971883Z test@126.com YiHui 110   20
2019-09-16T03:06:47.10208337Z  test@126.com YiHui 110   21
2019-09-16T03:06:51.019641828Z test@126.com YiHui 110   22

> select count(*) from yhh where time>'2019-09-16T03:06:41.523971883Z' and time<'2019-07-23T13:50:43.440000821Z'  GROUP BY time(2m)
# 为了显示方便，将数据的时间戳改成日期方式展示
> precision rfc3339

> select * from add_test;
name: add_test
time                email        name  phone user_id
----                -----        ----  ----- -------
1568603201523971883 test@126.com YiHui 110   20
1568603207102083370 test@126.com YiHui 110   21
1568603211019641828 test@126.com YiHui 110   22

#只是临时修改
> precision rfc3339
> 
> select * from add_test;
name: add_test
time                           email        name  phone user_id
----                           -----        ----  ----- -------
2019-09-16T03:06:41.523971883Z test@126.com YiHui 110   20
2019-09-16T03:06:47.10208337Z  test@126.com YiHui 110   21
2019-09-16T03:06:51.019641828Z test@126.com YiHui 110   22

> select count(*) from yhh where time>'2019-09-16T03:06:41.523971883Z' and time<'2019-07-23T13:50:43.440000821Z'  GROUP BY time(2m)

  • select后面跟上的是单个or多个field的聚合操作，根据时间进行分组时，不允许查询具体的field值

  • where条件限定查询的时间范围，否则会得到很多数据

  • group by time(2m) 表示每2分钟做一个分组， group by time(2s)则表示每2s做一个分组

> select * from yhh where time>'2019-07-23T13:44:38.654374538Z' and time<'2019-07-23T13:50:43.440000821Z'  GROUP BY time(2m)
ERR: GROUP BY requires at least one aggregate function
> select * from yhh where time>'2019-07-23T13:44:38.654374538Z' and time<'2019-07-23T13:50:43.440000821Z'  GROUP BY time(2m)
ERR: GROUP BY requires at least one aggregate function

四、排序 ​

  • 在influxdb中排序，只支持针对time进行排序，其他的field，tag（因为是string类型，也没法排）是不能进行排序的

  • 语法： 根据时间倒序/升序

order by time desc/asc
order by time desc/asc
## 根据时间进行倒排(从高往下)
> select * from add_test order by time desc
name: add_test
time                           email        name  phone user_id
----                           -----        ----  ----- -------
2019-09-16T03:06:51.019641828Z test@126.com YiHui 110   22
2019-09-16T03:06:47.10208337Z  test@126.com YiHui 110   21
2019-09-16T03:06:41.523971883Z test@126.com YiHui 110   20
## 根据时间进行倒排(从高往下)
> select * from add_test order by time desc
name: add_test
time                           email        name  phone user_id
----                           -----        ----  ----- -------
2019-09-16T03:06:51.019641828Z test@126.com YiHui 110   22
2019-09-16T03:06:47.10208337Z  test@126.com YiHui 110   21
2019-09-16T03:06:41.523971883Z test@126.com YiHui 110   20

4.1 查询限制 ​

  • 我们常见的分页就是limit语句，我们常见的limit语句为 limit page, size，可以实现分页；然而在influxdb中则不同，limit后面只能跟上一个数字，表示限定查询的最多条数

  • limit

SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] LIMIT <N>

N指定每次measurement返回的point个数
SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] LIMIT <N>

N指定每次measurement返回的point个数
> select * from add_test limit 2
name: add_test
time                           email        name  phone user_id
----                           -----        ----  ----- -------
2019-09-16T03:06:41.523971883Z test@126.com YiHui 110   20
2019-09-16T03:06:47.10208337Z  test@126.com YiHui 110   21




#显示一条信息
> select * from weather limit 1
name: weather
time                altitude area humidity temperature
----                -------- ---- -------- -----------
1607604432455278300 1001     南    -5       10

#limit 10 offset 15，就是从第15行开始之后的10条数据
> select * from weather limit 2 offset 2
name: weather
time                altitude area humidity temperature
----                -------- ---- -------- -----------
1607656662027484500 1001     南    -5       11
1607656706278952000 999      南    -5       11
> select * from add_test limit 2
name: add_test
time                           email        name  phone user_id
----                           -----        ----  ----- -------
2019-09-16T03:06:41.523971883Z test@126.com YiHui 110   20
2019-09-16T03:06:47.10208337Z  test@126.com YiHui 110   21




#显示一条信息
> select * from weather limit 1
name: weather
time                altitude area humidity temperature
----                -------- ---- -------- -----------
1607604432455278300 1001     南    -5       10

#limit 10 offset 15，就是从第15行开始之后的10条数据
> select * from weather limit 2 offset 2
name: weather
time                altitude area humidity temperature
----                -------- ---- -------- -----------
1607656662027484500 1001     南    -5       11
1607656706278952000 999      南    -5       11
  • 分组之后，再限定查询条数
> select * from yhh group by "name" limit 1
> select * from yhh group by "name" limit 1

4.2 slimit ​

  • N指定从指定measurement返回的series数

  • 语法

SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] GROUP BY *[,time(<time_interval>)] [ORDER_BY_clause] SLIMIT <N>
SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] GROUP BY *[,time(<time_interval>)] [ORDER_BY_clause] SLIMIT <N>
> select * from add_test group by * slimit 3
name: add_test
tags: name=YiHui, phone=110
time                           email        user_id
----                           -----        -------
2019-09-16T03:06:41.523971883Z test@126.com 20
2019-09-16T03:06:47.10208337Z  test@126.com 21
2019-09-16T03:06:51.019641828Z test@126.com 22
> select * from add_test group by * slimit 3
name: add_test
tags: name=YiHui, phone=110
time                           email        user_id
----                           -----        -------
2019-09-16T03:06:41.523971883Z test@126.com 20
2019-09-16T03:06:47.10208337Z  test@126.com 21
2019-09-16T03:06:51.019641828Z test@126.com 22

slimit 有待于查看

4.3 分页 ​

  • 语法： offset来实现分页
SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] LIMIT_clause OFFSET <N> [SLIMIT_clause]
SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] LIMIT_clause OFFSET <N> [SLIMIT_clause]
# 查询结果只有2条数据，从第三个开始（0开始计数）
> select * from add_test limit 2 offset 3
# 查询结果只有2条数据，从第三个开始（0开始计数）
> select * from add_test limit 2 offset 3

4.4 查询数据库大小 ​

You can see the approximate size of the InfluxDB data directory by running the following query against the _internal databases shard measurement:

select sum(diskBytes) / 1024 / 1024 /1024 from _internal."monitor"."shard" where time > now() - 10s
select sum(diskBytes) / 1024 / 1024 /1024 from _internal."monitor"."shard" where time > now() - 10s

The result will be in gigabytes. You can also see the size per database by adding a group by "database":

>select sum(diskBytes) / 1024 / 1024 /1024 from _internal."monitor"."shard" where time > now() - 10s group by "database"

name: shard
tags: database=_internal
time                sum
----                ---
1568618563361835183 0.04565209522843361

name: shard
tags: database=hello
time                sum
----                ---
1568618563361835183 8.130446076393127e-7

name: shard
tags: database=test
time                sum
----                ---
1568618563361835183 0.0000015720725059509277
>select sum(diskBytes) / 1024 / 1024 /1024 from _internal."monitor"."shard" where time > now() - 10s group by "database"

name: shard
tags: database=_internal
time                sum
----                ---
1568618563361835183 0.04565209522843361

name: shard
tags: database=hello
time                sum
----                ---
1568618563361835183 8.130446076393127e-7

name: shard
tags: database=test
time                sum
----                ---
1568618563361835183 0.0000015720725059509277

6.聚合 ​

https://docs.influxdata.com/influxdb/v1.7/query_language/functions/

聚合类 ​

函数描述语法
COUNT()返回一个字段中的非空值的数量#SELECT COUNT( [ * |<field_key> |/<regular_expression>/ ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]#
DISTINCT()返回一个字段去重后的唯一值<SELECT DISTINCT( [ * |<field_key> |/<regular_expression>/ ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]>
INTEGRAL()返回曲线下面的字段值

https://blog.csdn.net/yue530tomtom/article/details/82688453

6.1count()函数 ​

返回一个（field）字段中的非空值的数量

SELECT COUNT(<field_key>) FROM <measurement_name> [WHERE <stuff>] [GROUP BY <stuff>]



> select count(humidity) from weather
name: weather
time count
---- -----
0    6
SELECT COUNT(<field_key>) FROM <measurement_name> [WHERE <stuff>] [GROUP BY <stuff>]



> select count(humidity) from weather
name: weather
time count
---- -----
0    6

6.2MEAN() 函数 ​

返回一个字段（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
> SELECT MEAN(humidity) from weather
name: weather
time mean
---- ----
0    -3.8333333333333335

6.3MEDIAN()函数 ​

从单个字段（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
> SELECT MEAN(humidity) from weather
name: weather
time mean
---- ----
0    -3.8333333333333335

6.4SPREAD()函数 ​

返回字段的最小值和最大值之间的差值。数据的类型必须是长整型或float64

语法：SELECT SPREAD(<field_key>) FROM <measurement_name> [WHERE <stuff>] [GROUP BY <stuff>]

> select spread(humidity) from weather
name: weather
time spread
---- ------
0    3
> select spread(humidity) from weather
name: weather
time spread
---- ------
0    3

6.5SUM()函数 ​

返回一个字段中的所有值的和。字段的类型必须是长整型或float64。

语法：SELECT SUM(<field_key>) FROM <measurement_name> [WHERE <stuff>] [GROUP BY <stuff>

> select sum(humidity) from weather
name: weather
time sum
---- ---
0    -23
> select sum(humidity) from weather
name: weather
time sum
---- ---
0    -23

6.6INTEGRAL()函数 ​

返回曲线

语法：SELECT INTEGRAL( [ * | <field_key> | /<regular_expression>/ ] [ , <unit> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]`` 

> select INTEGRAL(temperature) from weather
name: weather
time integral
---- --------
0    497728.82358215
> select INTEGRAL(temperature) from weather
name: weather
time integral
---- --------
0    497728.82358215

6.7distinc()函数 ​

去重

> select distinct(temperature) from weather
name: weather
time distinct
---- --------
0    10
0    9
0    11
> select distinct(temperature) from weather
name: weather
time distinct
---- --------
0    10
0    9
0    11

7.连续查询 ​

当数据超过保存策略里指定的时间之后就会被删除，但是这时候可能并不想数据被完全删掉，怎么办？ influxdb提供了联系查询，可以做数据统计采样。

  • 查看数据库的Continous Queries
show continuous queries
show continuous queries
  • 创建新的Continous Queries
create continous query cq_name on db_name begin select sum(count) into new_table_name from table_name group by time(30m) end
create continous query cq_name on db_name begin select sum(count) into new_table_name from table_name group by time(30m) end
- cq_name：连续查询名字；
- db_name：数据库名字；
- sum(count)：计算总和；
- table_name：当前表名；
- new_table_name：存新的数据的表名；
- 30m：时间间隔为30分钟
- cq_name：连续查询名字；
- db_name：数据库名字；
- sum(count)：计算总和；
- table_name：当前表名；
- new_table_name：存新的数据的表名；
- 30m：时间间隔为30分钟
  • 删除Continous Queries
drop continous query cp_name on db_name
drop continous query cp_name on db_name

8.其他操作 ​

7.1查询操作 ​

#----综合使用
书写顺序
select distinct * from '表名' where '限制条件'  group by '分组依据' having '过滤条件' order by  limit '展示条数'
执行顺序
from       -- 查询
where      -- 限制条件
group by   -- 分组
having     -- 过滤条件
order by   -- 排序
limit      -- 展示条数
distinct   -- 去重
select     -- 查询的结果
#----综合使用
书写顺序
select distinct * from '表名' where '限制条件'  group by '分组依据' having '过滤条件' order by  limit '展示条数'
执行顺序
from       -- 查询
where      -- 限制条件
group by   -- 分组
having     -- 过滤条件
order by   -- 排序
limit      -- 展示条数
distinct   -- 去重
select     -- 查询的结果
#查询最新表数据
select * from talbe_name order by time desc limit 2;
#查询最新表数据
select * from talbe_name order by time desc limit 2;
SHOW FIELD KEYS --查看当前数据库所有表的字段

SHOW series from pay --查看key数据

SHOW TAG KEYS FROM "pay" --查看key中tag key值

SHOW TAG VALUES FROM "pay" WITH KEY = "merId" --查看key中tag 指定key值对应的值

SHOW TAG VALUES FROM cpu WITH KEY IN ("region", "host") WHERE service = 'redis'

DROP SERIES FROM <measurement_name[,measurement_name]> WHERE <tag_key>='<tag_value>' --删除key

SHOW CONTINUOUS QUERIES   --查看连续执行命令

SHOW QUERIES  --查看最后执行命令

KILL QUERY <qid> --结束命令

SHOW RETENTION POLICIES ON mydb  --查看保留数据

查询数据
SELECT * FROM /.*/ LIMIT 1  --查询当前数据库下所有表的第一行记录
select * from pay  order by time desc limit 2
select * from  db_name."POLICIES name".measurement_name --指定查询数据库下数据保留中的表数据 POLICIES name数据保留

删除数据
delete from "query" --删除表所有数据，则表就不存在了
drop MEASUREMENT "query"   --删除表（注意会把数据保留删除使用delete不会）
DELETE FROM cpu
DELETE FROM cpu WHERE time < '2000-01-01T00:00:00Z'
DELETE WHERE time < '2000-01-01T00:00:00Z'
DROP DATABASE “testDB” --删除数据库
DROP RETENTION POLICY "dbbak" ON mydb --删除保留数据为dbbak数据
DROP SERIES from pay where tag_key='' --删除key中的tag

SHOW SHARDS  --查看数据存储文件
DROP SHARD 1
SHOW SHARD GROUPS
SHOW SUBSCRIPTIONS
SHOW FIELD KEYS --查看当前数据库所有表的字段

SHOW series from pay --查看key数据

SHOW TAG KEYS FROM "pay" --查看key中tag key值

SHOW TAG VALUES FROM "pay" WITH KEY = "merId" --查看key中tag 指定key值对应的值

SHOW TAG VALUES FROM cpu WITH KEY IN ("region", "host") WHERE service = 'redis'

DROP SERIES FROM <measurement_name[,measurement_name]> WHERE <tag_key>='<tag_value>' --删除key

SHOW CONTINUOUS QUERIES   --查看连续执行命令

SHOW QUERIES  --查看最后执行命令

KILL QUERY <qid> --结束命令

SHOW RETENTION POLICIES ON mydb  --查看保留数据

查询数据
SELECT * FROM /.*/ LIMIT 1  --查询当前数据库下所有表的第一行记录
select * from pay  order by time desc limit 2
select * from  db_name."POLICIES name".measurement_name --指定查询数据库下数据保留中的表数据 POLICIES name数据保留

删除数据
delete from "query" --删除表所有数据，则表就不存在了
drop MEASUREMENT "query"   --删除表（注意会把数据保留删除使用delete不会）
DELETE FROM cpu
DELETE FROM cpu WHERE time < '2000-01-01T00:00:00Z'
DELETE WHERE time < '2000-01-01T00:00:00Z'
DROP DATABASE “testDB” --删除数据库
DROP RETENTION POLICY "dbbak" ON mydb --删除保留数据为dbbak数据
DROP SERIES from pay where tag_key='' --删除key中的tag

SHOW SHARDS  --查看数据存储文件
DROP SHARD 1
SHOW SHARD GROUPS
SHOW SUBSCRIPTIONS

https://blog.csdn.net/vtnews/article/details/80197045?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522165132454816781432975844%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=165132454816781432975844&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_ecpm_v1~rank_v31_ecpm-13-80197045.142^v9^pc_search_result_cache,157^v4^new_style&utm_term=InfluxDB+DTDB+对比&spm=1018.2226.3001.4187