CODEWITHSUNDEEP
mongodb
millisami
millisami

Reputation: 10161

Efficient mongodb query to find the average time in a collection of 10K+ records?

Following is the one record of a collections named outputs.

db.outputs.findOne()
{
    "_id" : ObjectId("4e4131e8c7908d3eb5000002"),
    "company" : "West Edmonton Mall",
    "country" : "Canada",
    "created_at" : ISODate("2011-08-09T13:11:04Z"),
    "started_at" : ISODate("2011-08-09T11:11:04Z"),
    "end_at" : ISODate("2011-08-09T13:09:04Z")
}

The above is just a document. There are around 10K docs and it will keep increasing.

What I need is to find the average hours (taking started_at and end_at) for the past 1 week (taking created_at)?

Upvotes: 0

Views: 2226

Answers (2)

Samarth Bhargava
Samarth Bhargava

Reputation: 4228

You can maintain the sum and counts in a separate collection using $inc operator with a value of _id that represents a week. That way, you don't have to query all 10k records. You can just query the collection mantaining sum & count, and divide the sum by count to get the average.

I have explained this in detail in the following post:

http://samarthbhargava.wordpress.com/2012/02/01/real-time-analytics-with-mongodb/

Upvotes: 0

Bryan Migliorisi
Bryan Migliorisi

Reputation: 9210

Right now, youre going to need to query the documents you need to average, likely selecting only the fields you need (started_at and end_at) and do the calculation in your app code.

If you wait for the next major version of MongoDB, there will be a new aggregation framework that will allow you to build an aggregation pipeline for querying documents, selecting fields, and performing calculations on them, and finally returning the calculated value(s). its very cool.

https://www.mongodb.org/display/DOCS/Aggregation+Framework

Upvotes: 1

Related Questions