关于influxdb:InfluxDB-influxQL的group和fill

43次阅读

共计 4373 个字符，预计需要花费 11 分钟才能阅读完成。

group 是 influxQL 中常见的聚合函数，罕用于按工夫聚合 (一段时间内的最大 / 最小 / 均匀)；若在聚合时没有足够的数据点，可指定 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: 填充前一个序列的值；

原始数据：两头短少了 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

正文完