Database Manual / Reference / Query Language / Expressions

$expMovingAvg (expression operator)

Definition

New in version 5.0.

$expMovingAvg

Returns the exponential moving average of numeric expressions applied to documents in a partition defined in the $setWindowFields stage.

$expMovingAvg is only available in the $setWindowFields stage.

$expMovingAvg syntax:

{
   $expMovingAvg: {
      input: <input expression>,
      N: <integer>,
      alpha: <float>
   }
}

$expMovingAvg takes a document with these fields:

FieldDescription

input

Specifies the expression to evaluate. Non-numeric expressions are ignored.

N

An integer that specifies the number of historical documents that have a significant mathematical weight in the exponential moving average calculation, with the most recent documents contributing the most weight.

You must specify either N or alpha. You cannot specify both.

The N value is used in this formula to calculate the current result based on the expression value from the current document being read and the previous result of the calculation:

current result = current value * ( 2 / ( N + 1 ) ) +
                 previous result * ( 1 - ( 2 / ( N + 1 ) ) )

alpha

A double that specifies the exponential decay value to use in the exponential moving average calculation. A higher alpha value assigns a lower mathematical significance to previous results from the calculation.

You must specify either N or alpha. You cannot specify both.

The alpha value is used in this formula to calculate the current result based on the expression value from the current document being read and the previous result of the calculation:

current result = current value * alpha +
                 previous result * ( 1 - alpha )

Behavior

You must specify either N or alpha. You cannot specify both.

$expMovingAvg ignores non-numeric values, null values, and missing fields.

Examples

Create a stockPrices collection that contains prices for stocks named "MDB" and "MSFT":

db.stockPrices.insertMany( [
   { stock: "MDB", date: new Date( "2020-05-18T20:00:00Z" ), price: 13 },
   { stock: "MDB", date: new Date( "2020-05-19T20:00:00Z" ), price: 15.4 },
   { stock: "MDB", date: new Date( "2020-05-20T20:00:00Z" ), price: 12 },
   { stock: "MDB", date: new Date( "2020-05-21T20:00:00Z" ), price: 11.7 },
   { stock: "MSFT", date: new Date( "2020-05-18T20:00:00Z" ), price: 82 },
   { stock: "MSFT", date: new Date( "2020-05-19T20:00:00Z" ), price: 94 },
   { stock: "MSFT", date: new Date( "2020-05-20T20:00:00Z" ), price: 112 },
   { stock: "MSFT", date: new Date( "2020-05-21T20:00:00Z" ), price: 97.3 }
] )

Exponential Moving Average Using N

This example uses $expMovingAvg in the $setWindowFields stage to output the exponential moving average for the stock prices weighted for two historical documents (two days for the example documents) using N set to 2:

db.stockPrices.aggregate( [
   {
      $setWindowFields: {
         partitionBy: "$stock",
         sortBy: { date: 1 },
         output: {
            expMovingAvgForStock: {
               $expMovingAvg: { input: "$price", N: 2 }
            }
         }
      }
   }
] )

