MongoDB复杂查询计数分组函数

Question

我有一个名为“my_emails”的集合，其中存储了电子邮件地址：

[
    { email:"[e-mail]"},
    { email:"[e-mail]"},
    { email:"[e-mail]"},
]

我试图获取使用最多的前10个主机名...

[
{host: "gmail.com", count: 1000},
{host: "yahoo.com", count: 989}, ...
]

如果我有MySQL，我会执行以下查询：

SELECT substr(email,locate('@',email)+1,255) AS host,count(1) AS count
FROM my_emails 
WHERE email like '%@%' 
GROUP BY substr(email,locate('@',email)+1,255)
ORDER BY count(1) DESC 
LIMIT 10

我该如何处理mongodb？我尝试了以下代码但没有结果：

db.my_emails.aggregate([ { $group : {_id : "$host", count : { $sum : 1 }}}]);

我不知道如何在不添加新属性到我的记录中的情况下创建$host值。

- sly63

---

如果您提供带有期望结果的示例文档，您很可能会得到更好的答案。此外，您应该避免使用类似“MongoDB vs SQL query”的标题。 - styvane

Answer 1

MongoDB没有提供像locate这样的操作符，但是你可以使用.mapReduce来实现：

db.collection.mapReduce(
    function() {
        emit(this.email.substr(this.email.indexOf('@') + 1), 1);  
    }, 
    function(host, count) { 
        return Array.sum(count) ; }, 
    { out: "hosts" } 
)

然后db.hosts.find().sort({'value': -1}).limit(10)返回前十个主机名：

{ "_id" : "yahoo.com", "value" : 2 }
{ "_id" : "gmail.com", "value" : 1 }

- styvane

Answer 2

一个替代方案是通过在模式中引入另一个字段来修改数据结构，该字段仅包含电子邮件地址的域值。可以使用Bulk API操作进行批量更新，这些操作提供更好的写入响应，即有关更新期间实际发生情况的有用信息。

var bulk = db.my_emails.initializeUnorderedBulkOp(),    
    count = 0;

db.my_emails.find().forEach(function(doc) {
    var domain = doc.email.replace(/.*@/, ""),
        update = { domain: domain };
    bulk.find({ "_id": doc._id }).updateOne({
        "$set": update
    })
    count++;
    if (count % 1000 == 0) {            
        bulk.execute();
        bulk = db.my_emails.initializeUnorderedBulkOp();
    }
})

if (count % 1000 != 0) { bulk.execute(); } 

样本的批量更新响应：

BulkWriteResult({
    "writeErrors" : [ ],
    "writeConcernErrors" : [ ],
    "nInserted" : 0,
    "nUpserted" : 0,
    "nMatched" : 3,
    "nModified" : 3,
    "nRemoved" : 0,
    "upserted" : [ ]
})

在此更新后，对集合 db.my_emails.find().pretty() 的查询将产生以下结果：

{
    "_id" : ObjectId("561618af645a64b1a70af2c5"),
    "email" : "[e-mail]",
    "domain" : "gmail.com"
}
{
    "_id" : ObjectId("561618af645a64b1a70af2c6"),
    "email" : "[e-mail]",
    "domain" : "yahoo.com"
}
{
    "_id" : ObjectId("561618af645a64b1a70af2c7"),
    "email" : "[e-mail]",
    "domain" : "yahoo.com"
}

现在，拥有域字段将使聚合框架更容易通过$sum操作符在$group管道中返回主机计数。以下管道操作将返回所需的结果：

db.my_emails.aggregate([
    {
        "$group": {
            "_id": "$domain",
            "count": { "$sum": 1 }
        }
    }
])

输出:

{ "_id" : "yahoo.com", "count" : 2 }
{ "_id" : "gmail.com", "count" : 1 }

- chridam