mongo与sql聚合操作对应图

雨点打透心脏的1/2处 2022-06-01 06:38 140阅读 0赞

<table style="color:rgb(69,69,69); font-size:16px"> 
 <tbody> 
 <tr> 
 <td> SQL Terms, Functions, and Concepts </td> 
 <td> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;MongoDB Aggregation Operators </td> 
 </tr> 
 <tr> 
 <td> WHERE </td> 
 <td> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;<a href="http://docs.mongodb.org/manual/reference/operator/aggregation/match/#pipe._S_match" style="background:transparent; color:rgb(202,12,22); margin:0px; padding:0px; outline:0px" rel="nofollow">$match</a> </td> 
 </tr> 
 <tr> 
 <td> GROUP BY </td> 
 <td> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;<a href="http://docs.mongodb.org/manual/reference/operator/aggregation/group/#pipe._S_group" style="background:transparent; color:rgb(202,12,22); margin:0px; padding:0px; outline:0px" rel="nofollow">$group</a> </td> 
 </tr> 
 <tr> 
 <td> HAVING </td> 
 <td> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;<a href="http://docs.mongodb.org/manual/reference/operator/aggregation/match/#pipe._S_match" style="background:transparent; color:rgb(202,12,22); margin:0px; padding:0px; outline:0px" rel="nofollow">$match</a> </td> 
 </tr> 
 <tr> 
 <td> SELECT </td> 
 <td> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;<a href="http://docs.mongodb.org/manual/reference/operator/aggregation/project/#pipe._S_project" style="background:transparent; color:rgb(202,12,22); margin:0px; padding:0px; outline:0px" rel="nofollow">$project</a> </td> 
 </tr> 
 <tr> 
 <td> ORDER BY </td> 
 <td> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;<a href="http://docs.mongodb.org/manual/reference/operator/aggregation/sort/#pipe._S_sort" style="background:transparent; color:rgb(202,12,22); margin:0px; padding:0px; outline:0px" rel="nofollow">$sort</a> </td> 
 </tr> 
 <tr> 
 <td> LIMIT </td> 
 <td> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;<a href="http://docs.mongodb.org/manual/reference/operator/aggregation/limit/#pipe._S_limit" style="background:transparent; color:rgb(202,12,22); margin:0px; padding:0px; outline:0px" rel="nofollow">$limit</a> </td> 
 </tr> 
 <tr> 
 <td> SUM() </td> 
 <td> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;<a href="http://docs.mongodb.org/manual/reference/operator/aggregation/sum/#grp._S_sum" style="background:transparent; color:rgb(202,12,22); margin:0px; padding:0px; outline:0px" rel="nofollow">$sum</a> </td> 
 </tr> 
 <tr> 
 <td> COUNT() </td> 
 <td> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;<a href="http://docs.mongodb.org/manual/reference/operator/aggregation/sum/#grp._S_sum" style="background:transparent; color:rgb(202,12,22); margin:0px; padding:0px; outline:0px" rel="nofollow">$sum</a> </td> 
 </tr> 
 <tr> 
 <td> join </td> 
 <td> No direct corresponding operator;&nbsp;however, the&nbsp;<a href="http://docs.mongodb.org/manual/reference/operator/aggregation/unwind/#pipe._S_unwind" style="background:transparent; color:rgb(202,12,22); margin:0px; padding:0px; outline:0px" rel="nofollow">$unwind</a>operator allows for somewhat similar functionality, but with fields embedded within the document. </td> 
 </tr> 
 </tbody> 
</table>

实例：  
\[td\]

