蒙戈DB总结
问题描述：
我有一个看起来像条目的集合：蒙戈DB总结
{"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
}
]
}
]
}
即每地名与天都的列表独特的creditAccounts，debitAccounts，taxAccounts及其总和。我已成功地做分组的领域之一，它看起来像：
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}},
])
但我不知道如何管理办分组对所有3 creditAccount，taxAccount & debitAccount。也许在mapReduce中可能会出现这种情况，但由于我读取的性能比使用聚合框架要糟糕得多，所以没有考虑到这一点。
答
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*分组添加thoose？ 像： _id：{_ ID： “$地名”，日期：{$ dateToString：{格式为： “％Y-％间％d”，日期： “$创建”}}，creditAccount：“$ creditAccount '，debitAccount：'$ debitAccount'，taxAccount：'$ taxAccount'}， –
@FrejHåkansson如果你想要列表中的唯一项目，使用$ addToSet而不是$ push，因为我已经更新了答案。 –
也许我有点不清楚。字段taxAccount/creditAccount/debitAccount是ID和列表：creditAccounts/debitAccounts/taxAccounts应包含每个taxAccount/creditAccount/debitAccount的taxAmount/debitAmount/creditAmount的总和。因此，在我的测试数据中，我有9个可能的creditAccount值，因此每天的creditAccounts列表不应该大于9，而应该增加总和。运行您的建议答案我每天在每个列表中获得数百个条目。因此，回复中的条目数量绝不会减少。 –