关于influxdb:InfluxDB-influxQL的group和fill

group是influxQL中常见的聚合函数，罕用于按工夫聚合(一段时间内的最大/最小/均匀)；若在聚合时没有足够的数据点，可指定fill填充特定的值。

group和fill的语法

SELECT <function>(<field_key>) FROM_clause 
WHERE <time_range> 
GROUP BY time(time_interval,[<offset_interval])[,tag_key] [fill(<fill_option>)]

fill()用于填充没有数据的时序序列的值，其选项为：

• null: 默认，显示工夫戳但value=null的序列；
• none：在后果中不显示该工夫戳的序列；
• 数值：fill(0)，填充0；
• linear: 线性插入数值；
• previous: 填充前一个序列的值；

demo

原始数据：两头短少了09:05:00工夫点的数据

> select * from cpu_used_percent where endpoint='Datacenter_172.118.16.19' and time >= '2021-09-17T09:00:00Z' and time <= '2021-09-17T09:10:00Z';
name: cpu_used_percent
time                 dstype endpoint                 step value
----                 ------ --------                 ---- -----
2021-09-17T09:00:00Z GAUGE  Datacenter_172.118.16.19 60   2.2341273798866914
2021-09-17T09:01:00Z GAUGE  Datacenter_172.118.16.19 60   2.7063496857597222
2021-09-17T09:02:00Z GAUGE  Datacenter_172.118.16.19 60   2.837302089909386
2021-09-17T09:03:00Z GAUGE  Datacenter_172.118.16.19 60   2.1944448331746718
2021-09-17T09:04:00Z GAUGE  Datacenter_172.118.16.19 60   2.400794076077173
2021-09-17T09:06:00Z GAUGE  Datacenter_172.118.16.19 60   2.063492429025008
2021-09-17T09:07:00Z GAUGE  Datacenter_172.118.16.19 60   2.198413087845874
2021-09-17T09:08:00Z GAUGE  Datacenter_172.118.16.19 60   2.269841671927509
2021-09-17T09:09:00Z GAUGE  Datacenter_172.118.16.19 60   2.472222660158808
2021-09-17T09:10:00Z GAUGE  Datacenter_172.118.16.19 60   1.9841273356009694

默认：fill(null)，显示value=null

> select mean(value) from cpu_used_percent where endpoint='Datacenter_172.118.16.19' and time >= '2021-09-17T09:00:00Z' and time <= '2021-09-17T09:10:00Z' group by time(1m);
name: cpu_used_percent
time                 mean
----                 ----
2021-09-17T09:00:00Z 2.2341273798866914
2021-09-17T09:01:00Z 2.7063496857597222
2021-09-17T09:02:00Z 2.837302089909386
2021-09-17T09:03:00Z 2.1944448331746718
2021-09-17T09:04:00Z 2.400794076077173
2021-09-17T09:05:00Z
2021-09-17T09:06:00Z 2.063492429025008
2021-09-17T09:07:00Z 2.198413087845874
2021-09-17T09:08:00Z 2.269841671927509
2021-09-17T09:09:00Z 2.472222660158808
2021-09-17T09:10:00Z 1.9841273356009694
>

fill none: 不显示短少的工夫点

> select mean(value) from cpu_used_percent where endpoint='Datacenter_172.118.16.19' and time >= '2021-09-17T09:00:00Z' and time <= '2021-09-17T09:10:00Z' group by time(1m) fill(none);
name: cpu_used_percent
time                 mean
----                 ----
2021-09-17T09:00:00Z 2.2341273798866914
2021-09-17T09:01:00Z 2.7063496857597222
2021-09-17T09:02:00Z 2.837302089909386
2021-09-17T09:03:00Z 2.1944448331746718
2021-09-17T09:04:00Z 2.400794076077173
2021-09-17T09:06:00Z 2.063492429025008
2021-09-17T09:07:00Z 2.198413087845874
2021-09-17T09:08:00Z 2.269841671927509
2021-09-17T09:09:00Z 2.472222660158808
2021-09-17T09:10:00Z 1.9841273356009694

fill(99)，将短少的值填成99：

> select mean(value) from cpu_used_percent where endpoint='Datacenter_172.118.16.19' and time >= '2021-09-17T09:00:00Z' and time <= '2021-09-17T09:10:00Z' group by time(1m) fill(99);
name: cpu_used_percent
time                 mean
----                 ----
2021-09-17T09:00:00Z 2.2341273798866914
2021-09-17T09:01:00Z 2.7063496857597222
2021-09-17T09:02:00Z 2.837302089909386
2021-09-17T09:03:00Z 2.1944448331746718
2021-09-17T09:04:00Z 2.400794076077173
2021-09-17T09:05:00Z 99
2021-09-17T09:06:00Z 2.063492429025008
2021-09-17T09:07:00Z 2.198413087845874
2021-09-17T09:08:00Z 2.269841671927509
2021-09-17T09:09:00Z 2.472222660158808
2021-09-17T09:10:00Z 1.9841273356009694

fill(linear)线性插入值：前后时序的平均值

> select mean(value) from cpu_used_percent where endpoint='Datacenter_172.118.16.19' and time >= '2021-09-17T09:00:00Z' and time <= '2021-09-17T09:10:00Z' group by time(1m) fill(linear);
name: cpu_used_percent
time                 mean
----                 ----
2021-09-17T09:00:00Z 2.2341273798866914
2021-09-17T09:01:00Z 2.7063496857597222
2021-09-17T09:02:00Z 2.837302089909386
2021-09-17T09:03:00Z 2.1944448331746718
2021-09-17T09:04:00Z 2.400794076077173
2021-09-17T09:05:00Z 2.2321432525510905
2021-09-17T09:06:00Z 2.063492429025008
2021-09-17T09:07:00Z 2.198413087845874
2021-09-17T09:08:00Z 2.269841671927509
2021-09-17T09:09:00Z 2.472222660158808
2021-09-17T09:10:00Z 1.9841273356009694

fill(previous)插入前一个时序的值：

> select mean(value) from cpu_used_percent where endpoint='Datacenter_172.118.16.19' and time >= '2021-09-17T09:00:00Z' and time <= '2021-09-17T09:10:00Z' group by time(1m) fill(previous);
name: cpu_used_percent
time                 mean
----                 ----
2021-09-17T09:00:00Z 2.2341273798866914
2021-09-17T09:01:00Z 2.7063496857597222
2021-09-17T09:02:00Z 2.837302089909386
2021-09-17T09:03:00Z 2.1944448331746718
2021-09-17T09:04:00Z 2.400794076077173
2021-09-17T09:05:00Z 2.400794076077173
2021-09-17T09:06:00Z 2.063492429025008
2021-09-17T09:07:00Z 2.198413087845874
2021-09-17T09:08:00Z 2.269841671927509
2021-09-17T09:09:00Z 2.472222660158808
2021-09-17T09:10:00Z 1.9841273356009694