I have a collection contains 2 statuses of orders "Shipped" and "Delivered". I want to calculate the average in hours
Formula (Delivered 1 - Shipped 1) + (Delivered 2 - Shipped 2) + (Delivered N - Shipped N)/N
here is my collection
{
trackingHistory: [
{
status: 'Shipped',
time: ISODate("2022-11-22T06:30:49.000Z")
},
{
status: 'Delivered',
time: ISODate("2022-11-25T15:30:00.000Z")
}
]
},
{
trackingHistory: [
{
status: 'Shipped',
time: ISODate("2022-11-22T09:29:45.000Z")
},
{
status: 'Delivered',
time: ISODate("2022-11-23T19:26:00.000Z")
}
]
}
here is my code
db.client_order_news.aggregate([
{ $match : {
receiverCity : 'New York',
created_at:{$gte:ISODate("2022-11-01T00:00:00.398Z"),$lt:ISODate("2022-11-30T23:59:59.398Z")},
"trackingHistory. status":"Shipped",
"trackingHistory.status":"Delivered"
} },
{ $project : { _id : 0, trackingHistory : {$filter: {
input: '$trackingHistory',
as: 'tracking',
cond: {$or: [{ $eq: ['$$tracking.status', "Shipped"] }, { $eq: ['$$tracking.status',"Delivered"] }]}
}}, } },
{$project: { "$sum": ["$price", { "$subtract": ["$deposits.amount&qu开发者_如何学编程ot;] } ] }}
]).pretty()
精彩评论