InfluxQL analysis functions

Use technical analysis functions to apply algorithms to your data–often used to analyze financial and investment data.

Each analysis function below covers syntax, including parameters to pass to the function, and examples of how to use the function. Examples use NOAA water sample data.

• Predictive analysis:
  • HOLT_WINTERS()
• Technical analysis:
  • CHANDE_MOMENTUM_OSCILLATOR()
  • EXPONENTIAL_MOVING_AVERAGE()
  • DOUBLE_EXPONENTIAL_MOVING_AVERAGE()
  • KAUFMANS_EFFICIENCY_RATIO()
  • KAUFMANS_ADAPTIVE_MOVING_AVERAGE()
  • TRIPLE_EXPONENTIAL_MOVING_AVERAGE()
  • TRIPLE_EXPONENTIAL_DERIVATIVE()
  • RELATIVE_STRENGTH_INDEX()

Predictive analysis

Predictive analysis functions are a type of technical analysis algorithms that predict and forecast future values.

HOLT_WINTERS()

Returns N number of predicted field values using the Holt-Winters seasonal method. Supports int64 and float64 field value data types. Works with data that occurs at consistent time intervals. Requires an InfluxQL function and the GROUP BY time() clause to ensure that the Holt-Winters function operates on regular data.

Use HOLT_WINTERS() to:

• Predict when data values will cross a given threshold
• Compare predicted values with actual values to detect anomalies in your data

Syntax

SELECT HOLT_WINTERS[_WITH-FIT](<function>(<field_key>),<N>,<S>) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

HOLT_WINTERS(function(field_key),N,S) returns N seasonally adjusted predicted field values for the specified field key.

The N predicted values occur at the same interval as the GROUP BY time() interval. If your GROUP BY time() interval is 6m and N is 3 you’ll receive three predicted values that are each six minutes apart.

S is the seasonal pattern parameter and delimits the length of a seasonal pattern according to the GROUP BY time() interval. If your GROUP BY time() interval is 2m and S is 3, then the seasonal pattern occurs every six minutes, that is, every three data points. If you do not want to seasonally adjust your predicted values, set S to 0 or 1.

HOLT_WINTERS_WITH_FIT(function(field_key),N,S) returns the fitted values in addition to N seasonally adjusted predicted field values for the specified field key.

Examples

Predict field values associated with a field key

Sample data

The examples use the following subset of the NOAA water sample data:

SELECT "water_level" FROM "noaa"."autogen"."h2o_feet" WHERE "location"='santa_monica' AND time >= '2019-08-17T00:00:00Z' AND time <= '2019-08-22T00:00:00Z'

Step 1: Match the trends of the raw data

Write a GROUP BY time() query that matches the general trends of the raw water_level data. Here, we use the FIRST() function:

SELECT FIRST("water_level") FROM "noaa"."autogen"."h2o_feet" WHERE "location"='santa_monica' and time >= '2019-08-17T00:00:00Z' AND time <= '2019-08-22T00:00:00Z' GROUP BY time(6h,6h)

In the GROUP BY time() clause, the first argument (6h) matches the length of time that occurs between each peak and trough in the water_level data. The second argument (6h) is the offset interval. The offset interval alters the default GROUP BY time() boundaries to match the time range of the raw data.

Step 2: Determine the seasonal pattern

Identify the seasonal pattern in the data using the information from the query in step 1.

The pattern in the water_level data repeats about every 12 hours. There are two data points per season, so 2 is the seasonal pattern argument.

Step 3: Apply the HOLT_WINTERS() function

Add the Holt-Winters function to the query. Here, we use HOLT_WINTERS_WITH_FIT() to view both the fitted values and the predicted values:

SELECT HOLT_WINTERS_WITH_FIT(FIRST("water_level"),10,2) FROM "noaa"."autogen"."h2o_feet" WHERE "location"='santa_monica' AND time >= '2019-08-17 00:00:00' AND time <= '2019-08-22 00:00:00' GROUP BY time(6h,6h)

In the HOLT_WINTERS_WITH_FIT() function, the first argument (10) requests 10 predicted field values. Each predicted point is 6h apart, the same interval as the first argument in the GROUP BY time() clause. The second argument in the HOLT_WINTERS_WITH_FIT() function (2) is the seasonal pattern that we determined in the previous step.

Common issues with HOLT_WINTERS()

Receiving fewer than N points

In some cases, you may receive fewer predicted points than requested by the N parameter. That behavior typically occurs when the math becomes unstable and cannot forecast more points. In this case, HOLT_WINTERS() may not be suited for the dataset or the seasonal adjustment parameter is invalid.

