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}