MongoDB分组怎么用?
官网:https://docs.mongodb.com/manual/reference/operator/aggregation-pipeline/
不指定字段分组
案例1:
db.getCollection('6117Decartes').aggregate(
[
{
$group : {
_id : null,
"count":{$sum:} }
}
],
{
allowDiskUse: true
}
);
要注意的是,字段可被统计才行,否则会报错,看个例子。
代码 InstitutionID 是非统计字段
db.getCollection('6117Decartes').aggregate(
[
{
$group : {
_id : null,
"InstitutionID":"$InstitutionID"
}
}
],
{
allowDiskUse: true
}
);
正确的写法是:
db.getCollection('6117Decartes').aggregate(
[
{
$group : {
_id : null,
"InstitutionID":{$max:"$InstitutionID"}
}
}
],
{
allowDiskUse: true
}
);
否则报错如下:
案例2:
db.sales.insert([
{ "_id" : , "item" : "abc", "price" : , "quantity" : , "date" : ISODate("2014-03-01T08:00:00Z") },
{ "_id" : , "item" : "jkl", "price" : , "quantity" : , "date" : ISODate("2014-03-01T09:00:00Z") },
{ "_id" : , "item" : "xyz", "price" : , "quantity" : , "date" : ISODate("2014-03-15T09:00:00Z") },
{ "_id" : , "item" : "xyz", "price" : , "quantity" : , "date" : ISODate("2014-04-04T11:21:39.736Z") },
{ "_id" : , "item" : "abc", "price" : , "quantity" : , "date" : ISODate("2014-04-04T21:23:13.331Z") }
]);
不指定数据分组
db.sales.aggregate(
[
{
$group : {
_id : null,
minPrice:{$min:"$price"}, //最小值
maxPrice:{$max:"$price"}, //最大值
avgPrice:{ $avg: "$price" }, //平均值
totalPrice: { $sum: { $multiply: [ "$price", "$quantity" ] } }, //总价 $sum(price * quantity)
lastItem:{$last:"$item"}, //取最后一条
count: { $sum: } // 总条目数
}
}
]
);
多字段分组
db.getCollection('6117Decartes').aggregate(
[
{
$group : {
_id : {"InstitutionID":"$InstitutionID","logtype":"$logtype"},
"总条目数":{$sum:} }
}
],
{
allowDiskUse: true
}
);
案例2:
db.sales.aggregate(
[
{
$group : {
_id : {"商品":"$item", "价格":"$price"},
"总条目数":{$sum:} }
}
]
);
将 $push 聚合分组指定到列结构到数组中
db.books.insert([
{ "_id" : , "title" : "The Banquet", "author" : "Dante", "copies" : },
{ "_id" : , "title" : "Divine Comedy", "author" : "Dante", "copies" : },
{ "_id" : , "title" : "Eclogues", "author" : "Dante", "copies" : },
{ "_id" : , "title" : "The Odyssey", "author" : "Homer", "copies" : },
{ "_id" : , "title" : "Iliad", "author" : "Homer", "copies" : }
]);
db.books.aggregate(
[
{ $group : { _id : "$author", books: { $push: "$title" } } }
]
);
可能出现的问题:分组内存使用超过限制时错误
{
"message" : "Exceeded memory limit for $group, but didn't allow external sort. Pass allowDiskUse:true to opt in.",
"stack" : "MongoError: Exceeded memory limit for $group, but didn't allow external sort. Pass allowDiskUse:true to opt in." +
"at queryCallback (/tmp/.mount_nosqlbO7RhZG/app/resources/app.asar/node_modules/mongodb-core/lib/cursor.js:223:25)" +
"at /tmp/.mount_nosqlbO7RhZG/app/resources/app.asar/node_modules/mongodb-core/lib/connection/pool.js:541:18" +
"at _combinedTickCallback (internal/process/next_tick.js:131:7)" +
"at process._tickCallback (internal/process/next_tick.js:180:9)",
"name" : "MongoError",
"ok" : ,
"errmsg" : "Exceeded memory limit for $group, but didn't allow external sort. Pass allowDiskUse:true to opt in.",
"code" : ,
"codeName" : "Location16945"
}
怎么解决?
db.getCollection('6117Decartes').aggregate(
[
{
$group : {
_id : {"InstitutionID":"$InstitutionID","logtype":"$logtype"},
"总条目数":{$sum:} }
}
],
{
allowDiskUse: true
}
);