influxdb基础（七）——select查询基础语法（类sql，细节与踩坑总结）

布满荆棘的人生 2022-12-19 13:13 951阅读 0赞

### 文章目录 ###

*  一、前言
 *  二、导入官方示例数据
 *  三、SELECT基本语法
 *   *  1、简单查询示例数据
     *  2、没有in还有or
     *  3、模糊查询
     *  4、group by
     *   *  （1）group by tag
         *  （2）group by time(1m)
         *  （3）fill(0)填充null
     *  5、order by
     *  6、limit和slimit
     *  7、from多表查询
 *  四、inflxudb保留字
 *  五、总结

# 一、前言 #

`influxdb`的查询语法在基本组成上和mysql非常相似，都有基本的`select … from …`、`group by`、`order by`、`limit`、聚合函数等，在一定程度上降低了`influxdb`的学习门槛。但是也有很多细节需要注意，比如：

1.  `group by`后面只能对`tags`和`time`分组。
2.  `order by` 只能对`time`进行排序。
3.  聚合函数只能作用于`fields`。
4.  `limit`语法也不太一样。

友情提示：篇幅冗长，所有的细节可直接看总结就完事了！

# 二、导入官方示例数据 #

`inflxudb`官方提供了[示例数据][Link 1]供使用者模拟查询。

1、首先将示例数据下载到机器本地：

# 将示例数据下载到某个地方
    curl https://s3.amazonaws.com/noaa.water-database/NOAA_data.txt -o NOAA_data.txt

2、导入数据

# 如果influx 设置了权限验证，需要加上 -username和-password。
    # NOAA_data.txt中已包含创建数据库NOAA_water_database的语句。
    influx -import -path=NOAA_data.txt -precision=s -database=NOAA_water_database

# 三、SELECT基本语法 #

SELECT field keys [time | tag kyes | * ]  FROM measurements WHERE conditions GROUP BY [tag keys | time] ORDER BY time [asc | desc] LIMIT number [OFFSET number]

*  `SELECT`后面查询显示字段**必须至少有一个`field key`**，否则会抛异常`ERR: at least 1 non-time field must be queried`。还可显示`tag keys`、`time`，或者`*`显示所有字段。
 *  `FROM`查询数据来源一个或者多个`measurement`。
 *  `WHERE`查询条件可为`tag keys`和`time`，`field keys` 也可作为查询条件但是不常用，因为不是索引，查询效率比较低。`tag keys`条件操作符支持`=、!=、<>、正则`，`field keys`支持`=、!=、<>、>、>=、<、<=、正则`。
 *  `GROUP BY`只能对`tag keys`和`time`进行合分组，可以多字段排序，如`group by tag1,tag2,time`，也可一个`*`对所有的`tag`进行分组聚合（不包括`time`）。
 *  `ORDER BY`只能对`time`进行排序，`asc`升序，`desc`降序。
 *  `LIMIT`分页，`LIMIT`后面的数字是查询显示多少条，`OFFSET`后面数字代表偏移量（从0开始代表第一条），如`limit 10 offset 10`意思为从第11条开始往后10条。

## 1、简单查询示例数据 ##

现在开始简单查询之前导入的示例数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70_pic_center]

（1）查询average\_temperature的数据。

# 因为数据量比较大，配合limit防止返回大量数据。
    select * from average_temperature limit 2

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70_pic_center 1]  
（2）查询`average_temperature`，`location`为`coyote_creek`的数据，字段只显示`time`和`degrees`。

select time, degrees from average_temperature where location='coyote_creek' limit 2

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70_pic_center 2]

上面的数据显示格式是column，且时间是时间戳，前面的文章也介绍过，可执行如下指令进行设置：

# 输入json格式
    format json
    # 让json格式输出更好看
    pretty
    # 格式化时间显示
    precision rfc3339

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70_pic_center 3]

## 2、没有in还有or ##

`where`后面没有`in`来范围查询，但是有`or`也可以做到异曲同工之妙。

select * from h2o_pH where location='coyote_creek' or location='santa_monica' limit 4

## 3、模糊查询 ##

`tag keys`和`field keys`都支持正则，可通过如下正则达到模糊查询的效果。

# location名称中包含coyote的数据
    select * from h2o_quality where location=~/.*coyote.*/ limit 10

## 4、group by ##

### （1）group by tag ###