Technical analysis functions

Technical analysis functions apply widely used algorithms to your data. While they are primarily used in finance and investing, they have application in other industries.

For technical analysis functions, consider whether to include the PERIOD, HOLD_PERIOD, and WARMUP_TYPE arguments:

PERIOD

Required, integer, min=1

The sample size for the algorithm, which is the number of historical samples with significant effect on the output of the algorithm. For example, 2 means the current point and the point before it. The algorithm uses an exponential decay rate to determine the weight of a historical point, generally known as the alpha (α). The PERIOD controls the decay rate.

HOLD_PERIOD

integer, min=-1

How many samples the algorithm needs before emitting results. The default of -1 means the value is based on the algorithm, the PERIOD, and the WARMUP_TYPE. Verify this value is enough for the algorithm to emit meaningful results.

Default hold periods:

For most technical analysis functions, the default HOLD_PERIOD is determined by the function and the WARMUP_TYPE shown in the following table:

Kaufman algorithm default hold periods:

WARMUP_TYPE

default=‘exponential’

Controls how the algorithm initializes for the first PERIOD samples. It is essentially the duration for which it has an incomplete sample set.

simple

Simple moving average (SMA) of the first PERIOD samples. This is the method used by ta-lib.

exponential

Exponential moving average (EMA) with scaling alpha (α). Uses an EMA with PERIOD=1 for the first point, PERIOD=2 for the second point, and so on, until the algorithm has consumed PERIOD number of points. As the algorithm immediately starts using an EMA, when this method is used and HOLD_PERIOD is unspecified or -1, the algorithm may start emitting points after a much smaller sample size than with simple.

none

The algorithm does not perform any smoothing at all. Method used by ta-lib. When this method is used and HOLD_PERIOD is unspecified, HOLD_PERIOD defaults to PERIOD - 1.

CHANDE_MOMENTUM_OSCILLATOR()

The Chande Momentum Oscillator (CMO) is a technical momentum indicator developed by Tushar Chande. The CMO indicator is created by calculating the difference between the sum of all recent higher data points and the sum of all recent lower data points, then dividing the result by the sum of all data movement over a given time period. The result is multiplied by 100 to give the -100 to +100 range. ^Source

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use CHANDE_MOMENTUM_OSCILLATOR() with a GROUP BY time() clause, see Advanced syntax.

Basic syntax

CHANDE_MOMENTUM_OSCILLATOR([ * | <field_key> | /regular_expression/ ], <period>[, <hold_period>, [warmup_type]])

Arguments

• period
• (Optional) hold_period
• (Optional) warmup_type

CHANDE_MOMENTUM_OSCILLATOR(field_key, 2)
Returns the field values associated with the field key processed using the Chande Momentum Oscillator algorithm with a 2-value period and the default hold period and warmup type.

CHANDE_MOMENTUM_OSCILLATOR(field_key, 10, 9, 'none')
Returns the field values associated with the field key processed using the Chande Momentum Oscillator algorithm with a 10-value period a 9-value hold period, and the none warmup type.

CHANDE_MOMENTUM_OSCILLATOR(MEAN(<field_key>), 2) ... GROUP BY time(1d)
Returns the mean of field values associated with the field key processed using the Chande Momentum Oscillator algorithm with a 2-value period and the default hold period and warmup type.

CHANDE_MOMENTUM_OSCILLATOR(/regular_expression/, 2)
Returns the field values associated with each field key that matches the regular expression processed using the Chande Momentum Oscillator algorithm with a 2-value period and the default hold period and warmup type.

CHANDE_MOMENTUM_OSCILLATOR(*, 2)
Returns the field values associated with each field key in the measurement processed using the Chande Momentum Oscillator algorithm with a 2-value period and the default hold period and warmup type.

CHANDE_MOMENTUM_OSCILLATOR() supports int64 and float64 field value data types.

EXPONENTIAL_MOVING_AVERAGE()

An exponential moving average (EMA) (or exponentially weighted moving average) is a type of moving average similar to a simple moving average, except more weight is given to the latest data.

This type of moving average reacts faster to recent data changes than a simple moving average. ^Source

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use EXPONENTIAL_MOVING_AVERAGE() with a GROUP BY time() clause, see Advanced syntax.

Basic syntax

EXPONENTIAL_MOVING_AVERAGE([ * | <field_key> | /regular_expression/ ], <period>[, <hold_period)[, <warmup_type]])

EXPONENTIAL_MOVING_AVERAGE(field_key, 2)
Returns the field values associated with the field key processed using the Exponential Moving Average algorithm with a 2-value period and the default hold period and warmup type.

