我希望可以使用以下数据集,计算每个代理商每月的总佣金收入:
结果如下:
db.comm.find()
/* 0 */
{
month: 1,
deals: [
{ agent: { _id: 1, name: 'Joe' }, deal: { _id: 1, comm: 10000 } },
{ agent: { _id: 1, name: 'Joe' }, deal: { _id: 2, comm: 13000 } },
{ agent: { _id: 2, name: 'Sue' }, deal: { _id: 3, comm: 20000 } }
],
referrals: [
{ agent: { _id: 1, name: 'Joe' }, referral_comm: 3000 },
{ agent: { _id: 3, name: 'Pete' }, referral_comm: 2500, other_comm: 1000 }
]
}
/* 1 */
{
month: 2,
deals: [
{ agent: { _id: 1, name: 'Joe' }, deal: { _id: 4, comm: 11000 } },
{ agent: { _id: 3, name: 'Pete' }, deal: { _id: 5, comm: 21000 } }
],
referrals: [
{ agent: { _id: 2, name: 'Sue' }, referral_comm: 2100, other_comm: 1100 },
{ agent: { _id: 4, name: 'Judy' }, referral_comm: 1100 }
]
}
我在使用以下流程时得到了错误的结果:
db.comm.aggregate([
{
$unwind: "$deals"
},
{
$project: {
month: 1,
agent: "$deals.agent",
comm: "$deals.deal.comm",
referrals: 1
}
},
{
$unwind: "$referrals"
},
{
$project: {
month: 1,
agent: 1,
comm: 1,
referral_comm: {
$add: [
"$referrals.referral_comm",
"$referrals.other_comm"
]
}
}
},
{
$project: {
month: 1,
agent: 1,
comm: {
$add: [ "$comm", "$referral_comm" ]
}
}
},
{
$group: {
_id: {
month: "$month",
agent: "$agent"
},
total: {
$sum: "$comm"
}
}
}
])
结果如下:
/* 0 */
{
"result" : [
{
"_id" : {
"month" : 1,
"agent" : {
"_id" : 2,
"name" : "Sue"
}
},
"total" : 23500 //expected 20000
},
{
"_id" : {
"month" : 1,
"agent" : {
"_id" : 1,
"name" : "Joe"
}
},
"total" : 30000 //expected 26000
}, //missing Pete in Month 2
{
"_id" : {
"month" : 2,
"agent" : {
"_id" : 3,
"name" : "Pete"
}
},
"total" : 24200 //expected 21000
},
{
"_id" : {
"month" : 2,
"agent" : {
"_id" : 1,
"name" : "Joe"
}
},
"total" : 14200 //expected 11000
}
], //missing Sue and Judy
"ok" : 1
}
我遇到的问题是,在管道中似乎找不到正确的组合方式,将成交和推荐结合起来,使佣金留给同一代理。这个问题可以通过Mongo的聚合框架实现吗?还是唯一的方法是使用MapReduce?