MongoDB聚合查询以及和MySQL类比总结

可能大家对mysql的查询都比较熟悉,对mongodb的查询不是太熟练,为了便于理解,先将常见的mongo的聚合操作和mysql的查询做下类比。

SQL 操作/函数   mongodb聚合操作
where$match
group by$group
having$match
select$project
order by$sort
limit $limit
sum()$sum
count()$sum
join$lookup  (v3.2 新增)

假设有一张orders表,里面有数据

1.统计orders表所有记录

mongodb查询mysql查询
db.orders.aggregate( [
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] )
SELECT
COUNT(*) AS count
FROM orders

2.对orders表计算所有price求和

mongodb查询mysql查询
db.orders.aggregate( [
{
$group: {
_id: null,
total: { $sum: “$price” }
}
}
] )
SELECT
SUM(price) AS total
FROM orders

3.对每一个唯一的cust_id, 计算price总和

mongodb查询mysql查询
db.orders.aggregate( [
{
$group: {
_id: “$cust_id”,
total: { $sum: “$price” }
}
}
] )
SELECT cust_id,
SUM(price) AS total
FROM orders
GROUP BY cust_id

4.对每一个唯一对cust_id和ord_date分组,计算price总和,不包括日期的时间部分

mongodb查询mysql查询
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” }
}
}
] )
SELECT cust_id,
ord_date,
SUM(price) AS total
FROM orders
GROUP BY cust_id,
ord_date

0

发表评论

邮箱地址不会被公开。