项目中用sql实现一个导购下所拥有客户的订单总数，返回订单数和导购id

有个bug是关于图表数据统计，导购数据没问题，部门数据计算不正确
因为数据按照一个导购来算，所以设计上把一个导购存入redis一个hash key，取部门的情况下需要批量取hash中的key，金额公式大致为导购下所有客户的订单金额/订单数，因为取出来的是计算好的结果，所以为了算部门下所有导购，需要做乘订单求和再除总订单的情况
总关系如下，即部门-导购-员工-订单

关键在于agent_id作为导购id，旗下客户为member_id，则可以得到下面的sql，agent_id分组，则能算出旗下订单数

SELECT a.agent_id , count(o.member_id) as orderCount
FROM orders o 
INNER JOIN  
(
	SELECT member_id,agent_id from member_analysis WHERE agent_id in (12887,13083)
) a on o.member_id = a.member_id
WHERE o.member_id 
in(
SELECT member_id
FROM `member_analysis`
where agent_id in (12887,13083)
)
and pay_status='PAY_FINISH'
GROUP BY agent_id

mapper，用到的两个in全部传自同一个传参，注意进方法前一定要判空，这里mybatis没做判空处理

@Select("<script>" +
            "SELECT a.agent_id , count(o.member_id) as orderCount\n" +
            "FROM orders o \n" +
            "INNER JOIN  \n" +
            "(" +
            "SELECT member_id,agent_id from member_analysis WHERE agent_id in <foreach item='item' index='index' collection='agentIds' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            ") a on o.member_id = a.member_id\n" +
            "WHERE o.member_id \n" +
            "in(" +
            "SELECT member_id\n" +
            "FROM `member_analysis`\n" +
            "where agent_id in <foreach item='item' index='index' collection='agentIds' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            ")" +
            "and pay_status='PAY_FINISH'\n" +
            "GROUP BY agent_id" +
            "</script>")
    List<MemberCountAndAgentVO> getOrderCountWithAgentId(@Param("agentIds") List<String> agentIds);

service使用
先判空，拿到list

List<MemberCountAndAgentVO> orderCountAndAgentlist = new ArrayList<>();
if (ObjectUtil.isNotEmpty(memberIds)){
	orderCountAndAgentlist = memberAnalysisMapper.getOrderCountWithAgentId(memberIds);
}

聚合成map，key为导购id，value为订单数量，算出总订单数作为分母

Map<String, Object> orderCountAndAgentMap = orderCountAndAgentlist.stream().collect(Collectors.toMap(MemberCountAndAgentVO::getAgentId, MemberCountAndAgentVO::getOrderCount, (k1, k2) -> k1));
Integer orderCount = orderCountAndAgentlist.stream().collect(Collectors.summingInt(MemberCountAndAgentVO::getOrderCount));
orderCount = orderCount == 0 ? 1 : orderCount;

进循环，遍历累加，先判断传进来的实体类id要一一对应

if (orderCountAndAgentMap.containsKey(portraitVO.getMemberId())){
	Integer orderOldCount = Integer.parseInt(orderCountAndAgentMap.get(portraitVO.getMemberId()).toString());
	customerUnitPriceDouble = Double.parseDouble(portraitVO.getCustomerUnitPrice()) * orderOldCount;
}
customerUnitPrice = customerUnitPrice.add(BigDecimal.valueOf(customerUnitPriceDouble));

在循环外做除法

customerUnitPrice = customerUnitPrice.divide(BigDecimal.valueOf(orderCount),4,BigDecimal.ROUND_HALF_UP);

最终返回结果

计算出来是正确的，完美