时间戳

首页 > 解决方案 > Mongo聚合多个组和总和

mongodb - Mongo聚合多个组和总和

问题描述

我有一个扁平的数据结构，如下所示：

{
 state: String,
 caseA: Number,
 caseB: Number,
 caseC: Number
}

我想对每个案例进行汇总和总和，然后我想按州对每个案例进行总和。

我可以做第一部分，但不知道如何做第二个分组。

{$group: {
 _id: null,
 total_caseA: {$sum: "$caseA"},
 total_caseB: {$sum: "$caseB"},
 total_caseC: {$sum: "$caseC"},
}}

我希望结果如下所示：

{
 total_caseA: <sum of all caseA across all states>,
 total_caseB: <sum of all caseB across all states>,
 total_caseC: <sum of all caseB across all states>,
 states: [
  "AK": {
   caseA: <sum of caseA for state>,
   caseB: <sum of caseB for state>,
   caseB: <sum of caseC for state>,
  },
  "CA": {
   caseA: <sum of caseA for state>,
   caseB: <sum of caseB for state>,
   caseB: <sum of caseC for state>,
  },
  ...
 ]
}

谢谢你。

标签： mongodbaggregation-framework

解决方案

您可以尝试以下聚合

$facet将为您提供两个不同的数组，一个用于total案例计数，一个用于$groupbystates及其案例计数...在状态计数中，您必须将所有值推入一个数组以使值作为键使用$arrayToObject...最后$replaceRoot使用第零个元素两个数组将最终确定您的输出

db.collection.aggregate([
  { "$facet": {
    "total": [
      { "$group": {
        "_id": null,
        "total_caseA": { "$sum": "$caseA" },
        "total_caseB": { "$sum": "$caseB" },
        "total_caseC": { "$sum": "$caseC" }
      }}
    ],
    "states": [
      { "$group": {
        "_id": "$state",
        "total_caseA": { "$sum": "$caseA" },
        "total_caseB": { "$sum": "$caseB" },
        "total_caseC": { "$sum": "$caseC" }
      }},
      { "$group": {
        "_id": null,
        "data": {
          "$push": {
            "k": "$_id",
            "v": {
              "total_caseA": { "$sum": "$total_caseA" },
              "total_caseB": { "$sum": "$total_caseB" },
              "total_caseC": { "$sum": "$total_caseC" }
            }
          }
        }
      }},
      { "$replaceRoot": { "newRoot": { "$arrayToObject": "$data" } } }
    ]
  }},
  { "$unwind": "$total" },
  { "$addFields": { "total.states": "$states" } },
  { "$project": { "states": 0 } },
  { "$replaceRoot": { "newRoot": "$total" } }
])

输出

[
  {
    "_id": null,
    "states": [
      {
        "AK": {
          "total_caseA": 1,
          "total_caseB": 2,
          "total_caseC": 3
        },
        "CA": {
          "total_caseA": 2,
          "total_caseB": 4,
          "total_caseC": 6
        }
      }
    ],
    "total_caseA": 3,
    "total_caseB": 6,
    "total_caseC": 9
  }
]

MongoPlayGround

推荐阅读