在同一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(distinct Subscriber_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}
              }}
])

但我希望在相同结果中计数(subscriber_id)，计数(distinct Subscriber_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屋！