Mongodb 分组和排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6547441/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mongodb group and sort
提问by Sreeraj
How can I translate the following Sqlquery for Mongo?:
如何翻译以下Sql查询Mongo？：
select a,b,sum(c) csum from coll where active=1 group by a,b order by a
Is there any way to execute a group and sort query with Mongo?
有没有办法用 Mongo 执行组和排序查询？
回答by B.Mr.W.
Inspired by this exampleon mongo's website.
灵感来自mongo 网站上的这个例子。
GENERATE DUMMY DATA:
生成虚拟数据：
> db.stack.insert({a:1,b:1,c:1,active:1})
> db.stack.insert({a:1,b:1,c:2,active:0})
> db.stack.insert({a:1,b:2,c:3,active:1})
> db.stack.insert({a:1,b:2,c:2,active:0})
> db.stack.insert({a:2,b:1,c:3,active:1})
> db.stack.insert({a:2,b:1,c:10,active:1})
> db.stack.insert({a:2,b:2,c:10,active:0})
> db.stack.insert({a:2,b:2,c:5,active:1})
MONGO QUERY:
MONGO 查询：
> db.stack.aggregate(
... {$match:{active:1}},
... {$group:{_id:{a:"$a", b:"$b"}, csum:{$sum:"$c"}}},
... {$sort:{"_id.a":1}})
RESULT:
结果：
{"result" : [
{"_id" : {"a" : 1,"b" : 2},"csum" : 3},
{"_id" : {"a" : 1,"b" : 1},"csum" : 1},
{"_id" : {"a" : 2,"b" : 2},"csum" : 5},
{"_id" : {"a" : 2,"b" : 1},"csum" : 13}
],"ok" : 1}
(NOTE: I reformatted the shell result a bit so it is more readable)
（注意：我重新格式化了 shell 结果，使其更具可读性）
回答by Marius
Using the aggregate framework, you can do the following:
使用聚合框架，您可以执行以下操作：
db.coll.aggregate({
$group: {
_id: "$a",
countA: { $sum: 1},
sumC:{ $sum: "$c"},
},
$sort:{a:1}
});
However, if you have too much data you may get the following error message:
但是，如果您有太多数据，您可能会收到以下错误消息：
{
"errmsg" : "exception: aggregation result exceeds maximum document size (16MB)",
"code" : 16389,
"ok" : 0
}
See more about SQL to Mongo translation here: http://docs.mongodb.org/manual/reference/sql-aggregation-comparison/
在此处查看有关 SQL 到 Mongo 翻译的更多信息：http: //docs.mongodb.org/manual/reference/sql-aggregation-comparison/
回答by Yuri Shatrov
The mongodb aggregation API seems to have changed. Now you would do
mongodb 聚合 API 似乎发生了变化。现在你会做
db.coll.aggregate([
{
$group: {
_id: "$a",
countA: { $sum: 1},
sumC:{ $sum: "$c"},
}
},
{
$sort:{a:1}
}
])
Note the Array syntax for the argument to aggregate(). You'd also add things link $match, $limit etc. as elements of this array.
请注意aggregate() 参数的数组语法。您还可以添加链接 $match、$limit 等作为该数组的元素。
回答by Rusty
I built up a histogram and what I did with version 2.2.2 was:
我建立了一个直方图，我对版本 2.2.2 所做的是：
answer = db.coll.group(...)
db.histo.insert(answer)
db.histo.find().sort({ field: 1 })
At this point, if you don't need it, just db.histo.drop().
此时，如果您不需要它，只需db.histo.drop().
You can also avoid the variable and do:
您还可以避免使用该变量并执行以下操作：
db.histo.insert(db.coll.group(...))
db.histo.ensureIndex({ field: 1 })
回答by Sergey Gavruk
db.coll.group(
{key: { a:true, b:true },
cond: { active:1 },
reduce: function(obj,prev) { prev.csum += obj.c; },
initial: { csum: 0 }
});
You can execute it in MongoDB
你可以在MongoDB中执行
回答by Guy
Until the Aggregation Frameworkis release in MongoDB 2.1, the call for group as in this answer, is rather slow, since it is using the JavaScript part of the DB.
在 MongoDB 2.1 中发布聚合框架之前，本答案中的group 调用速度相当慢，因为它使用的是数据库的 JavaScript 部分。
You can use a faster approach for countinggroups:
您可以使用更快的方法计算组数：
var res = [];
for( var cur_a = db.coll.distinct('a'); cur_a.hasNext(); ) {
var a = cur_a.next();
for( var cur_b = db.coll.distinct('b'); cur_b.hasNext(); ) {
var b = cur_b.next();
res.push({ 'a': a, 'b' : b 'count': db.coll.count({'a':a,'b':b})}
}
}
It will be faster if you have indexes on aand b
如果您在a和b上有索引，速度会更快
db.coll.ensureIndex({'a':1,'b':1})
回答by Chris Neve
Adding to previous answers, if you want to sort on the sum (the result of the aggregate) instead of on the actual column, you can do this:
添加到先前的答案中，如果您想对总和（聚合结果）而不是实际列进行排序，则可以执行以下操作：
db.your_collection.aggregate([
{
$group: {_id: "$your_document_name", count: {$sum: 1}}
},
{
$sort: {"count":-1}
}
])
This would be the equivalent of the following standard-SQL-ish syntax:
这将等效于以下标准 SQL 语法：
select col_a, count(col_a) as b
from table
group by col_a
order by b desc
回答by Carlos Rivero
I want to add the following query, as an example, it may be useful in case of two groupings.
我想添加以下查询，例如，它在两个分组的情况下可能很有用。
Query:
询问：
db.getCollection('orders').aggregate([
{$match:{
tipo: {$regex:"[A-Z]+"}
}
},
{$group:
{
_id:{
codigo:"1",
fechaAlta:{$substr:["$fechaAlta",0,10]},
},
total:{$sum:1}
}
},
{$sort:
{"_id":1}
},
{$group:
{
_id:"$_id.codigo",
fechasAltas:
{
$push:
{
fechaAlta:"$_id.fechaAlta",
total:"$total"
}
},
totalGeneral:{$sum:"$total"}
}
}
]);
Response:
回复：
{
"_id" : "1",
"fechasAltas" : [
{
"fechaAlta" : "1940-01-01",
"total" : 13.0
},
{
"fechaAlta" : "2007-05-14",
"total" : 115.0
},
{
"fechaAlta" : "2008-09-30",
"total" : 58.0
},
.
.
.
],
"totalGeneral" : 50620.0
}
