CODEWITHSUNDEEP
mongodb
Randy Ooorton
Randy Ooorton

Reputation: 75

Aggregate value of each hour by MongoDB

Like the image, the above table represents my original data, time field is irregular. Now I want to get the data that represents the average value between every hour. What I thought was by using $match, $group, $project even with for method. I don't get an accurate idea and method. enter image description here

id: ObjectId,
value: Number,
time: Date()

Upvotes: 1

Views: 640

Answers (2)

Randy Ooorton
Randy Ooorton

Reputation: 75

Finally, I solve this issue. Below is my code. enter image description here

Upvotes: 0

prasad_
prasad_

Reputation: 14317

I have sample collection, hours.:

{ "_id" : 1, "value" : 10, "dt" : ISODate("2019-10-17T00:01:32Z") }
{ "_id" : 2, "value" : 16, "dt" : ISODate("2019-10-17T00:02:12Z") }
{ "_id" : 3, "value" : 8, "dt" : ISODate("2019-10-17T01:04:09Z") }
{ "_id" : 4, "value" : 12, "dt" : ISODate("2019-10-17T02:14:21Z") }
{ "_id" : 5, "value" : 6, "dt" : ISODate("2019-10-17T02:54:02Z") }
{ "_id" : 6, "value" : 11, "dt" : ISODate("2019-10-17T04:06:31Z") }

The following aggregation query returns the average value by the hour (the hour is of the date field):

db.hours.aggregate( [
{ $project: { value: 1, hr: { $hour: "$dt" } } } ,
{ $addFields: { hour: { $add: [ "$hr", 1 ] } } },
{ $group: { _id: "$hour",
                  count: { $sum: 1 },
                  totalValue: { $sum: "$value" },
                  avgValue: { $avg: "$value" }
                }
 },
{ $project: { hour: "$_id", _id: 0, count: 1, totalValue: 1,  avgValue: 1} }
] )

=>
{ "count" : 2, "totalValue" : 18, "avgValue" : 9, "hour" : 3 }
{ "count" : 1, "totalValue" : 8, "avgValue" : 8, "hour" : 2 }
{ "count" : 1, "totalValue" : 11, "avgValue" : 11, "hour" : 5 }
{ "count" : 2, "totalValue" : 26, "avgValue" : 13, "hour" : 1 }

Upvotes: 1

Related Questions