需要帮助才能汇总$ group状态

Question

我收集了1000个文件的集合：

{ 
    "_id" : ObjectId("628b63d66a5951db6bb79905"), 
    "index" : 0, 
    "name" : "Aurelia Gonzales", 
    "isActive" : false, 
    "registered" : ISODate("2015-02-11T04:22:39.000+0000"), 
    "age" : 41, 
    "gender" : "female", 
    "eyeColor" : "green", 
    "favoriteFruit" : "banana", 
    "company" : {
        "title" : "YURTURE", 
        "email" : "[email protected]", 
        "phone" : "+1 (940) 501-3963", 
        "location" : {
            "country" : "USA", 
            "address" : "694 Hewes Street"
        }
    }, 
    "tags" : [
        "enim", 
        "id", 
        "velit", 
        "ad", 
        "consequat"
    ]
}

我想按年份和性别进行分组。像2014年男性注册105和女性注册131。最后返回这样的文件：

{
    _id:2014,
    male:105,
    female:131,
    total:236
},
{
    _id:2015,
    male:136,
    female:128,
    total:264
}

我已经尝试了注册和性别 this：this：this：

db.persons.aggregate([
    { $group: { _id: { year: { $year: "$registered" }, gender: "$gender" }, total: { $sum: NumberInt(1) } } },
    { $sort: { "_id.year": 1,"_id.gender":1 } }
])

它是这样的返回文档：

{ 
    "_id" : {
        "year" : 2014, 
        "gender" : "female"
    }, 
    "total" : 131
}
{ 
    "_id" : {
        "year" : 2014, 
        "gender" : "male"
    }, 
    "total" : 105
}

请指导从整体中弄清楚。

Answer 1

db.collection.aggregate([
  {
    "$group": { //Group things
      "_id": "$_id.year",
      "gender": {
        "$addToSet": {
          k: "$_id.gender",
          v: "$total"
        }
      },
      sum: { //Sum it
        $sum: "$total"
      }
    }
  },
  {
    "$project": {//Reshape it
      g: {
        "$arrayToObject": "$gender"
      },
      _id: 1,
      sum: 1
    }
  },
  {
    "$project": { //Reshape it
      _id: 1,
      "g.female": 1,
      "g.male": 1,
      sum: 1
    }
  }
])

play

Answer 2

只需在您的聚合管道中再添加一个小组阶段，例如：

db.persons.aggregate([
    { $group: { _id: { year: { $year: "$registered" }, gender: "$gender" }, total: { $sum: NumberInt(1) } } },
    { $sort: { "_id.year": 1,"_id.gender":1 } },
{
  $group: {
    _id: "$_id.year",
    male: {
      $sum: {
        $cond: {
          if: {
            $eq: [
              "$_id.gender",
              "male"
            ]
          },
          then: "$total",
          else: 0
        }
      }
    },
    female: {
      $sum: {
        $cond: {
          if: {
            $eq: [
              "$_id.gender",
              "female"
            ]
          },
          then: "$total",
          else: 0
        }
      }
    },
    total: {
      $sum: "$total"
    }
  },
}
]);

这是工作 link 。我们在最后一步中按一年进行分组，并计算有条件的性别计数，而总数只是计数的总数，而不论性别如何。

Answer 3

Besides @Gibbs mentioned in the comment which proposes the solution with 2 $group stages,

You can achieve the result as below:

1. $group - Group by year of <代码>注册。将性别值添加到genders array。

2. $ sort - 订购_id。

3. $ project - 装饰输出文档。

   3.1。 男性 - 从$ filter获取数组的大小“男性”在“ genders”数组中的值。

   3.2。 女性 - 从$ filter获取数组的大小“女性”阵列中的“女性”数量。

   3.3。 Total - 获取“ Genders”数组的大小。

如果您期望您计算并返回“男性”和“女性”性别领域，请提出此方法。

db.collection.aggregate([
  {
    $group: {
      _id: {
        $year: "$registered"
      },
      genders: {
        $push: "$gender"
      }
    }
  },
  {
    $sort: {
      "_id": 1
    }
  },
  {
    $project: {
      _id: 1,
      male: {
        $size: {
          $filter: {
            input: "$genders",
            cond: {
              $eq: [
                "$this",
                "male"
              ]
            }
          }
        }
      },
      female: {
        $size: {
          $filter: {
            input: "$genders",
            cond: {
              $eq: [
                "$this",
                "female"
              ]
            }
          }
        }
      },
      total: {
        $size: "$genders"
      }
    }
  }
])

示例mongo playground