进入MongoDB中文手册(4.2版本)目录
聚合管道允许的MongoDB提供原生的聚合功能,对应于许多常见的使用SQL继续的数据聚合操作。
下表概述了常见的SQL聚合术语,函数和概念以及相应的MongoDB 聚合运算符:
SQL术语,函数和概念 | MongoDB聚合运算符 |
---|---|
WHERE | $match |
GROUP BY | $group |
HAVING | $match |
SELECT | $project |
ORDER BY | $sort |
LIMIT | $limit |
SUM() | $sum |
COUNT() | $sum $sortByCount |
join | $lookup |
SELECT INTO NEW_TABLE | $out |
MERGE INTO TABLE | $merge (MongoDB 4.2开始可用) |
有关所有聚合管道和表达式运算符的列表,请参见《 聚合管道快速参考》。
也可以看看:
示例
下表提供了SQL聚合语句和相应的MongoDB语句的快速参考。表格中的示例假定以下条件:
- SQL示例假设有两个表,orders和order_lineitem通过order_lineitem.order_id和orders.id列进行连接。
- MongoDB示例假定一个orders集合包含以下原型的文档:
{
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 } ]
}
SQL示例 | MongoDB示例 | 描述 |
---|---|---|
SELECT COUNT(*) AS count FROM orders | db.orders.aggregate( [ { $group: { _id: null, count: { $sum: 1 } } } ] ) | 计算来自orders的所有记录 |
SELECT SUM(price) AS total FROM orders | db.orders.aggregate( [ { $group: { _id: null, total: { $sum: “$price” } } } ] ) | 对orders中的price字段求和 |
SELECT cust_id, SUM(price) AS total FROM orders GROUP BY cust_id | db.orders.aggregate( [ { $group: { _id: “$cust_id”, total: { $sum: “$price” } } } ] ) | 对于每个唯一的cust_id, 对price字段求和。 |
SELECT cust_id, SUM(price) AS total FROM orders GROUP BY cust_id ORDER BY total | db.orders.aggregate( [ { $group: { _id: “$cust_id”, total: { $sum: “$price” } } }, { $sort: { total: 1 } } ] ) | 对于每个唯一字段cust_id, 对price字段求和,结果按和排序。 |
SELECT cust_id, ord_date, SUM(price) AS total FROM orders GROUP BY cust_id, ord_date | db.orders.aggregate( [ { $group: { _id: { cust_id: $cust_id", ord_date: { $dateToString: { format: “%Y-%m-%d”, date: “$ord_date” }} }, total: { $sum: “$price” } } ] ) | 基于cust_id和ord_date分组, 对price字段求和。 并排除日期的时间部分。 |
SELECT cust_id, count(*), FROM orders GROUP BY cust_id HAVING count(*) > 1 | db.orders.aggregate( [ { $group: { _id: “$cust_id”, count: { $sum: 1 } } }, { $match: { count: { $gt: 1 } } } ] ) | 对于具有多个记录的cust_id, 返回cust_id和对应的记录计数。 |
SELECT cust_id, ord_date, SUM(price) AS total FROM orders GROUP BY cust_id, ord_date HAVING total > 250 | db.orders.aggregate( [ { $group: { _id: { cust_id: “$cust_id”, ord_date: { $dateToString: { format: “%Y-%m-%d”, date: “$ord_date” }} }, total: { $sum: “$price” } } }, { $match: { total: { $gt: 250 } } } ] ) | 基于cust_id和ord_date分组, 对price字段求和,且仅返回其中总和大于250的内容, 且不包括的日期的时间部分。 |
SELECT cust_id, SUM(price) as total FROM orders WHERE status = 'A’ GROUP BY cust_id | db.orders.aggregate( [ { $match: { status: ‘A’ } }, { $group: { _id: “$cust_id”, total: { $sum: “$price” } } } ] ) | 对于每个唯一的具有状态A的cust_id , 对price字段求和。 |
SELECT cust_id, SUM(price) as total FROM orders WHERE status = 'A’ GROUP BY cust_id HAVING total > 250 | db.orders.aggregate( [ { $match: { status: ‘A’ } }, { $group: { _id: “$cust_id”, total: {\ $sum: “$price” } } }, { $match: { total: { $gt: 250 } } } ] ) | 对于每个具有statusA的唯一的cust_id, 对price字段求和并仅在总和大于250时返回。 |
SELECT cust_id, SUM(li.qty) as qty FROM orders o, order_lineitem li WHERE li.order_id = o.id GROUP BY cust_id | db.orders.aggregate( [ { $unwind: “$items” }, { $group: { _id: “$cust_id”, qty: { $sum: “$items.qty” } } } ] ) | 对于每个唯一的cust_id, 对与订单关联的相应订单项qty字段求和。 |
SELECT COUNT(*) FROM (SELECT cust_id, ord_date FROM orders GROUP BY cust_id, ord_date) as DerivedTable | db.orders.aggregate( [ { $group: { _id: { cust_id: “$cust_id”, ord_date:{$dateToString: { format: “%Y-%m-%d”, date: “$ord_date” }} } } { $group: { _id: null, count: { $sum: 1 } } } ] ) | 基于cust_id,ord_date分组并返回计数。 排除日期的时间部分。 |