"Mongo" aggregation function

http://blog.csdn.net/miyatang/article/details/20997313

SQL Terms, Functions, and concepts
MongoDB Aggregation Operators
WHERE
$match
GROUP by
$group
Having
$match
SELECT
$project
ORDER by
$sort
LIMIT
$limit
SUM ()
$sum
COUNT ()
$sum
Join
No direct corresponding operator; However, the $unwindoperator allows for somewhat similar functionality, but with fields embedded within the document.
Instance:
[TD]
SQL Example
MongoDB Example
Description
SELECT COUNT (*) as Countfrom orders

Db.orders.aggregate ([{$group: {_id:null, Count: {$sum: 1}}])

Count All Records Fromorders
SELECT SUM (price) as Totalfrom orders

Db.orders.aggregate ([{$group: {_id:null, total: {$sum: "$price"}}])

Sum theprice field from orders, this is very useful, see the official note, said _id is necessary, but did not think can be null,
SELECT cust_id, SUM (price) as Totalfrom Ordersgroup by cust_id

Db.orders.aggregate ([{$group: {_id: "$cust _id", total: {$sum: "$price"}}])

For each uniquecust_id, sum the Pricefield.
SELECT cust_id, SUM (price) as Totalfrom Ordersgroup by Cust_idorder

Db.orders.aggregate ([{$group: {_id: "$cust _id", total: {$sum: "$price"}}, {$sort: {total:1}}])

For each uniquecust_id, sum of the Pricefield, results sorted by sum.
SELECT cust_id, Ord_date, SUM (price) as Totalfrom Ordersgroup by cust_id, ord_date

Db.orders.aggregate ([{$group: {_id: {cust_id: "$cust _id", ord_date: "_date"}, Total: {$sum: "$price"}}])

For each uniquecust_id,ord_dategrouping, sum the Pricefield.
SELECT cust_id, COUNT (*) from Ordersgroup by cust_idhaving count (*) > 1

Db.orders.aggregate ([{$group: {_id: ' $cust _id ', Count: {$sum: 1}}}, {$match: {count: {$gt: 1}}])

For Cust_idwith multiple records, return thecust_id and the corresponding record count.
SELECT cust_id, Ord_date, SUM (price) as Totalfrom Ordersgroup by cust_id, ord_datehaving total > 250

Db.orders.aggregate ([{$group: {_id: {cust_id: "$cust _id", ord_date: "_date"}, Total: {$sum: "$price"}}, {$ Match: {total: {$gt: 250}}])

For each uniquecust_id,ord_dategrouping, the sum of the Pricefield and return only where the sum is greater than 250.
SELECT cust_id, SUM (price) as totalfrom orderswhere status = ' A ' GROUP by cust_id

Db.orders.aggregate ([{$match: {status: ' A '}}, {$group: {_id: "$cust _id", total: {$sum: "$price"}}])

For each uniquecust_id with status A, sum the Pricefield.
SELECT cust_id, SUM (price) as totalfrom orderswhere status = ' A ' GROUP by cust_idhaving total > 250

Db.orders.aggregate ([{$match: {status: ' A '}}, {$group: {_id: ' $cust _id ', total: {$sum: ' $price '}}}, {$match: {total: {$gt: 250}}] )

For each uniquecust_id with status A, the sum of the Pricefield and return only where the sum is greater than 250.
SELECT cust_id, SUM (li.qty) as Qtyfrom orders O, order_lineitem liwhere li.order_id = O.idgroup by cust_id

Db.orders.aggregate ([{$unwind: "$items"}, {$group: {_id: "$cust _id", Qty: {$sum: "$items. Qty"}}])

For each uniquecust_id, the sum of the corresponding line item is qtyfields 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: ' _date '}}}, {$group: {_id:null, Count: {$sum: 1}}])

"Mongo" aggregation function