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.有关所有聚合管道和表达式运算符的列表，请参阅聚合管道快速参考。

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:表中的示例假设以下条件：

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. 对于每个唯一的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. 对于每个唯一的cust_id、ord_date分组，对price字段求和，并仅在总和大于250时返回。Excludes the time portion of the date.不包括日期的时间部分。
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，将与订单关联的相应行项目qyt字段相加。
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.不包括日期的时间部分。

See also参阅