对`location`分组查询，返回结果是一个大`results`对象结果集内包含两个`series`，第一个`series`是`location`为`coyote_creek`的聚合结果集，第二个`series`是`location`为`santa_monica`的聚合结果集。

select * from h2o_pH group by location limit 2
    # 查询结果显示
    {
        "results": [
            {
                "series": [
                    {
                        "name": "h2o_pH",
                        "tags": {
                            "location": "coyote_creek"
                        },
                        "columns": [
                            "time",
                            "pH"
                        ],
                        "values": [
                            [
                                "2019-08-17T00:00:00Z",
                                7
                            ],
                            [
                                "2019-08-17T00:06:00Z",
                                8
                            ]
                        ]
                    }
                ]
            },
            {
                "series": [
                    {
                        "name": "h2o_pH",
                        "tags": {
                            "location": "santa_monica"
                        },
                        "columns": [
                            "time",
                            "pH"
                        ],
                        "values": [
                            [
                                "2019-08-17T00:00:00Z",
                                6
                            ],
                            [
                                "2019-08-17T00:06:00Z",
                                6
                            ]
                        ]
                    }
                ]
            }
        ]
    }

### （2）group by time(1m) ###

对`time`分组时并不是简单的`group by time`，`time`后面还需要加一个分组聚合的持续时间，如`group by time(1m)`。支持的持续时间单位有：

<table> 
 <thead> 
  <tr> 
   <th>单位</th> 
   <th>备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>u or µ</td> 
   <td>microseconds (1 millionth of a second)</td> 
  </tr> 
  <tr> 
   <td>ms</td> 
   <td>milliseconds (1 thousandth of a second)</td> 
  </tr> 
  <tr> 
   <td>s</td> 
   <td>second</td> 
  </tr> 
  <tr> 
   <td>m</td> 
   <td>minute</td> 
  </tr> 
  <tr> 
   <td>h</td> 
   <td>hour</td> 
  </tr> 
  <tr> 
   <td>d</td> 
   <td>day</td> 
  </tr> 
  <tr> 
   <td>w</td> 
   <td>week</td> 
  </tr> 
 </tbody> 
</table>

对`time`进行分组，查询结果必须对`field`使用聚合函数，如`count()`、`sum()`、`max()`、`min()`等。

select sum(pH) from h2o_pH  group by time(1m) limit 4
    # 查询结果显示
    {
        "results": [
            {
                "series": [
                    {
                        "name": "h2o_pH",
                        "columns": [
                            "time",
                            "sum"
                        ],
                        "values": [
                            [
                                "2019-08-17T08:00:00+08:00",
                                13
                            ],
                            [
                                "2019-08-17T08:01:00+08:00",
                                null
                            ]
                        ]
                    }
                ]
            }
        ]
    }

由于`Influxdb` 时区默认是`UTC`，若以一天进行数据聚合，需要加上便宜量 -8h 进行聚合：

select sum(pH) from h2o_pH  group by time(1d,-8h) fill(0) limit 4

### （3）fill(0)填充null ###

可以看到`values`结果集中有`null`的情况，可在查询语句中加`fill(0)`，遇到`null`用`0`来填充。`fill()`中只能填数字。

## 5、order by ##

`order by` 只能对`time`进行排序，`asc`升序，`desc`降序。

select sum(pH) from h2o_pH  group by time(1m) fill(0) order by time asc limit 2

## 6、limit和slimit ##

`limit`可单独使用，也可配合`offset`使用，`offset`偏移量的意思。

select sum(pH) from h2o_pH  group by time(1h) fill(0) order by time asc limit 2 offset 10

`select`进行多个`tag`分组后，会返回多个`series`，可使用`slmit`限制`series`返回的个数。`slimit`不像`limit`有偏移量`offset`，但是可以配合`limit`一起使用：

# 查询显示 前两个series，且每个series里显示2条数据，偏移量10
    select * from h2o_quality group by * limit 2 offset 10 slimit 2

## 7、from多表查询 ##

多表查询，以时间进行连接，不存在的值用null填充。一般情况连接的两张表tag和field上有一定的联系和比较，毫不相干的两表连接查询没什么价值。

