mongoose 如何在mongoDB中统计订单(按日期合计，按产品统计数量)？

我有下面的订单数据模型，我想查询统计订单：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
  }
]

我想按产品统计订单数量，我该怎么做呢？

我认为你的解决方案不起作用，因为你比较了createdAt和由range生成的日期的确切值。我使用了更简单的方法，使用$bucket聚合。
请确保在startDate变量创建字符串中添加“Z”，以避免时区导致的意外结果。
下面是对给定示例文档有效的解决方案：

// select database
use("data")

// dates that we care
let endDate = new Date("2022-10-04T00:08:17.731Z");
let startDate = new Date("2022-09-25T00:08:56.161Z");

// generate boundaries
let boundaries = [startDate]
while (boundaries.slice(-1)[0] <= endDate){
  boundaries.push(
    new Date(new Date(boundaries.slice(-1)[0]).getTime() + (1000 * 60 * 60 * 24))
  )
}

// create aggregation query
p = [
  { $match: { "createdAt": { $gte: startDate, $lte: endDate } } },
  {
    $bucket: {
      boundaries: boundaries,
      groupBy: "$createdAt",
      default: "other",
      output: {
        subtotal: {$sum: 1},
        totalPrice: {$sum: "$total"},
      }
    }
  },
  {
    $densify: {
      field: "_id",
      range:{
        step: 1,
        unit: "day",
        bounds: [startDate, endDate],
      }
    }
  },
  {
    $project:{
      totalPrice: {$ifNull: ["$totalPrice", 0]},
      subtotal: {$ifNull: ["$subtotal", 0]},
      date: { $dateToString: { date: "$_id" } },
      _id: 0,
    }
  },
]
// run
db.your_collection.aggregate(p)

结果是：

[
    {
        "totalPrice": 0,
        "subtotal": 0,
        "date": "2022-09-25T00:08:56.161Z"
    },
    {
        "totalPrice": 0,
        "subtotal": 0,
        "date": "2022-09-26T00:08:56.161Z"
    },
    {
        "totalPrice": 0,
        "subtotal": 0,
        "date": "2022-09-27T00:08:56.161Z"
    },
    {
        "totalPrice": 0,
        "subtotal": 0,
        "date": "2022-09-28T00:08:56.161Z"
    },
    {
        "totalPrice": 0,
        "subtotal": 0,
        "date": "2022-09-29T00:08:56.161Z"
    },
    {
        "totalPrice": 97000,
        "subtotal": 1,
        "date": "2022-09-30T00:08:56.161Z"
    },
    {
        "totalPrice": 727500,
        "subtotal": 2,
        "date": "2022-10-01T00:08:56.161Z"
    },
    {
        "totalPrice": 0,
        "subtotal": 0,
        "date": "2022-10-02T00:08:56.161Z"
    },
    {
        "totalPrice": 0,
        "subtotal": 0,
        "date": "2022-10-03T00:08:56.161Z"
    }
]

mongoose 如何在mongoDB中统计订单(按日期合计，按产品统计数量)？

1条答案

相关问题

热门标签

最新问答