MongoDB多集合关联查询，实现物料月度/季度/年度Qty求和

我来帮你调整聚合查询，实现你需要的年度、最近3个月、最近1个月的Qty多维度求和需求。先梳理下你的核心需求：关联material_master和monthly_forcast_table两个集合，对每个物料分别计算指定年度的总Qty、最近3个月的Qty总和，以及最近1个月的Qty总和。

原代码的问题分析 #

你当前的聚合逻辑存在几个关键问题：

• $group阶段的_id写法错误，$cond的条件表达式不符合语法，没有按物料分组的逻辑；
• 缺少对月度数据的筛选、排序和多维度求和的处理；
• 没有保留物料主表的关键信息，输出结构不符合预期。

调整后的聚合查询方案 #

以下是完整的聚合管道代码，我会一步步解释每个阶段的作用：

db.material_master.aggregate([
  // 1. 关联预测表：将匹配的预测记录加入到forcast数组中
  {
    "$lookup": {
      "from": "monthly_forcast_table",
      "localField": "Material",
      "foreignField": "MATERIAL",
      "as": "forcast"
    }
  },
  // 2. 展开预测记录数组：保留没有预测数据的物料（preserveNullAndEmptyArrays）
  {
    "$unwind": {
      "path": "$forcast",
      "preserveNullAndEmptyArrays": true
    }
  },
  // 3. 筛选指定员工的预测记录：只保留EMPLOYEE CODE为47000006的数据
  {
    "$match": {
      "forcast.EMPLOYEE CODE": "47000006",
      // 如果需要限定特定年度，可添加如下条件
      // "forcast.Financial Year": "2015-2016"
    }
  },
  // 4. 按物料+年度+月份分组：确保同一物料同一月份的Qty被求和（避免多条重复记录）
  {
    "$group": {
      "_id": {
        "Material": "$Material",
        "FinancialYear": "$forcast.Financial Year",
        "Month": "$forcast.Month"
      },
      "monthlyQty": { "$sum": "$forcast.Qty" },
      "materialInfo": { "$first": "$$ROOT" } // 保留物料主表的原始信息
    }
  },
  // 5. 按物料+年度分组：计算年度总Qty，并收集所有月度数据
  {
    "$group": {
      "_id": {
        "Material": "$_id.Material",
        "FinancialYear": "$_id.FinancialYear"
      },
      "yearlyTotal": { "$sum": "$monthlyQty" },
      "monthlyRecords": { "$push": { "Month": "$_id.Month", "Qty": "$monthlyQty" } },
      "materialInfo": { "$first": "$materialInfo" }
    }
  },
  // 6. 计算最近1个月和最近3个月的总和：先对月度数据按时间倒序排序，再切片求和
  {
    "$addFields": {
      "sortedMonthlyRecords": { "$sortArray": { "input": "$monthlyRecords", "sortBy": { "Month": -1 } } },
      "last1MonthSum": {
        "$cond": [
          { "$gte": [{ "$size": "$sortedMonthlyRecords" }, 1] },
          { "$arrayElemAt": ["$sortedMonthlyRecords.Qty", 0] },
          0.0
        ]
      },
      "last3MonthsSum": {
        "$cond": [
          { "$gte": [{ "$size": "$sortedMonthlyRecords" }, 3] },
          { "$sum": { "$slice": ["$sortedMonthlyRecords.Qty", 0, 3] } },
          { "$sum": "$sortedMonthlyRecords.Qty" }
        ]
      }
    }
  },
  // 7. 整理输出格式：只保留需要的字段，让结果更清晰
  {
    "$project": {
      "_id": 0,
      "Material": "$_id.Material",
      "MaterialDescription": "$materialInfo.Material description",
      "FinancialYear": "$_id.FinancialYear",
      "YearlySum": "$yearlyTotal",
      "Last3MonthsSum": "$last3MonthsSum",
      "Last1MonthSum": "$last1MonthSum",
      "MonthlyBreakdown": "$sortedMonthlyRecords"
    }
  }
], { "allowDiskUse": true });

代码逻辑说明 #

1. 关联与展开：通过$lookup关联两个集合，$unwind展开数组确保每条预测记录独立处理；
2. 筛选数据：$match阶段过滤指定员工的记录，可按需添加年度筛选条件；
3. 月度求和：先按物料+年度+月份分组，确保每个月的Qty是该月的总合；
4. 年度求和与数据收集：再按物料+年度分组，计算年度总Qty，同时收集所有月度数据；
5. 多维度求和：使用$sortArray对月度数据倒序排序，通过$slice和$sum计算最近1/3个月的总和；
6. 格式整理：$project阶段精简输出字段，让结果更易读。

预期输出示例 #

运行上述代码后，你会得到类似如下的结构化结果：

{
  "Material": "810000094",
  "MaterialDescription": "Tech Hexaconazole",
  "FinancialYear": "2015-2016",
  "YearlySum": 15.0,
  "Last3MonthsSum": 15.0,
  "Last1MonthSum": 15.0,
  "MonthlyBreakdown": [
    { "Month": "2015-04-01", "Qty": 15.0 }
  ]
}

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