MongoDB Aggregation Group (i)

A. Introduction

Db.collection.group () uses JavaScript, which is subject to some performance limitations. In most cases, the $ group in aggregation pipeline provides an alternative that has less restrictive applicability. A simple aggregation function can be performed by specifying the document in the collection of keys. In version 2.2, the returned array can contain a maximum of 20,000 elements, or up to 20,000 unique groupings.

We are more familiar with the GROUP by SQL statement Select Key from Table GroupBy key, and MongoDB does not provide SQL as a group by to easily implement the database grouping function, we through the interface to achieve

Db.collection.group ({key, reduce, initial[, KEYF] [, cond] [, finalize]})

Key	As a grouped key
Reduce	An aggregation function operates during the grouping operation of the document. These functions can return a sum or count. The function accepts two parameters: the current document and the result document of this group aggregation.
Initial	Initializes the aggregated result document variable, which automatically provides the initial variable for each column when it is empty.
Keyf	Optional. The Replacement key field. Specifies a function to create a "key object" as a grouped key. Use KEYF instead of the existing document domain key group by the Group by field.
Cond	Filter conditions
Finalize	Before Db.collection.group () returns the final result, this feature can modify the result document or replace the result document as a whole.

two. Mongo Vue Operations Group by 1.MonogoDB database added data for orders

/* 0 */{"_id": ObjectId ("552a330e05c27486b9b9b650"), "_class": "Com.mongo.model.Orders", "Onumber": "002", "date" : Isodate ("2014-01-03t16:03:00z"), "CNAME": "Zcy", "item": {"Quantity": 1, "Price": 4.0, "Pnumber": "p00 2 "}}/* 1 */{" _id ": ObjectId (" 552a331d05c275d8590a550d ")," _class ":" Com.mongo.model.Orders "," Onumber ":" 00 3 "," Date ": Isodate (" 2014-01-04t16:03:00z ")," CNAME ":" Zcy "," item ": {" Quantity ": Ten," Price ": 2.0," PNU Mber ":" P001 "}}/* 2 */{" _id ": ObjectId (" 552a333105c2f28194045a72 ")," _class ":" Com.mongo.model.Orders "," on Umber ":" 003 "," date ": Isodate (" 2014-01-04t16:03:00z ")," CNAME ":" Zcy "," item ": {" Quantity ": $," price ": 4.0, "Pnumber": "P002"}}//* 3 */{"_id": ObjectId ("552a333f05c2b62c01cff50e"), "_class": "COM.MONGO.MODEL.O
   Rders "," Onumber ":" 004 "," date ": Isodate (" 2014-01-05t16:03:00z ")," CNAME ":" Zcy "," item ": {" Quantity ": 5, "Price": 4.0, "Pnumber ":" P002 "}} 

2.MongoDB implementation grouping and statistics

1) We want to group the date and product code and calculate the quantity of the same product

SQL statements: Select date, pnumber,sum (quantity) as total from Orders,items Group by date, Pnumber (conditions associated with two tables are missing)

Mongodb:

Db.orders.group ({

Key: {date:1, ' Item.pnumber ': 1},

Initial: {"Total": 0},

Reduce:function Reduce (Doc, out) {

Out.total+=doc.item.quantity

} });

Results:

2) How many products are sold in a day, what is the amount, and what is the average price?

Db.orders.group ({

Key: {date:1},

Initial: {"Total": 0, "Money": 0},

Reduce:function Reduce (Doc, out) {

out.total+=doc.item.quantity;

Out.money+=doc.item.quantity*doc.item.price;

},

Finalize:function Finalize (out) {

Out.avg=out.money/out.total

Returnout;

}

}); Results:

3) Use of KEYF

KEYF processing dates and grouping them as keys

Db.orders.group ({

Keyf:function (DOC) {

return{' Month ':d oc.date.getMonth () +1};

},

Initial: {"Total": 0, "Money": 0},

Reduce:function Reduce (Doc, out) {

out.total+=doc.item.quantity;

Out.money+=doc.item.quantity*doc.item.price;

},

Finalize:function Finalize (out) {

Out.avg=out.money/out.total

Returnout;

}

}); Results:

three. The Java MongoDB implementation of Spring Data MongoDB provides a group with several interfaces Groupcommand Groupcommand=new Groupcommand (Inputcollection, keys, condition, initial, reduce, finalize);

1) We want to group the date and product code and calculate the quantity of the same product

           

<strong> </strong> @Override public void GetGroupCount (String collectionname) {basicdbobject key = 
		New Basicdbobject ();
		Key.put ("date", 1);
		Key.put ("Item.pnumber", 1);  
		Condition Basicdbobject cond = new Basicdbobject ();  
		Initialize Basicdbobject initial = new Basicdbobject ();  
		Initial.append ("Total", 0); Reduce String reduce = "function reduce (doc, out) {" + "out.total+=doc.item.quantity;" + "}  
		
		";
		SimpleDateFormat format=new SimpleDateFormat ("Yyyy-mm-dd");
		Basicdblist grouplist= (basicdblist) mongotemplate.getcollection (collectionname). Group (key, cond, initial, reduce);
			if (Grouplist!=null&&grouplist.size () >0) {System.out.println ("date Item.pnumber total");
				for (int i=0;i<grouplist.size (); i++) {Basicdbobject obj= (basicdbobject) grouplist.get (i); System.out.println (Format.format (obj.getdate ("date") + "" +obj.getstring ("Item.pnumber") + "" +obj.getint ("Total")
			; }
		}
	}

Results:

   

 

2) How many products are sold in a day, what is the amount, and what is the average price?

        @Override public void Getgroupavg (String collectionname) {basicdbobject key = new Basicdbobject ();
		
		Key.put ("date", 1);  
		Condition Basicdbobject cond = new Basicdbobject ();  
		Initialize Basicdbobject initial = new Basicdbobject (); 
		Initial.append ("Total", 0);
		
		Initial.append ("Money", 0.0);   Reduce String reduce = "function reduce (doc, out) {" + "out.total+=doc.item.quantity;" + " 
		
		Out.money+=doc.item.quantity*doc.item.price; "+"} ";
	        String finalize= "function finalize (out) {" + "out.avg=out.money/out.total;" + "return out;" +		
		"}";
		SimpleDateFormat format=new SimpleDateFormat ("Yyyy-mm-dd"); Basicdblist grouplist= (basicdblist) mongotemplate.getcollection (collectionname). Group (key, cond, initial, reduce,
		Finalize);
			if (Grouplist!=null&&grouplist.size () >0) {System.out.println ("date total money avg"); for (int i=0;i<grouplist.size (); i++) {BasicdbobjeCT obj= (basicdbobject) grouplist.get (i); System.out.println (Format.format (obj.getdate ("date") + "" +obj.getint ("total") + "" +obj.getint ("money") + "" +
			Obj.getdouble ("avg"));
 }
		}
		
	}

Results: