本文介绍如何在MongoDB中实现跨越两个月的短信发送统计，包括成功、失败及未知状态的数量计算，并通过Java 8 Stream API简化合并过程。
背景介绍
上篇spring mongoTemplate 分表分页查询介绍了数据被分散到多个集合中怎么查，现在同时也迎来了怎么统计的问题，由于原来所有数据在mysql一张表所以不管怎么查，怎么统计都很方便，我查看了一下原来统计的代码，发现大佬就写了一行，当然sql不止一行，后来我改好后发现，卧槽！！！写了300多行。
统计计划
- 由于发出去的短信实时都有状态报告回来，上篇文章中提到的2个集合实时都会有数据变化，但我这边业务上认定超过7天不回来的状态报告就不会回来了，所以我这边统计也是统计近7天的结果。
- 由于统计前7填的数据，因此存在同月和跨月的情况，如果是跨越的话计算出来的结果还需要按字段合并，这里使用java8的stream的reduce很方便
- 会统计成功数、失败数、未知数以及成功率4个纬度，解释下：
成功数：我放发送成功且第三方平台下发成功，分别两个字段记录
失败数：我放发送失败或者第三方平台下发失败
未知数：未接收到状态报告
所以我们可以知道只有失败数量时两张表中都会存在的，因为，失败数需要查两边合并起来
成功率：成功数/总数
这个总数应当是当前统计的总数，而不是发送的总数，因为短信发送可能持续较长一段时间，所以成功率短时间也是一直变化的
好了，现在我们看一下被扩展了300多行的代码吧！
public void execute() {
logger.info("---------------------------开始：短信发送结果回调统计任务---------------------------");
//1.查询出7天内的统计结果
// List<SendResultStatistics> resultList = sendResultStatisticsMapper.doSendResultStatistics();
List<SendResultStatistics> resultList = statistics7DaySendSmslogFromMongodb();
//2.遍历统计结果，根据taskId填充统计结果到对应数据中。
if (CollectionUtils.isNotEmpty(resultList)){
for(SendResultStatistics s : resultList) {
String taskId = s.getTaskId();
List<SmsLog> smsLogList = smsLogService.selectList(new EntityWrapper<SmsLog>().eq("tast_id", taskId));
if (smsLogList != null && smsLogList.size() > 0) {
SmsLog smsLog = smsLogList.get(0);
smsLog.setSuccess(s.getSuccess() == null ? 0 : s.getSuccess());
smsLog.setUnknow(s.getUnknow() == null ? 0 : s.getUnknow());
smsLog.setFailed(s.getFailed() == null ? 0 : s.getFailed());
smsLog.setSuccessRate(getSuccessRate(s.getSuccess(),s.getTotal().intValue()));
smsLogService.updateById(smsLog);
} else {
continue;
}
}
}
logger.info("---------------------------结束：短信发送结果回调统计任务---------------------------");
}
private Double getSuccessRate(Integer success,Integer total){
if (success == null || total == null || total == 0) {
return 0d;
}
double c = (double) success / (double) total;
c = new BigDecimal(c).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();
return c;
}
/**
* 从mongodb的短信发送全量日志和预日志中按任务号统计
* @return
*/
private List<SendResultStatistics> statistics7DaySendSmslogFromMongodb() {
Calendar calendar = Calendar.getInstance();
Date now = calendar.getTime();
int nowMonth = calendar.get(Calendar.MONTH) + 1;
calendar.add(Calendar.DATE, -7);
Date sevenDaysAgo = calendar.getTime();
int sevenDaysAgoMonth = calendar.get(Calendar.MONTH) + 1;
List<SendResultStatistics> list;
String waitCollectionName;
String allCollectionName;
Date start;
Date end;
if (nowMonth == sevenDaysAgoMonth){
//同月
start = sevenDaysAgo;
end = now;
waitCollectionName = getColletionName(now,0);
allCollectionName = getColletionName(now,1);
list = statisticsDataByCreateTimeBetween(end,start,waitCollectionName,allCollectionName);
}else{
//临月
waitCollectionName = getColletionName(now,0);
allCollectionName = getColletionName(now,1);
calendar = Calendar.getInstance();
calendar.setTime(now);
calendar.set(Calendar.DAY_OF_MONTH,calendar.getActualMinimum(Calendar.DAY_OF_MONTH));
calendar.set(Calendar.HOUR_OF_DAY,0);
calendar.set(Calendar.MINUTE,0);
calendar.set(Calendar.SECOND,0);
start = calendar.getTime();
end = now;
List<SendResultStatistics> list1 = statisticsDataByCreateTimeBetween(end,start,waitCollectionName,allCollectionName);
calendar = Calendar.getInstance();
calendar.setTime(now);
calendar.set(Calendar.DAY_OF_MONTH,calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
calendar.set(Calendar.HOUR_OF_DAY,23);
calendar.set(Calendar.MINUTE,59);
calendar.set(Calendar.SECOND,59);
end = calendar.getTime();
start = sevenDaysAgo;
waitCollectionName = getColletionName(sevenDaysAgo,0);
allCollectionName = getColletionName(sevenDaysAgo,1);
List<SendResultStatistics> list2 = statisticsDataByCreateTimeBetween(end,start,waitCollectionName,allCollectionName);
list = megerList(list1,list2);
}
return list;
}
private List<SendResultStatistics> megerList(List<SendResultStatistics> list1, List<SendResultStatistics> list2) {
if (CollectionUtils.isEmpty(list1) && CollectionUtils.isEmpty(list2)){
return new ArrayList<>();
}else if (CollectionUtils.isNotEmpty(list1) && CollectionUtils.isEmpty(list2)){
return list1;
}else if (CollectionUtils.isEmpty(list1) && CollectionUtils.isNotEmpty(list2)){
return list2;
}else {
// CollectionUtils.isNotEmpty(list1) && CollectionUtils.isNotEmpty(list2)
list1.addAll(list2);
return list1.stream()
.collect(Collectors.groupingBy(SendResultStatistics :: getTaskId))
.entrySet().stream().map(e -> {
if (e.getValue().size() == 1){
/**
* 1.临月查询可能性:
* 一般情况下一个任务发的一批短信日志都会在同一个月内
* 2.合并success，falied，unknow三个集合可能性：
* 由于各个集合查询mongodb集合不同，每个集合很有可能元素数量不一样，
* 合并集合中任务号不一致则说明没有任务号重复的元素直接返回不用合并字段
*/
return e.getValue().get(0);
}else{
/**
* 1.临月查询可能性:
* 由于一个任务发的一批短信量的缘故，并发发短信会有先后顺序，
* 极端情况可能出现短信创建时间前一批上0点前，后一批在零点后，
* 月底时候有可能跨月，这是由于构建短信日志对象时创建时间是new的
* 而不是取自该次任务的创建时间
* 2.合并success，falied，unknow三个集合可能性：
* 由于各个集合查询mongodb集合不同，每个集合很有可能元素数量不一样，
* 但肯定有不同集合中任务号一致的，任务号一致的字段合并
*/
return e.getValue().stream().reduce(
(x,y) -> new SendResultStatistics(
x.getTaskId(),
(x.getTotal() == null ? 0 : x.getTotal()) + (y.getTotal() == null ? 0 : y.getTotal()),
(x.getSuccess() == null ? 0 : x.getSuccess()) + (y.getSuccess() == null ? 0 : y.getSuccess()),
(x.getUnknow() == null ? 0 : x.getUnknow()) + (y.getUnknow() == null ? 0 : y.getUnknow()),
(x.getFailed() == null ? 0 : x.getFailed()) + (y.getFailed() == null ? 0 : y.getFailed())
)
).orElse(new SendResultStatistics());
}
}).collect(Collectors.toList());
}
}
private List<SendResultStatistics> statisticsDataByCreateTimeBetween(Date now, Date sevenDaysAgo,String waitCollectionName, String allCollectionName) {
//预日志:未知数量
Aggregation unknowAgg = Aggregation.newAggregation(
Aggregation.match(Criteria.where("createTime").gte(sevenDaysAgo).lte(now).and("state").is("0")),
Aggregation.group(
Aggregation.fields().and("batchNum")
).count().as("unknow")
);
AggregationResults<Map> unknowResults = this.mongoTemplate.aggregate(unknowAgg,waitCollectionName,Map.class);
List<SendResultStatistics> unknowList = null;
if (unknowResults != null){
unknowList = unknowResults.getMappedResults().stream().map(e -> {
SendResultStatistics statistics = new SendResultStatistics();
statistics.setTaskId(e.get("_id").toString());
statistics.setUnknow((Integer) e.get("unknow"));
return statistics;
}).collect(Collectors.toList());
}
//全量日志:成功数量
Aggregation successAgg = Aggregation.newAggregation(
Aggregation.match(Criteria.where("createTime").gte(sevenDaysAgo).lte(now).and("state").is("0").and("sentStatus").is("0")),
Aggregation.group(
Aggregation.fields().and("batchNum")
).count().as("success")
);
AggregationResults<Map> successResults = this.mongoTemplate.aggregate(successAgg,allCollectionName,Map.class);
List<SendResultStatistics> successList = null;
if (successResults != null){
successList = successResults.getMappedResults().stream().map(e -> {
SendResultStatistics statistics = new SendResultStatistics();
statistics.setTaskId(e.get("_id").toString());
statistics.setSuccess((Integer) e.get("success"));
return statistics;
}).collect(Collectors.toList());
}
//预日志:失败数量
Aggregation failedWaitAgg = Aggregation.newAggregation(
Aggregation.match(Criteria.where("createTime").gte(sevenDaysAgo).lte(now).and("state").is("1")),
Aggregation.group(
Aggregation.fields().and("batchNum")
).count().as("failed")
);
AggregationResults<Map> failedWaitResults = this.mongoTemplate.aggregate(failedWaitAgg,waitCollectionName,Map.class);
List<SendResultStatistics> failedWaitList = null;
if (failedWaitResults != null){
failedWaitList = failedWaitResults.getMappedResults().stream().map(e -> {
SendResultStatistics statistics = new SendResultStatistics();
statistics.setTaskId(e.get("_id").toString());
statistics.setFailed((Integer) e.get("failed"));
return statistics;
}).collect(Collectors.toList());
}
//全量日志:失败数量
Aggregation failedAllAgg = Aggregation.newAggregation(
Aggregation.match(Criteria.where("createTime").gte(sevenDaysAgo).lte(now).and("sentStatus").is("1")),
Aggregation.group(
Aggregation.fields().and("batchNum")
).count().as("failed")
);
AggregationResults<Map> failedAllResults = this.mongoTemplate.aggregate(failedAllAgg,allCollectionName,Map.class);
List<SendResultStatistics> failedAllList = null;
if (failedAllResults != null){
failedAllList = failedAllResults.getMappedResults().stream().map(e -> {
SendResultStatistics statistics = new SendResultStatistics();
statistics.setTaskId(e.get("_id").toString());
statistics.setFailed((Integer) e.get("failed"));
return statistics;
}).collect(Collectors.toList());
}
//合并失败数量集合
List<SendResultStatistics> failedList = null;
if (CollectionUtils.isEmpty(failedWaitList) && CollectionUtils.isNotEmpty(failedAllList)){
failedList = failedAllList;
}
if (CollectionUtils.isNotEmpty(failedWaitList) && CollectionUtils.isEmpty(failedAllList)){
failedList = failedWaitList;
}
if (CollectionUtils.isNotEmpty(failedWaitList) && CollectionUtils.isNotEmpty(failedAllList)){
failedWaitList.addAll(failedAllList);
failedList = failedWaitList.stream().collect(Collectors.groupingBy(SendResultStatistics :: getTaskId,Collectors.summingInt(SendResultStatistics :: getFailed)))
.entrySet()
.stream()
.map(e -> new SendResultStatistics(e.getKey(), e.getValue()))
.collect(Collectors.toList());
}
//合并成功、未知、失败三个集合
List<SendResultStatistics> result = new ArrayList<>();
if (CollectionUtils.isEmpty(successList) && CollectionUtils.isEmpty(unknowList) && CollectionUtils.isEmpty(failedList)){
return result;
}
if (CollectionUtils.isNotEmpty(successList)){
result = successList;
if (CollectionUtils.isNotEmpty(unknowList)){
result = megerList(result,unknowList);
}
if (CollectionUtils.isNotEmpty(failedList)){
result = megerList(result,failedList);
}
}else if (CollectionUtils.isNotEmpty(unknowList)){
result = unknowList;
if (CollectionUtils.isNotEmpty(successList)){
result = megerList(result,successList);
}
if (CollectionUtils.isNotEmpty(failedList)){
result = megerList(result,failedList);
}
}else{
// failedList 不为空
result = failedList;
if (CollectionUtils.isNotEmpty(successList)){
result = megerList(result,successList);
}
if (CollectionUtils.isNotEmpty(unknowList)){
result = megerList(result,unknowList);
}
}
if (CollectionUtils.isNotEmpty(result)) {
result.forEach(e -> {
int success = e.getSuccess() == null ? 0 : e.getSuccess();
int unknow = e.getUnknow() == null ? 0 : e.getUnknow();
int failed = e.getFailed() == null ? 0 : e.getFailed();
e.setTotal(success + unknow + failed);
});
}
return result;
}
private String getColletionName(Date date,int flag) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
int nowYear = calendar.get(Calendar.YEAR);
int nowMonth = calendar.get(Calendar.MONTH) + 1;
String month = nowMonth > 9 ? String.valueOf(nowMonth) : "0" + nowMonth;
String baseName = flag == 0 ? MarketConstant.WAIT_STATUS_REPORT_COLLECTION_NAME : MarketConstant.SEND_SMS_LOG_COLLECTION_NAME;
return nowYear + "_" + baseName + "_" + month;
}
扫一扫
1051
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
