SQL 到彙總(Aggregation)對應表

彙總管線(aggregation pipeline)允許 MongoDB 提供原生彙總功能,對應 SQL 中許多常見的資料彙總操作。

下表列出了常見的 SQL 彙總術語、功能及概念和相應的 MongoDB 彙總運算元概觀。
SQL 術語、功能及概念 MongoDB 彙總運算元
WHERE $match
GROUP BY $group
HAVING $match
SELECT $project
ORDER BY $sort
LIMIT $limit
SUM() $sum
COUNT() $sum
join 沒有直接對應的運算元,然而,$unwind 運算元允許有些類似的功能,但是與嵌入的欄位在文件內。

範例(Examples)

下表列出了 SQL 彙總語句和相應的 MongoDB 語句的快速參考。在表格中的範例假定有下列條件:
  • SQL 範例假設有兩張資料表 orders 及 order_lineitem,透過 order_lineitem.order_id 及 orders.id 資料行相連結。
  • 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 } ]
    }
    
  • MongoDB 語句中集合內的文件的欄位名稱在彙總操作時以 $ 字元為字首。

計算 orders 所有資料數
  • SQL 架構語句
    SELECT COUNT(*) AS count
    FROM orders
    
  • MongoDB 架構語句
    db.orders.aggregate( [
     { $group: { _id: null,
           count: { $sum: 1 } } }
    ] )
    

加總 orders 的 price 欄位
  • SQL 架構語句
    SELECT SUM(price) AS total
    FROM orders
    
  • MongoDB 架構語句
    db.orders.aggregate( [
     { $group: { _id: null,
           total: { $sum: "$price" } } }
    ] )
    

對不同的 cust_id 加總 price 欄位
  • SQL 架構語句
    SELECT cust_id,
        SUM(price) AS total
    FROM orders
    GROUP BY cust_id
    
  • MongoDB 架構語句
    db.orders.aggregate( [
     { $group: { _id: "$cust_id",
           total: { $sum: "$price" } } }
    ] )
    

對不同的 cust_id 加總 price 欄位,並依照加總排序
  • SQL 架構語句
    SELECT cust_id,
        SUM(price) AS total
    FROM orders
    GROUP BY cust_id
    ORDER BY total
    
  • MongoDB 架構語句
    db.orders.aggregate( [
     { $group: { _id: "$cust_id",
           total: { $sum: "$price" } } },
     { $sort: { total: 1 } }
    ] )
    

對不同的 cust_id 及 ord_date 分組,加總 price 欄位
  • SQL 架構語句
    SELECT cust_id,
        ord_date,
        SUM(price) AS total
    FROM orders
    GROUP BY cust_id, ord_date
    
  • MongoDB 架構語句
    db.orders.aggregate( [
     { $group: { _id: { cust_id: "$cust_id",
               ord_date: "$ord_date" },
           total: { $sum: "$price" } } }
    ] )
    

對於 cust_id 的多筆資料,返回 cust_id 和相應的資料數
  • SQL 架構語句
    SELECT cust_id, count()
    FROM orders
    GROUP BY cust_id
    HAVING count() > 1
    
  • MongoDB 架構語句
    db.orders.aggregate( [
     { $group: { _id: "$cust_id",
           count: { $sum: 1 } } },
     { $match: { count: { $gt: 1 } } }
    ] )
    

對不同的 cust_id 及 ord_date 分組,加總 price 欄位,並只返回總和大於 250 的資料
  • SQL 架構語句
    SELECT cust_id,
        ord_date,
        SUM(price) AS total
    FROM orders
    GROUP BY cust_id, ord_date
    HAVING total > 250
    
  • MongoDB 架構語句
    db.orders.aggregate( [
     { $group: { _id: { cust_id: "$cust_id",
               ord_date: "$ord_date" },
           total: { $sum: "$price" } } },
     { $match: { total: { $gt: 250 } } }
    ] )
    

對 status 為 A 的不同的 cust_id 加總 price 欄位
  • SQL 架構語句
    SELECT cust_id,
        SUM(price) as total
    FROM orders
    WHERE status = 'A'
    GROUP BY cust_id
    
  • MongoDB 架構語句
    db.orders.aggregate( [
     { $match: { status: 'A' } },
     { $group: { _id: "$cust_id",
           total: { $sum: "$price" } } }
    ] )
    

對 status 為 A 的不同的 cust_id 加總 price 欄位,並只返回總和大於 250 的資料
  • SQL 架構語句
    SELECT cust_id,
        SUM(price) as total
    FROM orders
    WHERE status = 'A'
    GROUP BY cust_id
    HAVING total > 250
    
  • MongoDB 架構語句
    db.orders.aggregate( [
     { $match: { status: 'A' } },
     { $group: { _id: "$cust_id",
           total: { $sum: "$price" } } },
     { $match: { total: { $gt: 250 } } }
    ] )
    

對不同的 cust_id 加總與 orders 相應的 order_lineitem 的 qty 欄位
  • SQL 架構語句
    SELECT cust_id,
        SUM(li.qty) as qty
    FROM orders o,
       order_lineitem li
    WHERE li.order_id = o.id
    GROUP BY cust_id
    
  • MongoDB 架構語句
    db.orders.aggregate( [
     { $unwind: "$items" },
     { $group: { _id: "$cust_id",
           qty: { $sum: "$items.qty" } } }
    ] )
    

計算不同的 cust_id 及 ord_date 分組數量
  • SQL 架構語句
    SELECT COUNT(*)
    FROM (SELECT cust_id, ord_date
       FROM orders
       GROUP BY cust_id, ord_date) as DerivedTable
    
  • MongoDB 架構語句
    db.orders.aggregate( [
     { $group: { id: { cust_id: "$cust_id",
               ord_date: "$ord_date" } } },
     { $group: { id: null, count: { $sum: 1 } } }
    ] )