derivative()

功能描述

计算相邻的两行值的变化率，可通过ORDER BY进行排序。

相邻两行的时间为t1、t2，其差值单位为us，derivative对应的value列的值为v1、v2，其单位为v，derivative的每个时间单位为unit，每个单位中的时间长度为k微秒，变化率计算公式如下，其结果单位为[1/unit]

例如，原始数据如下：

time	01:00:00	01:10:00	01:30:00	02:00:00	02:10:00
value	31	32	33	34	36

使用derivative(value,time,’10min’)，计算结果如下，将会去除第一行，并计算之后几行的变化率：

time	01:10:00	01:30:00	02:00:00	02:10:00
derivative	1.0	0.5	0.33333...	2.0

语法格式

derivative(value, time, unit)

参数说明

参数名	类型	属性	释义
value	数值型	必选	需要计算变化率的列或者列表达式。支持列的数据类型范围：TINYINT、SMALLINT、INTEGER、BIGINT、FLOAT4、FLOAT8 如果使用NUMERIC、DECIMAL、NUMBER、BINARY_DOUBLE、INTEGER[(p[,s])]、DEC等其他数值类型，将会产生隐式类型转换，从该类型转换到FLOAT8，value的大小不应该超过FLOAT8的上限。表达式类型：COUNT()、MEDIAN()、SUM()、FIRST()、LAST()、MIN()、MAX()、AVG()、LOCF()、INTERPOLATE() 。
time	TIMESTAMP或TIMESTAMPTZ	必选	进行计算变化率的时间列/或时间表达式，当使用GAPFILL作为时间列时，将当前参数设置为NULL，并需要将GAPFILL放到TargetList中去。支持的表达式类型：TIME_BUCKET()
unit	INTEVAL	可选	计算变化率的单位，默认1s，支持从字符串隐式转换为INTERVAL，比如’1h’会被转换为’1h’::INTERVAL。unit的值不能为负。可选范围：s、min、hour、day、week。假设第一行时间为17：10：00.20，值为10 假设第二行时间为17：15：00.30，值为20 时间相差结果保留到Interval，其基础单位为1us，那么time_unit=1000000L，以下用[]来表示单位。变化率应为(20-10)/((17：15：00.30-17：10：00.20)[us]/1000000L[us/unit]) = (20-10)*1000000L/((17：15：00.30-17：10：00.20) [unit])，假设值的单位为T，最后结果的单位应该是[T/unit]，表示以每个单位为基准的变化率。

约束

如果相邻的两行数据，时间差（绝对值）小于unit单位，则报错。
在derivative中不支持嵌套使用derivative或difference，无论是在derivative的value列或是在time列，还是在unit列。
使用ORDER BY确定对原始数据如何排序，derivative计算的结果将会在原始数据排序后进行计算。
如果相邻两行的time列相同，那么将会忽略第二行。
当derivative引用的某一行的value列或time列为空时，将忽略该行，不计算该行的变化率，而是计算上一个value列和time列均不为空的行到下一个value列和time列均不为空的行之间的变化率。
在TargetList中可能使用一个或多个derivative函数，也可能在使用derivative函数的同时使用一个或多个difference函数进行计算，当前规则适用于以上提及的两种情况，因此derivative函数和difference函数统称为D函数。当结果中的一行中有多个D函数计算结果时，如果所有D函数计算的结果均为空，则跳过这一行。如果任意一个D函数不为空，则保留当前行。但只有不为空的D函数会被输出到结果，其他列均为NULL。

兼容time_bucket_gapfill的特殊用法，如果要在时间列使用gapfill，那么需要将time参数设置为NULL，并且在TargetList使用time_bucket_gapfill（需要同时满足这两个条件才生效），例如：

SELECT time_bucket_gapfill('10 mins', time) AS ten_mins, derivative(avg(value),NULL,NULL)
FROM test_gapfill
where time > '2030-01-01 01:00:00' and time < '2030-01-01 02:20:00'
GROUP BY ten_mins;

除此之外支持locf/interpolate，示例如下：

SELECT time_bucket_gapfill('10 mins', time) AS ten_mins, derivative(locf(avg(value)),NULL,NULL)
FROM test_gapfill
where time > '2030-01-01 01:00:00' and time < '2030-01-01 02:20:00'
GROUP BY ten_mins;

