GaussDB(DWS) 带你走进IoT时代-时序函数和表达式

TIME_FILL系列表达式

上述表达式主要适用于对时序数据做聚合分析，并且对确实数据做填充的场景。以下通过一个场景来说明它的用法。

假设当前有一张时序表，它记录的信息是一个机房内部，各个主机的CPU使用率的情况。对于每一台主机有一个终端负责数据采集。表定义如下：

CREATE TABLE cpuinfo(
  cpu real TSField,
  server_ip text TSTag,
  time_string timestamp TSTime
)with (TTL='7 days', PERIOD = '1 hour', orientation=TIMESERIES);

插入一些简单的样例数据：

insert into cpuinfo values('0.1', '192.168.1.100', now());
insert into cpuinfo values('0.2', '192.168.1.100', now() + '30 min'::interval);
insert into cpuinfo values('0.3', '192.168.2.100', now() - '1 hour'::interval);
insert into cpuinfo values('0.4', '192.168.2.100', now() - '20 min'::interval);
insert into cpuinfo values('0.3', '192.168.2.100', now());
insert into cpuinfo values('0.4', '192.168.2.100', now() + '20 min'::interval);

样例数据如下：

 select * from cpuinfo order by server_ip, time_string;
 cpu |   server_ip   |          time_string
-----+---------------+-------------------------------
  .1 | 192.168.1.100 | 2022-04-28 10:10:26.160338+08
  .2 | 192.168.1.100 | 2022-04-28 10:40:26.16918+08
  .3 | 192.168.2.100 | 2022-04-28 09:10:26.17382+08
  .4 | 192.168.2.100 | 2022-04-28 09:50:26.18054+08
  .3 | 192.168.2.100 | 2022-04-28 10:10:26.184662+08
  .4 | 192.168.2.100 | 2022-04-28 10:30:26.315326+08
(6 rows)

客户想知道以小时为单位，过去一周内的每台主机的CPU占用的峰值情况，SQL语句应该怎么写呢？

用传统的方法可以通过以下语句来查询：

SELECT  
to_timestamp(floor(extract(epoch from "time_string")/3600)*3600) AS "time", 
max(cpu),server_ip AS cpu 
FROM cpuinfo 
WHERE  "time" BETWEEN now()- '7 days'::interval AND now() 
GROUP BY 1,3 ORDER BY 3,1,2;
          time          | max |      cpu
------------------------+-----+---------------
 2022-04-28 10:00:00+08 |  .2 | 192.168.1.100
 2022-04-28 09:00:00+08 |  .4 | 192.168.2.100
 2022-04-28 10:00:00+08 |  .4 | 192.168.2.100
(3 rows)

由于终端设备采集的数据的时间戳信息是连续的，不是按照小时做对齐的，因此做聚合时候不能直接按照时间列做group by，要先做一次加工。我们这里使用了to_timestamp(floor(extract(epoch from "time_string")/3600)*3600)表达来做加工。其中extract(epoch from "time_string")代表从time_string这个时间到1970-01-01 00:00:00-00以来的秒数，他对于3600取整，达到了按照小时对时间做对齐的效果。然后就可以分组聚合了。

但是这样，还有一个问题。在实际生产环境中，终端采集的数据，可能会由于网络问题，由于终端设备故障等等因素，导致部分时间段的数据采集失败，没有记录到时序表中，那么在做聚合后也无法获得对应时间段内的数据。假如，IP为'192.168.1.100'这个设备，中间由于故障，在‘2022-04-28 09:00:00’这个时间有1个小时的CPU信息没有采集到，那么上述聚合语句得到的结果就会缺失这个时间点的数据。

这种场景就可以使用time_fill相关表达式来补充缺失的时间点的数据：

SELECT time_fill('1 hour'::interval, time_string, '2022-04-28 09:00:00', '2022-04-28 10:00:00'), 
server_ip, 
fill_first(max(cpu))
FROM
cpuinfo
GROUP BY 1,2 
ORDER BY 2,1,3;
       time_fill        |   server_ip   | fill_first
------------------------+---------------+------------
 2022-04-28 09:00:00+08 | 192.168.1.100 |         .2
 2022-04-28 10:00:00+08 | 192.168.1.100 |         .2
 2022-04-28 09:00:00+08 | 192.168.2.100 |         .4
 2022-04-28 10:00:00+08 | 192.168.2.100 |         .4
(4 rows)

（对比上述数据结果与传统方案，多了一条“2022-04-28 09:00:00+08 | 192.168.1.100 |         .2”数据，这条数据是根据他下一个时间段生成出来的）

其中time_fill表达式最终会输出时间列，它有4个参数。第一个参数是interval类型，表示按照指定的时间单位做对齐。第二个参数是时序表的时间列。第三个和第四个参数分别表示做时间填充的起始值。这4个参数都必须全部指定。要注意的是，第三个，第四个参数的类型要与time_string一致，否则系统会自动做数据类型转化，比如讲timestamp with timezone转化成为timestamp类型。

fill_first表示填充策略，即如果当前时间窗的数据不存在，则用他的后一个时间窗的聚合数据做填充。我们还支持fill_last,fill_avg策略。其中fill_last表示利用当前缺失时间窗的前一个时间窗的聚合结果填充当前值，fill_avg表示使用它前一个时间窗和后一个时间窗的加权平均值来填充当前时间窗的值。

FIRST/LAST函数

这两个函数是聚合函数，用于计算分组内的第一条(最后一条)数据对应字段。简言之：first(column1, column2) 表示按照column2排序(升序)后，输出第一条数据的column1列的值。last(column1, column2) 表示按照column2排序(降序)后，输出第一条数据的column1列的值。

以上述表为例，输出按照IP分组，每个分组收集的第一条数据：

SELECT  first(cpu, time_string) FROM cpuinfo GROUP BY  server_ip order by 1;
      first
------------------
 .100000001490116
 .300000011920929
(2 rows)

输出按照IP分组，每个分组收集的最后一条数据：

SELECT last(cpu, time_string) FROM cpuinfo GROUP BY  server_ip order by 1 ;
       last
------------------
 .200000002980232
 .400000005960464
(2 rows)