Mongodb group and sort

问题:

I am using mongod as database with rails 3

How do i execute similar code for the following sql in mongod. Is there any way to execute group and sort in mongod

select a,b,sum(c) csum from coll where active=1 group by a,b order by a

Thanks

回答1:

Inspired by this example on mongo's website.

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:

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

回答2:

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/

回答3:

I built up a histogram and what I did with version 2.2.2 was:

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().

You can also avoid the variable and do:

db.histo.insert(db.coll.group(...))
db.histo.ensureIndex({ field: 1 })

回答4:

The mongodb aggregation API seems to have changed. Now you would do

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.

回答5:

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

回答6:

See

http://www.mongodb.org/display/DOCS/Aggregation http://www.mongodb.org/display/DOCS/SQL+to+Mongo+Mapping+Chart

or combine it using map-reduce.

回答7:

Until the Aggregation Framework is 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.

You can use a faster approach for counting groups:

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 a and b

db.coll.ensureIndex({'a':1,'b':1})