如果不在TargetList中显式使用derivative，例如在group by、distinct、order by等子句中使用，那么derivative的作用是将value列转换为Numeric，而不会进行任何计算，例如：
```
select avg(value)
from test_gapfill
group by derivative(value,time,NULL);
```

在group by中使用time_bucket并在derivative中使用time_bucket作为time列时，需要保持bucket_width格式一致（包括增加减少空格），例如以下语句可以正常运行：

select time_bucket('2 hour', time) as bucket,
derivative(avg(temperature), time_bucket('2 hour', time), '2hour') 
from weather group by bucket order by bucket;

但当使用不同格式时，将无法运行：

select time_bucket('2 hour', time) as bucket,
derivative(avg(temperature), time_bucket('120min', time), '2hour') 
from weather group by bucket order by bucket;

支持普通表。
返回值为Numeric。
相邻两列时间相同时，将会忽略当前行与上一行的计算结果。

示例

-- 数据样例
Uqbar=# select * from weather;
          time          | city  | temperature 
------------------------+-------+-------------
 2023-02-14 09:00:00+08 | jinan |          10
 2023-02-14 10:00:00+08 | jinan |          11
 2023-02-14 11:00:00+08 | jinan |        12.5
 2023-02-14 12:00:00+08 | jinan |          13
 2023-02-14 13:00:00+08 | jinan |          14
(5 rows)

-- 查询每小时温度变化率
Uqbar=# select time,derivative(temperature, time,'1h') from weather order by time;
          time          |       derivative       
------------------------+------------------------
 2023-02-14 10:00:00+08 |     1
 2023-02-14 11:00:00+08 |       1.5
 2023-02-14 12:00:00+08 |      .5
 2023-02-14 13:00:00+08 |     1
(4 rows)
 
-- 查询每半小时温度变化率
Uqbar=# select time,derivative(temperature, time, '30min') from weather order by time;
          time          |      derivative       
------------------------+-----------------------
 2023-02-14 10:00:00+08 |     .5
 2023-02-14 11:00:00+08 |     .75
 2023-02-14 12:00:00+08 |     .25
 2023-02-14 13:00:00+08 |     .5
(4 rows)
 
-- 查询每两小时温度变化率
Uqbar=# select time_bucket('2 hour', time) as bucket,derivative(avg(temperature), time_bucket('2 hour', time), '2hour') from weather group by bucket order by bucket;
         bucket         |     derivative     
------------------------+--------------------
 2023-02-14 10:00:00+08 |   1.75
 2023-02-14 12:00:00+08 |   1.75
(2 rows)

-- 特殊用法，使用time_bucket_gapfill查询每30分钟温度变化率
Uqbar=# SELECT time_bucket_gapfill('30 mins', time) AS bucket, derivative(locf(avg(temperature)),NULL,'30min')
FROM weather where time >= '2023-02-14 09:00:00+08' and time <= '2023-02-14 13:00:00+08' GROUP BY bucket;
         bucket         |           derivative           
------------------------+--------------------------------
 2023-02-14 09:30:00+08 |       0
 2023-02-14 10:00:00+08 |             1
 2023-02-14 10:30:00+08 |       0
 2023-02-14 11:00:00+08 |               1.5
 2023-02-14 11:30:00+08 |       0
 2023-02-14 12:00:00+08 |            .5
 2023-02-14 12:30:00+08 |       0
 2023-02-14 13:00:00+08 |             1
(8 rows)

-- 补充数据样例
Uqbar=# select * from weather;
          time          | city  | temperature | pressure
-------------------------------------+---------+--------------------+------------------
 2023-02-14 09:00:00+08 | jinan |          10   |   23
 2023-02-14 10:00:00+08 | jinan |           |   24
 2023-02-14 11:00:00+08 | jinan |        12.5    |    
 2023-02-14 12:00:00+08 | jinan |           |    
 2023-02-14 13:00:00+08 | jinan |          14  |   25
(5 rows)

-- 约束5使用示例
Uqbar=# select time, derivative(temperature , time,'1h') ,derivative(pressure, time,'1h') from weather order by time;
          time          | difference   | difference
------------------------+------------+------------
 2023-02-14 10:00:00+08 |            |  1
 2023-02-14 11:00:00+08 |        1.25  |  
 2023-02-14 13:00:00+08 |        0.75  |  0.333333
(3 rows)