MongoDB 聚合 - 统计特定元素的不同数量

Question

我有一个包含以下文档的集合。我想使用聚合来计算里面有多少个客户，但我遇到了一些问题。我可以获得总行数，但无法获得总（独特的）客户数量。

[{
    _id: "n001",
    channel: "Kalipare",
    trans: {
        _id: "trans001",
        customerID: "customerCXLA93",
        customerName: "Kalipare Fried Chicked"
    }
}, {
    _id: "n002",
    channel: "Kalipare",
    trans: {
        _id: "trans002",
        customerID: "customerCXLA93",
        customerName: "Kalipare Fried Chicked"
    }
}, {
    _id: "n003",
    channel: "Kalipare",
    trans: {
        _id: "trans003",
        customerID: "customerPWR293",
        customerName: "Kalipare Papabun"
    }
}, {
    _id: "n004",
    channel: "Kalipare",
    trans: {
        _id: "trans004",
        customerID: "customerPWR293",
        customerName: "Kalipare Papabun"
    }
}, {
    _id: "n005",
    channel: "Tumpakrejo",
    trans: {
        _id: "trans005",
        customerID: "customerPWR293",
        customerName: "Tumpakrejo Big Burger"
    }
}]

这是我的代码。

db.col.aggregate([
    { $group: {
        _id: "$channel",
        totalRow: { $sum: 1 }
    } }
])

我该如何统计独立客户并生成下列数据。

[{
    _id: "Kalipare",
    totalRow: 4,
    totalCustomer: 2
}, {
    _id: "Tumpakrejo",
    totalRow: 1,
    totalCustomer: 1
}]

- novalagung

Answer 1

要获取唯一客户数量，需要使用 $addToSet 运算符在组管道中创建唯一客户 ID 集合。一旦获取了数组，请在 $project 管道中使用 $size 运算符获取长度，从而得到唯一计数。请运行以下聚合管道以获得所需结果：

db.col.aggregate([
    {
        "$group": {
            "_id": "$channel",
            "totalRows": { "$sum": 1 },
            "totalCustomer": { "$addToSet": "$trans.customerID" }
        }
    },
    {
        "$project": {
            "totalRows": 1,
            "totalCustomers": { "$size": "$totalCustomer" }
        }
    }
])

输出:

{
    "result" : [ 
        {
            "_id" : "Tumpakrejo",
            "totalRows" : 1,
            "totalCustomers" : 1
        }, 
        {
            "_id" : "Kalipare",
            "totalRows" : 4,
            "totalCustomers" : 2
        }
    ],
    "ok" : 1
}

- chridam