mongodb中sum总结

https://blog.csdn.net/u013476435/article/details/81560089

当对数据进行sum时,如果要sum字段是NumberLong型或者Integer类型  直接聚合函数就可以搞定

db.getCollection('aUAUserCurrencyDetails').aggregate(
[
{
$match : { "memberId":321318604292097 , "issuedStatus":1 }
} ,


{
$group :
{
_id : "$memberId", totalAmount: {$sum:"$shouldCurrencyNums"},count: { $sum: 1 }
}
}
,{$sort:{count:-1}}
]
)

 

注: 其中 aUAUserCurrencyDetails 是表名,match中的memberId和issuedStatus是过滤的条件,group是分组查询的条件,shouldCurrencyNums是我要sum的字段

对应Java的写法:

Aggregation agg = Aggregation.newAggregation(
Aggregation.match(Criteria.where("memberId").is(memberId).and("issuedStatus").is(1))
, Aggregation.group("memberId").sum("shouldCurrencyNums").as("totalAmount"),
Aggregation.sort(Sort.Direction.DESC, "totalAmount")
);
AggregationResults<BasicDBObject> results = mongoTemplate.aggregate(agg, "aUAUserCurrencyDetails", BasicDBObject.class);
//得到结果
List<BasicDBObject> basicDBObjectList = results.getMappedResults();
if (!PubMethod.isEmpty(basicDBObjectList)) {
for (int i = 0; i < basicDBObjectList.size(); i++) {
System.out.print(basicDBObjectList.get(i).get("totalAmount"));
branchRecordMap.put("totalAmount",basicDBObjectList.get(i).get("totalAmount"));
}
}

 

但如果MongoDB中要sum的字段是String 类型(在Java中可能是BigDecimal)时,  上面的方式统计时一直是0,可以用MapReduce的方式,对应Java写法:

double total = 0L;
Criteria criteria = new Criteria();
criteria.and("memberId").is(memberId).and("issuedStatus").is(1);

String reduce = "function(doc, aggr){" +
" aggr.total += parseFloat((Math.round((doc.shouldCurrencyNums)*100)/100).toFixed(2));" +
" }";
Query query = new Query();
if (criteria != null) {
query.addCriteria(criteria);
}
DBObject result = mongoTemplate.getCollection("aUAUserCurrencyDetails").group(null,
query.getQueryObject(),
new BasicDBObject("total", total),
reduce);

Map<String, BasicDBObject> map = result.toMap();
if (map.size() > 0) {
BasicDBObject bdbo = map.get("0");
if (bdbo != null && bdbo.get("total") != null) {
total = bdbo.getDouble("total");
}
}
branchRecordMap.put("totalAmount", total);