0058、按月分组查询近12个月的数据（性能优化）

优化思路：

1）原功能逻辑为for循环12次，将年月做为参数去数据库查询12次数据

2）优化思路：尽量将数据一次性从数据库中查询出来，通过java进行逻辑处理，减少和数据库交互次数，提升性能

java方法如下：

public CompanyCountDTO getCompanyCount() {
    long start1 = System.currentTimeMillis();
    List<CountDTO> countList = new ArrayList<>();
    CompanyCountDTO dto = new CompanyCountDTO();
    Calendar instance = Calendar.getInstance();
    int year = instance.get(Calendar.YEAR);
    int month = instance.get(Calendar.MONTH) + 1;

    int y = year - 1;
    int m = month + 1;
    //初始化12个月点日期
    if (m > 12) {
        y = y + 1;
        m = m - 12;
    }
    for (int i = 0; i < 12; i++) {
        CountDTO counDto = new CountDTO();
        String data = String.valueOf(m) + "月";
        counDto.setDate(data);
        counDto.setCount(companyMapper.selectCountCompanycode(m, y));
        countList.add(counDto);
        m++;
        if (m > 12) {
            y++;
            m -= 12;
        }

    }
    long end1 = System.currentTimeMillis();
    long cost1 = end1 -start1;
    log.info("方式1查询耗时==========================================：" + cost1);
    int y2 = year - 1;
    int m2 = month + 1;
    //初始化12个月点日期
    if (m2 > 12) {
        y2 = y2 + 1;
        m2 = m2 - 12;
    }
    String m2Str = m2<10?("0"+m2):(""+m2);
    // 从ec_order表查询近12月的全部数据
    List<MemberIdAndMonthDTO> memberIdAndMonthDTOList = companyMapper.selectMemberIdFor12Months(y2 + "" + m2Str);
    //取出memberId并去重形成集合
    List<String> memberIds = memberIdAndMonthDTOList.stream().map(e -> e.getMemberId()).distinct().collect(Collectors.toList());
    List<CompanyCodeDTO> companyCodeDTOList = null;
    if(org.apache.commons.collections.CollectionUtils.isNotEmpty(memberIds)){
        companyCodeDTOList = companyMapper.selectDistinctCompanyByAccountVip(memberIds);
    }
    //按月分组
    Map<String, List<MemberIdAndMonthDTO>> monthDTOMap = memberIdAndMonthDTOList.stream().collect(Collectors.groupingBy(e -> e.getOrderMonth()));
    String tmpYearMonth = "";
    List<CountDTO> countList2 = new ArrayList<>();
    for (int i = 0; i < 12; i++) {
        tmpYearMonth = y2 + "" + (m2<10?("0"+m2):(""+m2));
        CountDTO counDto = new CountDTO();
        String data = String.valueOf(m2) + "月";
        counDto.setDate(data);
        List<MemberIdAndMonthDTO> memberIdAndMonthDTOS = monthDTOMap.get(tmpYearMonth);
        List<String> memberIdList = new ArrayList<>();
        if(org.apache.commons.collections.CollectionUtils.isNotEmpty(memberIdAndMonthDTOS)){
            memberIdList = memberIdAndMonthDTOS.stream().map(e->e.getMemberId()).collect(Collectors.toList());
        }
        final List<String> memberIdListFinal = memberIdList;
        long count = 0;
        if(org.apache.commons.collections.CollectionUtils.isNotEmpty(memberIdList) && org.apache.commons.collections.CollectionUtils.isNotEmpty(companyCodeDTOList)){
            //由于可能存在两条数据，companyCode+pCompanyCode相同，但是accountVip不同，此处统计时，应该没统计为一条数据
            count = companyCodeDTOList.stream().filter(e -> memberIdListFinal.contains(e.getAccountVip()))
                    .collect(Collectors.collectingAndThen(
                            Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(o -> o.getCompanyCode() + ";" + o.getPCompanyCode()))), ArrayList::new))
                    .size();
        }
        counDto.setCount((int) count);
        countList2.add(counDto);
        m2++;
        if (m2 > 12) {
            y2++;
            m2 -= 12;
        }

    }

    long end2 = System.currentTimeMillis();
    long cost2 = end2 -end1;
    log.info("方式2查询耗时=======================================：" + cost2);
    log.info("方式1查询到的数据为=================================：");
    for (CountDTO countDTO : countList) {
        log.info("月份：" +countDTO.getDate() + "，数量：" + countDTO.getCount());
    }
    log.info("方式2查询到的数据为=================================：");
    for (CountDTO countDTO : countList2) {
        log.info("月份：" +countDTO.getDate() + "，数量：" + countDTO.getCount());
    }



    dto.setCountList(countList);
    return dto;
}

 

用到的sql语句如下：

1）

<select id="selectCountCompanycode" resultType="java.lang.Integer">
   select count(*)
   from
   (select distinct COMPANY_CODE,PCOMPANY_CODE
    from ESGCC_DBUSER.EC_TRAVEL_VIP_INFO
    where   ACCOUNT_VIP in
    (select MEMBER_ID
    from ESGCC_DBUSER.EC_ORDER
    where
    TO_CHAR(ORDER_TIME,'yyyy')=#{year}
    and TO_CHAR(ORDER_TIME,'mm')=#{month}))

</select>

2）

<select id="selectMemberIdFor12Months" resultType="com.dingxianginc.audit.pojo.oracle.purchase.zcompanycode.dto.MemberIdAndMonthDTO">
    select MEMBER_ID memberId,TO_CHAR(ORDER_TIME,'yyyyMM') orderMonth
    from ESGCC_DBUSER.EC_ORDER
    where
    TO_CHAR(ORDER_TIME,'yyyyMM') <![CDATA[ >= ]]> #{yearMonth}
</select>

3）

<select id="selectDistinctCompanyByAccountVip" resultType="com.dingxianginc.audit.pojo.oracle.purchase.zcompanycode.dto.CompanyCodeDTO">
    select  COMPANY_CODE companyCode,PCOMPANY_CODE pCompanyCode,ACCOUNT_VIP accountVip
    from ESGCC_DBUSER.EC_TRAVEL_VIP_INFO
    where   ACCOUNT_VIP in
    <foreach collection="memberIds" item="item" open="(" close=")" separator=",">
        #{item}
    </foreach>
</select>