Mongo常用语法（java代码）
1、根据agentId字段分组，并对totalCustomerNum、refundCustomerNum字段 sum求和，同时取别名
@Override
public List<AgentCountInfoBean> selectCurrentMonthNewResource(Set<String> orderTypeSet, List<String> agentIds,
LocalDateTime startTime, LocalDateTime endTime) {
Aggregation aggr = newAggregation(
match(where("agentId").in(agentIds)
.and("agentOrderType").in(orderTypeSet)
.and("agentOrderStatus").in(Arrays.asList(AgentOrderStatusEnum.NOT_START.status, AgentOrderStatusEnum.PROCESSING.status))
.andOperator(
EmptyUtils.isNotEmpty(startTime) ? Criteria.where("createTime").gte(startTime) : new Criteria(),
EmptyUtils.isNotEmpty(endTime) ? Criteria.where("createTime").lte(endTime) : new Criteria(),
EmptyUtils.isNotEmpty(endTime) ? Criteria.where("hopeStartTime").lte(endTime) : new Criteria()
)
),
group("agentId").first("agentId").as("agentId").sum("totalCustomerNum").as("totalCount")
.sum("refundCustomerNum").as("refundCustomerNum")
);
AggregationResults<AgentCountInfoBean> results = secondaryMongoTemplate.aggregate(aggr, ResAgentOrderMo.class, AgentCountInfoBean.class);
if (EmptyUtils.isNotEmpty(results)) {
return results.getMappedResults();
}
return null;
}
2、对agentId分组，并统计每个agentId分组后的个数，类似于mysql的select agentId，count(agentId) from tabel group by agentId;
@Override
public List<String> findNonFirstMonthBuyResource(LocalDateTime startDate, List<String> leadingEagleIds, Set<String> agentOrderSetsT) {
Aggregation aggr = newAggregation(
match(where("agentId").in(leadingEagleIds)
.and("agentOrderType").in(agentOrderSetsT)
.and("agentOrderStatus").ne(AgentOrderStatusEnum.NOT_PAY.status)
.andOperator(
EmptyUtils.isNotEmpty(startDate) ? Criteria.where("payTime").lte(startDate) : new Criteria()
)
),
group("agentId").first("agentId").as("agentId").count().as("count")
);
AggregationResults<AgentCountBean> results = secondaryMongoTemplate.aggregate(aggr, ResAgentOrderMo.class, AgentCountBean.class);
if (EmptyUtils.isNotEmpty(results)) {
return results.getMappedResults().stream().map(AgentCountBean::getAgentId).collect(Collectors.toList());
}
return null;
}
3、根据条件查询，然后只返回某个字段同时去重
@Override
public List<String> findNotEndAgentIdsByTypeAndSaleAreas(Set<String> agentOrderTypes, List<String> areaIds) {
Query query = query(
where("agentOrderType").in(agentOrderTypes)
.and("agentOrderStatus").in(Arrays.asList(AgentOrderStatusEnum.NOT_START.status, AgentOrderStatusEnum.PROCESSING.status))
.and("hopeStartTime").lte(LocalDateTime.now())
.and("notMatchCustomerNum").gt(0)
.andOperator(EmptyUtils.isNotEmpty(areaIds) ? Criteria.where("areaId").in(areaIds) : new Criteria())
);
return secondaryMongoTemplate.findDistinct(query, "agentId", ResAgentOrderMo.class, String.class);
}
4、根据两个字段分组，同时根据某个字段排序
@Override
public List<AgentOrderBcVo> findFirstOrder(String agentId) {
Criteria criteria = new Criteria();
// 排除每日抢领活动
criteria.and("agentOrderType").ne(AgentOrderTypeEnum.DAILY_GRABBING.type);
criteria.and("notMatchCustomerNum").gt(0);
criteria.and("agentId").is(agentId);
criteria.orOperator(
Criteria.where("agentOrderStatus").is(AgentOrderStatusEnum.NOT_START.status),
Criteria.where("agentOrderStatus").is(AgentOrderStatusEnum.PROCESSING.status)
);
List<AggregationOperation> operations = new ArrayList();
operations.add(Aggregation.match(criteria));
Sort sort = Sort.by(Sort.Direction.ASC, "hopeStartTime");
//按照期望开始时间正序
operations.add(Aggregation.sort(sort));
operations.add(Aggregation.group("agentOrderType", "agentOrderDesc")
.first("_id").as("agentOrderId").first("payTime").as("payTime").first("agentOrderType").as("agentOrderType")
.first("agentOrderDesc").as("agentOrderDesc")
.first("agentName").as("agentName").first("phoneNumber").as("phoneNumber").first("buyCustomerNum").as("buyCustomerNum")
.first("giveCustomerNum").as("giveCustomerNum").first("couponCustomerNum").as("couponCustomerNum").first("payType").as("payType")
.first("payPrice").as("payPrice").first("agentOrderStatus").as("agentOrderStatus").first("areaName").as("areaName")
.first("hopeCity").as("hopeCity").first("hopeStartTime").as("hopeStartTime").first("totalCustomerNum").as("totalCustomerNum")
.first("notMatchCustomerNum").as("notMatchCustomerNum")
);
sort = Sort.by(Sort.Direction.DESC, "notMatchCustomerNum");
//按累计未分配客户数量排序
operations.add(Aggregation.sort(sort));
Aggregation aggregation = Aggregation.newAggregation(operations);
AggregationResults<AgentOrderBcVo> aggregate = secondaryMongoTemplate.aggregate(aggregation, "agentOrderMo", AgentOrderBcVo.class);
return aggregate.getMappedResults();
}
其中first用法是，分组时对于多条数据取第一条的数据的相关字段
5、根据某个字段分组，对某个字段求和以后，然后再筛选，类似于mysql的 select type, sum(price) as count from table group by type having count >10
@Override
public List<String> filterByWeekAndMonthAndDay(String target, List<String> businessModels, String pastMonth, int monthNum,int weekNum,int dayNum,List<String> agentIds) {
Aggregation aggregation = newAggregation(
Aggregation.match(where("businessModel").in(businessModels)
.and("pastMonth").is(pastMonth)
.and("target").is(target)
.and("agentId").in(agentIds)
.and("isDeleted").is(false)),
Aggregation.group("agentId").first("agentId").as("agentId").sum("thisMonthNum").as("monthMatchSumNum")
.sum("thisWeekNum").as("weekMatchSumNum").sum("todayNum").as("dayMatchSumNum"),
Aggregation.match(where("monthMatchSumNum").lt(monthNum).and("weekMatchSumNum").lt(weekNum).and("dayMatchSumNum").lt(dayNum))
);
AggregationResults<AgentIdAndMatchNumBean> aggregate = secondaryMongoTemplate.aggregate(aggregation, ResDacMatchAgentWeightMo.class, AgentIdAndMatchNumBean.class);
if (EmptyUtils.isNotEmpty(aggregate)) {
List<AgentIdAndMatchNumBean> results = aggregate.getMappedResults();
return results.stream().map(AgentIdAndMatchNumBean::getAgentId).collect(Collectors.toList());
}
return null;
}
6、match, group,sort,limit一起使用
@Override
public List<AgentWeightBean> findSortByAgentIds(List<String> agentIds, List<String> targets, List<String> targetExtras, String businessModel, String pastMonth, int limit) {
Aggregation aggr = newAggregation(
match(where("agentId").in(agentIds)
.and("businessModel").is(businessModel)
.and("pastMonth").is(pastMonth)
.orOperator(
EmptyUtils.isNotEmpty(targets) ? Criteria.where("target").in(targets) : Criteria.where("target").is(""),
EmptyUtils.isNotEmpty(targetExtras) ? Criteria.where("targetExtra").in(targetExtras) : Criteria.where("targetExtra").is("")
)
.and("isDeleted").is(false)
),
group("agentId").first("agentId").as("agentId").sum("weight").as("weight"),
sort(Sort.by(Sort.Direction.DESC,"weight")),
limit(limit)
);
AggregationResults<AgentWeightBean> results = secondaryMongoTemplate.aggregate(aggr, ResDacMatchAgentWeightMo.class,AgentWeightBean.class);
return results.getMappedResults();
}
7、更新操作
@Override
public void updateTarget(String agentId, List<String> targets, List<String> targetExtras, String businessModel, String pastMonth) {
Query query = query(where("agentId").is(agentId)
.and("businessModel").is(businessModel)
.and("pastMonth").is(pastMonth)
.orOperator(
EmptyUtils.isNotEmpty(targets) ? Criteria.where("target").in(targets) : Criteria.where("target").is(""),
EmptyUtils.isNotEmpty(targetExtras) ? Criteria.where("targetExtra").in(targetExtras) : Criteria.where("targetExtra").is("")
)
.and("isDeleted").is(false)
);
Update update = update("updateTime",LocalDateTime.now())
//inc是自增操作，原子性操作
.inc("todayNum",1)
.inc("thisWeekNum",1)
.inc("twoWeekNum",1)
.inc("thisMonthNum",1)
.inc("weight",-0.08);
secondaryMongoTemplate.updateMulti(query,update,ResDacMatchAgentWeightMo.class);
}
8、聚合查询时对多个字段进行运算符操作，比如 sum,divide
@Override
public List<ReportAgentBean> getReportDataByAId(String aId, String aIdType, String reportType, String reportDate) {
Query query = new Query();
Criteria criteria = new Criteria();
criteria.and("aIdType").is(aIdType);
criteria.and("reportType").is(reportType);
criteria.and("aId").is(aId);
criteria.and("reportDate").is(reportDate);
query.addCriteria(criteria);
Aggregation aggregation = newAggregation(
//project表示需要用到那些字段，返回那些字段
Aggregation.project(PROJECT_FIELDS)
//总标化保费
.and(this.totalCriterionAmountExpression()).as("totalCriterionAmount")
//成交服务单均保费
.and(this.averageIssueAmountExpression()).as("averageIssueAmount")
//件均保费
.and(this.averageAmountExpression()).as("averageAmount")
.and(this.totalAmountExpression()).as("totalAmount")
.and(this.pmAgentNumRateExpression()).as("pmAgentNumRate")
,
Aggregation.match(criteria),
Aggregation.sort(Sort.by(Sort.Direction.DESC, "totalCriterionAmount"))
);
AggregationResults<ReportAgentBean> results = mongoTemplate.aggregate(aggregation, ReportAgentDataMo.class, ReportAgentBean.class);
return results.getMappedResults();
}
private static final String[] PROJECT_FIELDS = {"id", "aIdType", "aId", "reportDate", "reportType", "advanceCriterionAmountDacT", "advanceNumDacT",
"advanceAmountDacT", "buyCustDacT", "giveCustDacT", "matchDacT", "upToStandardNum",
"pmDacT", "nonPmDacT", "policyConfirmNumDacT", "policyConfirmOrderNumDacT",
"upToStandardAgentNum", "dealInternetAmountDacT", "dealLifeAmountDacT",
"dealLifeCriterionAmountDacT", "dealHighAmountDacT", "dealCarAmountDacT", "dealGroupAmountDacT",
"dealHighCriterionAmountDacT", "dealCarCriterionAmountDacT", "dealGroupCriterionAmountDacT", "digitalAgentNum",
"dealInternetNumDacT", "dealLifeNumDacT", "dealHighNumDacT", "dealCarNumDacT", "cashCoupon", "cashCouponAmount",
"dealGroupNumDacT", "createTime"};
//totalCriterionAmountExpression这个方法返回的数据5个字段相加后的结果
private AggregationExpression totalCriterionAmountExpression() {
return ArithmeticOperators.Add.valueOf("dealInternetAmountDacT")
.add("dealHighCriterionAmountDacT")
.add("dealCarCriterionAmountDacT")
.add("dealGroupCriterionAmountDacT")
.add("dealLifeCriterionAmountDacT");
}
//averageIssueAmountExpression这个方法会拿到totalCriterionAmountExpression方法的返回值然后和policyConfirmNumDacT字段相除
private AggregationExpression averageIssueAmountExpression() {
AggregationExpression totalCriterionAmount = this.totalCriterionAmountExpression();
return ConditionalOperators.when(new Criteria("policyConfirmNumDacT").ne(0))
.thenValueOf(ArithmeticOperators.Divide.valueOf(totalCriterionAmount).divideBy("policyConfirmNumDacT"))
.otherwise(0);
}
9、聚合查询时的分页查询
@Override
public List<ReportAgentBean> digitalAgentQuery(PageMsg pageMsg) {
Criteria criteria = new Criteria();
criteria.and("aIdType").is(AIdTypeEnum.agent.name());
criteria.and("reportType").is(ReportTypeEnum.TOTAL.type);
//累计服务单数 大于等于30
criteria.and("digitalSvNum").gte(DigitalLevelEnum.digital_1.matchDacT);
//标化总保费大于 5W
criteria.and("digitalPolicyPerformance").gte(DigitalLevelEnum.digital_1.totalCriterionAmount);
//累计成交订单数 大于5
criteria.and("digitalTurnoverPerformance").gte(DigitalLevelEnum.digital_1.policyConfirmNumDacT);
Aggregation aggregation = newAggregation(
Aggregation.match(criteria),
Aggregation.project(DIGITAL_FIELDS)
//总标化保费
.and("digitalPolicyPerformance").as("totalCriterionAmount")
.and("digitalSvNum").as("matchDacT")
.and("digitalTurnoverPerformance").as("policyConfirmNumDacT")
,
Aggregation.sort(Sort.by(Sort.Direction.DESC, "totalCriterionAmount")),
Aggregation.skip((long) (pageMsg.getPageNum() - 1) * pageMsg.getPageSize()),
Aggregation.limit(pageMsg.getPageSize())
);
AggregationResults<ReportAgentBean> results = mongoTemplate.aggregate(aggregation, ReportAgentDataMo.class, ReportAgentBean.class);
return results.getMappedResults();
}
