mysql与mongodb聚合对比
mysql | mongodb |
---|---|
where | $match |
group by | $group |
having | $match |
select | $project |
order by | $sort |
limit | $limit |
sum() | $sum |
count() | $sum |
join | $lookup |
常用例子
数据库文档内容
/* 1 */
{
“_id” : ObjectId(“512bc95fe835e68f199c8686”),
“author” : “dave”,
“score” : 80.0,
“views” : 100.0
}
/* 2 */
{
“_id” : ObjectId(“512bc962e835e68f199c8687”),
“author” : “dave”,
“score” : 85.0,
“views” : 521.0
}
/* 3 */
{
“_id” : ObjectId(“55f5a192d4bede9ac365b257”),
“author” : “ahn”,
“score” : 60.0,
“views” : 1000.0
}
/* 4 */
{
“_id” : ObjectId(“55f5a192d4bede9ac365b258”),
“author” : “li”,
“score” : 55.0,
“views” : 5000.0
}
/* 5 */
{
“_id” : ObjectId(“55f5a1d3d4bede9ac365b259”),
“author” : “annT”,
“score” : 60.0,
“views” : 50.0
}
/* 6 */
{
“_id” : ObjectId(“55f5a1d3d4bede9ac365b25a”),
“author” : “li”,
“score” : 94.0,
“views” : 999.0
}
/* 7 */
{
“_id” : ObjectId(“55f5a1d3d4bede9ac365b25b”),
“author” : “ty”,
“score” : 95.0,
“views” : 1000.0
}
1.一次性查出指定的多个作者名下的分数和查看数,每个作者的记录数不超过100条
js:
// An highlighted block
db.getCollection('articles').aggregate([
{$match:{author:{$in:["dave", "ahn", "li", "annT", "ty"]}}},
{$group:{"_id":"$author",authorInfo:{$push:{"score":"$score","views":"$views"}}}},
{$project:{"author_info":{$slice:["$authorInfo", 100]}}},
])
查询结果:
/* 1 */
{
“_id” : “ty”,
“author_info” : [
{
“score” : 95.0,
“views” : 1000.0
}
]
}
/* 2 */
{
“_id” : “dave”,
“author_info” : [
{
“score” : 80.0,
“views” : 100.0
},
{
“score” : 85.0,
“views” : 521.0
}
]
}
/* 3 */
{
“_id” : “ahn”,
“author_info” : [
{
“score” : 60.0,
“views” : 1000.0
}
]
}
/* 4 */
{
“_id” : “li”,
“author_info” : [
{
“score” : 55.0,
“views” : 5000.0
},
{
“score” : 94.0,
“views” : 999.0
}
]
}
/* 5 */
{
“_id” : “annT”,
“author_info” : [
{
“score” : 60.0,
“views” : 50.0
}
]
}
“$group”
$group使用时
语法
// An highlighted block
{ $group: { _id: <expression>, <field1>: { <accumulator1> : <expression1> }, ... } }
参数以及用法说明
1. _id字段必须,值可以为null
2. accumulator1必须要有,分组了之后,必须要对分组的记录做些什么(不然只会查到_id这一个),简单的统计个数、平均数’,复杂一点的用$push把想要的数据放到数组,如上所示。