# 时序函数 DWS支持的时序计算函数参见[表1]。 表1时序计算函数列表 | 时序计算函数 | 函数描述 | |:---|:---| | [delta(field numeric)] | 计算按照时间排序后两行之间的差值。 | | [spread(field numeric)] | 计算某段时间内最大和最小值的差值。 | | [mode() within group (order by value anyelement)] | 对给定的列，返回出现频率最高的值，如果多个值频率相同，返回这些值中最小的那个值。 | | [value_of_percentile(column float, percentile float, compression float)] | 计算百分位。是percentile_cont的近似算法。 | | [percentile_of_value(column float, percentilevalue float, compression float)] | 给定百分位，计算对应的值。是value_of_percentile的逆运算。 | | [first(column1, column2)] | 通过比较分组内column2列的值，找到其中的最小值，输出对应column1列的值。 | | [last(column1, column2)] | 通过比较分组内column2列的值，找到其中的最大值，输出对应column1列的值。 | #### delta(field numeric) 描述：计算按照时间排序后两行之间的差值。该函数通常用于时序场景计算按照时间排序后相邻两行差值，用于流量，速度等指标监控。 参数说明：field，需要计算的列。 ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/caution_3.0-zh-cn.png) delta是一个窗口函数，需要与over窗口函数使用。且over中rows语句不会改变delta函数结果，比如，delta(value) over(order by time rows 1 preceding) 和 delta(value) over(order by time rows 3 preceding) 返回的结果是一致的。 示例： ``` SELECT delta(value) over (rows 1 preceding) FROM (VALUES ('2019-07-12 00:00:00'::timestamptz, 1), ('2019-07-12 00:01:00'::timestamptz, 3), ('2019-07-12 00:02:00'::timestamptz, 7)) v(time,value) order by 1; delta ------- 0 2 4 (3 rows) ``` #### spread(field numeric) 描述：计算每个指标在分组内的增量，通常按照时间排序后计算。 参数说明：field，需要计算的列。 ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/caution_3.0-zh-cn.png) 每个分组内如果少于2个元组，返回结果为0，不要和over窗口函数混用。 示例： ``` SELECT time, SPREAD(value) FROM (VALUES ('2019-07-12 00:00:00'::timestamptz, 1), ('2019-07-12 00:00:00'::timestamptz, 3), ('2019-07-12 00:01:00'::timestamptz, 3), ('2019-07-12 00:01:00'::timestamptz, 6), ('2019-07-12 00:02:00'::timestamptz, 7), ('2019-07-12 00:02:00'::timestamptz, 9)) v(time,value) group by time order by time; time | spread ------------------------+-------- 2019-07-12 00:00:00+08 | 2 2019-07-12 00:01:00+08 | 3 2019-07-12 00:02:00+08 | 2 (3 rows) ``` #### mode() within group (order by value anyelement) 描述：对给定的列，返回出现频率最高的值，如果多个值频率相同，返回这些值中最小的那个值。 参数说明：value，查询列。 ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/caution_3.0-zh-cn.png) - 该函数需要与within group一起使用，无within group语句，会报错。该函数参数放在group的order by后面。 - 不能和over子句一起使用。 示例： ``` SELECT mode() within group (order by value) FROM (VALUES ('2019-07-12 00:00:00'::timestamptz, 1), ('2019-07-12 00:01:00'::timestamptz, 1), ('2019-07-12 00:02:00'::timestamptz, 7)) v(time,value); mode ------ 1 (1 row) ``` #### value_of_percentile(column float, percentile float, compression float) 描述：对于给定的列按照从小到大的顺序返回百分位的近似值。是percentile_cont的近似结果，但是性能比percentile_cont好。 表2参数说明 | 参数名 | 类型 | 描述 | |:---|:---|:---| | column | float | 要计算百分位的列。 | | percentile | float | 指定的百分位的值，取值范围\[0，1\]。 | | compression | float | 指定的压缩系数，取值范围为\[0，500\]，默认值是300，这个值取值越大，函数计算过程占用内存越大，同时结果的精度也相对高。如果指定的值不在取值范围内，会按照300求解。 | 示例： ``` SELECT value_of_percentile(values, 0.8, 0) from TABLE; ``` #### percentile_of_value(column float, percentilevalue float, compression float) 描述：对于给定的列按照从小到大的顺序返回百分位。是value_of_percentile的逆过程。 表3参数说明 | 参数名 | 类型 | 描述 | |:---|:---|:---| | column | float | 要计算百分位的列。 | | percentilevalue | float | 指定该值，用于计算它所在的百分位。 | | compression | float | 指定的压缩系数，取值范围为\[0，500\]，默认值是300，这个值取值越大，函数计算过程占用内存越大，同时结果的精度也相对高。如果指定的值不在取值范围内，会按照300求解。 | 示例： ``` SELECT percentile_of_value(values, 80, 0) from TABLE; ``` #### first(column1, column2) 描述：聚合函数。通过比较分组内column2列的值，找到其中的最小值，输出对应column1列的值。 表4参数说明 | 参数名 | 类型 | 描述 | |:---|:---|:---| | column1 | bigint/text/double/numeric | 最终的输出列。 | | column2 | timestamp/timestamptz/numeric | 比较列。 | 示例：样例数据参考[time_fill()]表达式中的表定义和数据。 求按照scope_name分组，每个分组内按照时间排序最靠前的idle的值： ``` select first(idle, time_string) from dcs_cpu group by scope_name; first ------- 1 3 (2 rows) ``` #### last(column1, column2) 描述：聚合函数。通过比较分组内column2列的值，找到其中的最大值，输出对应column1列的值。 表5参数说明 | 参数名 | 类型 | 描述 | |:---|:---|:---| | column1 | bigint/text/double/numeric | 最终的输出列。 | | column2 | timestamp/timestamptz/numeric | 比较列。 | 示例：样例数据参考[time_fill()]表达式中的表定义和数据。 求按照scope_name分组，每个分组内按照时间排序最靠后的idle的值： ``` select last(idle, time_string) from dcs_cpu group by scope_name; last ------ 3 2 (2 rows) ``` #### 时序填充函数 DWS支持的时序填充函数参见[表6]。 表6时序填充函数列表 | 时序填充表达式 | 描述 | |:---|:---| | time_fill(interval, time_column, start_time, end_time), **fill_last**(agg_function(agg_column)) | 按照时间列排序后，补充缺失的时间数据信息（聚合以后的结果） 用按照时间排序后的**前值填充后值**。 | | time_fill(interval, time_column, start_time, end_time), **fill_first**(agg_function(agg_column)) | 按照时间列排序后，补充缺失的时间数据信息（聚合以后的结果） 用按照时间排序后的**后值填充前值**。 | | time_fill(interval, time_column, start_time, end_time), **fill_avg**(agg_function(agg_column)) | 按照时间列排序后，补充缺失的时间数据信息（聚合以后的结果） 用按照时间排序后**前后两者的值填充当前值**。 | ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/caution_3.0-zh-cn.png) - time_fill函数需要作为聚合函数使用，group by需要引用自身计算结果，不支持与自身嵌套使用，不支持单条查询内多次调用，不支持作为下层计算节点使用，不支持与within group使用。 - time_fill在使用时，必须与fill_avg、fill_first或者fill_last配合使用；或者与Agg函数组合使用。 - time_fill必须出现在group by中，并且group by中只能出现这一列。 - time_fill不支持出现在处理select后面的其他位置，比如where后面或其他关联条件中。 - start时间戳的值必须小于finish时间戳的值， 且两者间距需要大于window_width的值。 - 所有参数不支持空值，start和finish需为确定值。 表7时序填充表达式的参数说明 | 参数名 | 类型 | 描述 | |:---|:---|:---| | interval | 时间间隔类型：INTERVAL，最小单位是1秒。 | 按照时间分组的时间间隔。 | | time_column | 时间类型，timestamp/timestamptz。 | 按照指定列做时间分组。 | | start_time | 时间类型，timestamp/timestamptz。 | 分组的起始时间。 | | end_time | 时间类型，timestamp/timestamptz。 | 分组的结束时间。 | | agg_function(agg_column) | 指定Agg函数对指定列做聚合。比如max(col)。 | 对Agg的结果按照指定的填充方法填充。 | #### time_fill(interval, time_column, start_time, end_time), fill_last(agg_function(agg_column)) 描述：按照时间列排序后，用前值补充后值。 创建示例表dcs_cpu，并且插入数据。 ``` create table dcs_cpu( idle real, vcpu_num int, node text, scope_name text, server_ip text, iowait numeric, time_string timestamp )with (orientation=column) distribute by hash(node); insert into dcs_cpu VALUES(1.0,1,'node_a','scope_a','1.1.1.1',1.0,'2019-07-12 00:10:10'); insert into dcs_cpu VALUES(2.0,2,'node_b','scope_a','1.1.1.2',2.0,'2019-07-12 00:12:10'); insert into dcs_cpu VALUES(3.0,3,'node_c','scope_b','1.1.1.3',3.0,'2019-07-12 00:13:10'); ``` 以1min为单位，求分组内的均值。用前一时间段的值，填充后一时间段的值。 ``` select time_fill(interval '1 min',time_string,'2019-07-12 00:09:00','2019-07-12 00:14:00'), fill_last(avg(idle)) from dcs_cpu group by time_fill order by time_fill; time_fill | fill_last ---------------------+----------- 2019-07-12 00:09:00 | 2019-07-12 00:10:00 | 1 2019-07-12 00:11:00 | 1 2019-07-12 00:12:00 | 2 2019-07-12 00:13:00 | 3 2019-07-12 00:14:00 | 3 (6 rows) ``` #### time_fill(interval, time_column, start_time, end_time), fill_first(agg_function(agg_column)) 描述：按照时间列排序后，用后值补充前值。 示例： 以1min为单位，求分组内的均值。用后一时间段的值，填充前一时间段的值（样例数据参考[time_fill(interval, time_column, start_time, end_time), fill_last(agg_function(agg_column))]中的表定义和数据）。 ``` select time_fill(interval '1 min',time_string,'2019-07-12 00:09:00','2019-07-12 00:14:00'), fill_first(avg(idle)) from dcs_cpu group by time_fill order by time_fill; time_fill | fill_first ---------------------+------------ 2019-07-12 00:09:00 | 1 2019-07-12 00:10:00 | 1 2019-07-12 00:11:00 | 2 2019-07-12 00:12:00 | 2 2019-07-12 00:13:00 | 3 2019-07-12 00:14:00 | (6 rows) ``` #### time_fill(interval, time_column, start_time, end_time), fill_avg(agg_function(agg_column)) 描述：按照时间排序后，前后两者的值填充当前值。 示例： 以1min为单位，求分组内的平均值，用前后两个时间的加权平均值，填充当前值（样例数据参考[time_fill(interval, time_column, start_time, end_time), fill_last(agg_function(agg_column))]中的表定义和数据）。 ``` select time_fill(interval '1 min',time_string,'2019-07-12 00:09:00','2019-07-12 00:14:00'), fill_avg(avg(idle)) from dcs_cpu group by time_fill order by time_fill; time_fill | fill_avg ---------------------+---------- 2019-07-12 00:09:00 | 1 2019-07-12 00:10:00 | 1 2019-07-12 00:11:00 | 1.5 2019-07-12 00:12:00 | 2 2019-07-12 00:13:00 | 3 2019-07-12 00:14:00 | 3 (6 rows) ```