MongoDB使用aggregate分组后如何计算指定列的数值总和

你当前的查询用$first仅提取了分组内第一条文档的字段值，要实现分组后计算CFD、TC Passed、TC Failed、TC Blocked的总和，需要替换为MongoDB的$sum累加器，同时注意你的示例文档中这四个字段均为字符串类型，累加前需要先转换为数值类型，修改后的完整查询如下：

db.getCollection('emer').aggregate([
    {
        "$group":{
            "_id": {
                "Region": "$Region",
                "Theatre": "$Theatre",
                "Customer": "$Customer",
                "Test Type": "$Test Type",
                "Test Sub-type": "$Test Sub-type",
                "Manual/Auto": "$Manual/Auto",
                "Report Date": "$Report Date"
            },
            // 累加同分组内对应字段的数值
            "total_CFD": { "$sum": { "$toInt": "$CFD" } },
            "total_TC_Passed": { "$sum": { "$toInt": "$TC Passed" } },
            "total_TC_Failed": { "$sum": { "$toInt": "$TC Failed" } },
            "total_TC_Blocked": { "$sum": { "$toInt": "$TC Blocked" } }
        }
    },
    {
        "$project": {
            "_id": 0,
            // 从分组ID中提取维度字段
            "Region": "$_id.Region",
            "Theatre": "$_id.Theatre",
            "Customer": "$_id.Customer",
            "Test Type": "$_id.Test Type",
            "Test Sub-type": "$_id.Test Sub-type",
            "Manual/Auto": "$_id.Manual/Auto",
            "Report Date": "$_id.Report Date",
            // 映射求和结果，保持原有字段名
            "CFD": "$total_CFD",
            "TC Passed": "$total_TC_Passed",
            "TC Failed": "$total_TC_Failed",
            "TC Blocked": "$total_TC_Blocked"
        }
    }
])

如果你的实际业务中这四个字段已经是数值类型，可以去掉$toInt转换逻辑，直接写"$sum": "$字段名"即可。

内容的提问来源于stack exchange，提问作者subhra_user