数据查询 sql查询优化，一次带出 多项数据

fengxiang

2018-03-26 369454bcdbdd0c6fb0fa7ac6ec19112b9b6a179e

数据查询 sql查询优化，一次带出 多项数据

 src/main/java/com/moral/controller/ReportController.java

@@ -35,7 +35,7 @@
        return new ResultBean<Map<String,List<Object>>>(demo);

    }

    @PostMapping("line-chart")

    public ResultBean <Map<String, List<List<PairData>>>> lineChart(@RequestBody LineChartCriteria lineChartCriteria){

    public ResultBean <Map<String, List<List<Double>>>> lineChart(@RequestBody LineChartCriteria lineChartCriteria){

        return  new ResultBean<>(historyMinutelyService.queryLineChartDateByCrieria(lineChartCriteria));

    }

    @GetMapping("excel")


 src/main/java/com/moral/mapper/HistoryMinutelyMapper.java

@@ -13,5 +13,13 @@
    Map<String, Double> getSersionAvgByDevice(Map<String, Object> parameters);



    List<Map<String, Object>> getMonitorPointOrDeviceAvgData(Map<String, Object> parameters);

    List<PairData> selectLineChartDateByCrieria(@Param("sensorKey") String sensorKey, @Param("timePeriod") TimePeriod timePeriod, @Param("dataCondition") DataCondition dataCondition);



    /**

     *

     * @param sensorKeys

     * @param timePeriod

     * @param dataCondition

     * @return {format_time:string,e1:double,e2...............}

     */

    List<Map<String, Object>> selectLineChartDateByCrieria(@Param("sensorKeys")List<String> sensorKeys, @Param("timePeriod") TimePeriod timePeriod, @Param("dataCondition") DataCondition dataCondition);

}

 src/main/java/com/moral/service/HistoryMinutelyService.java

@@ -17,5 +17,5 @@
    Map<String, Object> getMonthAverageBySensor(Map<String, Object> parameters);





    Map<String, List<List<PairData>>> queryLineChartDateByCrieria(LineChartCriteria lineChartCriteria);

    Map<String, List<List<Double>>> queryLineChartDateByCrieria(LineChartCriteria lineChartCriteria);

}


 src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java

@@ -26,6 +26,7 @@
import java.util.concurrent.ExecutorCompletionService;

import java.util.concurrent.ExecutorService;

import java.util.concurrent.Executors;

import java.util.stream.Collectors;



import javax.annotation.Resource;



