SQL到聚合的映射表

进入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聚合语句和相应的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分组并返回计数。 排除日期的时间部分。

进入MongoDB中文手册（4.2版本）目录