INFLUXDB Learning influxdb Common functions (III.) Transformation class functions

1) derivative () function

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

Influxdb calculates the difference between the field values sorted by time and translates these results to the unit change rate. Where the unit can be specified, the default is 1s.

Grammar:

SELECT derivative (<field_key>, [<unit>]) from <measurement_name> [WHERE <stuff>]

Where the unit value can be the following:

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

The derivative () function can also be nested with an aggregate function under the conditions of Group by Time () in the following format:

SELECT derivative (aggregation_function (<field_key>), [<unit>]) from <measurement_name> WHERE < Stuff> GROUP by Time (<aggregation_interval>)

Example:

Suppose the location = Santa_monica condition has the following data:

Name:h2o_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 per second:

> SELECT derivative (water_level) from h2o_feet WHERE location = ' Santa_monica ' LIMIT 5name:h2o_feet--------------time derivative2015-08-18t00:06:00z 0.000144444444444444572015-08-18t00:12:00z-0.0002444444 44444444652015-08-18t00:18:00z 0.00027222222222222182015-08-18t00:24:00z-0.0002361111111111112015-08-18t00 : 30:00z 2.777777777777842e-05

The first row of data is calculated as (2.116-2.064)/(360S/1S)

Calculate the rate of change every six minutes

> SELECT derivative (water_level,6m) from h2o_feet WHERE location = ' Santa_monica ' LIMIT 5name:h2o_feet--------------t IME derivative2015-08-18t00:06:00z 0.0520000000000000462015-08-18t00:12:00z-0.088000000     000000082015-08-18t00:18:00z 0.097999999999999862015-08-18t00:24:00z-0.084999999999999962015-08-18t00:30:00z 0.010000000000000231

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

Calculate the rate of change every 12 minutes:

> SELECT derivative (water_level,12m) from h2o_feet WHERE location = ' Santa_monica ' LIMIT 5name:h2o_feet-------------- Time derivative2015-08-18t00:06:00z 0.104000000000000092015-08-18t00:12:00z-0.176000000     000000162015-08-18t00:18:00z 0.195999999999999732015-08-18t00:24:00z-0.169999999999999932015-08-18t00:30:00z 0.020000000000000462

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

Calculates the rate of change for the maximum value every 12 minutes

> SELECT derivative (MAX (water_level)) from H2o_feet WHERE location = ' Santa_monica ' and Time >= ' 2015-08-18t00:00:00 Z ' and Time < ' 2015-08-18t00:36:00z ' GROUP by Time (12m) Name:h2o_feet--------------time Deriv ative2015-08-18t00:12:00z 0.0099999999999997872015-08-18t00:24:00z-0.07499999999999973

This function is very versatile, very complex, more detailed explanation of this function please crossing: https://docs.influxdata.com/influxdb/v0.13/query_language/functions/#derivative

2) difference () function

Effect: Returns the difference between successive time values in a field. The field type must be Long integer or float64.

The most basic syntax:

SELECT difference (<field_key>) from <measurement_name> [WHERE <stuff>]

Syntax format used with GROUP by Time () and other nested functions:

SELECT difference (<function> (<field_key>)) from <measurement_name> WHERE <stuff> GROUP by Time (<time_interval>)

Where functions can contain the following:

COUNT (), MEAN (), MEDIAN (), SUM (), first (), Last (), MIN (), MAX (), and percentile ().

Using the example

The source data used in the example is as follows:

> SELECT water_level from H2o_feet WHERE location= ' Santa_monica ' and Time >= ' 2015-08-18t00:00:00z ' and Time <= ' 2015-08-18t00:36:00z ' Name:h2o_feet--------------time water_level2015-08-18t00:00:00z 2.06 42015-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.0512015-08-18t00:36:00z 2.067

Calculate the difference between Water_level:

> SELECT Difference (water_level) from H2o_feet WHERE location= ' Santa_monica ' and Time >= ' 2015-08-18t00:00:00z ' and Time <= ' 2015-08-18t00:36:00z ' Name:h2o_feet--------------time difference2015-08-18t00:06:0 0Z 0.0520000000000000462015-08-18t00:12:00z-0.088000000000000082015-08-18t00:18:00z 0.09799999999999986201 5-08-18t00:24:00z-0.084999999999999962015-08-18t00:30:00z 0.0100000000000002312015-08-18t00:36:00z 0.01600 0000000000014

Data types are of type float.

3) ELAPSED () function

Function: Returns the difference between a field at successive intervals, with the interval unit optional, which defaults to 1 nanoseconds.

Grammar:

