var start = new Date();
start.setHours(0, 0, 0, 0);
start.setMonth(start.getMonth() - 12);
var end = new Date();
end.setHours(23, 59, 59, 999);
Orders.aggregate([{
$match: {
'created_at': {
$gte: start,
$lte: end
}
}
}, {
"$group": {
"_id": {
"year": { "$year": "$created_at" },
"month": { "$month": "$created_at" }
},
"total_sales": { "$sum": "$total" }
},
},
{
$sort: { '_id.year': 1, '_id.month': 1, }
},
{
$addFields: {
'_id.month': {
$let: {
vars: {
monthsInString: [, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sept', 'Oct', 'Nov', 'Dec']
},
in: {
$arrayElemAt: ['$$monthsInString', '$_id.month']
}
}
}
}
},
])
上述查询返回:
[
{ _id: { year: 2019, month: 'Dec' }, total_sales: 1800 },
{ _id: { year: 2020, month: 'Apr' }, total_sales: 5700 },
{ _id: { year: 2020, month: 'May' }, total_sales: 6630 },
{ _id: { year: 2020, month: 'Jun' }, total_sales: 690 },
{ _id: { year: 2020, month: 'Jul' }, total_sales: 1600 },
{ _id: { year: 2020, month: 'Aug' }, total_sales: 13430 }
]
如果不存在该月的文档，我如何更改它，使它返回0表示该月的total_sales。例如，一月份没有订单。我希望它返回该月的total_sales=0以及每隔一个没有文档的月的total_sales=0。过去12个月或6个月。
样单:
_id : 5f3d0c6677d1471d8a8438e1
orderid :"aydc1kehke1aj47l"
user :5f3cf2c61282c415bfd2a432
products :Array
total : 1800
firstName:"ishak"
lastName: "ertem"
created_at:2020-03-21T11:26:30.647+00:00
具有以下结构($sort阶段之后）:
[
{ _id: { year: 2019, month: 12 }, total_sales: 1800 },
{ _id: { year: 2020, month: 4 }, total_sales: 5700 },
{ _id: { year: 2020, month: 5 }, total_sales: 6630 },
{ _id: { year: 2020, month: 6 }, total_sales: 690 },
{ _id: { year: 2020, month: 7 }, total_sales: 1600 },
{ _id: { year: 2020, month: 8 }, total_sales: 13430 }
]
您可以使用$range生成一个12个整数的列表，可以使用$map为每年迭代该列表，并使用$filter和$arrayelemat与传入数据匹配。此外，$IFNULL可用于指定默认值。
db.collection.aggregate([
{
$group: {
_id: "$_id.month",
months: { $push: "$$ROOT" }
}
},
{
$project: {
months: {
$map: {
input: { $range: [ 1, 12 ] },
as: "monthIndex",
in: {
$ifNull: [
{ $arrayElemAt: [ { $filter: { input: "$months", cond: { $eq: [ "$$this.month", "$$monthIndex" ] } } } , 0 ] },
{ _id: { year: "$_id", month: "$$monthIndex" }, total_sales: 0 }
]
}
}
}
}
},
{
$unwind: "$months"
},
{
$replaceRoot: { newRoot: "$months" }
}
])
蒙哥游乐场
