logo标签列表

Mongo DB 聚合函数

mongodbaggregation-framework
3

我有一个集合，其中的条目看起来像这样：

{"placeId" : "0b980482-ab4b-4685-95ab-ecc2dcba1837","placeName" : "B8 Göteborg","created" : ISODate("2017-04-09T14:53:01.005Z"),"debitAccount" : "1581","debitAmount" : 26.0,"creditAccount" : "3013","creditAmount" : 23.2142791748047,"taxAccount" : "2620","taxAmount" : 2.78572010993958},
{"placeId" : "0b980482-ab4b-4685-95ab-ecc2dcba1837","placeName" : "B8 Göteborg","created" : ISODate("2017-04-09T14:53:01.005Z"),"debitAccount" : "1581","debitAmount" : 26.0,"creditAccount" : "3014","creditAmount" : 53.2142791748047,"taxAccount" : "2621","taxAmount" : 5.78572010993958}

我希望输出的结果是这样的：

我想要的输出结果类似于：

{
    "_id" : "B8 - Göteborg",
    "days" : [ 
        {
            "date" : "2017-03-24",
            "creditAccounts" : [ 
                {
                    "creditAccount" : "3013",
                    "creditAccountSum" : 23.214279174804
                }, 
                {
                    "creditAccount" : "3014",
                    "creditAccountSum" : 53.2142791748047,
                }, 
            ],
            "debitAccounts" : [ 
                {
                    "debitAccount" : "1581",
                    "debitAccountSum" : 52
                }
            ],
            "taxAccounts" : [ 
                {
                    "taxAccount" : "2620",
                    "taxAccountSum" : 2.78572010993958
                },
                {
                    "taxAccount" : "2621",
                    "taxAccountSum" : 5.78572010993958
                }
            ]
        }
    ]
}

根据地点和日期，列出所有唯一的信用账户、借记账户、税收账户及其总和。我已经成功对其中一个字段进行了分组，看起来像这样：