In the example:

  • partitionBy: "$stock" partitions the documents in the collection by stock. There are partitions for "MDB" and "MSFT".

  • sortBy: { date: 1 } sorts the documents in each partition by date in ascending order (1), so the earliest date is first.

  • output returns the exponential moving average for the stock price field with N set to 2:

    • In the input documents, there is one document for each day and the documents are ordered by date. Therefore, with N is set to 2, the price in the current document and the price in the previous document, if available, are allocated the highest weight in the exponential moving average formula.
    • The exponential moving average for the price field is stored in a new field called expMovingAvgForStocks, as shown in the following results.
{ "_id" : ObjectId("60d11fef833dfeadc8e6286b"), "stock" : "MDB",
  "date" : ISODate("2020-05-18T20:00:00Z"), "price" : 13,
  "expMovingAvgForStock" : 13 }
{ "_id" : ObjectId("60d11fef833dfeadc8e6286c"), "stock" : "MDB",
  "date" : ISODate("2020-05-19T20:00:00Z"), "price" : 15.4,
  "expMovingAvgForStock" : 14.6 }
{ "_id" : ObjectId("60d11fef833dfeadc8e6286d"), "stock" : "MDB",
  "date" : ISODate("2020-05-20T20:00:00Z"), "price" : 12,
  "expMovingAvgForStock" : 12.866666666666667 }
{ "_id" : ObjectId("60d11fef833dfeadc8e6286e"), "stock" : "MDB",
  "date" : ISODate("2020-05-21T20:00:00Z"), "price" : 11.7,
  "expMovingAvgForStock" : 12.088888888888889 }
{ "_id" : ObjectId("60d11fef833dfeadc8e6286f"), "stock" : "MSFT",
  "date" : ISODate("2020-05-18T20:00:00Z"), "price" : 82,
  "expMovingAvgForStock" : 82 }
{ "_id" : ObjectId("60d11fef833dfeadc8e62870"), "stock" : "MSFT",
  "date" : ISODate("2020-05-19T20:00:00Z"), "price" : 94,
  "expMovingAvgForStock" : 90 }
{ "_id" : ObjectId("60d11fef833dfeadc8e62871"), "stock" : "MSFT",
  "date" : ISODate("2020-05-20T20:00:00Z"), "price" : 112,
  "expMovingAvgForStock" : 104.66666666666667 }
{ "_id" : ObjectId("60d11fef833dfeadc8e62872"), "stock" : "MSFT",
  "date" : ISODate("2020-05-21T20:00:00Z"), "price" : 97.3,
  "expMovingAvgForStock" : 99.75555555555556 }

Exponential Moving Average Using alpha

This example uses $expMovingAvg in the $setWindowFields stage to output the exponential moving average for the stock prices using alpha set to 0.75:

db.stockPrices.aggregate( [
   {
      $setWindowFields: {
         partitionBy: "$stock",
         sortBy: { date: 1 },
         output: {
            expMovingAvgForStock: {
               $expMovingAvg: { input: "$price", alpha: 0.75 }
            }
         }
      }
   }
] )

In the example:

  • partitionBy: "$stock" partitions the documents in the collection by stock. There are partitions for "MDB" and "MSFT".

  • sortBy: { date: 1 } sorts the documents in each partition by date in ascending order (1), so the earliest date is first.

  • output sets the exponential moving average for the stock prices in a new field called expMovingAvgForStock, as shown in the following results. The value for alpha is set to 0.75 in the exponential moving average formula.
{ "_id" : ObjectId("60d11fef833dfeadc8e6286b"), "stock" : "MDB",
  "date" : ISODate("2020-05-18T20:00:00Z"), "price" : 13,
  "expMovingAvgForStock" : 13 }
{ "_id" : ObjectId("60d11fef833dfeadc8e6286c"), "stock" : "MDB",
  "date" : ISODate("2020-05-19T20:00:00Z"), "price" : 15.4,
  "expMovingAvgForStock" : 14.8 }
{ "_id" : ObjectId("60d11fef833dfeadc8e6286d"), "stock" : "MDB",
  "date" : ISODate("2020-05-20T20:00:00Z"), "price" : 12,
  "expMovingAvgForStock" : 12.7 }
{ "_id" : ObjectId("60d11fef833dfeadc8e6286e"), "stock" : "MDB",
  "date" : ISODate("2020-05-21T20:00:00Z"), "price" : 11.7,
  "expMovingAvgForStock" : 11.95 }
{ "_id" : ObjectId("60d11fef833dfeadc8e6286f"), "stock" : "MSFT",
  "date" : ISODate("2020-05-18T20:00:00Z"), "price" : 82,
  "expMovingAvgForStock" : 82 }
{ "_id" : ObjectId("60d11fef833dfeadc8e62870"), "stock" : "MSFT",
  "date" : ISODate("2020-05-19T20:00:00Z"), "price" : 94,
  "expMovingAvgForStock" : 91 }
{ "_id" : ObjectId("60d11fef833dfeadc8e62871"), "stock" : "MSFT",
  "date" : ISODate("2020-05-20T20:00:00Z"), "price" : 112,
  "expMovingAvgForStock" : 106.75 }
{ "_id" : ObjectId("60d11fef833dfeadc8e62872"), "stock" : "MSFT",
  "date" : ISODate("2020-05-21T20:00:00Z"), "price" : 97.3,
  "expMovingAvgForStock" : 99.6625 }