Docs Home → MongoDB Manual

SQL to Aggregation Mapping ChartSQL到聚合映射图

On this page本页内容

The aggregation pipeline allows MongoDB to provide native aggregation capabilities that corresponds to many common data aggregation operations in SQL.聚合管道允许MongoDB提供与SQL中许多常见数据聚合操作相对应的本地聚合功能。

The following table provides an overview of common SQL aggregation terms, functions, and concepts and the corresponding MongoDB aggregation operators:下表概述了常见的SQL聚合术语、函数和概念以及相应的MongoDB聚合运算符：

SQL Terms, Functions, and ConceptsSQL术语、函数和概念MongoDB Aggregation OperatorsMongoDB聚合运算符
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 (Available starting in MongoDB 4.2)（从MongoDB 4.2开始提供）
UNION ALL$unionWith (Available starting in MongoDB 4.4)（从MongoDB 4.4开始提供）

For a list of all aggregation pipeline and expression operators, see Aggregation Pipeline Quick Reference.有关所有聚合管道和表达式运算符的列表，请参阅聚合管道快速参考。

Tip

See also: 另请参阅：

SQL to MongoDB Mapping ChartSQL到MongoDB的映射图

Examples实例

The following table presents a quick reference of SQL aggregation statements and the corresponding MongoDB statements. 下表提供了SQL聚合语句和相应的MongoDB语句的快速参考。The examples in the table assume the following conditions:表中的示例假设了以下条件：

  • The SQL examples assume two tables, orders and order_lineitem that join by the order_lineitem.order_id and the orders.id columns.SQL示例假定了两个表orders和order_lineitem，它们通过order_lineitem.order_id和orders.id列连接。
  • The MongoDB examples assume one collection orders that contain documents of the following prototype: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 ExampleMongoDB ExampleDescription描述
 
SELECT COUNT(*) AS count
FROM orders
 
db.orders.aggregate( [
   {
     $group: {
        _id: null,
        count: { $sum: 1 }
     }
   }
] )
Count all records from orders对orders中的所有记录进行计数
SELECT SUM(price) AS total
FROM orders
 
db.orders.aggregate( [
   {
     $group: {
        _id: null,
        total: { $sum: "$price" }
     }
   }
] )
Sum the price field from orders对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" }
     }
   }
] )
For each unique cust_id, sum the price field.就每个唯一的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 } }
] )
For each unique cust_id, sum the price field, results sorted by sum.对于每个唯一的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" }
     }
   }
] )
For each unique cust_id, ord_date grouping, sum the price field. Excludes the time portion of the date.对于每个唯一的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 } } }
] )
For cust_id with multiple records, return the cust_id and the corresponding record count.对于具有多条记录的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 } } }
] )
For each unique cust_id, ord_date grouping, sum the price field and return only where the sum is greater than 250. Excludes the time portion of the date.对于每个唯一的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" }
     }
   }
] )
For each unique cust_id with status A, sum the price field.对于每个状态为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 } } }
] )
For each unique cust_id with status A, sum the price field and return only where the sum is greater than 250.对于每个状态为A的唯一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" }
     }
   }
] )
For each unique cust_id, sum the corresponding line item qty fields associated with the orders.就每个唯一的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 }
     }
   }
] )
Count the number of distinct cust_id, ord_date groupings. 计算不同的cust_id、ord_date分组的数量。Excludes the time portion of the date.撇除日期的时间部分。
Tip

See also: 另请参阅：