Common InfluxDB functions for InfluxDB Learning (3) Transformation Class functions

1) DERIVATIVE () function

Function: returns the rate of change of a field in a series.

InfluxDB calculates the differences between field values sorted by time and converts these results into a unit rate of change. Where the unit can be specified, and the default is 1s.

Syntax:

SELECT DERIVATIVE (, []) FROM [WHERE]

The values of unit can be as follows:

U-- microsecondss-- secondsm-- minutesh-- hoursd-- daysw-- weeks

The DERIVATIVE () function can also be nested with the aggregate function under the condition of GROUP BY time (), in the following format:

SELECT DERIVATIVE (AGGREGATION_FUNCTION (),]) FROM WHERE GROUP BY time ()

Example:

Suppose there are the following items of data under the condition of location = santa_monica:

Name: h3o_feet-time water_level2015-08-18T00:00:00Z 2.0642015-08-18T00:06:00Z 2.1162015-08-18T00:12:00Z 2.0282015-08-18T00:18:00Z 2.1262015-08-18T00:24:00Z 2.0412015-08-18T00:30:00Z 2.051

Calculate the rate of change for each second:

> SELECT DERIVATIVE (water_level) FROM h3o_feet WHERE location = 'santa_monica' LIMIT 5name: h3o_feet-time derivative2015-08-18T00:06:00Z 0.00014444444444572015-08-18T00:12:00Z-0.00024444444444652015-08-18T00:18:00Z 0.0002722222222182015-08-18T00:24:00Z-0.00023611111111112015-08-18T00:30:00Z 2.777777777777842e-05

The formula for the first row of data is (2.116-2.064) / (360s / 1s)

Calculate the rate of change every six minutes

> SELECT DERIVATIVE (water_level,6m) FROM h3o_feet WHERE location = 'santa_monica' LIMIT 5name: h3o_feet-time derivative2015-08-18T00:06:00Z 0.05200000000000462015-08-18T00:12:00Z-0.0880000000000082015-08-18T00:18:00Z 0.0979999999999862015-08-18T00:24:00Z-0.0849999999999962015-08-18T00:30:00Z 0.0100000000000231

The calculation process of the first row of data is as follows: (2.116-2.064) / (6m / 6m)

Calculate the rate of change every 12 minutes:

> SELECT DERIVATIVE (water_level,12m) FROM h3o_feet WHERE location = 'santa_monica' LIMIT 5name: h3o_feet-time derivative2015-08-18T00:06:00Z 0.1040000000000092015-08-18T00:12:00Z-0.1760000000000162015-08-18T00:18:00Z 0.1959999999999732015-08-18T00:24:00Z-0.1699999999999932015-08-18T00:30:00Z 0.0200000000000462

The first row of data is calculated as follows: (2.116-2.064 / (6m / 12m)

Calculate the rate of change of the maximum value every 12 minutes

> SELECT DERIVATIVE (MAX (water_level)) FROM h3o_feet WHERE location = 'santa_monica' AND time > =' 2015-08-18T00VOV 00Z' AND time

< '2015-08-18T00:36:00Z' GROUP BY time(12m)name: h3o_feet--------------time derivative2015-08-18T00:12:00Z 0.0099999999999997872015-08-18T00:24:00Z -0.07499999999999973 这个函数功能非常多，也非常复杂，更多对于此功能的详细解释请看官网：https://docs.influxdata.com/influxdb/v0.13/query_language/functions/#derivative 2）DIFFERENCE()函数 作用：返回一个字段中连续的时间值之间的差异。字段类型必须是长整型或float64。 最基本的语法： SELECT DIFFERENCE() FROM [WHERE ] 与GROUP BY time()以及其他嵌套函数一起使用的语法格式： SELECT DIFFERENCE(()) FROM WHERE GROUP BY time() 其中，函数可以包含以下几个： COUNT(), MEAN(), MEDIAN(),SUM(), FIRST(), LAST(), MIN(), MAX(), 和 PERCENTILE()。 使用示例 例子中使用的源数据如下所示： >

SELECT water_level FROM h3o_feet WHERE location='santa_monica' AND time > = '2015-08-18T00 and time SELECT DIFFERENCE 00Z' and time SELECT DIFFERENCE (water_level) FROM h3o_feet WHERE location='santa_monica' AND time > =' 2015-08-18T00 and time SELECT ELAPSED 00Z' and time SELECT ELAPSED (water_level) FROM h3o_feet WHERE location='santa_monica' AND time > = '2015-08-18T00RV 00Z' and time SELECT ELAPSED (water_level) 1m) FROM h3o_feet WHERE location = 'santa_monica' AND time > =' 2015-08-18T00 and time SELECT ELAPSED 00RV 00Z' and time SELECT ELAPSED (water_level) 1h) FROM h3o_feet WHERE location = 'santa_monica' AND time > =' 2015-08-18T00 and time SELECT water_level FROM h3o_feet WHERE location 00Z' and time SELECT water_level FROM h3o_feet WHERE location = 'santa_monica' AND time > =' 2015-08-18T00VOO 00RV 00Z' and time SELECT STDDEV (water_level) FROM h3o_feetname: h3o_feet-time stddev1970-01-01T00:00:00Z 2.279144584196145

Example 2:

> SELECT STDDEV (water_level) FROM h3o_feet WHERE time > = '2015-08-18T00 and time 00RV 00Z' and time <' 2015-09-18T12 FROM h3o_feet WHERE time 06RV 00Z' GROUP BY time (1w) Locationname: h3o_feettags: location = coyote_creektime stddev-----2015-08-13T00:00:00Z 2.24372630801939852015-08-20T00:00:00Z 2.1212761501447192015-08-27T00:00:00Z 3.041621707862152015-09-03T00:00:00Z 2.53480650254352072015-09-10T00:00:00Z 2.5840039548826732015-09: 00Rue 00Z 2.2587514836274414name: h3o_feettags: location = santa_monicatime stddev-----2015-08-13T00:00:00Z 1.111563445875532015-08-20T00:00:00Z 1.09098492790823662015-08-27T00:00:00Z 1.987011600969622015-09-03T00:00:00Z 1.35167784509020672015-09-10T00:00:00Z 1 .49605738115005882015-09-17T00:00:00Z 1.075701669442093