db.getCollection('accounting_records').aggregate([
    {$match : {...},  
    {$group: {_id: {placeName :'$placeName',year: {$substr: ['$created',0,4]},month: {$substr: ['$created',5,2]},dayOfMonth: {$substr: ['$created',8,2]},creditAccount:'$creditAccount'}, created: {$max: '$created'},records: { $addToSet: "$$ROOT" }}},
    {$sort : {created : 1}},
    {$group: {_id: {placeName :'$_id.placeName',year: '$_id.year',month: '$_id.month',dayOfMonth: '$_id.dayOfMonth'},created: {$max: '$created'}, creditAccounts:{$push :{creditAccount: '$_id.creditAccount',sum : {$sum: '$records.creditAmount'}, creditAccountName:'$records.creditAccountName'}}}},
    {$sort : {created : 1}},
    {$group : {_id : '$_id.placeName', created: {$max: '$created'}, days : {$push: {date: {$concat: ['$_id.year','-','$_id.month','-','$_id.dayOfMonth']},creditAccounts : '$creditAccounts'}}}},
    {$sort : {_id : 1}},
])

但我不知道如何对所有的信用账户、税务账户和借记账户进行分组管理。也许可以使用MapReduce来实现，但是由于阅读到性能比使用聚合框架要差得多，因此尚未深入研究。

1个回答
1
You need to `$group` each credit/debit/tax-Account/Amount and calculate sum.



db.collection.aggregate([
    {
        $group: {
            _id:{_id:"$placeName",date:{$dateToString:{format:"%Y-%m-%d" , date:"$created"}}, creditAccount:"$creditAccount" },
            creditAccountSum : {$sum: "$creditAmount"},
            remaining: {$push: {debitAccount:"$debitAccount",debitAmount:"$debitAmount",taxAccount:"$taxAccount",taxAmount:"$taxAmount"}}
        }
    },
    {$unwind: "$remaining"},
    {
        $group: {
            _id:{_id:"$_id._id",date:"$_id.date", debitAccount:"$remaining.debitAccount"},
            debitAccountSum : {$sum: "$remaining.debitAmount"},
            remaining: {$push: {creditAccount:"$_id.creditAccount",creditAccountSum:"$creditAccountSum", taxAccount:"$remaining.taxAccount",taxAmount:"$remaining.taxAmount"}}
        }
    },
    {$unwind: "$remaining"},
    {
        $group: {
            _id:{_id:"$_id._id",date:"$_id.date", taxAccount:"$remaining.taxAccount"},
            taxAccountSum : {$sum: "$remaining.taxAmount"},
            remaining: {$push :{creditAccount:"$remaining.creditAccount",creditAccountSum:"$remaining.creditAccountSum", debitAccount:"$_id.debitAccount", debitAccountSum:"$debitAccountSum"}}
        }
    },
    {$unwind: "$remaining"},
    {   
        $group : {
                _id:{_id:"$_id._id",date:"$_id.date"},
                creditAccounts:{$addToSet:{creditAccount:"$remaining.creditAccount",creditAccountSum:"$remaining.creditAccountSum"}},
                debitAccounts:{$addToSet:{debitAccount:"$remaining.debitAccount",debitAccountSum:"$remaining.debitAccountSum"}},
                taxAccounts:{$addToSet:{taxAccount:"$_id.taxAccount",taxAccountSum:"$taxAccountSum"}}      
        }
    },
    {
        $group : {
            _id : {_id:"$_id._id"},
            days : {$push: {date:"$_id.date",creditAccounts:"$creditAccounts",debitAccounts:"$debitAccounts", taxAccounts:"$taxAccounts"}}
        }
    },
    {
        $project: {
            _id:"$_id._id",
            days:"$days"
        }
    }
]).pretty()

测试数据：

{"placeId" : "0b980482-ab4b-4685-95ab-ecc2dcba1837","placeName" : "B8 Göteborg","created" : ISODate("2017-04-09T14:53:01.005Z"),"debitAccount" : "1581","debitAmount" : 26.0,"creditAccount" : "3013","creditAmount" : 23.2142791748047,"taxAccount" : "2620","taxAmount" : 2.78572010993958},
{"placeId" : "0b980482-ab4b-4685-95ab-ecc2dcba1837","placeName" : "B8 Göteborg","created" : ISODate("2017-04-09T14:53:01.005Z"),"debitAccount" : "1581","debitAmount" : 26.0,"creditAccount" : "3014","creditAmount" : 53.2142791748047,"taxAccount" : "2621","taxAmount" : 5.78572010993958},
{"placeId" : "0b980482-ab4b-4685-95ab-ecc2dcba1837","placeName" : "B8 Göteborg","created" : ISODate("2017-05-09T14:53:01.005Z"),"debitAccount" : "1581","debitAmount" : 26.0,"creditAccount" : "3014","creditAmount" : 53.2142791748047,"taxAccount" : "2621","taxAmount" : 5.78572010993958}

输出：

{
    "_id" : "B8 Göteborg",
    "days" : [
        {
            "date" : "2017-04-09",
            "creditAccounts" : [
                {
                    "creditAccount" : "3014",
                    "creditAccountSum" : 53.2142791748047
                },
                {
                    "creditAccount" : "3013",
                    "creditAccountSum" : 23.2142791748047
                }
            ],
            "debitAccounts" : [
                {
                    "debitAccount" : "1581",
                    "debitAccountSum" : 52
                }
            ],
            "taxAccounts" : [
                {
                    "taxAccount" : "2621",
                    "taxAccountSum" : 5.78572010993958
                },
                {
                    "taxAccount" : "2620",
                    "taxAccountSum" : 2.78572010993958
                }
            ]
        },
        {
            "date" : "2017-05-09",
            "creditAccounts" : [
                {
                    "creditAccount" : "3014",
                    "creditAccountSum" : 108.4285583496094
                }
            ],
            "debitAccounts" : [
                {
                    "debitAccount" : "1581",
                    "debitAccountSum" : 52
                }
            ],
            "taxAccounts" : [
                {
                    "taxAccount" : "2621",
                    "taxAccountSum" : 12.57144021987916
                }
            ]
        }
    ]
}
感谢回复。但这并不能解决我的问题，因为我希望creditAccounts/debitAccounts/taxaccounts中的每个条目都是唯一的。在您发布的响应中，“debitAccount”：“1581”出现了两次，所以我想我必须将它们添加到_id顶层分组中？像：_id：{_id：“$placeName”，date：{$dateToString：{format：“％Y-％m-％d”，date：“$created”}}，creditAccount：'$creditAccount'，debitAccount：'$debitAccount'，taxAccount：'$taxAccount'}， - Frej Håkansson
@FrejHåkansson 如果你想在列表中获取唯一的项目，请使用$addToSet而不是$push，就像我在答案中更新的那样。 - Ra Ka
也许我表达不太清楚。字段taxAccount/creditAccount/debitAccount是ID，而列表：creditAccounts/debitAccounts/taxAccounts应该包含每个taxAccount/creditAccount/debitAccount的taxAmount/debitAmount/creditAmount之和。因此，在我的测试数据中，creditAccount有9个可能的值，因此每天的creditAccounts列表不应超过9个，而应增加总和。运行您建议的答案后，我每天在每个列表中都会得到数百个条目。因此，响应中的条目数从未减少。 - Frej Håkansson
@FrejHåkansson，希望我现在理解你了。请检查更新后的答案。 - Ra Ka
先生，您说得对。我想我必须使用unwind，但真的不明白如何保存结果。非常感谢，您是一个巫师。 - Frej Håkansson
你好！现在我的数据结构有些变化，但我想要相同的输出。每个条目现在包含收入账户、借记账户和贷记账户的列表。每个列表都包含名称、账号和金额。如果我从这些列表开始展开，总和就会出错。你觉得你能帮我吗？ - Frej Håkansson
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接