CODEWITHSUNDEEP
phpmongodbnosql
cam
cam

Reputation: 113

MongoDB - Request for Moving average

I use a MongoDB database with the following structure

"_id": ObjectId("5531214ff31ca6e6368b54e6"),
"d_second": NumberLong(51),
"d_minute": NumberLong(5),
"d_hour": NumberLong(17),
"d_day": NumberLong(17),
"d_week": NumberLong(16),
"d_month": NumberLong(4),
"d_year": NumberLong(2015),
"amount": 1.30005,

How can a build a request which will return an array providing for each second the average of the "amount" value for the last 30 seconds for example

Thanks in advance!

Upvotes: 0

Views: 282

Answers (1)

thegreenogre
thegreenogre

Reputation: 1579

You can use aggragation 

db.Testing.aggregate([{ $project: 
        { date: 
            { "$concat": [
                    { "$cond": [
                        { "$lte": [ "$d_month", 9 ] },
                        { "$concat": [
                                "0",
                                { "$substr": [ "$d_month" , 0, 2 ] },
                        ]},
                        { "$substr": [ "$d_month", 0, 2 ] }
                    ]},
                    " ", 
                    { "$cond": [
                        { "$lte": [ "$d_day", 9 ] },
                        { "$concat": [
                                "0",
                                { "$substr": [ "$d_day" , 0, 2 ] },
                        ]},
                        { "$substr": [ "$d_day", 0, 2 ] }
                    ]},
                    " ", 
                    { "$substr": [ "$d_year", 0, 4 ] },
                    " ", 
                    { "$cond": [
                        { "$lte": [ "$d_hour", 9 ] },
                        { "$concat": [
                                "0",
                                { "$substr": [ "$d_hour" , 0, 2 ] },
                        ]},
                        { "$substr": [ "$d_hour", 0, 2 ] }
                    ]},
                    ":", 
                    { "$cond": [
                        { "$lte": [ "$d_minute", 9 ] },
                        { "$concat": [
                                "0",
                                { "$substr": [ "$d_minute" , 0, 2 ] },
                        ]},
                        { "$substr": [ "$d_minute", 0, 2 ] }
                    ]},
                    ":", 
                    { "$cond": [
                        { "$lte": [ "$d_second", 9 ] },
                        { "$concat": [
                                "0",
                                { "$substr": [ "$d_second" , 0, 2 ] },
                        ]},
                        { "$substr": [ "$d_second", 0, 2 ] }
                    ]},
            ]},
            'amount' :1 
        }
    },
    {'$group' : {'_id' : '$date' ,'avg' : {'$avg' : '$amount'}}},
    {'$sort' : {'_id' : -1}},
    {'$limit' : 30}
])

Remember this will give you the average for last 30 time-stamps and not 30 seconds.

Also it would be better to use a Date or a time-stamp object instead of different keys. In that case you can match for documents last 30 seconds and then do a grouping on date/time-stamp to get the average.

Upvotes: 1

Related Questions