如何在MongoDB中计算指定时段各月份过去2年的滚动销售额总和

需求说明 #

• 指定时段为2020年1月至2022年1月，对该时段内的每个月份，计算其往前推24个月的总销售额。
• 示例：计算2020年1月的总和时，累加2018年1月至2020年1月的销售额；计算2020年2月时，累加2018年2月至2020年2月的销售额，以此类推。

集合结构 #

sales集合的文档结构如下：

[
  {
    "saleDate": ISODate("2018-01-01"),
    "salesCount": 121
  },
  {
    "saleDate": ISODate("2018-01-02"),
    "salesCount": 234
  },
  {
    "saleDate": ISODate("2018-01-03"),
    "salesCount": 521
  }
]

期望结果 #

最终需要输出如下格式的文档：

[
  {
    "year": 2020,
    "month": 1,
    "lastTwoYearSales": 8198
  },
  {
    "year": 2020,
    "month": 2,
    "lastTwoYearSales": 9928
  },
  {
    "year": 2020,
    "month": 3,
    "lastTwoYearSales": 9218
  },
  ...
  {
    "year": 2022,
    "month": 1,
    "lastTwoYearSales": 11219
  }
]

现有聚合管道的问题 #

当前使用的聚合管道存在两个核心问题：

1. 计算2021年7月的滚动总和时，错误包含了2019年1月至6月的销售额，而非仅2019年7月至2021年7月的数据。
2. 累加的是过去两年到时段结束的销售额，而非截至当前月份的总和。

原始聚合管道：

[
  {
    $match: {
      saleDate: {
        $gte: ISODate("2018-01-01"),
        $lt: ISODate("2022-02-01")
      }
    }
  },
  {
    $group: {
      _id: {
        year: { $year: "$saleDate" },
        month: { $month: "$saleDate" }
      },
      monthlySales: { $sum: "$salesCount" }
    }
  },
  {
    $group: {
      _id: "$_id.month",
      monthly_sales: {
        $push: {
          year: "$_id.year",
          monthlySales: "$monthlySales"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      month: "$_id",
      rolling_sum: {
        $map: {
          input: "$monthly_sales",
          as: "sales",
          in: {
            year: "$$sales.year",
            monthlySales: {
              $sum: {
                $cond: [
                  { $gte: ["$$sales.year", { $subtract: ["$_id", 2] }] },
                  "$$sales.monthlySales",
                  0
                ]
              }
            }
          }
        }
      }
    }
  }
]

修正后的聚合管道 #

以下是解决上述问题的正确聚合管道：

[
  // 筛选所需时间范围的数据（包含滚动计算需要的前24个月）
  {
    $match: {
      saleDate: {
        $gte: ISODate("2018-01-01"),
        $lt: ISODate("2022-02-01")
      }
    }
  },
  // 按年-月分组，计算每月总销售额
  {
    $group: {
      _id: {
        year: { $year: "$saleDate" },
        month: { $month: "$saleDate" }
      },
      monthlySales: { $sum: "$salesCount" }
    }
  },
  // 生成每个年月对应的时间戳，计算滚动起始时间
  {
    $addFields: {
      currentMonthTimestamp: {
        $dateFromParts: {
          year: "$_id.year",
          month: "$_id.month",
          day: 1
        }
      },
      rollingStartTimestamp: {
        // MongoDB 5.0+可用$dateAdd实现更精确的月份计算
        $dateAdd: { 
          startDate: "$currentMonthTimestamp", 
          amount: -24, 
          unit: "month" 
        }
      }
    }
  },
  // 收集所有年月销售额数据到数组
  {
    $group: {
      _id: null,
      allMonthlySales: { $push: "$$ROOT" }
    }
  },
  // 遍历计算每个月份的滚动总和，并过滤目标时段
  {
    $project: {
      _id: 0,
      rollingSales: {
        $filter: {
          input: {
            $map: {
              input: "$allMonthlySales",
              as: "current",
              in: {
                year: "$$current._id.year",
                month: "$$current._id.month",
                lastTwoYearSales: {
                  $sum: {
                    $map: {
                      input: {
                        $filter: {
                          input: "$allMonthlySales",
                          as: "item",
                          cond: {
                            $and: [
                              // 时间在滚动起始到当前月份之间
                              { $gte: ["$$item.currentMonthTimestamp", "$$current.rollingStartTimestamp"] },
                              { $lte: ["$$item.currentMonthTimestamp", "$$current.currentMonthTimestamp"] },
                              // 仅匹配同月份数据
                              { $eq: ["$$item._id.month", "$$current._id.month"] }
                            ]
                          }
                        }
                      },
                      as: "filteredItem",
                      in: "$$filteredItem.monthlySales"
                    }
                  }
                }
              }
            }
          },
          // 只保留2020年1月至2022年1月的结果
          cond: {
            $and: [
              { $gte: ["$$this.year", 2020] },
              { $lte: ["$$this.year", 2022] },
              { $or: [
                  { $ne: ["$$this.year", 2020], $ne: ["$$this.year", 2022] },
                  { $eq: ["$$this.year", 2020], $gte: ["$$this.month", 1] },
                  { $eq: ["$$this.year", 2022], $lte: ["$$this.month", 1] }
                ]
              }
            ]
          }
        }
      }
    }
  },
  // 展开数组并调整文档结构
  { $unwind: "$rollingSales" },
  { $replaceRoot: { newRoot: "$rollingSales" } }
]

关键修正点说明 #

1. 精确时间范围控制：用$dateAdd计算往前24个月的起始时间，替代近似的毫秒数减法，避免跨年月的计算误差。
2. 同月份匹配：添加$eq: ["$$item._id.month", "$$current._id.month"]条件，确保每个月份的滚动总和仅包含对应月份的数据，不会混入其他月份销售额。
3. 目标时段过滤：通过$filter严格筛选出2020年1月至2022年1月的结果，完全符合需求中的指定时段。

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