我有收藏账户:
{
"_id": ObjectId("571a0e145d29024733793cfe"),
"name": "Cash",
"user": ObjectId("571a0e145d29024733793cfc")
}
以及交易。交易可以有不同的字段:
{
"_id": ObjectId("571a0e145d29024733793d06"),
"amount": 100,
"type": "earned",
"account": ObjectId("571a0e145d29024733793cfe"),
"user": ObjectId("571a0e145d29024733793cfc"),
},
{
"_id": ObjectId("571a0e145d29024733793d04"),
"amount": 300,
"type": "spent",
"account": ObjectId("571a0e145d29024733793cfe"),
"user": ObjectId("571a0e145d29024733793cfc")
},
{
"_id": ObjectId("571a0e145d29024733793d07"),
"amount": 100,
"type": "transfer",
"sourceAccount": ObjectId("571a0e145d29024733793cfd"),
"destinationAccount": ObjectId("571a0e145d29024733793cfe"),
"user": ObjectId("571a0e145d29024733793cfc"),
}
我希望按每个账户创建一个统计组。 我向数据库编写了一个聚合框架查询:
db.transactions.aggregate([
{ $match: { user: user._id } },
{
$group: {
_id: '$account',
earned: {
$sum: {
$cond: [{ $eq: ['$type', 'earned'] }, '$amount', 0]
}
},
spent: {
$sum: {
$cond: [{ $eq: ['$type', 'spent'] }, '$amount', 0]
}
},
deposits: {
$sum: {
$cond: [{ $eq: ['$type', 'transfer'] }, '$amount', 0]
}
},
withdrawal: {
$sum: {
$cond: [{ $eq: ['$type', 'transfer'] }, '$amount', 0]
}
},
maxEarned: {
$max: {
$cond: [{ $eq: ['$type', 'earned'] }, '$amount', 0]
}
},
maxSpent: {
$max: {
$cond: [{ $eq: ['$type', 'spent'] }, '$amount', 0]
}
},
count: { $sum: 1 }
}
}
]);
但它不能正常工作。它只适用于带有账户字段的交易。我想按账户、源账户或目标账户分组。
我还尝试在"_id"字段中编写:
_id: { account: '$account', sourceAccount: '$sourceAccount', destinationAccount: '$destinationAccount' }
或者
_id: {$or: ['$account', '$sourceAccount', '$destinationAccount']}
或者
_id: {$in: ['$account', '$sourceAccount', '$destinationAccount']}
但它可能会分错组或无法正常工作。
我如何从不同的字段进行分组?