EXPONENTIAL_MOVING_AVERAGE(field_key, 10, 9, 'exponential')
Returns the field values associated with the field key processed using the Exponential Moving Average algorithm with a 10-value period a 9-value hold period, and the exponential warmup type.

EXPONENTIAL_MOVING_AVERAGE(MEAN(<field_key>), 2) ... GROUP BY time(1d)
Returns the mean of field values associated with the field key processed using the Exponential Moving Average algorithm with a 2-value period and the default hold period and warmup type.

EXPONENTIAL_MOVING_AVERAGE(/regular_expression/, 2)
Returns the field values associated with each field key that matches the regular expression processed using the Exponential Moving Average algorithm with a 2-value period and the default hold period and warmup type.

EXPONENTIAL_MOVING_AVERAGE(*, 2)
Returns the field values associated with each field key in the measurement processed using the Exponential Moving Average algorithm with a 2-value period and the default hold period and warmup type.

EXPONENTIAL_MOVING_AVERAGE() supports int64 and float64 field value data types.

Arguments

• period
• (Optional) hold_period
• (Optional) warmup_type

DOUBLE_EXPONENTIAL_MOVING_AVERAGE()

The Double Exponential Moving Average (DEMA) attempts to remove the inherent lag associated with moving averages by placing more weight on recent values. The name suggests this is achieved by applying a double exponential smoothing which is not the case. The value of an EMA is doubled. To keep the value in line with the actual data and to remove the lag, the value “EMA of EMA” is subtracted from the previously doubled EMA. ^Source

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use DOUBLE_EXPONENTIAL_MOVING_AVERAGE() with a GROUP BY time() clause, see Advanced syntax.

Basic syntax

DOUBLE_EXPONENTIAL_MOVING_AVERAGE([ * | <field_key> | /regular_expression/ ], <period>[, <hold_period)[, <warmup_type]])

DOUBLE_EXPONENTIAL_MOVING_AVERAGE(field_key, 2)
Returns the field values associated with the field key processed using the Double Exponential Moving Average algorithm with a 2-value period and the default hold period and warmup type.

DOUBLE_EXPONENTIAL_MOVING_AVERAGE(field_key, 10, 9, 'exponential')
Returns the field values associated with the field key processed using the Double Exponential Moving Average algorithm with a 10-value period a 9-value hold period, and the exponential warmup type.

DOUBLE_EXPONENTIAL_MOVING_AVERAGE(MEAN(<field_key>), 2) ... GROUP BY time(1d)
Returns the mean of field values associated with the field key processed using the Double Exponential Moving Average algorithm with a 2-value period and the default hold period and warmup type.

DOUBLE_EXPONENTIAL_MOVING_AVERAGE(/regular_expression/, 2)
Returns the field values associated with each field key that matches the regular expression processed using the Double Exponential Moving Average algorithm with a 2-value period and the default hold period and warmup type.

DOUBLE_EXPONENTIAL_MOVING_AVERAGE(*, 2)
Returns the field values associated with each field key in the measurement processed using the Double Exponential Moving Average algorithm with a 2-value period and the default hold period and warmup type.

DOUBLE_EXPONENTIAL_MOVING_AVERAGE() supports int64 and float64 field value data types.

Arguments

• period
• (Optional) hold_period
• (Optional) warmup_type

KAUFMANS_EFFICIENCY_RATIO()

Kaufman’s Efficiency Ration, or simply “Efficiency Ratio” (ER), is calculated by dividing the data change over a period by the absolute sum of the data movements that occurred to achieve that change. The resulting ratio ranges between 0 and 1 with higher values representing a more efficient or trending market.

The ER is very similar to the Chande Momentum Oscillator (CMO). The difference is that the CMO takes market direction into account, but if you take the absolute CMO and divide by 100, you you get the Efficiency Ratio. ^Source

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use KAUFMANS_EFFICIENCY_RATIO() with a GROUP BY time() clause, see Advanced syntax.

Basic syntax

KAUFMANS_EFFICIENCY_RATIO([ * | <field_key> | /regular_expression/ ], <period>[, <hold_period>])

KAUFMANS_EFFICIENCY_RATIO(field_key, 2)
Returns the field values associated with the field key processed using the Efficiency Index algorithm with a 2-value period and the default hold period and warmup type.

KAUFMANS_EFFICIENCY_RATIO(field_key, 10, 10)
Returns the field values associated with the field key processed using the Efficiency Index algorithm with a 10-value period and a 10-value hold period.

