mongodb 聚合操作

本页内容

聚合管道允许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

有关所有聚合管道和表达式操作符的列表，请参见聚合管道快速参考。

另请参阅

SQL to MongoDB Mapping Chart

示例

下表快速引用了SQL聚合语句和相应的MongoDB语句。下表中的例子假设了以下条件:

  • SQL示例假设两个表orders和order_lineitem，两个表通过order_lineitem.order_id和orders.id联接。

  • MongoDB示例假设一个集合订单，其中包含以下原型的文档:

    {
  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" }
     }
   }
] )
For each unique cust_id, sum the price field. 
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. 
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: {
               month: { $month: "$ord_date" },
               day: { $dayOfMonth: "$ord_date" },
               year: { $year: "$ord_date"}
           }
        },
        total: { $sum: "$price" }
     }
   }
] )
For each unique cust_id, ord_date grouping, sum the price field. 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. 
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: {
              month: { $month: "$ord_date" },
              day: { $dayOfMonth: "$ord_date" },
              year: { $year: "$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. 
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. 
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. 
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. 
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: {
              month: { $month: "$ord_date" },
              day: { $dayOfMonth: "$ord_date" },
              year: { $year: "$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.

另请参阅


原文：https://docs.mongodb.com/manual/reference/sql-aggregation-comparison/

© 2024 21doc.net V1.3.1.190819