<table style="color:rgb(69,69,69); font-size:16px"> 
 <tbody> 
 <tr> 
 <td> SQL Example </td> 
 <td> MongoDB Example </td> 
 <td> Description </td> 
 </tr> 
 <tr> 
 <td> SELECT&nbsp;COUNT(*)&nbsp;AScountFROM&nbsp;orders </td> 
 <td> db.orders.aggregate( [&nbsp; &nbsp;{ $group: { _id: null,&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;count: { $sum: 1 } } }] ) </td> 
 <td> Count all records fromorders </td> 
 </tr> 
 <tr> 
 <td> SELECT&nbsp;SUM(price)&nbsp;AStotalFROM&nbsp;orders </td> 
 <td> db.orders.aggregate( [&nbsp; &nbsp;{ $group: { _id: null,&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;total: { $sum: "$price" } } }] ) </td> 
 <td> Sum theprice field from orders，这个非常有用，看官方说明，说_ID是必须，但没想到可以为NULL, </td> 
 </tr> 
 <tr> 
 <td> SELECT&nbsp;cust_id,&nbsp; &nbsp;&nbsp; &nbsp;SUM(price)AS&nbsp;totalFROM&nbsp;ordersGROUPBYcust_id </td> 
 <td> db.orders.aggregate( [&nbsp; &nbsp;{ $group: { _id: "$cust_id",&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;total: { $sum: "$price" } } }] ) </td> 
 <td> For each uniquecust_id, sum the pricefield. </td> 
 </tr> 
 <tr> 
 <td> SELECT&nbsp;cust_id,&nbsp; &nbsp;&nbsp; &nbsp;SUM(price)AS&nbsp;totalFROM&nbsp;ordersGROUPBYcust_idORDERBY&nbsp;total </td> 
 <td> db.orders.aggregate( [&nbsp; &nbsp;{ $group: { _id: "$cust_id",&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;total: { $sum: "$price" } } },&nbsp; &nbsp;{ $sort: { total: 1 } }] ) </td> 
 <td> For each uniquecust_id, sum the pricefield, results sorted by sum. </td> 
 </tr> 
 <tr> 
 <td> SELECT&nbsp;cust_id,&nbsp; &nbsp;&nbsp; &nbsp; ord_date,&nbsp; &nbsp;&nbsp; &nbsp;SUM(price)&nbsp;AS&nbsp;totalFROMordersGROUPBY&nbsp;cust_id, ord_date </td> 
 <td> db.orders.aggregate( [&nbsp; &nbsp;{ $group: { _id: { cust_id: "$cust_id",&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; ord_date: "$ord_date" },&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;total: { $sum: "$price" } } }] ) </td> 
 <td> For each uniquecust_id,ord_dategrouping, sum the pricefield. </td> 
 </tr> 
 <tr> 
 <td> SELECT&nbsp;cust_id,count(*)FROMordersGROUPBYcust_idHAVINGcount(*)&gt;&nbsp;1 </td> 
 <td> db.orders.aggregate( [&nbsp; &nbsp;{ $group: { _id: "$cust_id",&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;count: { $sum: 1 } } },&nbsp; &nbsp;{ $match: { count: { $gt: 1 } } }] ) </td> 
 <td> For cust_idwith multiple records, return thecust_id and the corresponding record count. </td> 
 </tr> 
 <tr> 
 <td> SELECT&nbsp;cust_id,&nbsp; &nbsp;&nbsp; &nbsp; ord_date,&nbsp; &nbsp;&nbsp; &nbsp;SUM(price)&nbsp;AS&nbsp;totalFROMordersGROUPBY&nbsp;cust_id, ord_dateHAVING&nbsp;total&gt;&nbsp;250 </td> 
 <td> db.orders.aggregate( [&nbsp; &nbsp;{ $group: { _id: { cust_id: "$cust_id",&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; ord_date: "$ord_date" },&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;total: { $sum: "$price" } } },&nbsp; &nbsp;{ $match: { total: { $gt: 250 } } }] ) </td> 
 <td> For each uniquecust_id,ord_dategrouping, sum the pricefield and return only where the sum is greater than 250. </td> 
 </tr> 
 <tr> 
 <td> SELECT&nbsp;cust_id,&nbsp; &nbsp;&nbsp; &nbsp;SUM(price)as&nbsp;totalFROM&nbsp;ordersWHEREstatus=&nbsp;'A'GROUPBY&nbsp;cust_id </td> 
 <td> db.orders.aggregate( [&nbsp; &nbsp;{ $match: { status: 'A' } },&nbsp; &nbsp;{ $group: { _id: "$cust_id",&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;total: { $sum: "$price" } } }] ) </td> 
 <td> For each uniquecust_id with status A, sum the pricefield. </td> 
 </tr> 
 <tr> 
 <td> SELECT&nbsp;cust_id,&nbsp; &nbsp;&nbsp; &nbsp;SUM(price)as&nbsp;totalFROM&nbsp;ordersWHEREstatus=&nbsp;'A'GROUPBYcust_idHAVING&nbsp;total&gt;&nbsp;250 </td> 
 <td> db.orders.aggregate( [&nbsp; &nbsp;{ $match: { status: 'A' } },&nbsp; &nbsp;{ $group: { _id: "$cust_id",&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;total: { $sum: "$price" } } },&nbsp; &nbsp;{ $match: { total: { $gt: 250 } } }] ) </td> 
 <td> For each uniquecust_id with status A, sum the pricefield and return only where the sum is greater than 250. </td> 
 </tr> 
 <tr> 
 <td> SELECT&nbsp;cust_id,&nbsp; &nbsp;&nbsp; &nbsp;SUM(li.qty)as&nbsp;qtyFROM&nbsp;orders o,&nbsp; &nbsp;&nbsp;&nbsp;order_lineitem liWHEREli.order_id=&nbsp;o.idGROUPBYcust_id </td> 
 <td> db.orders.aggregate( [&nbsp; &nbsp;{ $unwind: "$items" },&nbsp; &nbsp;{ $group: { _id: "$cust_id",&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;qty: { $sum: "$items.qty" } } }] ) </td> 
 <td> For each uniquecust_id, sum the corresponding line item qtyfields associated with the orders. </td> 
 </tr> 
 <tr> 
 <td> SELECT&nbsp;COUNT(*)FROM(SELECT&nbsp;cust_id, ord_date&nbsp; &nbsp;&nbsp; &nbsp;FROM&nbsp;orders&nbsp; &nbsp;&nbsp; &nbsp;GROUP&nbsp;BYcust_id, ord_date)&nbsp;asDerivedTable </td> 
 <td> db.orders.aggregate( [&nbsp; &nbsp;{ $group: { _id: { cust_id: "$cust_id",&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; ord_date: "$ord_date" } } },&nbsp; &nbsp;{ $group: { _id: null, count: { $sum: 1 } } }] ) </td> 
 </tr> 
 </tbody> 
</table>