如何在MongoDB聚合中仅保留分区最后一项的累计求和值？

现有输入数据 #

[
  { "grp": "A", "seq": 1, "score": 1, "x": 0 },
  { "grp": "A", "seq": 1, "score": 2, "x": 0 },
  { "grp": "A", "seq": 1, "score": 3, "x": 0 },
  { "grp": "A", "seq": 1, "score": 4, "x": 0 }
]

现有聚合阶段及结果 #

使用$setWindowFields计算分区内的累计求和：

{
  partitionBy: "$grp",
  sortBy: { seq: 1 },
  output: {
    x: {
      $sum: "$score",
      window: {
        documents: ["unbounded", "current"]
      }
    }
  }
}

得到的结果：

[
  { "grp": "A", "seq": 1, "score": 1, "x": 1 },
  { "grp": "A", "seq": 1, "score": 2, "x": 3 },
  { "grp": "A", "seq": 1, "score": 3, "x": 6 },
  { "grp": "A", "seq": 1, "score": 4, "x": 10 }
]

需求 #

仅保留分区最后一项的累计求和值x，其余项的x恢复为0，期望结果：

[
  { "grp": "A", "seq": 1, "score": 1, "x": 0 },
  { "grp": "A", "seq": 1, "score": 2, "x": 0 },
  { "grp": "A", "seq": 1, "score": 3, "x": 0 },
  { "grp": "A", "seq": 1, "score": 4, "x": 10 }
]

解决方案 #

方法一：在$setWindowFields中标记最后项并赋值

扩展$setWindowFields的输出，新增字段标记当前文档是否为分区最后一项，再通过$set阶段判断赋值：

[
  {
    $setWindowFields: {
      partitionBy: "$grp",
      sortBy: { seq: 1 },
      output: {
        cumulativeX: {
          $sum: "$score",
          window: { documents: ["unbounded", "current"] }
        },
        isLast: {
          $rank: {
            sortBy: { seq: -1 }
          }
        }
      }
    }
  },
  {
    $set: {
      x: { $cond: [{ $eq: ["$isLast", 1] }, "$cumulativeX", 0] },
      cumulativeX: "$$REMOVE",
      isLast: "$$REMOVE"
    }
  }
]

• $rank按seq倒序排序后，分区最后一条文档的排名为1，以此标记是否为最后项
• 用$cond判断：如果是最后项则保留累计值，否则设为0，最后移除中间生成的辅助字段

方法二：先计算分区总累计值再赋值

先通过$setWindowFields计算整个分区的总累计值，同时标记最后项，再赋值：

[
  {
    $setWindowFields: {
      partitionBy: "$grp",
      sortBy: { seq: 1 },
      output: {
        totalX: {
          $sum: "$score",
          window: { documents: ["unbounded", "unbounded"] }
        },
        isLast: {
          $last: true,
          window: { documents: ["unbounded", "unbounded"] }
        }
      }
    }
  },
  {
    $set: {
      x: { $cond: ["$isLast", "$totalX", 0] },
      totalX: "$$REMOVE",
      isLast: "$$REMOVE"
    }
  }
]

• window: ["unbounded", "unbounded"]会计算整个分区的score总和（即最后一条的累计值）
• $last: true会给分区最后一条文档标记为true，其余为false，再通过$cond完成赋值

内容的提问来源于stack exchange，提问作者Joseph D.