InfluxQL analysis functions

Use technical analysis functions to apply algorithms to your time series data. Many of these algorithms are often used to analyze financial and investment data, but have application in other use cases as well.

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

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. HOLT_WINTERS_WITH_FIT() returns the fitted values in addition to N seasonally adjusted predicted field values.

Input data points must occur at regular time intervals. To ensure regular time intervals, HOLT_WINTERS requires an aggregate expression as input and a a GROUP BY time() to apply the aggregate operation at regular intervals.

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

HOLT_WINTERS[_WITH_FIT](aggregate_expression, N, S)

Arguments

• aggregate_expression: Aggregate operation on a specified expression. The operation can use any aggregate function. The expression can operate on a field key, constant, regular expression, or wildcard (*). Supports numeric fields.
• N: Number of values to predict. Predicted values occur at the same interval specified in the GROUP BY time() clause.
• S: Seasonal pattern length (number of values per season) to use when adjusting for seasonal patterns. To not seasonally adjust predicted values, set S to 0 or 1.

Notable behaviors

• In some cases, you may receive fewer than N predicted points. This typically occurs when the seasonal adjustment (S) is invalid or when input data is not suited for the Holt Winters algorithm.

Examples

The following examples use the NOAA Bay Area weather sample data.

SELECT
  HOLT_WINTERS(MEAN(temp_avg), 12, 12)
FROM weather
WHERE
  location = 'San Francisco'
  AND time >= '2020-01-01T00:00:00Z'
  AND time <= '2022-12-31T00:00:00Z'
GROUP BY time(30d)

SELECT
  HOLT_WINTERS(MEAN(temp_avg), 12, 0)
FROM weather
WHERE
  location = 'San Francisco'
  AND time >= '2020-01-01T00:00:00Z'
  AND time <= '2022-12-31T00:00:00Z'
GROUP BY time(30d)

SELECT
  HOLT_WINTERS_WITH_FIT(MEAN(temp_avg), 12, 12)
FROM weather
WHERE
  location = 'San Francisco'
  AND time >= '2021-01-01T00:00:00Z'
  AND time <= '2022-12-31T00:00:00Z'
GROUP BY time(30d)

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.

• Notable behaviors of technical analysis functions
• CHANDE_MOMENTUM_OSCILLATOR()
• DOUBLE_EXPONENTIAL_MOVING_AVERAGE()
• EXPONENTIAL_MOVING_AVERAGE()
• KAUFMANS_EFFICIENCY_RATIO()
• KAUFMANS_ADAPTIVE_MOVING_AVERAGE()
• RELATIVE_STRENGTH_INDEX()
• TRIPLE_EXPONENTIAL_MOVING_AVERAGE()
• TRIPLE_EXPONENTIAL_DERIVATIVE()

Notable behaviors of technical analysis functions

Must use aggregate or selector functions when grouping by time

All technical analysis functions support GROUP BY clauses that group by tags, but do not directly support GROUP BY clauses that group by time. To use technical analysis functions with with a GROUP BY time() clause, apply an aggregate or selector function to the field_expression argument. The technical analysis function operates on the result of the aggregate or selector operation.

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

CHANDE_MOMENTUM_OSCILLATOR(field_expression, period[, hold_period[, warmup_type]])

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric field types.

• period: Number of values to use as the sample size for the algorithm. Supports integers greater than or equal to 1. This also controls the exponential decay rate (α) used to determine the weight of an historical value. With a period of 3, the algorithm uses the current value and the previous two values.

• hold_period: Number of values the algorithm needs before emitting results. Default is -1, which means the hold_period is determined by the warmup_type and period. Must be an integer greater than or equal to -1.

  Warmup type	Default hold_period
  exponential	period
  simple	period
  none	period - 1

• warmup_type: Controls how the algorithm initializes the first period values. Supports the following warmup types:

  • exponential: (Default) Exponential moving average of the first period values with scaling alpha (α). 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.
  • simple: Simple moving average of the first period values.
  • none: The algorithm does not perform any warmup at all.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