@@ -456,68 +457,66 @@
     * @return

     */

    @Override

    public Map<String, List<List<PairData>>> queryLineChartDateByCrieria(LineChartCriteria lineChartCriteria){

        Map<String,List<List<PairData>>> listMap = new HashMap<>();

        for(String sensorKey:lineChartCriteria.getSensorKeys()){

            TimePeriod timePeriod = lineChartCriteria.getTimePeriod();

    public Map<String, List<List<Double>>> queryLineChartDateByCrieria(LineChartCriteria lineChartCriteria){

        Map<String,List<List<Double>>> listMap = new HashMap<>();

        List<String> sensorKeys = lineChartCriteria.getSensorKeys();

            List<DataCondition> dataConditionList = lineChartCriteria.getDataConditions();

            List<List<PairData>> list =  new ArrayList<>();

            dataConditionList.forEach(item -> {

                List<PairData> pairDataList =  queryOneLineChartDateByCrieria(sensorKey,timePeriod,item);

                list.add(pairDataList);

        TimePeriod timePeriod = lineChartCriteria.getTimePeriod();

        sensorKeys.forEach(sensorKey -> {

            listMap.put(sensorKey,new ArrayList<List<Double>>(dataConditionList.size()));

            });

            listMap.put(sensorKey,list);

        }



        dataConditionList.forEach(dataCondition -> {

            Map<String,List<Double>> dataMap =  queryOneLineChartDateByCrieria(sensorKeys,timePeriod,dataCondition);

            // 数据装载

            listMap.forEach( (sensorKey,list) -> {

                List<Double> rowData = dataMap.get(sensorKey);

                list.add(rowData);

            });

        });

        return  listMap;

    }



    /**

     * 根据线性表单的条件规则，获取一条线性表单数据

     * @param sensorKey

     * @param sensorKeys

     * @param timePeriod

     * @param dataCondition

     * @return

     */

    public List<PairData> queryOneLineChartDateByCrieria(String sensorKey, TimePeriod timePeriod, DataCondition dataCondition){

    public Map<String,List<Double>> queryOneLineChartDateByCrieria(List<String> sensorKeys, TimePeriod timePeriod, DataCondition dataCondition){

        List<String> timeList = ReportTimeFormat.makeTimeList(timePeriod);

        List<PairData> lineChartDatas = historyMinutelyMapper.selectLineChartDateByCrieria(sensorKey,timePeriod,dataCondition);

        List<PairData> lineChartDatasWithEmpty = new ArrayList<>();

        List<Map<String,Object>> lineChartDatas = historyMinutelyMapper.selectLineChartDateByCrieria(sensorKeys,timePeriod,dataCondition);

        Map<String,List<Double>> lineChartDatasWithEmpty = new HashMap<>();

        // lineChartDatasWithEmpty 初始化

        sensorKeys.forEach(sensorKey -> {

            lineChartDatasWithEmpty.put(sensorKey,timeList.stream().map(time -> {

                Double data = null;

                return data;

            }).collect(Collectors.toList()));

        });

        // m 为查询data的index，此处要防止m越界

        int m = 0;

        int dataLength = lineChartDatas.size()-1;

        m = dataLength>-1?0:-1;

        if(m>-1){

            for(int n =0;n<timeList.size();n++){

                String time = timeList.get(n);

                if(m>-1){

                    PairData pairData = lineChartDatas.get(m);

                    String keyTime = pairData.getCategory();

                     String time = timeList.get(n);

                     Map<String,Object> rowData = lineChartDatas.get(m);

                     String keyTime = rowData.get("format_time").toString();

                    if(time.equals(keyTime)){

                        lineChartDatasWithEmpty.add(pairData);

                         // list to map

                         int finalN = n;

                         sensorKeys.forEach(sensorKey -> {

                             Double sensorValue = rowData.get(sensorKey)!= null?new Double(rowData.get(sensorKey).toString()):null;

                             lineChartDatasWithEmpty.get(sensorKey).set(finalN,sensorValue);

                         });

                         // 置为 -1，防止越界

                        m = m<dataLength ? m+1 : -1;

                    }else{

                        lineChartDatasWithEmpty.add(generateEmptyData(time));

                    }

                }else {

                        lineChartDatasWithEmpty.add(generateEmptyData(time));

                }



            }

        }else{

            fillEmptyDataToList(timeList,lineChartDatasWithEmpty);

        }

        return  lineChartDatasWithEmpty;

    }

    private PairData generateEmptyData(String time) {

        PairData pairData = new PairData();

        pairData.setCategory(time);

        pairData.setValue(null);

        return pairData;

    }

    private void fillEmptyDataToList(List<String> timeList,List<PairData> lineChartDatasWithEmpty){

        timeList.forEach( time ->{

            PairData pairData = generateEmptyData(time);

            lineChartDatasWithEmpty.add(pairData);

        });

    }

}


 src/main/resources/mapper/HistoryMinutelyMapper.xml

@@ -41,17 +41,22 @@
        ORDER BY

            time

    </select>

    <select id="selectLineChartDateByCrieria" resultMap="PairDataResultMap">

    <select id="selectLineChartDateByCrieria" resultType="java.util.Map">

            SELECT

                <choose>

                    <when test="'MINUTE'==dataCondition.timeUnits.toString() and 'DEVICE'==dataCondition.areaRange.toString()">

                        json->'$.${sensorKey}[0]'

                        <foreach collection="sensorKeys"

                                 item="sensorKey" separator="," close=",">

                                 json->'$.${sensorKey}[0]' as '${sensorKey}'

                        </foreach>

                    </when>

                    <otherwise>

                        AVG(json->'$.${sensorKey}[0]')

                        <foreach collection="sensorKeys"

                                 item="sensorKey" separator="," close=",">

                            AVG(json->'$.${sensorKey}[0]') as '${sensorKey}'

                        </foreach>

                    </otherwise>

                </choose>

              as value,

             DATE_FORMAT(time,'${@com.moral.common.util.ReportTimeFormat@toMySqlTimeFormat(timePeriod.timeUnits)}') as format_time

             FROM

            `history_minutely` hmi