derivative()

Function Description

Calculate the rate of change of two adjacent rows of values, which can be sorted by ORDER BY.

The time of the two neighboring rows is t1, t2, and the unit of its difference is us, the value of the value column corresponding to the derivative is v1, v2, and the unit of its unit is v. Each time unit of the derivative is unit, and the length of the time in each unit is k microseconds, and the rate of change is computed as follows, and the unit of its result is [1/unit]

For example, the raw data is as follows:

Using derivative(value,time,'10min'), the following calculation will remove the first row and calculate the rate of change for the following rows:

Syntax

derivative(value, time, unit)

Parameter Description

Constraints

1. If the time difference (absolute value) between two adjacent rows of data is less than the unit unit, an error will be reported.

2. Nested use of derivative or difference is not supported in derivative, whether in the value column of the derivative, the time column, or the unit column.

3. Use ORDER BY to determine how to sort the original data. The results of the derivative calculation will be calculated after the original data is sorted.

4. If the time column of two adjacent rows is the same, the second row will be ignored.

5. When the value column or time column of a row referenced by derivative is empty, the row will be ignored and the change rate of the row will not be calculated. Instead, the previous row whose value column and time column are not empty will be calculated to the next row. The rate of change between rows where neither the value column nor the time column is empty.

6. One or more derivative functions may be used in TargetList, or one or more difference functions may be used for calculation while using the derivative function. The current rules apply to the two situations mentioned above, so the derivative function and the difference function 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, this 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.

7. Compatible with the special usage of time_bucket_gapfill. If you want to use gapfill in the time column, you need to set the time parameter to NULL and use time_bucket_gapfill in TargetList (these two conditions need to be met at the same time to take effect), for example:

   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;

   In addition, locf/interpolate is supported. Examples are as follows:

   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;

8. If you do not use derivative explicitly in TargetList, such as in group by, distinct, order by and other clauses, then the function of derivative is to convert the value column to Numeric without any calculation, for example:

   select avg(value)
   from test_gapfill
   group by derivative(value,time,NULL);

9. When using time_bucket in group by and time_bucket as the time column in derivative, you need to keep the bucket_width format consistent (including increasing and decreasing spaces). For example, the following statement can run normally:

   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;

   But when using a different format, it won't work:

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

10. Support common tables.

11. The return value is Numeric.

12. When two adjacent columns have the same time, the calculation results of the current row and the previous row will be ignored.

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)

-- Query the hourly temperature change rate
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)
 
-- Query the temperature change rate every half hour
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)
 
-- Query the temperature change rate every two hours
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)

-- Special usage, use time_bucket_gapfill to query the temperature change rate every 30 minutes
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)

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

-- Constraint 5 usage example
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)