Spirng中Mongodb中group实战

mongdb查询这个样子

select uid1, deviceId, dateHour,avg from xxx where createTime >? group by  uid1, deviceId, DateFormat("createTime","yyyy-MM-dd HH") as dateHour order by uid1, deviceId, dateHour limit ? ?
           
db.xxxx.aggregate([
   { "$match" : { "createTime" : { "$gte" : { "$date" : "2018-03-01T16:00:00.000Z"}}}},
   { "$project" : { "dateHour" : { "$dateToString" : { "format" : "%Y-%m-%d %H", "date" : "$createTime" } }, "_id" : 0, "deviceId" : 1, "uid" : 1, "value1" : 1 } },
   { "$group" : { "_id" : { "uid" : "$uid", "deviceId" : "$deviceId", "dateHour" : "$dateHour" }, "avg" : { "$avg" : "$value1" } } },
   { "$sort" : { "_id" : 1 }},
   { "$skip" : 1 },
   { "$limit" : 2 },    
   ])

查询结果

/* 1 */
{
	"_id" : {
		"uid" : "uid1",
		"deviceId" : "deviceId3",
		"dateHour" : "2018-03-14 06"
	},
	"avg" : 1.0
},


/* 2 */
{
	"_id" : {
		"uid" : "uid1",
		"deviceId" : "deviceId3",
		"dateHour" : "2018-03-15 02"
	},
	"avg" : 1.0
}

Dao实现方法

Date from = DateUtil.getDiffDay(new Date(), -1);

Criteria criteria = Criteria.where("createTime").gte(from);

DBObject match = new BasicDBObject("$match",criteria.getCriteriaObject());
DBObject projectMap = new BasicDBObject();
DBObject format = new BasicDBObject("format", "%Y-%m-%d %H");
format.put("date", "$createTime");
DBObject formattedDate = new BasicDBObject("$dateToString", format);
projectMap.put("dateHour", formattedDate);

projectMap.put("_id",0);
projectMap.put("deviceId",1);
projectMap.put("uid",1);
projectMap.put("value1",1);


DBObject aggregate = new BasicDBObject("$project", projectMap);

DBObject groupMap = new BasicDBObject();
DBObject idmap = new BasicDBObject();
idmap.put("uid","$uid");
idmap.put("deviceId","$deviceId");
idmap.put("dateHour","$dateHour");
groupMap.put("_id", idmap);

groupMap.put("avg", new BasicDBObject("$avg", "$value1"));

BasicDBObject groupOption = new BasicDBObject("$group", groupMap);

//$skip,$limit,$sort执行顺序跟位置一致
DBObject sort = new BasicDBObject("$sort", new BasicDBObject("_id", 1));
DBObject skip = new BasicDBObject("$skip", page*2);
DBObject limit = new BasicDBObject("$limit", 2);

List<DBObject> list = new ArrayList<>();
list.add(match);
list.add(aggregate);
list.add(groupOption);
list.add(sort);
list.add(skip);
list.add(limit);

Iterator<DBObject> iterator = mongoTemplate.getCollection(xxxx).aggregate(list).results().iterator();

iterator.forEachRemaining(System.out ::println);

运行结果

{ "_id" : { "uid" : "uid1" , "deviceId" : "deviceId3" , "dateHour" : "2018-03-14 02"} , "avg" : 1.0}
{ "_id" : { "uid" : "uid1" , "deviceId" : "deviceId3" , "dateHour" : "2018-03-14 06"} , "avg" : 1.0}