如何使用MongoDB聚合生成年度销售报表？修正价格计算错误

问题 #

需要生成年度销售报表，包含以下数据：

• 已完成（completed）和已取消（declined）订单的数量
• 已完成和已取消订单的各自总金额

提供的Mongoose Schema如下：

const orderSchema = mongoose.Schema(
{
  orderStatus: {
         type: String,
         enum: ["pending", "preparing", "completed", "declined"],
         default: "pending",
  },
   products: [
          {
            product: {
              productId: {
                type: mongoose.Schema.Types.ObjectId,
                ref: "Product",
              },
              productName: String,
              productPrice: Number,
              categoryName: String,
            },
            quantity: {
              type: Number,
              required: true,
            }
            
          },
   ],
   totalPrice: { type: Number },
   acceptDeclineTime: {
      type: Date,
      default: Date.now,
   },
}
);

尝试了以下聚合代码，但价格计算错误——原因是$unwind阶段会将每个订单拆分成与产品数量对等的文档，导致订单级别的totalPrice被重复累加，最终统计结果偏大：

orderSchema.aggregate(
    [
     {
        $unwind: {
          path: "$products",
        },
      },
     {
     $group: {
          _id: { $year: { date: "$acceptDeclineTime", timezone: "+03:00" } },
              totalCompletedPrice: {
                $sum: {
                  $cond: [{ $eq: ["$orderStatus", "completed"] }, "$totalPrice", 0],
                },
              },
              totalDeclinedPrice: {
                $sum: {
                  $cond: [{ $eq: ["$orderStatus", "declined"] }, "$totalPrice", 0],
                },
              },
              totalItems: {
                $sum: "$products.quantity",
              },
              completedSales: {
                $sum: {
                  $cond: [{ $eq: ["$orderStatus", "completed"] }, "$products.quantity", 0],
                },
              },
              cancelledSales: {
                $sum: {
                  $cond: [{ $eq: ["$orderStatus", "declined"] }, "$products.quantity", 0],
                },
              },
            },
          },
]);

解决方案 #

核心思路是避免订单级数据被重复计算，以下提供两种可行方案：

方案一：先订单维度聚合，再年度汇总（性能更优） #

直接在订单级别提取所需统计字段，再按年度分组汇总，完全规避$unwind带来的重复计算问题：

orderSchema.aggregate([
  // 筛选仅需要统计的订单状态
  {
    $match: {
      orderStatus: { $in: ["completed", "declined"] }
    }
  },
  // 为每个订单标记年度、状态对应金额、商品数量等字段
  {
    $addFields: {
      year: { $year: { date: "$acceptDeclineTime", timezone: "+03:00" } },
      completedPrice: { $cond: [{ $eq: ["$orderStatus", "completed"] }, "$totalPrice", 0] },
      declinedPrice: { $cond: [{ $eq: ["$orderStatus", "declined"] }, "$totalPrice", 0] },
      orderItemCount: { $sum: "$products.quantity" },
      completedItemCount: { 
        $cond: [{ $eq: ["$orderStatus", "completed"] }, { $sum: "$products.quantity" }, 0] 
      },
      declinedItemCount: { 
        $cond: [{ $eq: ["$orderStatus", "declined"] }, { $sum: "$products.quantity" }, 0] 
      }
    }
  },
  // 按年度分组汇总所有数据
  {
    $group: {
      _id: "$year",
      completedOrderCount: { $sum: { $cond: [{ $eq: ["$orderStatus", "completed"] }, 1, 0] } },
      declinedOrderCount: { $sum: { $cond: [{ $eq: ["$orderStatus", "declined"] }, 1, 0] } },
      totalCompletedPrice: { $sum: "$completedPrice" },
      totalDeclinedPrice: { $sum: "$declinedPrice" },
      totalCompletedItems: { $sum: "$completedItemCount" },
      totalDeclinedItems: { $sum: "$declinedItemCount" },
      totalItems: { $sum: "$orderItemCount" }
    }
  },
  // 可选：按年份升序排序
  {
    $sort: { _id: 1 }
  }
]);

方案二：保留$unwind，通过订单ID去重计算 #

如果需要保留$unwind处理更细粒度的产品数据，可以先按订单ID分组去重，再按年度汇总：

orderSchema.aggregate([
  { $unwind: "$products" },
  // 先按订单ID分组，确保每个订单只被计算一次
  {
    $group: {
      _id: {
        year: { $year: { date: "$acceptDeclineTime", timezone: "+03:00" } },
        orderId: "$_id"
      },
      orderStatus: { $first: "$orderStatus" },
      totalPrice: { $first: "$totalPrice" },
      itemCount: { $sum: "$products.quantity" }
    }
  },
  // 再按年度汇总统计数据
  {
    $group: {
      _id: "$_id.year",
      completedOrderCount: { 
        $sum: { $cond: [{ $eq: ["$orderStatus", "completed"] }, 1, 0] } 
      },
      declinedOrderCount: { 
        $sum: { $cond: [{ $eq: ["$orderStatus", "declined"] }, 1, 0] } 
      },
      totalCompletedPrice: { 
        $sum: { $cond: [{ $eq: ["$orderStatus", "completed"] }, "$totalPrice", 0] } 
      },
      totalDeclinedPrice: { 
        $sum: { $cond: [{ $eq: ["$orderStatus", "declined"] }, "$totalPrice", 0] } 
      },
      totalCompletedItems: { 
        $sum: { $cond: [{ $eq: ["$orderStatus", "completed"] }, "$itemCount", 0] } 
      },
      totalDeclinedItems: { 
        $sum: { $cond: [{ $eq: ["$orderStatus", "declined"] }, "$itemCount", 0] } 
      },
      totalItems: { $sum: "$itemCount" }
    }
  },
  { $sort: { _id: 1 } }
]);

方案对比 #

• 方案一无需拆分订单，性能更高，适合仅需要订单级统计的场景；
• 方案二保留了产品维度的处理能力，可扩展产品级统计需求，但多了一次分组操作，性能略低。

内容的提问来源于stack exchange，提问作者Abdi mussa