From 369454bcdbdd0c6fb0fa7ac6ec19112b9b6a179e Mon Sep 17 00:00:00 2001
From: fengxiang <110431245@qq.com>
Date: Mon, 26 Mar 2018 14:03:12 +0800
Subject: [PATCH] 数据查询 sql查询优化，一次带出 多项数据
---
src/main/java/com/moral/service/HistoryMinutelyService.java | 2
src/main/java/com/moral/mapper/HistoryMinutelyMapper.java | 10 ++++
src/main/resources/mapper/HistoryMinutelyMapper.xml | 13 ++++--
src/main/java/com/moral/controller/ReportController.java | 2
src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java | 91 ++++++++++++++++++++++-----------------------
5 files changed, 65 insertions(+), 53 deletions(-)
diff --git a/src/main/java/com/moral/controller/ReportController.java b/src/main/java/com/moral/controller/ReportController.java
index d5dd547..348dfa0 100644
--- a/src/main/java/com/moral/controller/ReportController.java
+++ b/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")
diff --git a/src/main/java/com/moral/mapper/HistoryMinutelyMapper.java b/src/main/java/com/moral/mapper/HistoryMinutelyMapper.java
index 7149776..ee03fe0 100644
--- a/src/main/java/com/moral/mapper/HistoryMinutelyMapper.java
+++ b/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);
}
\ No newline at end of file
diff --git a/src/main/java/com/moral/service/HistoryMinutelyService.java b/src/main/java/com/moral/service/HistoryMinutelyService.java
index 6fee90d..f1dacc2 100644
--- a/src/main/java/com/moral/service/HistoryMinutelyService.java
+++ b/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);
}
diff --git a/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java b/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java
index f29602a..a35f50d 100644
--- a/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java
+++ b/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();
- List<DataCondition> dataConditionList = lineChartCriteria.getDataConditions();
- List<List<PairData>> list = new ArrayList<>();
- dataConditionList.forEach(item -> {
- List<PairData> pairDataList = queryOneLineChartDateByCrieria(sensorKey,timePeriod,item);
- list.add(pairDataList);
- });
- listMap.put(sensorKey,list);
- }
-
+ 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();
+ TimePeriod timePeriod = lineChartCriteria.getTimePeriod();
+ sensorKeys.forEach(sensorKey -> {
+ listMap.put(sensorKey,new ArrayList<List<Double>>(dataConditionList.size()));
+ });
+ 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){
+ 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();
- if(time.equals(keyTime)){
- lineChartDatasWithEmpty.add(pairData);
- m = m<dataLength ? m+1 : -1;
- }else{
- lineChartDatasWithEmpty.add(generateEmptyData(time));
- }
- }else {
- lineChartDatasWithEmpty.add(generateEmptyData(time));
- }
-
+ if(m>-1) {
+ String time = timeList.get(n);
+ Map<String,Object> rowData = lineChartDatas.get(m);
+ String keyTime = rowData.get("format_time").toString();
+ if(time.equals(keyTime)){
+ // 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{
- 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);
- });
}
}
diff --git a/src/main/resources/mapper/HistoryMinutelyMapper.xml b/src/main/resources/mapper/HistoryMinutelyMapper.xml
index 797834d..148f6a4 100644
--- a/src/main/resources/mapper/HistoryMinutelyMapper.xml
+++ b/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
--
Gitblit v1.8.0