MongoDB聚合实现按日期、区域二级分组统计各部门数值总和

输入数据 #

集合中存储的样例文档结构如下：

[
  {
    "year": "2022-10-01",
    "Area": "abc",
    "Engineering": 100,
    "Commerce": 20,
    "Arts": 10
  },
  {
    "year": "2022-10-01",
    "Area": "def",
    "Engineering": 90,
    "Commerce": 60,
    "Arts": 40
  },
  {
    "year": "2021-01-01",
    "Area": "abc",
    "Engineering": 70,
    "Commerce": 30,
    "Arts": 90
  },
  {
    "year": "2022-01-01",
    "Area": "def",
    "Engineering": 100,
    "Commerce": 10,
    "Arts": 50
  }
]

需求说明 #

需要实现的统计逻辑：

• 首先按year字段做一级分组
• 同一年份下按Area字段做二级分组
• 每个二级分组内分别对Engineering、Commerce、Arts三个字段求和
• 最终输出一级key为年份、二级key为区域、三级属性为各部门求和结果的嵌套对象，结构参考：

{
  "2022-10-01": {
    "abc": {
      "Engineering": "<total engineering count>",
      "Commerce": "<total commerce count>",
      "Arts": "<total arts count>"
    },
    "def": {
      "Engineering": "<total engineering count>",
      "Commerce": "<total commerce count>",
      "Arts": "<total arts count>"
    }
  },
  "2021-01-01": {
    "abc": {
      "Engineering": "<total engineering count>",
      "Commerce": "<total commerce count>",
      "Arts": "<total arts count>"
    },
    "def": {
      "Engineering": "<total engineering count>",
      "Commerce": "<total commerce count>",
      "Arts": "<total arts count>"
    }
  }
}

聚合实现代码 #

直接执行如下聚合管道即可得到目标结果，核心通过两次分组求和后，用$arrayToObject将分组键转换为对象属性，最终替换根节点输出要求的嵌套结构：

// 替换yourCollection为实际集合名
db.yourCollection.aggregate([
  // 第一步：按 年份+区域 联合分组，计算三个部门的累计值
  {
    $group: {
      _id: {
        year: "$year",
        area: "$Area"
      },
      sumEngineering: { $sum: "$Engineering" },
      sumCommerce: { $sum: "$Commerce" },
      sumArts: { $sum: "$Arts" }
    }
  },
  // 第二步：按年份分组，把同一年下所有区域的统计值整理为键值对数组
  {
    $group: {
      _id: "$_id.year",
      areaList: {
        $push: {
          k: "$_id.area",
          v: {
            Engineering: "$sumEngineering",
            Commerce: "$sumCommerce",
            Arts: "$sumArts"
          }
        }
      }
    }
  },
  // 第三步：把所有年份的统计值整理为键值对数组
  {
    $group: {
      _id: null,
      yearList: {
        $push: {
          k: "$_id",
          v: { $arrayToObject: "$areaList" }
        }
      }
    }
  },
  // 第四步：将年份键值对数组转为顶层对象，输出最终结构
  {
    $replaceRoot: {
      newRoot: { $arrayToObject: "$yearList" }
    }
  }
])

运行结果 #

针对给出的4条样例数据，执行后返回结果如下：

{
  "2022-10-01": {
    "abc": {
      "Engineering": 100,
      "Commerce": 20,
      "Arts": 10
    },
    "def": {
      "Engineering": 90,
      "Commerce": 60,
      "Arts": 40
    }
  },
  "2021-01-01": {
    "abc": {
      "Engineering": 70,
      "Commerce": 30,
      "Arts": 90
    }
  },
  "2022-01-01": {
    "def": {
      "Engineering": 100,
      "Commerce": 10,
      "Arts": 50
    }
  }
}

说明：默认逻辑下，没有数据的年份-区域组合不会出现在结果中。如果需要强制补全所有年份下的所有区域节点，可以在管道最开始增加$facet阶段分别拉取全量年份、全量区域列表，做笛卡尔积后再关联统计值即可。

内容的提问来源于stack exchange，提问作者mu-Stack