Database Manual / Aggregation Operations / Reference

SQL to Aggregation Mapping ChartSQL到聚合映射图

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
UNION ALL$unionWith

For a list of all aggregation pipeline and expression operators, see:有关所有聚合管道和表达式运算符的列表，请参阅：

Tip

SQL to MongoDB Mapping ChartSQL到MongoDB映射图

Examples示例

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

  • 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_linetemorder_id和ordersid列连接。

  • 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 ExampleMongoDB示例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. 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. Excludes the time portion of the date.统计不同cust_id、ord_date分组的数量。不包括日期的时间部分。

Tip