SELECT
  CHANDE_MOMENTUM_OSCILLATOR(temp, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  CHANDE_MOMENTUM_OSCILLATOR(*, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  CHANDE_MOMENTUM_OSCILLATOR(temp, 4, 6)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  CHANDE_MOMENTUM_OSCILLATOR(temp, 4, -1, 'simple')
FROM home
WHERE
  room = 'Kitchen'

SELECT
  CHANDE_MOMENTUM_OSCILLATOR(MEAN(temp_avg), 4)
FROM weather
WHERE
  location = 'San Francisco'
  AND time >= '2020-01-01T00:00:00Z'
  AND time <= '2022-12-31T23:59:59Z'
GROUP BY time(90d)

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

DOUBLE_EXPONENTIAL_MOVING_AVERAGE(field_expression, period[, hold_period[, warmup_type]])

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric field types.

• period: Number of values to use as the sample size for the algorithm. Supports integers greater than or equal to 1. This also controls the exponential decay rate (α) used to determine the weight of an historical value. With a period of 3, the algorithm uses the current value and the previous two values.

• hold_period: Number of values the algorithm needs before emitting results. Default is -1, which means the hold_period is determined by the warmup_type and period. Must be an integer greater than or equal to -1.

  Warmup type	Default hold_period
  exponential	period - 1
  simple	(period - 1) × 2

• warmup_type: Controls how the algorithm initializes the first period values. Supports the following warmup types:

  • exponential: (Default) Exponential moving average of the first period values with scaling alpha (α). 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.
  • simple: Simple moving average of the first period values.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

SELECT
  DOUBLE_EXPONENTIAL_MOVING_AVERAGE(temp, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  DOUBLE_EXPONENTIAL_MOVING_AVERAGE(*, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  DOUBLE_EXPONENTIAL_MOVING_AVERAGE(temp, 4, 6)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  DOUBLE_EXPONENTIAL_MOVING_AVERAGE(temp, 4, -1, 'simple')
FROM home
WHERE
  room = 'Kitchen'

SELECT
  DOUBLE_EXPONENTIAL_MOVING_AVERAGE(MEAN(temp_avg), 4)
FROM weather
WHERE
  location = 'San Francisco'
  AND time >= '2020-01-01T00:00:00Z'
  AND time <= '2022-12-31T23:59:59Z'
GROUP BY time(90d)

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

EXPONENTIAL_MOVING_AVERAGE(field_expression, period[, hold_period[, warmup_type]])

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric field types.

• period: Number of values to use as the sample size for the algorithm. Supports integers greater than or equal to 1. This also controls the exponential decay rate (α) used to determine the weight of an historical value. With a period of 3, the algorithm uses the current value and the previous two values.

• hold_period: Number of values the algorithm needs before emitting results. Default is -1, which means the hold_period is determined by the warmup_type and period. Must be an integer greater than or equal to -1.

  Warmup type	Default hold_period
  exponential	period - 1
  simple	period - 1

• warmup_type: Controls how the algorithm initializes the first period values. Supports the following warmup types:

  • exponential: (Default) Exponential moving average of the first period values with scaling alpha (α). 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.
  • simple: Simple moving average of the first period values.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

SELECT
  EXPONENTIAL_MOVING_AVERAGE(temp, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  EXPONENTIAL_MOVING_AVERAGE(*, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  EXPONENTIAL_MOVING_AVERAGE(temp, 4, 6)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  EXPONENTIAL_MOVING_AVERAGE(temp, 4, -1, 'simple')
FROM home
WHERE
  room = 'Kitchen'

SELECT
  EXPONENTIAL_MOVING_AVERAGE(MEAN(temp_avg), 4)
FROM weather
WHERE
  location = 'San Francisco'
  AND time >= '2020-01-01T00:00:00Z'
  AND time <= '2022-12-31T23:59:59Z'
GROUP BY time(90d)

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

KAUFMANS_EFFICIENCY_RATIO(field_expression, period[, hold_period])

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric field types.
• period: Number of values to use as the sample size for the algorithm. Supports integers greater than or equal to 1. This also controls the exponential decay rate (α) used to determine the weight of an historical value. With a period of 3, the algorithm uses the current value and the previous two values.
• hold_period: Number of values the algorithm needs before emitting results. Default is the same as period. Must be an integer greater than or equal to 1.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

SELECT
  KAUFMANS_EFFICIENCY_RATIO(temp, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  KAUFMANS_EFFICIENCY_RATIO(*, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  KAUFMANS_EFFICIENCY_RATIO(temp, 4, 6)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  KAUFMANS_EFFICIENCY_RATIO(MEAN(temp_avg), 4)
FROM weather
WHERE
  location = 'San Francisco'
  AND time >= '2020-01-01T00:00:00Z'
  AND time <= '2022-12-31T23:59:59Z'
GROUP BY time(90d)

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

KAUFMANS_ADAPTIVE_MOVING_AVERAGE(field_expression, period[, hold_period])

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric field types.
• period: Number of values to use as the sample size for the algorithm. Supports integers greater than or equal to 1. This also controls the exponential decay rate (α) used to determine the weight of an historical value. With a period of 3, the algorithm uses the current value and the previous two values.
• hold_period: Number of values the algorithm needs before emitting results. Default is the same as period. Must be an integer greater than or equal to 1.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

SELECT
  KAUFMANS_ADAPTIVE_MOVING_AVERAGE(temp, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  KAUFMANS_ADAPTIVE_MOVING_AVERAGE(*, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  KAUFMANS_ADAPTIVE_MOVING_AVERAGE(temp, 4, 6)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  KAUFMANS_ADAPTIVE_MOVING_AVERAGE(MEAN(temp_avg), 4)
FROM weather
WHERE
  location = 'San Francisco'
  AND time >= '2020-01-01T00:00:00Z'
  AND time <= '2022-12-31T23:59:59Z'
GROUP BY time(90d)

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

RELATIVE_STRENGTH_INDEX(field_expression, period[, hold_period[, warmup_type]])

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric field types.

• period: Number of values to use as the sample size for the algorithm. Supports integers greater than or equal to 1. This also controls the exponential decay rate (α) used to determine the weight of an historical value. With a period of 3, the algorithm uses the current value and the previous two values.

• hold_period: Number of values the algorithm needs before emitting results. Default is -1, which means the hold_period is the same as the period. Must be an integer greater than or equal to -1.

• warmup_type: Controls how the algorithm initializes the first period values. Supports the following warmup types:

  • exponential: (Default) Exponential moving average of the first period values with scaling alpha (α). 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.
  • simple: Simple moving average of the first period values.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

SELECT
  RELATIVE_STRENGTH_INDEX(temp, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  RELATIVE_STRENGTH_INDEX(*, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  RELATIVE_STRENGTH_INDEX(temp, 4, 6)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  RELATIVE_STRENGTH_INDEX(temp, 4, -1, 'simple')
FROM home
WHERE
  room = 'Kitchen'

SELECT
  RELATIVE_STRENGTH_INDEX(MEAN(temp_avg), 4)
FROM weather
WHERE
  location = 'San Francisco'
  AND time >= '2020-01-01T00:00:00Z'
  AND time <= '2022-12-31T23:59:59Z'
GROUP BY time(90d)

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

TRIPLE_EXPONENTIAL_MOVING_AVERAGE(field_expression, period[, hold_period[, warmup_type]])

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric field types.

• period: Number of values to use as the sample size for the algorithm. Supports integers greater than or equal to 1. This also controls the exponential decay rate (α) used to determine the weight of an historical value. With a period of 3, the algorithm uses the current value and the previous two values.

• hold_period: Number of values the algorithm needs before emitting results. Default is -1, which means the hold_period is determined by the warmup_type and period. Must be an integer greater than or equal to -1.

  Warmup type	Default hold_period
  exponential	period - 1
  simple	(period - 1) × 3

• warmup_type: Controls how the algorithm initializes the first period values. Supports the following warmup types:

  • exponential: (Default) Exponential moving average of the first period values with scaling alpha (α). 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.
  • simple: Simple moving average of the first period values.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

SELECT
  TRIPLE_EXPONENTIAL_MOVING_AVERAGE(temp, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  TRIPLE_EXPONENTIAL_MOVING_AVERAGE(*, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  TRIPLE_EXPONENTIAL_MOVING_AVERAGE(temp, 4, 6)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  TRIPLE_EXPONENTIAL_MOVING_AVERAGE(temp, 4, -1, 'simple')
FROM home
WHERE
  room = 'Kitchen'

SELECT
  TRIPLE_EXPONENTIAL_MOVING_AVERAGE(MEAN(temp_avg), 4)
FROM weather
WHERE
  location = 'San Francisco'
  AND time >= '2020-01-01T00:00:00Z'
  AND time <= '2022-12-31T23:59:59Z'
GROUP BY time(90d)

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

TRIPLE_EXPONENTIAL_DERIVATIVE(field_expression, period[, hold_period[, warmup_type]])

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric field types.

• period: Number of values to use as the sample size for the algorithm. Supports integers greater than or equal to 1. This also controls the exponential decay rate (α) used to determine the weight of an historical value. With a period of 3, the algorithm uses the current value and the previous two values.

• hold_period: Number of values the algorithm needs before emitting results. Default is -1, which means the hold_period is determined by the warmup_type and period. Must be an integer greater than or equal to -1.

  Warmup type	Default hold_period
  exponential	period
  simple	(period - 1) × 3 + 1

• warmup_type: Controls how the algorithm initializes the first period values. Supports the following warmup types:

  • exponential: (Default) Exponential moving average of the first period values with scaling alpha (α). 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.
  • simple: Simple moving average of the first period values.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

SELECT
  TRIPLE_EXPONENTIAL_DERIVATIVE(temp, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  TRIPLE_EXPONENTIAL_DERIVATIVE(*, 4)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  TRIPLE_EXPONENTIAL_DERIVATIVE(temp, 4, 6)
FROM home
WHERE
  room = 'Kitchen'

SELECT
  TRIPLE_EXPONENTIAL_DERIVATIVE(temp, 4, -1, 'simple')
FROM home
WHERE
  room = 'Kitchen'

SELECT
  TRIPLE_EXPONENTIAL_DERIVATIVE(MEAN(temp_avg), 4)
FROM weather
WHERE
  location = 'San Francisco'
  AND time >= '2020-01-01T00:00:00Z'
  AND time <= '2022-12-31T23:59:59Z'
GROUP BY time(90d)