-
- 最近在搞新项目开发,很多逻辑都是要看老项目的代码,老项目用的是oracle库,新项目换了MongoDb,那么问题来了,如何把sql代码转换成NoSql?
- 下面分享下今天搞的功能,把如下代码转换成mongodb写法:
- shell 代码部分:
- key : 里面填写需要分组的字段;
- cond: 查询条件;
- inital: 初始化统计字段的值;
- reduce :function Reduce(doc, out) :用来处理数据计算,或其他业务;
- doc:所查询集合的某个字段【doc.amnt==plnmioRec.amnt】;
- out : 绑定被初始化的字段值【out.amnt】,也就是在inital中初始化的字段;
- 具体代码如下:
-
db.plnmioRec.group({
key: { 'applNo':1,'bankCode':1,'bankAccNo':1,'accCustName':1},
cond: {'plnmioRecId':2}, initial : {"amnt":0,"count":0,plnmioRecIds:0},
reduce : function Reduce(doc, out) {
out.amnt+=doc.amnt;//金额累加
out.count+=1;//统计条数
out.plnmioRecIds+=doc.plnmioRecId+","//这句为了实现wm_concat()的行专列功能;
}});
-
- java代码:
- //需要分组的字段
BasicDBObject key = new BasicDBObject();
key.put("applNo", 1);
key.put("bankCode", 1);
key.put("bankAccNo", 1);
key.put("accCustName", 1);
//条件
BasicDBObject cond = new BasicDBObject();
cond.put("mioTypeCode", "1");
BasicDBList values = new BasicDBList();
values.add("1");
values.add("2");
cond.put("tranState",new BasicDBObject("$in",values));
//初始化聚合结果
BasicDBObject initial = new BasicDBObject();
initial.append("count", 0);
initial.append("amnt", 0);
initial.append("plnmioRecIds", 0);
//即在分组操作过程中操作的函数
String reduce = "function Reduce(doc, out) { "
+ " out.amnt+=doc.amnt;"
+ " out.count+=1;"
+ " out.plnmioRecIds+=doc.plnmioRecId+',' ;"
+ "}";
BasicDBList groupList=(BasicDBList) mongoTemplate.getCollection("plnmioRec").group(key, cond, initial, reduce);
List<PlnmioRecGroupBean> prgbList = new ArrayList<PlnmioRecGroupBean>();
if(groupList!=null&&groupList.size()>0){
System.out.println("applNo,bankCode,bankAccNo,accCustName,count(plnmioRecId) count,sum(amnt) amnt");
for(int i=0;i<groupList.size();i++){
BasicDBObject obj=(BasicDBObject) groupList.get(i);
PlnmioRecGroupBean prgb = new PlnmioRecGroupBean();
System.out.println(obj.getString("applNo")+" "+obj.getString("bankCode")+" "+obj.getString("bankAccNo")+" "+obj.getString("accCustName")+" "+obj.getInt("count") +" "+obj.getDouble("amnt")+" "+obj.getString("plnmioRecIds"));
prgb.setApplNo(obj.getString("applNo"));
prgb.setBankCode(obj.getString("bankCode"));
prgb.setBankAccNo(obj.getString("bankAccNo"));
prgb.setAccCustName(obj.getString("accCustName"));
prgb.setCount(obj.getInt("count"));
prgb.setAmnts(obj.getDouble("amnt"));
prgb.setPlnmioRecId(obj.getString("plnmioRecIds"));
prgbList.add(prgb);
}
}
关于MongoDB分组统计的问题
最新推荐文章于 2024-04-28 07:28:19 发布