[MongoDB] 用aggregate做到大部分Select功能

在MongoDB中，要做到像之前SQL中一樣的查詢，簡單的還可以，複雜的可能就比較要花時間了，研究了一下後，找到MongoDB內有Aggregation Framework可以做到很多想用的操作，SQL to Aggregation Mapping Chart這篇文章中有很多範例了，基本上SQL中的WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, SUM, COUNT等操作，用aggregate都有方法代替，文章中已經有很多範例了，我再多寫幾個有用到的例子，這樣大部分別太複雜的查詢應該都足夠涵蓋了。

SQL

Mongo

SELECT MAX(price) AS 'max', 
       AVG(price) AS 'avg', 
       MIN(price) AS 'min' 
FROM dbo.orders
WHERE ord_date>='2013/10/1' 
      AND ord_date<'2013/11/1'

db.orders.aggregate([
  {$match: { ord_date:{  
    $gte:new Date('2013/10/1'),  
    $lt:new Date('2013/11/1')}}},
  {$group: { _id:null,
    max:{$max:'$price'},
    avg:{$avg:'$price'},
    min:{$min:'$price'}}}
])

SELECT TOP 20 
       cust_id, 
       status, 
       count(1) AS 'count' 
FROM dbo.orders
WHERE ord_date>='2013/10/1' 
      AND ord_date<'2013/11/1'
GROUP BY cust_id, status
ORDER BY 'count' DESC

db.orders.aggregate([
  {$match:{act:act,
    ord_date:{$gte:new Date('2013/10/1'),
      $lt:new Date('2013/11/1')}}},
  {$group:{
    _id:{cust_id:'$cust_id',
      status:'$status'},
    count:{$sum:1}}},
  {$sort:{count:-1}},
  {$limit:20}
])

SELECT status AS '_id', 
       sum(price) AS 'total' 
FROM dbo.orders
WHERE ord_date>='2013/10/1' 
      AND ord_date<'2013/11/1'
GROUP BY status

db.orders.aggregate([
  {$match: {
    ord_date:{$gte:new Date('2013/10/1'),
    $lt:new Date('2013/11/1')}}},
  {$group: {_id: '$status', 
    total: {$sum: '$price'}}}
])

其他範例在MongoDB的官方文件範例上都差不多有，我就不重複了，至於要用Join的方式我還沒試過，而更複雜的查詢就要用MapReduce了。