在同一个mongodb查询中选择按计数和不同计数分组 [英] Select Group by count and distinct count in same mongodb query

问题描述

我正在尝试做类似的事情

I am trying to do something like

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

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

This query giving results except count(distinct subscriber_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) 之外的结果

This following query giving results except 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)

but I want count(subscriber_id),count(distinct subscriber_id) in the same result

推荐答案

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

You are beginning to think along the right lines here as you were headed in the right direction. Changing your SQL mindset, "distinct" is really just another way of writing a $group operation in either language. That means you have two group operations happening here and, in aggregation pipeline terms, two pipeline stages.

仅使用简化的文档进行可视化:

Just with simplified documents to visualize:

{
    "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"值分组"起来并保留每个值的出现次数，然后在考虑管道"的同时，总计"每个活动"的这些计数，然后只计算不同的"作为一个单独的数字出现:

It stands to reason that for the given "campaign" combination the total count and "distinct" count are "3" and "2" respectively. So the logical thing to do is "group" up all of those "subscriber_id" values first and keep the count of occurrences for each, then while thinking "pipeline", "total" those counts per "campaign" and then just count the "distinct" occurrences as a separate number:

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 }
    }}
])

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

After the first "group" the output documents can be visualized like this:

{ 
    "_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 合计为了得到你在下一阶段做的总匹配文件，得到最终结果:

So from the "three" documents in the sample, "2" belong to one distinct value and "1" to another. This can still be totaled with $sum in order to get the total matching documents which you do in the following stage, with the final result:

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

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

A really good analogy for the aggregation pipeline is the unix pipe "|" operator, which allows "chaining" of operations so you can pass the output of one command through to the input of the next, and so on. Starting to think of your processing requirements in that way will help you understand operations with the aggregation pipeline better.

这篇关于在同一个mongodb查询中选择按计数和不同计数分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！