mongodb分组排序
写代码遇到一个需求,员工的培养记录表,判断是否是最新的一条记录。
1、表结构如下
{
“_id” : ObjectId(“60f035e0e0ee770f303d5be8”),
“_class” : “com.linkus.prj.model.TeSysUserPfm”,
“isValid” : true,
“emp” : {
“userId” : ObjectId(“5ce5c686e0ee7777b921bbae”),
“loginName” : “chenzx”,
“userName” : “陈振兴”,
“jobCode” : “89657”
},
“ym” : “202106”,
“score” : 3.392,
“addTime” : ISODate(“2021-07-15T13:19:27.126Z”),
}
2、SQL
这里以两个员工为例,每个员工可能有多条记录。
db.sysUserPfm.aggregate([
{
"$match":{
"emp.userId":{"$in":[ObjectId("5ce5c686e0ee7777b921bbae"),ObjectId("5a389f0510d00e056c73e22d")]}
}
},{
"$sort":{"addTime":-1} // 先根据addTime降序
},{
"$group":{
"_id":"$emp.userId", // _id必须有,可以为null,类似于group by,这里根据员工id分组
"addTime":{"$first":"$addTime"}, // 取第一个addTime,因为前面已经排序
"id":{"$first":"$_id"}, // 显示每个员工最新的addTime的文档主键id
}
}
])
说明
只需要把结果转换成员工id和最新记录的文档id映射集合,然后循环文档记录结果时,从集合get判断是否存在即可
3、代码实现
private Map<ObjectId, ObjectId> getLastestEmpRecords(List<ObjectId> sysUserIds) {
List<BasicDBObject> aggregate = new ArrayList<>(3);
BasicDBObject match = new BasicDBObject()
.append(DFN.sysUserPfm_isValid.n(), true)
.append(DFN.sysUserPfm_emp.dot(DFN.common_userId).n(), new BasicDBObject("$in", sysUserIds));
BasicDBObject sort = new BasicDBObject(DFN.sysUserPfm_addTime.n(), -1);
BasicDBObject group = new BasicDBObject()
.append("_id", DFN.sysUserPfm_emp.dot(DFN.common_userId).$n())
.append("sysUserPfmId", new BasicDBObject("$first", DFN.sysUserPfm_id.$n()));
aggregate.add(new BasicDBObject("$match", match));
aggregate.add(new BasicDBObject("$sort", sort));
aggregate.add(new BasicDBObject("$group", group));
AggregationOptions aggOpts = AggregationOptions.builder().outputMode(AggregationOptions.OutputMode.CURSOR).build();
Cursor cursor = mongoTemplate.getCollection(DBT.SYS_USER_PFM.n()).aggregate(aggregate, aggOpts);
Map<ObjectId, ObjectId> sysUserIdToSysUserPfm = new HashMap<>();
if (null != cursor) {
while (cursor.hasNext()) {
DBObject object = cursor.next();
ObjectId sysUserId = (ObjectId) object.get("_id");
ObjectId sysUserPfmId = (ObjectId) object.get("sysUserPfmId");
sysUserIdToSysUserPfm.put(sysUserId, sysUserPfmId);
}
}
return sysUserIdToSysUserPfm;
}