CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
hko04u
hko04u

Reputation: 73

Compute Simple Moving Average in Mongo Shell

I am developing a financial application with Nodejs. I wonder would it be possible to compute simple moving average which is the average last N days of price directly in Mongo Shell than reading it and computing it in Node js.

Document Sample.

[{code:'0001',price:0.10,date:'2014-07-04T00:00:00.000Z'},
{code:'0001',price:0.12,date:'2014-07-05T00:00:00.000Z'},{code:'0001',price:0.13,date:'2014-07-06T00:00:00.000Z'},
{code:'0001',price:0.12,date:'2014-07-07T00:00:00.000Z'}]

Upvotes: 1

Views: 342

Answers (2)

Peter Grainger
Peter Grainger

Reputation: 5097

If you have more than a trivial number of documents you should use the DB server to do the work rather than JS.

You don't say if you are using mongoose or the node driver directly. I'll assume you are using mongoose as that is the way most people are headed.

So your model would be:

// models/stocks.js
const mongoose = require("mongoose");
const conn = mongoose.createConnection('mongodb://localhost/stocksdb');

const StockSchema = new mongoose.Schema(
  {
    price: Number,
    code: String,
    date: Date,
  },
  { timestamps: true }
);

module.exports = conn.model("Stock", StockSchema, "stocks");

You rightly suggested that aggregation frameworks would be a good way to go here. First though if we are dealing with returning values between date ranges, the records in your database need to be date objects. From your example documents you may have put strings. An example of inserting objects with dates would be:

db.stocks.insertMany([{code:'0001',price:0.10,date:ISODate('2014-07-04T00:00:00.000Z')}, {code:'0001',price:0.12,date:ISODate('2014-07-05T00:00:00.000Z')},{code:'0001',price:0.13,date:ISODate('2014-07-06T00:00:00.000Z')}, {code:'0001',price:0.12,date:ISODate('2014-07-07T00:00:00.000Z')}])

The aggregation pipeline function accepts an array with one or more pipeline stages.

The first pipeline stage we should use is $match, $match docs, this filters the documents down to only the records we are interested in which is important for performance

{ $match: { 
     date: { 
       $gte:  new Date('2014-07-03'),
       $lte:  new Date('2014-07-07')
      }
  }
}

This stage will send only the documents that are on the 3rd to 7th July 2014 inclusive to the next stage (in this case all the example docs)

Next stage is the stage where you can get an average. We need to group the values together based on one field, multiple fields or all fields.

As you don't specify a field you want to average over I'll give an example for all fields. For this we use the $group object, $group docs

        {
            $group: {
                _id: null,
                average: {
                    $avg: '$price'
                }
             }
         }

This will take all the documents and display an average of all the prices.

In the case of your example documents this results in

{ _id: null, avg: 0.1175 }

Check the answer: 

(0.10 + 0.12 + 0.12 + 0.13) / 4 = 0.1175

FYI: I wouldn't rely on calculations done with javascript for anything critical as Numbers using floating points. See https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html for more details if you are worried about that.

For completeness here is the full aggregation query

const Stock = require("./models/stocks");

Stock.aggregate([{ $match: { 
                date: { 
                        $gte:  new Date('2014-07-03'),
                        $lte:  new Date('2014-07-07')
                    }
            }},
         {
            $group: {
                _id: null,
                avg: {
                    $avg: '$price'
                }
             }
         }])
 .then(console.log)
 .catch(error => console.error(error))

Upvotes: 2

tonysepia
tonysepia

Reputation: 3500

Not sure about your moving average formula, but here is how I would do it:

var moving_average = null
db.test.find().forEach(function(doc) {
    if (moving_average==null) {
        moving_average = doc.price;
    } 
    else {
        moving_average = (moving_average+doc.price)/2;
    } 
})

output:

> moving_average
0.3

And if you wan to define the N days to do the average for, just modify the argument for find:

db.test.find({ "date": { $lt: "2014-07-10T00:00:00.000Z" }, "date": { $gt: "2014-07-07T00:00:00.000Z" } })

And if you want to do the above shell code in one-line, you can assume that moving_average is undefined and just check for that before assigning the first value.

Upvotes: 0

Related Questions