KAUFMANS_EFFICIENCY_RATIO(MEAN(<field_key>), 2) ... GROUP BY time(1d)
Returns the mean of field values associated with the field key processed using the Efficiency Index algorithm with a 2-value period and the default hold period.

KAUFMANS_EFFICIENCY_RATIO(/regular_expression/, 2)
Returns the field values associated with each field key that matches the regular expression processed using the Efficiency Index algorithm with a 2-value period and the default hold period and warmup type.

KAUFMANS_EFFICIENCY_RATIO(*, 2)
Returns the field values associated with each field key in the measurement processed using the Efficiency Index algorithm with a 2-value period and the default hold period and warmup type.

KAUFMANS_EFFICIENCY_RATIO() supports int64 and float64 field value data types.

Arguments:

• period
• (Optional) hold_period

KAUFMANS_ADAPTIVE_MOVING_AVERAGE()

Kaufman’s Adaptive Moving Average (KAMA) is a moving average designed to account for sample noise or volatility. KAMA will closely follow data points when the data swings are relatively small and noise is low. KAMA will adjust when the data swings widen and follow data from a greater distance. This trend-following indicator can be used to identify the overall trend, time turning points and filter data movements. ^Source

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use KAUFMANS_ADAPTIVE_MOVING_AVERAGE() with a GROUP BY time() clause, see Advanced syntax.

Basic syntax

KAUFMANS_ADAPTIVE_MOVING_AVERAGE([ * | <field_key> | /regular_expression/ ], <period>[, <hold_period>])

KAUFMANS_ADAPTIVE_MOVING_AVERAGE(field_key, 2)
Returns the field values associated with the field key processed using the Kaufman Adaptive Moving Average algorithm with a 2-value period and the default hold period and warmup type.

KAUFMANS_ADAPTIVE_MOVING_AVERAGE(field_key, 10, 10)
Returns the field values associated with the field key processed using the Kaufman Adaptive Moving Average algorithm with a 10-value period and a 10-value hold period.

KAUFMANS_ADAPTIVE_MOVING_AVERAGE(MEAN(<field_key>), 2) ... GROUP BY time(1d)
Returns the mean of field values associated with the field key processed using the Kaufman Adaptive Moving Average algorithm with a 2-value period and the default hold period.

KAUFMANS_ADAPTIVE_MOVING_AVERAGE(/regular_expression/, 2)
Returns the field values associated with each field key that matches the regular expression processed using the Kaufman Adaptive Moving Average algorithm with a 2-value period and the default hold period and warmup type.

KAUFMANS_ADAPTIVE_MOVING_AVERAGE(*, 2)
Returns the field values associated with each field key in the measurement processed using the Kaufman Adaptive Moving Average algorithm with a 2-value period and the default hold period and warmup type.

KAUFMANS_ADAPTIVE_MOVING_AVERAGE() supports int64 and float64 field value data types.

Arguments:

• period
• (Optional) hold_period

TRIPLE_EXPONENTIAL_MOVING_AVERAGE()

The triple exponential moving average (TEMA) filters out volatility from conventional moving averages. While the name implies that it’s a triple exponential smoothing, it’s actually a composite of a single exponential moving average, a double exponential moving average, and a triple exponential moving average. ^Source

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use TRIPLE_EXPONENTIAL_MOVING_AVERAGE() with a GROUP BY time() clause, see Advanced syntax.

Basic syntax

TRIPLE_EXPONENTIAL_MOVING_AVERAGE([ * | <field_key> | /regular_expression/ ], <period>[, <hold_period)[, <warmup_type]])

TRIPLE_EXPONENTIAL_MOVING_AVERAGE(field_key, 2)
Returns the field values associated with the field key processed using the Triple Exponential Moving Average algorithm with a 2-value period and the default hold period and warmup type.

TRIPLE_EXPONENTIAL_MOVING_AVERAGE(field_key, 10, 9, 'exponential')
Returns the field values associated with the field key processed using the Triple Exponential Moving Average algorithm with a 10-value period a 9-value hold period, and the exponential warmup type.

TRIPLE_EXPONENTIAL_MOVING_AVERAGE(MEAN(<field_key>), 2) ... GROUP BY time(1d)
Returns the mean of field values associated with the field key processed using the Triple Exponential Moving Average algorithm with a 2-value period and the default hold period and warmup type.

TRIPLE_EXPONENTIAL_MOVING_AVERAGE(/regular_expression/, 2)
Returns the field values associated with each field key that matches the regular expression processed using the Triple Exponential Moving Average algorithm with a 2-value period and the default hold period and warmup type.

