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 ExampleSQL示例MongoDB Example实例Description描述
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. 对于每个唯一的cust_id、ord_date分组，对price字段求和。Excludes the time portion of the date.不包括日期的时间部分。 
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分组，对价格字段求和，并仅在总和大于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. Excludes the time portion of the date.计算不同cust_id、ord_date分组的数量。不包括日期的时间部分。
Tip提示
See also: 参阅：
←  Variables in Aggregation ExpressionsData Models →