MongoDB实现订单Shipped至Delivered状态的平均耗时计算（小时）

需求说明：需计算订单从Shipped（已发货）到Delivered（已送达）状态的平均耗时（单位：小时），公式为所有订单的「送达时间-发货时间」之和除以订单数量。

原代码存在的问题 #

• $match阶段中，"trackingHistory. status"包含多余空格，且重复声明"trackingHistory.status"会导致前一个条件被覆盖，无法正确筛选同时包含两种状态的订单；
• 最后一个$project阶段逻辑完全偏离需求，与耗时计算无关。

修正后的聚合代码 #

db.client_order_news.aggregate([
  // 1. 筛选目标订单：纽约收货、指定时间范围、同时包含Shipped和Delivered状态
  {
    $match: {
      receiverCity: 'New York',
      created_at: {
        $gte: ISODate("2022-11-01T00:00:00.398Z"),
        $lt: ISODate("2022-11-30T23:59:59.398Z")
      },
      $and: [
        { "trackingHistory.status": "Shipped" },
        { "trackingHistory.status": "Delivered" }
      ]
    }
  },
  // 2. 提取对应状态的时间记录（假设每个订单各状态仅出现一次）
  {
    $project: {
      _id: 0,
      shippedTime: {
        $arrayElemAt: [
          { $filter: {
              input: "$trackingHistory",
              as: "track",
              cond: { $eq: ["$$track.status", "Shipped"] }
          } },
          0
        ]
      },
      deliveredTime: {
        $arrayElemAt: [
          { $filter: {
              input: "$trackingHistory",
              as: "track",
              cond: { $eq: ["$$track.status", "Delivered"] }
          } },
          0
        ]
      }
    }
  },
  // 3. 计算单个订单的耗时（毫秒转小时：总毫秒数 ÷ 3600000）
  {
    $project: {
      hoursSpent: {
        $divide: [
          { $subtract: ["$deliveredTime.time", "$shippedTime.time"] },
          3600000
        ]
      }
    }
  },
  // 4. 统计所有订单的平均耗时
  {
    $group: {
      _id: null,
      averageHours: { $avg: "$hoursSpent" }
    }
  },
  // 5. 格式化输出结果（保留两位小数）
  {
    $project: {
      _id: 0,
      averageDeliveryHours: { $round: ["$averageHours", 2] }
    }
  }
]).pretty();

关键步骤说明 #

• $match阶段：通过$and确保订单同时包含两种状态，避免条件覆盖；
• 时间提取：用$filter筛选对应状态的记录，再通过$arrayElemAt取出唯一的时间条目；
• 耗时转换：$subtract计算时间差（返回毫秒数），除以3600000转换为小时单位；
• 平均计算：$group结合$avg统计整体平均值，$round优化结果可读性。

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