Mongo聚合，按两个不同数组中相同字段分组

Question

我希望可以使用以下数据集，计算每个代理商每月的总佣金收入：

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？

- jacoswarts

Answer 1

玩弄一下，似乎有一种方法可以通过聚合管道来使其工作，但坦率地说，您的文档结构非常糟糕，无法编写一个计算佣金/（月份*代理商）的管道。您是否考虑过让每个文档代表“佣金事件”，例如交易或推荐，而不是让每个文档代表一个月？

{
    "agent" : { "_id" : 1, "name" : "Joe" },
    "month" : 1,
    "type" : "deal",
    "deal_id" : 1,
    "comm" : 10000
}

这些文件的流程非常简单。

db.test.aggregate([
    { "$group" : { 
        "_id" : { "agent_id" : "$agent._id", "month" : "$month" },
        "comm" : { "$sum" : "$comm" }
    } }
])

我认为这种替代文档结构是有意义的，因为

• 聚合更容易编写（速度也更快，尽管它正在处理每个文档）
• 您可以使用此结构查询您可能关心的信息以及更具体的信息，比使用月份文档结构更容易；例如，对于您的月份文档，您如何查询所有佣金大于某个金额的记录？
• 月份文档将增长并可能需要在磁盘上移动，这会影响性能（仅适用于mmap存储引擎）

- wdberkeley

---

感谢建议，但该结构非常适合按月捕获数据，经过一些审批和审核流程等。当负荷较低且报告由少数用户每月完成时，所以该结构针对大量用户在短时间内提交账单的情况进行了优化。 - jacoswarts

上面的例子是简化的，仅用来说明我的问题。 - jacoswarts

Answer 2

我认为我发现了一条可行的流水线，尽管很昂贵：

db.comm.aggregate([
    {
        $unwind: "$deals"
    },
    {
        $unwind: "$referrals"
    },
    {
        $group: {
            _id: {
                month: "$month"
            }, 
            deals: {
                $push: {
                    agent: "$deals.agent",
                    comm: { $ifNull: ["$deals.deal.comm", 0] }
                }
            },
            referrals: {
                $push: {
                    agent: "$referrals.agent",
                    comm: {
                        $add: [{ $ifNull: ["$referrals.referral_comm", 0] }, { $ifNull: ["$referrals.other_comm", 0] }]
                    }
                }
            }
        }
    },
    {
        $project: {
            month: "$_id.month",
            comms: {
                $setUnion: ["$deals","$referrals"]
            }
        }
    }, {
        $unwind: "$comms"
    }, { 
        $group: {
            _id: {
                month: "$month",
                agent: "$comms.agent"
            },
            total: {
                $sum: "$comms.comm"
            }
        }
    }
])

步骤是取消两个交易和推荐，以创建具有相同字段的新数组。Mongo 2.6允许$setUnion创建这些不同数组的联合。请注意，我必须添加一个$ifNull的缺失字段检查，以获得正确的结果。最后，$unwind和$group只是跨月份和代理获取组合总数。

然后产生：

/* 0 */
{
    "result" : [ 
        {
            "_id" : {
                "month" : 2,
                "agent" : {
                    "_id" : 2,
                    "name" : "Sue"
                }
            },
            "total" : 3200
        }, 
        {
            "_id" : {
                "month" : 2,
                "agent" : {
                    "_id" : 4,
                    "name" : "Judy"
                }
            },
            "total" : 1100
        }, 
        {
            "_id" : {
                "month" : 2,
                "agent" : {
                    "_id" : 1,
                    "name" : "Joe"
                }
            },
            "total" : 11000
        }, 
        {
            "_id" : {
                "month" : 1,
                "agent" : {
                    "_id" : 2,
                    "name" : "Sue"
                }
            },
            "total" : 20000
        }, 
        {
            "_id" : {
                "month" : 2,
                "agent" : {
                    "_id" : 3,
                    "name" : "Pete"
                }
            },
            "total" : 21000
        }, 
        {
            "_id" : {
                "month" : 1,
                "agent" : {
                    "_id" : 3,
                    "name" : "Pete"
                }
            },
            "total" : 3500
        }, 
        {
            "_id" : {
                "month" : 1,
                "agent" : {
                    "_id" : 1,
                    "name" : "Joe"
                }
            },
            "total" : 26000
        }
    ],
    "ok" : 1
}

- jacoswarts