MongoDB aggregation: average sales per hour

user_383895 0
Viewed 1274

I have a collection with sales. Now I need to get the average number of sales per hour within a date range.

Up to now I have a query like this:

db.getCollection('sales').aggregate({
    "$match": {
        $and: [
            { "createdAt": { $gte: ISODate("2018-05-01T00:00:00.000Z") } },
            { "createdAt": { $lt: ISODate("2018-10-30T23:59:00.000Z") } },
        ]
    }
},{ 
    "$project": {
      "h":{"$hour":"$createdAt"},
     }
},{ 
     "$group":{ 
        "_id": "$h",
        "salesPerHour": { $sum: 1 },
      },
},{
    "$sort": { "salesPerHour": -1 }
});

The result looks like this: {"_id" : 15, "salesPerHour" : 681.0}

How can I get the average value of salesPerHour instead the sum?

Update 1 => Example document.

{
    "_id" : "pX6jj7j4274J9xpSA",
    "idFiscalSale" : "48",
    "documentYear" : "2018",
    "paymentType" : "cash",
    "cashReceived" : 54,
    "items" : [...],
    "customer" : null,
    "subTotal" : 23.89,
    "taxTotal" : 3.7139,
    "total" : 23.89,
    "rewardPointsValue" : 0,
    "rewardPointsEarned" : 24,
    "discountValue" : 0,
    "createdAt" : ISODate("2018-04-24T00:00:00.201Z")
}
aggregation-framework mongodb
2 Answers

You can use below aggregation query. 

db.sales.aggregate([
  {"$match":{
    "createdAt":{
      "$gte":ISODate("2018-05-01T00:00:00.000Z"), 
      "$lt":ISODate("2018-10-30T23:59:00.000Z")
    }
  }},
  {"$group":{
    "_id":{"$hour":"$createdAt"},
    "salesPerHour":{"$sum":1}
  }},
  {"$group":{
    "_id":null,
    "salesPerHour":{"$avg":"$salesPerHour"}
  }}
])
user_26838140

You can try below aggregation

You have to use $avg aggregation operator with the salesPerHour field

db.collection.aggregate([
  { "$match": {
    "$and": [
      { "createdAt": { "$gte": ISODate("2018-05-01T00:00:00.000Z") }},
      { "createdAt": { "$lt": ISODate("2018-10-30T23:59:00.000Z") }}
    ]
  }},
  { "$group": {
    "_id": { "$hour": "$createdAt" },
    "salesPerHour": {
      "$avg": "$salesPerHour"
    }
  }}
])
user_75106570
Related