停车入场城市排行榜1

需要做一个分析景区入场车辆城市的分布数据统计，一期就是吧所有数据都统计一遍， 因为数据量比较小

数据结构如下

ch_id(主键1--作用不打), project_id(主键2--查询条件), ch_plate(车牌号：如鲁B222), ch_crosstime（入场时间）, ch_out（出场时间）， park_id(主键3--project下的不同停车场)

1 52ef2a2cba694dcf9931e118a73fb389 临LS3251 2018-07-27 21:16:54 370211009
10 52ef2a2cba694dcf9931e118a73fb389 鲁MJ770H 2018-07-28 05:19:46 370211009
10 5b7e681775364fa8a549e4781000f4ac 鲁BUW256 2018-01-23 15:35:07 3
100 5b7e681775364fa8a549e4781000f4ac 鲁B6C8D0 2018-01-24 11:50:25 3
1000 52ef2a2cba694dcf9931e118a73fb389 鲁B1E7D1 2018-07-25 16:50:02 370211005
1000 52ef2a2cba694dcf9931e118a73fb389 鲁B9F73E 2018-07-24 14:32:37 370211006
1000 52ef2a2cba694dcf9931e118a73fb389 鲁U80806 2018-07-27 19:33:49 370211007
1000 52ef2a2cba694dcf9931e118a73fb389 鲁B86DG5 2018-08-10 21:06:58 370211008
1000 52ef2a2cba694dcf9931e118a73fb389 粤J2018领 2018-07-31 17:07:04 370211009
1000 5b7e681775364fa8a549e4781000f4ac 鲁B87KZ6 2018-02-12 07:54:45 3
10000 52ef2a2cba694dcf9931e118a73fb389 鲁BK6G66 2018-07-31 10:11:02 370211004
10000 52ef2a2cba694dcf9931e118a73fb389 豫A004NW 2018-08-04 14:59:24 370211006
10000 5b7e681775364fa8a549e4781000f4ac 鲁BF58T2 2018-04-30 15:24:15 3
10000_2 52ef2a2cba694dcf9931e118a73fb389 鲁BK6G66 2018-07-31 11:43:04 370211004
10000_2 52ef2a2cba694dcf9931e118a73fb389 豫A004NW 2018-08-04 16:16:57 370211006
10001 52ef2a2cba694dcf9931e118a73fb389 苏E356VD 2018-07-24 14:47:16 370211003
10001 52ef2a2cba694dcf9931e118a73fb389 鲁BKF916 2018-07-31 10:11:13 370211004

 

表2 车牌对应的省市表

id code city province

419 冀A 石家庄 河北
420 冀B 唐山 河北
421 冀C 秦皇岛 河北
422 冀D 邯郸 河北
423 冀E 邢台 河北
424 冀F 保定 河北
425 冀G 张家口 河北
426 冀H 承德 河北
427 冀J 沧州 河北
428 冀R 廊坊 河北

 

    @Transactional
    public void getCityTop() {
        LOGGER.info("start getCityTop");
        String projectId = "xxxxxxxxxx";
        List<CarNumAndCount> carNumAndCountList = getCarNumAndCountList(2,projectId);
        if (carNumAndCountList.size() > 0) {
            Integer totalFlux = 0;// 查询的总记录数， 要做分母
            /*
            @MapKey("code")
            Map<String, Carnum> getAll();
            
            select 
            code, city,province
            from carnum
            */
            Map<String, Carnum> carNumMap = carnumMapper.getAll();

            Map<String, Integer> topCityMap = new TreeMap<String, Integer>();
            for (CarNumAndCount carNumAndCount : carNumAndCountList) {
                totalFlux += carNumAndCount.getCarCount();
                // 按照车牌查询城市，如果对应表中不能获得数据， 那么就放置到其他车牌中
                
                String city = "其他";
                Carnum carnum = carNumMap.get(carNumAndCount.getCarPrefix());//从车牌城市对照表中获取车牌实体
                if(carnum != null) {
                    city = carnum.getCity();
                }

                Integer currentCityNum = topCityMap.get(city);//获取已经存了的，当前同一个城市的车牌数量
                if(currentCityNum == null) {
                    currentCityNum = 0;
                }
                Integer currentPaiNum = carNumAndCount.getCarCount();//当前循环中这个前缀的车牌数量
                
                currentCityNum += currentPaiNum;
                topCityMap.put(city, currentCityNum);
            }
            System.out.println(totalFlux);

            
            Map<Integer, Set<String>> sortMap = new TreeMap<>(new Comparator<Integer>() {

                @Override
                public int compare(Integer o1, Integer o2) {
                    return o2.compareTo(o1);
                }
            });

            for (String keyCity : topCityMap.keySet()) {
                // 获取当前城市对应的数字
                Integer sortMapKey = topCityMap.get(keyCity);
                Set<String> sortMapValueSet = new TreeSet<>(new Comparator<String>() {
                    @Override
                    public int compare(String o1, String o2) {
                        return CHINA_COMPARE.compare(o1, o2);
                    }
                });// 初始化城市的一个排序序列
                    // 如果当前已经存在这个车流量数字
                if (sortMap.containsKey(sortMapKey)) {
                    sortMapValueSet = sortMap.get(sortMapKey);// 取出当前的城市set类型数据
                }

                sortMapValueSet.add(keyCity);// 把当前循环的这个城市也加入到set类型中

                sortMap.put(sortMapKey, sortMapValueSet);// 重新赋值到map中
            }
            String currentDataTimeString = DateUtils.getCurrentDateTime();
            int c = 1;
            List<Top> tops = new ArrayList<>();
            for (Integer sortKey : sortMap.keySet()) {
                Set<String> vSet = sortMap.get(sortKey);
                // System.out.println(vSet);
                for (String finalCity : vSet) {
                    // System.out.println(finalCity);
                    Top tmpTop = new Top();
                    tmpTop.setCity(finalCity);

                    BigDecimal bignum1 = new BigDecimal(sortKey.toString());
                    BigDecimal bignum2 = new BigDecimal(totalFlux.toString());
                    BigDecimal proportion = bignum1.divide(bignum2, 6, BigDecimal.ROUND_HALF_UP);
                    // proportion = proportion.multiply(new BigDecimal("100"));
                    tmpTop.setProportion(proportion.toPlainString());

                    tmpTop.setRecordTime(currentDataTimeString);
                    tmpTop.setTop(c);
                    tops.add(tmpTop);
                    c++;
                }
            }
            
            topMapper.deleteAll();
            topMapper.insertfor(tops);
        } else {
            topMapper.deleteAll();
        }
    }
    

    
    private List<CarNumAndCount> getCarNumAndCountList(int substringCount, String projectId){
        Map<String, Object> carNumAndCountListMap = new HashMap<>();
        carNumAndCountListMap.put("substringCount", substringCount);
        carNumAndCountListMap.put("projectId", env.getProperty("project.id"));

        /*    
        select
         SUBSTRING(a.ch_plate,1,${substringCount}) as carPrefix, 
         COUNT(a.ch_plate) as carCount
        from pms_crosshistory a
        where project_id = #{projectId,jdbcType=VARCHAR}
             and ch_out = 0
        group by carPrefix
        */
        List<CarNumAndCount> carNumAndCountList = pmsCrosshistoryMapper
                .selectCarNumAndCount(carNumAndCountListMap);
        
        return carNumAndCountList;
    }