select * from h2o_feet,h2o_pH limit 1
    {
        "results": [
            {
                "series": [
                    {
                        "name": "h2o_feet",
                        "columns": [
                            "time",
                            "level description",
                            "location",
                            "pH",
                            "water_level"
                        ],
                        "values": [
                            [
                                "2019-08-17T00:00:00Z",
                                "below 3 feet",
                                "santa_monica",
                                null,
                                2.064
                            ]
                        ]
                    }
                ]
            },
            {
                "series": [
                    {
                        "name": "h2o_pH",
                        "columns": [
                            "time",
                            "level description",
                            "location",
                            "pH",
                            "water_level"
                        ],
                        "values": [
                            [
                                "2019-08-17T00:00:00Z",
                                null,
                                "coyote_creek",
                                7,
                                null
                            ]
                        ]
                    }
                ]
            }
        ]
    }

# 四、inflxudb保留字 #

在创建`database`、`measurements`以及`tag keys`、`field keys`时，尽量避开`inflxudb`的保留关键字，否则需要在select和insert语法上对保留字加英文双引号。

ALL           ALTER         ANALYZE       ANY           AS            ASC
    BEGIN         BY            CREATE        CONTINUOUS    DATABASE      DATABASES
    DEFAULT       DELETE        DESC          DESTINATIONS  DIAGNOSTICS   DISTINCT
    DROP          DURATION      END           EVERY         EXPLAIN       FIELD
    FOR           FROM          GRANT         GRANTS        GROUP         GROUPS
    IN            INF           INSERT        INTO          KEY           KEYS
    KILL          LIMIT         SHOW          MEASUREMENT   MEASUREMENTS  NAME
    OFFSET        ON            ORDER         PASSWORD      POLICY        POLICIES
    PRIVILEGES    QUERIES       QUERY         READ          REPLICATION   RESAMPLE
    RETENTION     REVOKE        SELECT        SERIES        SET           SHARD
    SHARDS        SLIMIT        SOFFSET       STATS         SUBSCRIPTION  SUBSCRIPTIONS
    TAG           TO            USER          USERS         VALUES        WHERE
    WITH          WRITE

例如`inflxudb`有一个内部数据库`_internal`，专门用来记录统计`influxdb`运作数据的。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70_pic_center 4]  
查询`shard`和`write`表时，必须将其用英文双引号括起来。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70_pic_center 5]

# 五、总结 #

1.  `select`后面显示的字段必须至少包含一个`field key`。
2.  `select`没有`in`但是有`or`代替效果。
3.  模糊查询可使用正则`~/.*xxx.*/`，xxx为需要模糊匹配的字符串。
4.  `group by` 只能对`tag keys` 和`time`分组，`group by *` 对所有`tag keys`分组，不包括`time`。
5.  `group by` 对time分组聚合，需要注意两点，查询结果需要对field使用聚合函数，time后面需要加分组聚合的持续时间，完整语法如：`select sum(field) from measurements group by time(1m)`。
6.  `group by tag, time(1m)`不管tag和time顺序如何，都是先对tag进行分组聚合，然后对每个`series`进行`time`分组聚合。
7.  分组聚合后难免有某个时间点的数据为`null`，可用`fill(0)`进行替换填充。
8.  `order by`只能对`time`排序。
9.  `limit offset`可实现分页效果，`limit n offset m`,偏移量m ，代表从m+1行开始返回n条数据。
10. `slimit`限制分组聚合series返回个数，可配合limit使用，如 `limit 2 offset 10 slimit 2`，表示只返回两个`series`，每个`series`返回2条数据，偏移量为10。（在位置上`slimit`必须在`limit`后面）
11. `from`后面跟多个`measurements`，达到多表连接查询的效果。
12. 如若`database`、`measurements`以及`tag keys`、`field keys`中有`influxdb`的保留关键词，语法中需要用英文双引号括起来。但是还是尽量避免使用`influxdb`的保留关键词进行命名。

PS: **如若文章中有错误理解，欢迎批评指正，同时非常期待你的评论、点赞和收藏。我是徐同学，愿与你共同进步！**

[Link 1]: https://docs.influxdata.com/influxdb/v1.8/query_language/sample-data/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/296a3c94bc1647d9a4fd97f1eaa46a22.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/44150aae397b486c9f295c67dd37895a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20201107201252960.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20201107201333732.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70_pic_center 4]: https://img-blog.csdnimg.cn/20201107201610891.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70_pic_center 5]: https://img-blog.csdnimg.cn/20201107201627950.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA==,size_16,color_FFFFFF,t_70#pic_center