mongodb - 在同一个mongodb查询中选择按计数和不同计数分组

Question

我正在尝试做类似的事情

select campaign_id,campaign_name,count(subscriber_id),count(distinct subscriber_id)
group by campaign_id,campaign_name from campaigns;

这个查询给出的结果除了 count(distinctsubscriber_id)

db.campaigns.aggregate([
    {$match: {subscriber_id: {$ne: null}}},
    {$group: { 
        _id: {campaign_id: "$campaign_id",campaign_name: "$campaign_name"},
        count: {$sum: 1}
    }}
])

以下查询给出的结果除了 count(subscriber_id)

db.campaigns_logs.aggregate([
    {$match : {subscriber_id: {$ne: null}}},
    {$group : { _id: {campaign_id: "$campaign_id",campaign_name: "$campaign_name",subscriber_id: "$subscriber_id"}}},
    {$group : { _id: {campaign_id: "$campaign_id",campaign_name: "$campaign_name"}, 
                count: {$sum: 1}
              }}
])

但我希望 count(subscriber_id),count(distinctsubscriber_id) 在相同的结果中

Answer 1

当你朝着正确的方向前进时，你开始沿着正确的路线思考。改变你的 SQL 思维方式，“distinct”实际上只是用任何一种语言编写 $group 操作的另一种方式。这意味着您有两个组操作在这里发生，并且在聚合管道术语中，两个管道阶段。

只需用简化的文档来可视化:

{
    "campaign_id": "A",
    "campaign_name": "A",
    "subscriber_id": "123"
},
{
    "campaign_id": "A",
    "campaign_name": "A",
    "subscriber_id": "123"
},
{
    "campaign_id": "A",
    "campaign_name": "A",
    "subscriber_id": "456"
}

按理说，对于给定的“广告系列”组合，总计数和“不同”计数分别为“3”和“2”。因此，合乎逻辑的做法是首先将所有这些“subscriber_id”值“分组”并保留每个值的出现次数，然后在考虑“管道”时，“总计”每个“事件”的这些计数，然后只计算“distinct"出现为单独的数字:

db.campaigns.aggregate([
    { "$match": { "subscriber_id": { "$ne": null }}},

    // Count all occurrences
    { "$group": {
        "_id": {
            "campaign_id": "$campaign_id",
            "campaign_name": "$campaign_name",
            "subscriber_id": "$subscriber_id"
        },
        "count": { "$sum": 1 }
    }},

    // Sum all occurrences and count distinct
    { "$group": {
        "_id": {
            "campaign_id": "$_id.campaign_id",
            "campaign_name": "$_id.campaign_name"
        },
        "totalCount": { "$sum": "$count" },
        "distinctCount": { "$sum": 1 }
    }}
])

在第一个“组”之后，输出文档可以这样可视化:

{ 
    "_id" : { 
        "campaign_id" : "A", 
        "campaign_name" : "A", 
        "subscriber_id" : "456"
    }, 
    "count" : 1 
}
{ 
    "_id" : { 
        "campaign_id" : "A", 
        "campaign_name" : "A", 
        "subscriber_id" : "123"
    }, 
    "count" : 2
}

因此，从样本中的“三个”文档中，“2”属于一个不同的值，而“1”属于另一个。这仍然可以与 $sum 相加，以获得您在下一个阶段所做的全部匹配文档，以及最终结果:

{ 
    "_id" : { 
        "campaign_id" : "A", 
        "campaign_name" : "A"
    },
    "totalCount" : 3,
    "distinctCount" : 2
}

聚合管道的一个很好的类比是 unix 管道“|”运算符，它允许“链接”操作，因此您可以将一个命令的输出传递到下一个命令的输入，依此类推。开始以这种方式考虑您的处理需求将帮助您更好地了解聚合管道的操作。