difference()

功能描述

用于计算与上一值的差值，可通过ORDER BY进行排序。

相邻两行的difference对应的value列的值为v1、v2，差值计算公式为：v2-v1

例如，原始数据如下：

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

使用difference(value)，计算结果如下，将会去除第一行01:00:00，并计算之后几行的变化率：

time	01:10:00	01:30:00	02:00:00	02:10:00
difference	1	1	1	2

语法格式

difference(value)

参数说明

参数名	类型	属性	释义
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()。

约束

在difference的value列中不支持嵌套使用difference或derivative。
使用ORDER BY确定对原始数据如何排序，difference计算的结果将会在原始数据排序后进行计算。
当difference引用的某一行的value列为空时，将会忽略该行，不计算该行的差值，而是计算上一个value列不为空的行到下一个value列不为空的行之间的差值。
在TargetList中可能使用一个或多个difference函数，也可能在使用difference函数同时使用一个或多个derivative函数进行计算，所以difference函数和derivative函数统称为D函数。当结果中一行中有多个D函数计算结果时，如果所有D函数计算的结果均为空，则跳过该行。如果任意一个D函数不为空，则保留当前行。但只有不为空的D函数会被输出到结果，其他列均为NULL。

兼容time_bucket_gapfill，可在支持difference中的value列使用locf/interpolate，示例如下：

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

如果不在TargetList中显式使用difference，比如在group by、distinct、order by等子句中使用，那么difference的作用是将value列转换为Numeric，而不会进行任何计算，例如：
```
SELECT avg(value) FROM test_gapfill GROUP BY difference(value);
```
支持普通表。
返回值为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, difference(temperature) from weather order by time;
          time          | difference 
------------------------+------------
 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_bucket('2 hour',time) as bucket, difference(avg(temperature)) from weather group by bucket order by bucket;
         bucket         | difference 
------------------------+------------
 2023-02-14 10:00:00+08 |       1.75
 2023-02-14 12:00:00+08 |       1.75
(2 rows)

-- 兼容time_bucket_gapfill中的locf/interpolate
Uqbar=# SELECT time_bucket_gapfill('30 mins', time) AS thirty_mins, difference(locf(avg(temperature))) FROM weather  where time > '2023-02-14 09:00:00+08' and time < '2023-02-14 13:00:00+08' GROUP BY thirty_mins;
      thirty_mins       | difference 
------------------------+------------
 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.0
 2023-02-14 12:00:00+08 |         .5
 2023-02-14 12:30:00+08 |          0
(5 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 |        NULL   |   24
 2023-02-14 11:00:00+08 | jinan |        12.5    |   NULL
 2023-02-14 12:00:00+08 | jinan |        NULL  |   NULL
 2023-02-14 13:00:00+08 | jinan |          14  |   25
(5 rows)

Uqbar=# select time, difference(temperature) ,difference(pressure) from weather order by time;
          time          | difference   | difference
------------------------+------------+------------
 2023-02-14 10:00:00+08 |        NULL  |  1
 2023-02-14 11:00:00+08 |        2.5  |  NULL
 2023-02-14 13:00:00+08 |         1.5  |  1
(3 rows)

-- 与derivative交互
Uqbar=# select time,difference(temperature), derivative(pressure, time,'1h') from weather order by time;
          time          | difference   | derivative
------------------------+------------+------------
 2023-02-14 10:00:00+08 |        NULL  |  1
 2023-02-14 11:00:00+08 |        2.5  |  NULL
 2023-02-14 13:00:00+08 |         1.5  |  0.333333333
(3 rows)