TRIPLE_EXPONENTIAL_MOVING_AVERAGE(*, 2)
Returns the field values associated with each field key in the measurement processed using the Triple Exponential Moving Average algorithm with a 2-value period and the default hold period and warmup type.

TRIPLE_EXPONENTIAL_MOVING_AVERAGE() supports int64 and float64 field value data types.

Arguments:

• period
• (Optional) hold_period
• (Optional) warmup_type

TRIPLE_EXPONENTIAL_DERIVATIVE()

The triple exponential derivative indicator, commonly referred to as “TRIX,” is an oscillator used to identify oversold and overbought markets, and can also be used as a momentum indicator. TRIX calculates a triple exponential moving average of the log of the data input over the period of time. The previous value is subtracted from the previous value. This prevents cycles that are shorter than the defined period from being considered by the indicator.

Like many oscillators, TRIX oscillates around a zero line. When used as an oscillator, a positive value indicates an overbought market while a negative value indicates an oversold market. When used as a momentum indicator, a positive value suggests momentum is increasing while a negative value suggests momentum is decreasing. Many analysts believe that when the TRIX crosses above the zero line it gives a buy signal, and when it closes below the zero line, it gives a sell signal. ^Source

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use TRIPLE_EXPONENTIAL_DERIVATIVE() with a GROUP BY time() clause, see Advanced syntax.

Basic syntax

TRIPLE_EXPONENTIAL_DERIVATIVE([ * | <field_key> | /regular_expression/ ], <period>[, <hold_period)[, <warmup_type]])

TRIPLE_EXPONENTIAL_DERIVATIVE(field_key, 2)
Returns the field values associated with the field key processed using the Triple Exponential Derivative algorithm with a 2-value period and the default hold period and warmup type.

TRIPLE_EXPONENTIAL_DERIVATIVE(field_key, 10, 10, 'exponential')
Returns the field values associated with the field key processed using the Triple Exponential Derivative algorithm with a 10-value period, a 10-value hold period, and the exponential warmup type.

TRIPLE_EXPONENTIAL_DERIVATIVE(MEAN(<field_key>), 2) ... GROUP BY time(1d)
Returns the mean of field values associated with the field key processed using the Triple Exponential Derivative algorithm with a 2-value period and the default hold period and warmup type.

TRIPLE_EXPONENTIAL_DERIVATIVE(/regular_expression/, 2)
Returns the field values associated with each field key that matches the regular expression processed using the Triple Exponential Derivative algorithm with a 2-value period and the default hold period and warmup type.

TRIPLE_EXPONENTIAL_DERIVATIVE(*, 2)
Returns the field values associated with each field key in the measurement processed using the Triple Exponential Derivative algorithm with a 2-value period and the default hold period and warmup type.

TRIPLE_EXPONENTIAL_DERIVATIVE() supports int64 and float64 field value data types.

RELATIVE_STRENGTH_INDEX()

The relative strength index (RSI) is a momentum indicator that compares the magnitude of recent increases and decreases over a specified time period to measure speed and change of data movements. ^Source

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time.

To use RELATIVE_STRENGTH_INDEX() with a GROUP BY time() clause, see Advanced syntax.

Basic syntax

RELATIVE_STRENGTH_INDEX([ * | <field_key> | /regular_expression/ ], <period>[, <hold_period)[, <warmup_type]])

RELATIVE_STRENGTH_INDEX(field_key, 2)
Returns the field values associated with the field key processed using the Relative Strength Index algorithm with a 2-value period and the default hold period and warmup type.

RELATIVE_STRENGTH_INDEX(field_key, 10, 10, 'exponential')
Returns the field values associated with the field key processed using the Relative Strength Index algorithm with a 10-value period, a 10-value hold period, and the exponential warmup type.

RELATIVE_STRENGTH_INDEX(MEAN(<field_key>), 2) ... GROUP BY time(1d)
Returns the mean of field values associated with the field key processed using the Relative Strength Index algorithm with a 2-value period and the default hold period and warmup type.

RELATIVE_STRENGTH_INDEX(/regular_expression/, 2)
Returns the field values associated with each field key that matches the regular expression processed using the Relative Strength Index algorithm with a 2-value period and the default hold period and warmup type.

RELATIVE_STRENGTH_INDEX(*, 2)
Returns the field values associated with each field key in the measurement processed using the Relative Strength Index algorithm with a 2-value period and the default hold period and warmup type.

RELATIVE_STRENGTH_INDEX() supports int64 and float64 field value data types.

Arguments:

• period
• (Optional) hold_period
• (Optional) warmup_type