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
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
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.
Note: Older points can still have an impact.
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:
| Algorithm \ Warmup Type | simple | exponential | none |
|---|---|---|---|
| EXPONENTIAL_MOVING_AVERAGE | PERIOD - 1 | PERIOD - 1 | n/a |
| DOUBLE_EXPONENTIAL_MOVING_AVERAGE | ( PERIOD - 1 ) * 2 | PERIOD - 1 | n/a |
| TRIPLE_EXPONENTIAL_MOVING_AVERAGE | ( PERIOD - 1 ) * 3 | PERIOD - 1 | n/a |
| TRIPLE_EXPONENTIAL_DERIVATIVE | ( PERIOD - 1 ) * 3 + 1 | PERIOD | n/a |
| RELATIVE_STRENGTH_INDEX | PERIOD | PERIOD | n/a |
| CHANDE_MOMENTUM_OSCILLATOR | PERIOD | PERIOD | PERIOD - 1 |
Kaufman algorithm default hold periods:
| Algorithm | Default Hold Period |
|---|---|
| KAUFMANS_EFFICIENCY_RATIO() | PERIOD |
| KAUFMANS_ADAPTIVE_MOVING_AVERAGE() | PERIOD |
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.
Note: The none warmup type is only available with the CHANDE_MOMENTUM_OSCILLATOR() function.
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.
Note: When aggregating data with a GROUP BY clause, you must include an aggregate function in your call to the CHANDE_MOMENTUM_OSCILLATOR() function.
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.
Note: When aggregating data with a GROUP BY clause, you must include an aggregate function in your call to the EXPONENTIAL_MOVING_AVERAGE() function.
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.
Note: When aggregating data with a GROUP BY clause, you must include an aggregate function in your call to the DOUBLE_EXPONENTIAL_MOVING_AVERAGE() function.
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.
Note: When aggregating data with a GROUP BY clause, you must include an aggregate function in your call to the KAUFMANS_EFFICIENCY_RATIO() function.
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.
Note: When aggregating data with a GROUP BY clause, you must include an aggregate function in your call to the KAUFMANS_ADAPTIVE_MOVING_AVERAGE() function.
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.
Note: When aggregating data with a GROUP BY clause, you must include an aggregate function in your call to the TRIPLE_EXPONENTIAL_MOVING_AVERAGE() function.
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.
Note: When aggregating data with a GROUP BY clause, you must include an aggregate function in your call to the TRIPLE_EXPONENTIAL_DERIVATIVE() function.
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.
Note: When aggregating data with a GROUP BY clause, you must include an aggregate function in your call to the RELATIVE_STRENGTH_INDEX() function.
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
Was this page helpful?
Thank you for your feedback!
Support and feedback
Thank you for being part of our community! We welcome and encourage your feedback and bug reports for InfluxDB and this documentation. To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support.