我有下面的订单数据模型,我想查询统计订单:mongoDB中按日期列出的总计和按产品列出的计数数量.......................................................................................................................................................................................
[
{
"status": "Not processed",
"total": 97000,
"_id": "62ef7da24b535854c85a4bfc",
"cart": {
"_id": "62ef7da24b535854c85a4bfa",
"user": "6280676782dc052a6c5cb91e",
"products": [
{
"purchasePrice": 97000,
"totalPrice": 97000,
"_id": "62ef7da24b535854c85a4bfb",
"quantity": 1,
"product": {
"_id": "62ab02ebd3e608133c947793",
}
}
],
},
"createdAt": "2022-09-30T12:08:56.161Z",
"updatedAt": "2022-10-01T15:48:08.868Z"
},
{
"status": "Shipped",
"total": 489500,
"_id": "62f27087e32f0e4364d71170",
"cart": {
"_id": "62f27087e32f0e4364d7116e",
"user": "6280676782dc052a6c5cb91e",
"products": [
{
"purchasePrice": 97900,
"totalPrice": 489500,
"_id": "62f27087e32f0e4364d7116f",
"quantity": 5,
"product": {
"_id": "62ab02ebd3e608133c947793",
}
}
]
},
"createdAt": "2022-10-01T11:08:17.731Z",
"updatedAt": "2022-10-02T08:24:21.091Z"
},
{
"status": "Processing",
"total": 238000,
"_id": "63005a7c9587382c2c20201d",
"cart": {
"_id": "63005a7c9587382c2c20201b",
"user": "6280676782dc052a6c5cb91e",
"products": [
{
"purchasePrice": 47600,
"totalPrice": 238000,
"quantity": 5,
"product": {
"_id": "62ab02ebd3e608133c947795",
}
}
],
"created": "2022-08-20T03:52:28.180Z",
"__v": 0
},
"createdAt": "2022-10-01T11:08:44.717Z",
"updatedAt": "2022-10-01T11:08:44.717Z"
}
]
我的查询MongoDb:
let endDate = new Date("2022-10-04T00:08:17.731Z");
let startDate = new Date("2022-09-25T00:08:56.161");
const order = await Order
.aggregate([
{ $match: { "createdAt": { $gte: startDate, $lte: endDate } } },
{
$addFields: {
createdAt: {
$dateFromParts: {
year: {
$year: "$createdAt"
}, month: {
$month: "$createdAt"
}, day: {
$dayOfMonth: "$createdAt"
}
}
},
dateRange: { $map: { input: { $range: [0, { $subtract: [endDate, startDate] }, 1000 * 60 * 60 * 24] }, in: { $add: [startDate, "$$this"] } } }
}
},
{ $unwind: "$dateRange" },
{
$group: {
_id: {
date: "$dateRange",
},
count: { $sum: { $cond: [{ $eq: ["$dateRange", "$createdAt"] }, 1, 0] } },
subtotal: { $sum: { $cond: [{ $eq: ["$dateRange", "$createdAt"] }, "$total", 0] } },
}
},
{
$group: {
_id: "$_id.date",
totalPrice: { $sum: "$count" },
subtotal: { $sum: "$subtotal" },
}
},
{ $sort: { _id: 1 } },
{
$project: {
_id: 0,
date: "$_id",
totalPrice: "$totalPrice",
subtotal: "$subtotal",
}
}
])
但实际结果是:
[
{
"date": "2022-09-24T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-09-25T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-09-26T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-09-27T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-09-28T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-09-29T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-09-30T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-10-01T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-10-02T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-10-03T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
}
]
我的预期结果是:
[
{
"date": "2022-09-24T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-09-25T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-09-26T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-09-27T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-09-28T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-09-29T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-09-30T17:08:56.161Z",
"totalPrice": 97000,
"subtotal": 1
},
{
"date": "2022-10-01T17:08:56.161Z",
"totalPrice": 727500,
"subtotal": 2
},
{
"date": "2022-10-02T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
},
{
"date": "2022-10-03T17:08:56.161Z",
"totalPrice": 0,
"subtotal": 0
}
]
我想按产品统计订单数量,我该怎么做呢?
1条答案
按热度按时间nhn9ugyo1#
我认为你的解决方案不起作用,因为你比较了createdAt和由range生成的日期的确切值。我使用了更简单的方法,使用$bucket聚合。
请确保在startDate变量创建字符串中添加“Z”,以避免时区导致的意外结果。
下面是对给定示例文档有效的解决方案:
结果是: