javascript - MongoDB:聚合每个日历周和每年的文档并计算一个字段的所有值的总数

Question

{
    "GU" : "Person2",
    "CW" : 2,
    "Year" : 2015,
    "_id" : "01"
},
{
    "GU" : "Person2",
    "CW" : 2,
    "Year" : 2015,
    "_id" : "02"
},
{
    "GU" : "Person1",
    "CW" : 2,
    "Year" : 2015,
    "_id" : "03"
},
{
    "GU" : "Person1",
    "CW" : 3,
    "Year" : 2015,
    "_id" : "04"
},
{
    "GU" : "Person1",
    "CW" : 51,
    "Year" : 2014,
    "_id" : "05"
},
{
    "GU" : "Person1",
    "CW" : 51,
    "Year" : 2014,
    "_id" : "06"
}

有没有办法聚合我的数据，以便将其按 CW 和年份进行分组，并且我将获得每周不同人员的计数？

所以最后我的数据应该是这样的:

{Year: 2014, CW:51, GU:{Person1:2}},
{Year: 2015, CW:2, GU:{Person1:1, Person2:2}},
{Year: 2015, CW:3, GU:{Person1:1}}

Answer 1

您可以通过连续两次分组来轻松实现这一点:

db.mycol.aggregate([
    {$group:{
        _id: {Year: "$Year", CW:"$CW", GU: "$GU"},
        total:{  
            $sum:1
        }
    }},
    {$group:{
        _id: {Year: "$_id.Year", CW:"$_id.CW"},
        GUs: { $push:  { GU: "$_id.GU", total: "$total" } }
    }},
    {$sort: {"_id.Year" : 1, "_id.CW" : 1}}
])

编辑:将排序添加到聚合管道

结果将是:

{
    "result" : [
            {
                    "_id" : {
                            "Year" : 2014,
                            "CW" : 51
                    },
                    "GUs" : [
                            {
                                    "GU" : "Person1",
                                    "total" : 2
                            }
                    ]
            },
            {
                    "_id" : {
                            "Year" : 2015,
                            "CW" : 2
                    },
                    "GUs" : [
                            {
                                    "GU" : "Person1",
                                    "total" : 1
                            },
                            {
                                    "GU" : "Person2",
                                    "total" : 2
                            }
                    ]
            },
            {
                    "_id" : {
                            "Year" : 2015,
                            "CW" : 3
                    },
                    "GUs" : [
                            {
                                    "GU" : "Person1",
                                    "total" : 1
                            }
                    ]
            }
    ],
    "ok" : 1
}