[MongoDB] Aggregate Example

统计示例：

引用自：http://icoon22.tistory.com/319

spring-data-mongodb语法：

BasicDBObject cmdBody = new BasicDBObject("aggregate", "productHistory");
ArrayList<BasicDBObject> pipeline = new ArrayList<BasicDBObject>();
String fmt = "yyyyMMddHHmmss";

Date fromDate = Utils.strToDate(StringUtils.defaultString("2013-01-01 00:00:00", Utils.tm2str("DEFAULT", new Date())));
Date toDate    = Utils.strToDate(StringUtils.defaultString("2013-02-28 23:59:59",  Utils.tm2str("DEFAULT", new Date())));

DBObject whereCondition = new BasicDBObject(2);
whereCondition.put("$gte", Utils.formatToDate(fmt, fromDate));
whereCondition.put("$lt", Utils.formatToDate(fmt, toDate));

DBObject receiverCondition = new BasicDBObject();
receiverCondition.put("$eq", new ObjectId(receiver.getId()));

BasicDBObject $where= new BasicDBObject();
$where.append("receiver.$id", receiverCondition);
// $where.append("date", whereCondition);
BasicDBObject $group = new BasicDBObject("_id",  new BasicDBObject("date", "$date"));
$group.append("totalBalance", new BasicDBObject("$sum", "$balance"));
$group.append("totalAmountRecommand", new BasicDBObject("$sum", "$amountRecommand"));

DBObject havingCondition = new BasicDBObject(2);
havingCondition.put("$gte", 304235000);
havingCondition.put("$lte", 404235000);
BasicDBObject $having = new BasicDBObject("totalBalance", havingCondition);

BasicDBObject $sort = new BasicDBObject("_id.date", -1); // -1 : DESC, 1 : ASC

// 옵션 : Result output 를 정의 한다.
BasicDBObject $project = new BasicDBObject();
$project.put("date", 1);
$project.put("totalBalance", 1);
$project.put("totalAmountRecommand", 1);


pipeline.add(new BasicDBObject("$match", $where));
pipeline.add(new BasicDBObject("$group", $group));
pipeline.add(new BasicDBObject("$match", $having));
pipeline.add(new BasicDBObject("$sort", $sort));
pipeline.add(new BasicDBObject("$limit", 5));
pipeline.add(new BasicDBObject("$project", $project));
cmdBody.put("pipeline", pipeline);

System.out.println(cmdBody.toString());

CommandResult cr = mongoOperations.executeCommand(cmdBody.toString());

System.out.println(cr.toString());

 

 翻译成SQL便于习惯思考

select
	 date
	,sum(balance) as totalBalance
	,sum(amountRecommand) as totalAmountRecommand
from productHistory
where date < '20130110000000' and    date > '20130412235959'
group date
having totalBalance <= 304235000 and totalBalance >= 404235000
order by date desc
limit 0, 5

 mongodb中的聚合语句，以下语句通过db.runCommand()函数执行。

{ "aggregate" : "productHistory" , "pipeline" : [
	    { "$match" : { "createDate" : { "$gte" : "20130101000000" , "$lt" : "20130228235959"}}}
	  , { "$group" : {
			 "_id" : { "createDate" : "$createDate"}
			, "totalBalance" : { "$sum" : "$balance"}
			, "totalAmountRecommand" : { "$sum" : "$amountRecommand"}
			}
		}
	  , { "$match" : { "totalBalance" : { "$gte" : 304235000 , "$lte" : 404235000}}}
	  , { "$sort" : { "_id.createDate" : -1}}
	  , { "$limit" : 5}
	  , { "$project" : { "createDate" : 1 , "totalBalance" : 1 , "totalAmountRecommand" : 1}}
 ]}

 聚合结果：

 { "serverUsed" : "/127.0.0.1:27017" , "result" : [
  { "_id" : { "createDate" : "20130228233505"} , "totalBalance" : 392745000 , "totalAmountRecommand" : 130915000}
, { "_id" : { "createDate" : "20130228230757"} , "totalBalance" : 334755000 , "totalAmountRecommand" : 111585000}
, { "_id" : { "createDate" : "20130228225344"} , "totalBalance" : 361920000 , "totalAmountRecommand" : 120640000}
, { "_id" : { "createDate" : "20130228190033"} , "totalBalance" : 395145000 , "totalAmountRecommand" : 131715000}
, { "_id" : { "createDate" : "20130228182751"} , "totalBalance" : 396270000 , "totalAmountRecommand" : 132090000}
 ] , "ok" : 1.0}