聚合查詢 · laravel5

[TOC] # mongo與mysql聚合類比 ![](https://box.kancloud.cn/faf3323ae66292c77226d250b9c00ba4_287x466.png) 下面舉了一些常用的mongo聚合例子和mysql對比，假設有一條如下的數據庫記錄（表名：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 } ] } ~~~ **統計orders表所有記錄** ~~~ db.orders.aggregate( [ { $group: { _id: null, count: { $sum: 1 } } } ] ) 類似mysql: SELECT COUNT(*) AS count FROM orders ~~~ **對orders表計算所有price求和** ~~~ db.orders.aggregate( [ { $group: { _id: null, total: { $sum: "$price" } } } ] ) 類似mysql; SELECT SUM(price) AS total FROM orders ~~~ **對每一個唯一的cust\_id, 計算price總和** ~~~ db.orders.aggregate( [ { $group: { _id: "$cust_id", total: { $sum: "$price" } } } ] ) 類似mysql: SELECT cust_id, SUM(price) AS total FROM orders GROUP BY cust_id ~~~ **對每一個唯一對cust\_id和ord\_date分組，計算price總和，不包括日期的時間部分** ~~~ 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" } } } ] ) 類似mysql： SELECT cust_id, ord_date, SUM(price) AS total FROM orders GROUP BY cust_id, ord_date ~~~ **對于有多個記錄的cust\_id，返回cust\_id和對應的數量** ~~~ db.orders.aggregate( [ { $group: { _id: "$cust_id", count: { $sum: 1 } } }, { $match: { count: { $gt: 1 } } } ] ) 類似mysql: SELECT cust_id, count(*) FROM orders GROUP BY cust_id HAVING count(*) > 1 ~~~ **對每個唯一的cust\_id和ord\_date分組，計算價格總和，并只返回price總和大于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 } } } ] ) 類似mysql: SELECT cust_id, ord_date, SUM(price) AS total FROM orders GROUP BY cust_id, ord_date HAVING total > 250 ~~~ **對每個唯一的cust\_id且status=A，計算price總和** ~~~ db.orders.aggregate( [ { $match: { status: 'A' } }, { $group: { _id: "$cust_id", total: { $sum: "$price" } } } ] ) 類似mysql: SELECT cust_id, SUM(price) as total FROM orders WHERE status = 'A' GROUP BY cust_id ~~~ **對每個唯一的cust\_id且status=A，計算price總和并且只返回price總和大于250的記錄** ~~~ db.orders.aggregate( [ { $match: { status: 'A' } }, { $group: { _id: "$cust_id", total: { $sum: "$price" } } }, { $match: { total: { $gt: 250 } } } ] ) 類似mysql: SELECT cust_id, SUM(price) as total FROM orders WHERE status = 'A' GROUP BY cust_id HAVING total > 250 ~~~ **對于每個唯一的cust\_id，將與orders相關聯的相應訂單項order\_lineitem的qty字段進行總計** ~~~ db.orders.aggregate( [ { $unwind: "$items" }, { $group: { _id: "$cust_id", qty: { $sum: "$items.qty" } } } ] ) 類似mysql: SELECT cust_id, SUM(li.qty) as qty FROM orders o, order_lineitem li WHERE li.order_id = o.id GROUP BY cust_id ~~~ **統計不同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"} } } } }, { $group: { _id: null, count: { $sum: 1 } } } ] ) 類似mysql: SELECT COUNT(*) FROM (SELECT cust_id, ord_date FROM orders GROUP BY cust_id, ord_date) as DerivedTable ~~~ # aggregate db.collection.aggregate()是基于數據處理的聚合管道，每個文檔通過一個由多個階段（stage）組成的管道，可以對每個階段的管道進行分組、過濾等功能，然后經過一系列的處理，輸出相應的結果。通過這張圖，可以了解Aggregate處理的過程。 ![](https://box.kancloud.cn/1db1bf7c63d0edd4b02cae13043851fe_701x507.png) 1. db.collection.aggregate() 可以用多個構件創建一個管道，對于一連串的文檔進行處理。這些構件包括：篩選操作的match、映射操作的project、分組操作的group、排序操作的sort、限制操作的limit、和跳過操作的skip。 2. db.collection.aggregate()使用了MongoDB內置的原生操作，聚合效率非常高,支持類似于SQL Group By操作的功能，而不再需要用戶編寫自定義的JavaScript例程。 3. 每個階段管道限制為100MB的內存。如果一個節點管道超過這個極限,MongoDB將產生一個錯誤。為了能夠在處理大型數據集,可以設置allowDiskUse為true來在聚合管道節點把數據寫入臨時文件。這樣就可以解決100MB的內存的限制。 4. db.collection.aggregate()可以作用在分片集合，但結果不能輸在分片集合，MapReduce可以作用在分片集合，結果也可以輸在分片集合。 5. db.collection.aggregate()方法可以返回一個指針（cursor），數據放在內存中，直接操作。跟Mongo shell 一樣指針操作。 6. db.collection.aggregate()輸出的結果只能保存在一個文檔中，BSON Document大小限制為16M。可以通過返回指針解決，版本2.6中后面：DB.collect.aggregate()方法返回一個指針，可以返回任何結果集的大小。