MongoDB中有条件的多个Group By

Question

我有字段group_id、category、account、sku和revenue。我想做一个聚合，收入分别按group_id、category、sku和account分组，在sku中有两个部分，一个有逗号，一个没有逗号。我该怎么做？

例如，如果我的数据是

[
{
"group_id": "ABC",
"category": "test",
"account": "abc1",
"sku": "grre,qrvf",
"revenue": 100,
},
{
"group_id": "ABC2",
"category": "test",
"account": "abc",
"sku": "grre,qrvf",
"revenue": 100,
},
{
"group_id": "ABC3",
"category": "test2",
"account": "abc2",
"sku": "qwe",
"revenue": 100,
},
{
"group_id": "ABC",
"category": "test2",
"account": "abc",
"sku": "qwe",
"revenue": 100,
},
{
"group_id": "ABC2",
"category": "test3",
"account": "abc2",
"sku": "asd,fgh",
"revenue": 100,
},
{
"group_id": "ABC",
"category": "test3",
"account": "abc",
"sku": "asd,fgh",
"revenue": 100,
},
{
"group_id": "ABC3",
"category": "test",
"account": "abc2",
"sku": "grre,qrvf",
"revenue": 100,
}
]

那么结果应该像

[
{groups: [{group_id: "ABC", "revenue": 300}, {group_id: "ABC2", "revenue": 200}, {group_id: "ABC3", "revenue": 200}]},
{categories: [{category: "test", "revenue": 300}, {category: "test2", "revenue": 200}, {category: "test3", "revenue": 200}]},
{accounts: [{account: "abc", "revenue": 300}, {account: "abc2", "revenue": 100}, {account: "abc3", "revenue": 300}]},
{skus: [{single: [{sku: "qwe", revenue: 100}, {sku: "iou", revenue: 100}]}, {multi: [{sku: "grre,qrvf", revenue: 300}, {sku: "asd,fgh", revenue: 200}]}]},
]

Answer 1

您可以使用$facet在一次往返中分别运行多个聚合，并使用$indexOfBytes来拆分skus:

db.collection.aggregate([
{
$facet: {
group: [ { $group: { _id: "$group_id", revenue: { $sum: "$revenue" } } }, { $project: { _id: 0, group: "$_id", revenue: 1 } } ],
categories: [ { $group: { _id: "$category", revenue: { $sum: "$revenue" } } }, { $project: { _id: 0, category: "$_id", revenue: 1 } } ],
accounts: [ { $group: { _id: "$account", revenue: { $sum: "$revenue" } } }, { $project: { _id: 0, account: "$_id", revenue: 1 } } ],
skus: [ 
{  $group: { _id: "$sku", revenue: { $sum: "$revenue" } } }, 
{  $group: { _id: null, aggregates: { $push: { sku: "$_id", revenue: "$revenue" } } } }, 
{  $project: { 
_id: 0,
single: { $filter: { input: "$aggregates", cond: { $eq: [ { $indexOfBytes: [ "$$this.sku", "," ] }, -1 ] } } },
multi: { $filter: { input: "$aggregates", cond: { $ne: [ { $indexOfBytes: [ "$$this.sku", "," ] }, -1 ] } } },
},
}
],
}
}
])

Mongo游乐场