在MongoDB中实现聚合函数

1

OrderId

INTEGER

2

OrderDate

STRING

3

Quantity

INTEGER

4

SalesAmt

DOUBLE

5

Profit

DOUBLE

6

CustomerName

STRING

7

City

STRING

8

State

STRING

9

ZipCode

STRING

10

Region

STRING

11

ProductId

INTEGER

12

ProductCategory

STRING

13

ProductSubCategory

STRING

14

ProductName

STRING

15

ShipDate

STRING

SUM

db.system.js.save( { _id : "Sum" , value : function(key,values) { var total = 0; for(var i = 0; i < values.length; i++) total += values[i]; return total; }});

AVERAGE

db.system.js.save( { _id : "Avg" , value : function(key,values) { var total = Sum(key,values); var mean = total/values.length; return mean; }});

MAX

db.system.js.save( { _id : "Max" , value : function(key,values) { var maxValue=values[0]; for(var i=1;i

MIN

db.system.js.save( { _id : "Min" , value : function(key,values) { var minValue=values[0]; for(var i=1;i

VARIANCE

db.system.js.save( { _id : "Variance" , value : function(key,values) { var squared_Diff = 0; var mean = Avg(key,values); for(var i = 0; i < values.length; i++) { var deviation = values[i] - mean; squared_Diff += deviation * deviation; } var variance = squared_Diff/(values.length); return variance; }});

STD DEVIATION

db.system.js.save( { _id : "Standard_Deviation" , value : function(key,values) { var variance = Variance(key,values); return Math.sqrt(variance); }});

SELECT

db.sales.runCommand( { mapreduce : "sales" ,

City, State, Region,

map:function() { // emit function handles the group by emit( { // Key city:this.City, state:this.State, region:this.Region}, // Values this.Quantity); },

AVG(Quantity)

reduce:function(key,values) { var result = Avg(key, values); return result; }

FROM sales

GROUP BY City, State, Region

// Group By is handled by the emit(keys, values) line in the map() function above

out : { inline : 1 } });

SELECT

db.sales.runCommand( { mapreduce : "sales" ,

ProductCategory, ProductSubCategory, ProductName,

map:function() { emit( // Key {key0:this.ProductCategory, key1:this.ProductSubCategory, key2:this.ProductName}, // Values this.SalesAmt); },

SUM(SalesAmt)

reduce:function(key,values) { var result = Sum(key, values); return result; }

FROM sales

GROUP BY ProductCategory, ProductSubCategory, ProductName

// Group By is handled by the emit(keys, values) line in the map() function above

out : { inline : 1 } });

SELECT

db.sales.runCommand( { mapreduce : "sales" ,

ProductId, ProductName,

map:function() { if(this.ProductId==1) emit( { key0:this.ProductId, key1:this.ProductName}, this.Profit); },

MAX(SalesAmt)

reduce:function(key,values) { var maxValue=Max(key,values); return maxValue; }

FROM sales

WHERE ProductId=’1’

// WHERE condition implementation is provided in map() function

GROUP BY ProductId, ProductName

// Group By is handled by the emit(keys, values) line in the map() function above

out : { inline : 1 } });

SELECT

db.sales.runCommand( { mapreduce : "sales" ,

Region, ProductCategory, ProductId,

map:function() { emit( { // Keys region:this.Region, productCategory:this.ProductCategory, productid:this.ProductId}, // Values {quantSum:this.Quantity, salesSum:this.SalesAmt, avgProfit:this.Profit} ); }

Sum(Quantity), Sum(Sales), Avg(Profit)

reduce:function(key,values) { var result= {quantSum:0,salesSum:0,avgProfit:0}; var count = 0; values.forEach(function(value) { // Calculation of Sum(Quantity) result.quantSum += values[i].quantSum; // Calculation of Sum(Sales) result.salesSum += values[i].salesSum; result.avgProfit += values[i].avgProfit; count++; } // Calculation of Avg(Profit) result.avgProfit = result.avgProfit / count; return result; },

FROM Sales

WHERE

Orderid between 1 and 10 AND Shipdate BETWEEN ‘01/01/2011’ and ‘12/31/2011’

query : { "OrderId" : { "$gt" : 1 }, "OrderId" : { "$lt" : 10 }, "ShipDate" : { "$gt" : "01/01/2011" }, "ShipDate" : { "$lt" : "31/12/2011" }, },

GROUP BY Region, ProductCategory, ProductId

// Group By is handled by the emit(keys, values) line in the map() function above

LIMIT 3;

limit : 3,

out : { inline : 1 } });

Shruthi Kumar Infosys公司Cloud Center of Excellence (CoE)的技术分析师，该公司在IT和商业咨询服务上位于全球领先的地位。Shruthi在Java、网格计算、云计算以及大数据应用架构上有5年的工作经验。她现在从事大数据解决方案的开发和咨询。Email: Shruthi_Kumar01@infosys.com.