SQL与Mongodb聚合的对应关系(举例说明)
SQL中的聚合函数和Mongodb中的管道相互对应的关系:
WHERE $match GROUP BY $group HAVING $match SELECT $project ORDER BY $sort LIMIT $limit SUM() $sum COUNT() $sum join $lookup
例子:
先创建文档,填充数据
/* 0 */
{
"_id" : ObjectId("5812b447311bb4272016496a"),
"cust_id" : "abc123",
"ord_date" : ISODate("2012-11-02T17:04:11.102Z"),
"status" : "A",
"price" : 50,
"items" : [{
"sku" : "xxx",
"qty" : 25,
"price" : 1
}, {
"sku" : "yyy",
"qty" : 25,
"price" : 1
}]
}
/* 1 */
{
"_id" : ObjectId("58131494311bb418b058fcba"),
"cust_id" : "a",
"ord_date" : ISODate("2012-11-02T17:04:11.102Z"),
"status" : "B",
"price" : 70,
"items" : [{
"sku" : "xxx",
"qty" : 25,
"price" : 1
}, {
"sku" : "yyy",
"qty" : 25,
"price" : 1
}]
}
/* 2 */
{
"_id" : ObjectId("581314b6311bb418b058fcbb"),
"cust_id" : "ab",
"ord_date" : ISODate("2012-11-02T17:04:11.102Z"),
"status" : "E",
"price" : 60,
"items" : [{
"sku" : "xxx",
"qty" : 55,
"price" : 1
}, {
"sku" : "yyy",
"qty" : 25,
"price" : 1
}]
}
例1:
SQL：
SELECT COUNT(*) AS count FROM orders
Mongodb：
db.orders.aggregate([
{
$group:{
_id:null,
count:{$sum:1}
}
}
])
例2：
SQL：
SELECT SUM(price) AS total FROM orders
Mongodb：
db.orders.aggregate(
[
{
$group:
{
_id:null,
total:{$sum:"$price"}
}
}
])
例3：
SQL:
SELECT cust_id,SUM(price) AS total FROM orders GROUP BY cust_id
Mongodb:
db.orders.aggregate([
{
$group:
{
_id:"$cust_id",
total:
{
$sum:"$price"
}
}
},
{
$sort:
{
total:1
}
}
])
例4：
SQL：
SELECT cust_id, ord_date,SUM(price) AS total FROM orders GROUP BY cust_id, ord_date
Mongodb：
db.orders.aggregate([
{
$group:
{
_id:
{
cust_id:"$cust_id",
ord_date:
{
month:{$month:"$ord_date"},
day:{$dayOfMonth:"$ord_date"},
year:{$year:"$ord_date"}
}
},
total:{$sum:"$price"}
}
}
])
例5:
SQL:
SELECT cust_id,count(*) FROM orders GROUP BY cust_id HAVING count(*) > 1
Mongodb：
db.orders.aggregate([
{
$group:{_id:"$cust_id",
count:{$sum:1}
}
},
{$match:{count:{$gt:1}}}
])
例6:
SQL:
SELECT cust_id,ord_date,SUM(price) AS total FROM orders GROUP BY cust_id,ord_date HAVING total > 250
Mongodb：
db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: {
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
},
total: { $sum: "$price" }
}
},
{ $match: { total: { $gt: 250 } } }
] )
例6：
SQL：
SELECT cust_id,SUM(price) as total FROM orders WHERE status = 'A' GROUP BY cust_id
Mongodb:
db.orders.aggregate([
{$match:{status:'A'}},
{$group:{_id:"$cust_id",total:{$sum:"$price"}}}
])
例7:
SQL:
SELECT cust_id,SUM(price) as total FROM orders WHERE status = 'A' GROUP BY cust_id HAVING total > 250
Mongodb:
db.orders.aggregate([
{ $match: { status: 'A' } },
{$group:{_id:"$cust_id",total:{$sum:"$price"}}},
{$match:{total:{$gt:250}}}
])
例8:
SQL:
SELECT cust_id,SUM(li.qty) as qty FROM orders o, order_lineitem li WHERE li.order_id = o.id GROUP BY cust_id
Mongodb：
$unwind的作用是将文档中的某一个数组类型字段拆分成多条，每条包含数组中的一个值
假如我们的需求是统计每个items出现的次数
这个时候就需要用到先将$unwind items拆分，然后根据具体的items来做分组统计
db.orders.aggregate([
{$unwind:"$items"},
{$group:{_id:"$cust_id",qty:{$sum:"$items.qty"}}}
])
例9:
SQL:
SELECT COUNT(*) FROM (SELECT cust_id,ord_date FROM orders GROUP BY cust_id,ord_date) as DerivedTable
Mongodb：
db.orders.aggregate([
{$group:
{
_id:{
cust_id:"$cust_id",
ord_date:{
month:{$month:"$ord_date"},
day:{$dayOfMonth:"$ord_date"},
year:{$year:"$ord_date"}
}
}
}
},
{
$group:{
_id:null,
count:{$sum:1}
}
}
])
格式要注意
db.orders.aggregate([
{$match:{}}, ----where
{$group:{ ----group
_id：排序字段
total:{聚合函数}
}},
{$match:{}} ----having
])
