difference()

Function Description

Used to calculate the difference from the previous value, which can be sorted by ORDER BY.

The values of the value column corresponding to the difference between two adjacent rows are v1 and v2. The difference calculation formula is: v2-v1

For example, the raw data is as follows:

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

Using difference(value), the calculation result is as follows. The first row 01:00:00 will be removed and the change rate of the following rows will be calculated:

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

Syntax

difference(value)

Parameter Description

Parameter Name	Type	Mandatory or Not	Description
value	numeric	Yes	A column or column expression is required to calculate the rate of change. Supported column data type range: TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT4, FLOAT8 If you use NUMERIC, DECIMAL, NUMBER, BINARY_DOUBLE, INTEGER[(p[,s])], DEC, etc. For numeric types, implicit type conversion will occur. When converting from this type to FLOAT8, the size of the value should not exceed the upper limit of FLOAT8. Expression types: COUNT(), MEDIAN(), SUM(), FIRST(), LAST(), MIN(), MAX(), AVG(), LOCF(), INTERPOLATE().

Constraints

Nested use of difference or derivative is not supported in the value column of difference.
Use ORDER BY to determine how to sort the original data. The difference calculation result will be calculated after the original data is sorted.
When the value column of a row referenced by difference is empty, the row will be ignored and the difference of the row will not be calculated. Instead, the difference from the previous row whose value column is not empty to the next row whose value column is not empty will be calculated. The difference between rows.
One or more difference functions may be used in TargetList, or one or more derivative functions may be used for calculation while using the difference function, so the difference function and the derivative function are collectively called D functions. When there are multiple D function calculation results in a row in the result, if the results of all D function calculations are empty, the row is skipped. If any D function is not empty, the current row is retained. But only the D function that is not empty will be output to the result, and other columns will be NULL.

Compatible with time_bucket_gapfill, you can use locf/interpolate in the value column in supported difference. The example is as follows:

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;

If difference is not used explicitly in TargetList, such as in group by, distinct, order by and other clauses, then the function of difference is to convert the value column to Numeric without any calculation, for example:
```
SELECT avg(value) FROM test_gapfill GROUP BY difference(value);
```
Support common tables.
The return value is Numeric.

Examples

-- Data sample
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)

-- Compatible with locf/interpolate in time_bucket_gapfill
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)

-- Supplementary Data Example
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)

-- Interact with 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)