SELECT ELAPSED (<field_key>, <unit>) from <measurement_name> [WHERE <stuff>]

Example:

Calculates the difference in the H2o_feet field at the nanosecond interval.

> SELECT ELAPSED (water_level) from h2o_feet WHERE location = ' Santa_monica ' and Time >= ' 2015-08-18t00:00:00z ' and t      IME <= ' 2015-08-18t00:24:00z ' Name:h2o_feet--------------time elapsed2015-08-18t00:06:00z 3600000000002015-08-18t00:12:00z 3600000000002015-08-18t00:18:00z 3600000000002015-08-18t00:24:00z 36000 0000000

Variance rate at one-minute intervals:

> SELECT ELAPSED (water_level,1m) from h2o_feet WHERE location = ' Santa_monica ' and Time >= ' 2015-08-18t00:00:00z ' an       D time <= ' 2015-08-18t00:24:00z ' Name:h2o_feet--------------time elapsed2015-08-18t00:06:00z 62015-08-18t00:12:00z 62015-08-18t00:18:00z 62015-08-18t00:24:00z 6

Note: If you set the time interval to be larger than the time interval between field data, the function returns 0, as follows:

> SELECT ELAPSED (water_level,1h) from h2o_feet WHERE location = ' Santa_monica ' and Time >= ' 2015-08-18t00:00:00z ' an       D time <= ' 2015-08-18t00:24:00z ' Name:h2o_feet--------------time elapsed2015-08-18t00:06:00z 02015-08-18t00:12:00z 02015-08-18t00:18:00z 02015-08-18t00:24:00z 0

4) Moving_average () function

Function: Returns the moving average of a continuous field value, which must be a long or float64 type.

Grammar:

Basic syntax

SELECT Moving_average (<field_key>,<window>) from <measurement_name> [WHERE <stuff>]

Syntax when used with other functions and group by TIME () statements

SELECT moving_average (<function> (<field_key>),<window>) from <measurement_name> WHERE < Stuff> GROUP by Time (<time_interval>)

This function can be used with the following functions:

COUNT (), MEAN (), MEDIAN (), SUM (), first (), Last (), MIN (), MAX (), and percentile ().

Example:

> SELECT water_level from h2o_feet WHERE location = ' Santa_monica ' and Time >= ' 2015-08-18t00:00:00z ' and time <= ' 2015-08-18t00:36:00z ' Name:h2o_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.0512015-08-18t00:36:00z 2.067

5) non_negative_derivative () function

Function: Returns the non-negative rate at which the value changes in a field in a series.

Grammar:

SELECT non_negative_derivative (<field_key>, [<unit>]) from <measurement_name> [WHERE <stuff> ]

The syntax for working with aggregate class functions is as follows:

SELECT non_negative_derivative (Aggregation_function (<field_key>), [<unit>]) from <measurement_name > WHERE <stuff> GROUP by Time (<aggregation_interval>)

For an example of this function, see: derivative () function

6) STDDEV () function

Function: Returns the standard deviation of a value in a field. The type of the value must be a long integer or float64 type.

Grammar:

SELECT STDDEV (<field_key>) from <measurement_name> [WHERE <stuff>] [GROUP by <stuff>]

Example:

> SELECT STDDEV (water_level) from H2o_feetname:h2o_feet--------------time stddev1970-01-01t0 0:00:00z 2.279144584196145

Example 2:

> select stddev (water_level)  FROM h2o_feet WHERE time >=  ' 2015-08-18t00:00:00z '  and time <  ' 2015-09-18t12:06:00z '  group by time (1w ), locationname: h2o_feettags: location = coyote_creektime                             stddev----                            ------ 2015-08-13t00:00:00z     2.24372630801939852015-08-20t00:00:00z      2.1212761501447192015-08-27t00:00:00z     3.04161221707862152015-09-03t00 :00:00z     2.53480650254352072015-09-10t00:00:00z      2.5840039548826732015-09-17t00:00:00z      2.2587514836274414name: h2o_feettags: location = santa_monicatime                             stddev----                             ------2015-08-13t00:00:00z     1.111563445875532015-08-20t00:00:00z      1.09098492790823662015-08-27T00:00:00Z      1.98701161800969622015-09-03t00:00:00z     1.35167784509020672015-09-10t00:00:00z      1.49605738115005882015-09-17T00:00:00Z      1.075701669442093

This article is from the "DBSpace" blog, so be sure to keep this source http://dbspace.blog.51cto.com/6873717/1880957

INFLUXDB Learning influxdb Common functions (III.) Transformation class functions