MongoDB 之 aggregate $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"
}
           

按照  campaign_id 與 campaign_name  分組，并查詢出每個分組下的記錄條數 及  subscriber_id  不同記錄的個數

關系型資料庫SQL示例：

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

在MongoDB下就存在兩種組合：1） campaign_id, campaign_name, subscriber_id  三個相同的分為一組，

                                                  2） campaign_id, campaign_name  兩個相同，subscriber_id 不同分為一組，

最後通過這兩種分組查詢出按照  campaign_id 與 campaign_name  分組，subscriber_id 不同記錄的個數

MongoDB示例：

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

文檔結果：

第一個 group：

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

第二個 group：

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

至此，我們已經查詢出一共有 3 條記錄，subscriber_id 有兩種不同的值

reference： Mongodb中Aggregation特性

關注公衆号，分享幹貨，讨論技術，你的支援是我最大的動力！！！