From f22a631422f3045a06aad627e6e83c802be2760d Mon Sep 17 00:00:00 2001
From: 于紫祥_1901 <email@yuzixiang_1910>
Date: Tue, 01 Dec 2020 11:07:29 +0800
Subject: [PATCH] 站点平均值数据导出
---
src/main/java/com/moral/util/AQICalculation2.java | 19 +-
src/main/resources/mapper/HistoryMapper.xml | 8
src/main/resources/mapper/HistoryHourlyMapper.xml | 17 +-
src/main/java/com/moral/controller/ScreenController.java | 207 +++++++++++++++++++++++++++++++++-
src/main/java/com/moral/service/impl/HistoryHourlyServiceImpl.java | 106 +++++++++++++++--
5 files changed, 315 insertions(+), 42 deletions(-)
diff --git a/src/main/java/com/moral/controller/ScreenController.java b/src/main/java/com/moral/controller/ScreenController.java
index cfe9a84..05b8a46 100644
--- a/src/main/java/com/moral/controller/ScreenController.java
+++ b/src/main/java/com/moral/controller/ScreenController.java
@@ -2718,7 +2718,7 @@
@ApiImplicitParams(value = {
@ApiImplicitParam(name = "monitorPointIds", value = "������monitorid", required = true, paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "time", value = "������(���������2020-03-19)", required = true, paramType = "query", dataType = "String")})
- public ResultBean<List<List<Map<String,Object>>>> monitorAVGData(HttpServletRequest request) throws Exception {
+ public ResultBean<List<Map<String,Object>>> monitorAVGData(HttpServletRequest request) throws Exception {
Map<String, Object> parameters = getParametersStartingWith(request, null);
String monitorPointIds = (String) parameters.get("monitorPointIds");
String time = (String) parameters.get("time");
@@ -2739,10 +2739,20 @@
Map<String,Object> dailyAvgData =new HashMap<>();
if (dataAvbByMIdAndTime.get(dataAvbByMIdAndTime.size() - 2).get("data")!=""){
dailyAvgData = (Map<String, Object>) dataAvbByMIdAndTime.get(dataAvbByMIdAndTime.size() - 2).get("data");
- dailyAvgData.put("maxO3_8h",new BigDecimal(maxO3).setScale(4,BigDecimal.ROUND_HALF_UP).doubleValue());
+ dailyAvgData.put("maxO3_8h",new BigDecimal(maxO3).setScale(0,BigDecimal.ROUND_HALF_UP).doubleValue());
}else {
dailyAvgData= historyHourlyService.getAvgDataByMId(mId, date1, date2);
- dailyAvgData.put("maxO3_8h",new BigDecimal(maxO3).setScale(4,BigDecimal.ROUND_HALF_UP).doubleValue());
+ if (dailyAvgData!=null){
+ dailyAvgData.put("maxO3_8h",new BigDecimal(maxO3).setScale(0,BigDecimal.ROUND_HALF_UP).doubleValue());
+ }else {
+ dailyAvgData = new HashMap<>();
+ dailyAvgData.put("PM2_5","");
+ dailyAvgData.put("NO2","");
+ dailyAvgData.put("SO2","");
+ dailyAvgData.put("PM10","");
+ dailyAvgData.put("O3_day","");
+ dailyAvgData.put("CO","");
+ }
}
dataAvbByMIdAndTime.remove(dataAvbByMIdAndTime.size() - 1);
dataAvbByMIdAndTime.remove(dataAvbByMIdAndTime.size() - 1);
@@ -2753,7 +2763,7 @@
for (int i = 0; i <dataAvbByMIdAndTime.size() ; i++) {
if (i!=dataAvbByMIdAndTime.size()-1){
Map<String, Object> map1 = AQICalculation2.hourlyAQI((Map<String, Object>) dataAvbByMIdAndTime.get(i).get("data"));
- if (Double.parseDouble(map1.get("AQI").toString())>=50.0){
+ if (Double.parseDouble(map1.get("AQI").toString())>50.0){
((Map<String, Object>) dataAvbByMIdAndTime.get(i).get("data")).put("primary_pollutants",map1.get("maxSensor"));
((Map<String, Object>) dataAvbByMIdAndTime.get(i).get("data")).put("AQI",map1.get("AQI"));
}else {
@@ -2762,7 +2772,10 @@
}
}else {
Map<String, Object> map1 = AQICalculation2.dayAQI((Map<String, Object>) dataAvbByMIdAndTime.get(i).get("data"));
- if (Double.parseDouble(map1.get("AQI").toString())>=50.0){
+ if (map1.get("AQI").toString().equals("")){
+ break;
+ }
+ if (Double.parseDouble(map1.get("AQI").toString())>50.0){
((Map<String, Object>) dataAvbByMIdAndTime.get(i).get("data")).put("primary_pollutants",map1.get("maxSensor"));
((Map<String, Object>) dataAvbByMIdAndTime.get(i).get("data")).put("AQI",map1.get("AQI"));
}else {
@@ -2770,6 +2783,74 @@
((Map<String, Object>) dataAvbByMIdAndTime.get(i).get("data")).put("AQI",map1.get("AQI"));
}
}
+ }
+ List timeList = new ArrayList();
+ for (int q = 0; q < dataAvbByMIdAndTime.size(); q++) {
+ timeList.add(dataAvbByMIdAndTime.get(q).get("time"));
+ }
+ if (dataAvbByMIdAndTime.size()<25){
+ for (int j = 1; j < 25; j++) {
+ Map<String,Object> map = null;
+ for (int q = 0; q < dataAvbByMIdAndTime.size(); q++) {
+ if (dataAvbByMIdAndTime.get(q).get("time").toString().length() > 2){
+ continue;
+ }else {
+ // System.out.println(Integer.parseInt(dataAvbByMIdAndTime.get(q).get("time").toString())+"==="+j);
+ String sj="";
+ if (j<10){
+ sj="0"+j;
+ }else {
+ if (j==24){
+ sj="00";
+ }else {
+ sj=""+j;
+ }
+ }
+ if (!timeList.contains(sj)){
+ map = new HashMap<>();
+ map.put("time",sj);
+ Map map1 = new HashMap();
+ map1.put("PM2_5","");
+ map1.put("NO2","");
+ map1.put("primary_pollutants","-");
+ map1.put("SO2","");
+ map1.put("O3","");
+ map1.put("PM10","");
+ map1.put("O3_8h","");
+ map1.put("CO","");
+ map1.put("AQI","");
+ map.put("data",map1);
+ dataAvbByMIdAndTime.add(map);
+ break;
+ }else {
+ break;
+ }
+ }
+ }
+ }
+ }
+ /* for (int i1 = 0; i1 < dataAvbByMIdAndTime.size(); i1++) {
+ System.out.println(dataAvbByMIdAndTime.get(i1));
+ }*/
+ Collections.sort(dataAvbByMIdAndTime, new Comparator<Map<String, Object>>(){
+ public int compare(Map<String, Object> o1, Map<String, Object> o2) {
+ String name1 =(String)o1.get("time");//name1���������list������������������������
+ String name2= (String)o2.get("time"); //name1���������list���������������������������name
+ return name1.compareTo(name2);
+ }
+ });
+ Map map1 = dataAvbByMIdAndTime.get(0);
+ dataAvbByMIdAndTime.remove(0);
+ dataAvbByMIdAndTime.add(map1);
+ for (int j = 0; j <dataAvbByMIdAndTime.size() ; j++) {
+ if (dataAvbByMIdAndTime.get(j).get("time").toString().length()>2){
+ Map map2 = dataAvbByMIdAndTime.get(j);
+ dataAvbByMIdAndTime.remove(j);
+ dataAvbByMIdAndTime.add(map2);
+ }
+ }
+ if (dataAvbByMIdAndTime.size()==1){
+ dataAvbByMIdAndTime=null;
}
lists.add(dataAvbByMIdAndTime);
//List<Device> devicesByMonitorPointId = deviceService.getDevicesByMonitorPointId(Integer.valueOf(mId));
@@ -2780,10 +2861,20 @@
Map<String,Object> dailyAvgData =new HashMap<>();
if (dataAvbByMIdAndTime.get(dataAvbByMIdAndTime.size() - 2).get("data")!=""){
dailyAvgData = (Map<String, Object>) dataAvbByMIdAndTime.get(dataAvbByMIdAndTime.size() - 2).get("data");
- dailyAvgData.put("maxO3_8h",new BigDecimal(maxO3).setScale(4,BigDecimal.ROUND_HALF_UP).doubleValue());
+ dailyAvgData.put("maxO3_8h",new BigDecimal(maxO3).setScale(0,BigDecimal.ROUND_HALF_UP).doubleValue());
}else {
dailyAvgData= historyHourlyService.getAvgDataByMId(mIds[i], date1, date2);
- dailyAvgData.put("maxO3_8h",new BigDecimal(maxO3).setScale(4,BigDecimal.ROUND_HALF_UP).doubleValue());
+ if (dailyAvgData!=null){
+ dailyAvgData.put("maxO3_8h",new BigDecimal(maxO3).setScale(0,BigDecimal.ROUND_HALF_UP).doubleValue());
+ }else {
+ dailyAvgData = new HashMap<>();
+ dailyAvgData.put("PM2_5","");
+ dailyAvgData.put("NO2","");
+ dailyAvgData.put("SO2","");
+ dailyAvgData.put("PM10","");
+ dailyAvgData.put("O3_day","");
+ dailyAvgData.put("CO","");
+ }
}
dataAvbByMIdAndTime.remove(dataAvbByMIdAndTime.size() - 1);
dataAvbByMIdAndTime.remove(dataAvbByMIdAndTime.size() - 1);
@@ -2791,11 +2882,109 @@
dayMap.put("data",dailyAvgData);
dayMap.put("time",date1.substring(0,11));
dataAvbByMIdAndTime.add(dayMap);
+ List timeList = new ArrayList();
+ for (int q = 0; q < dataAvbByMIdAndTime.size(); q++) {
+ timeList.add(dataAvbByMIdAndTime.get(q).get("time"));
+ }
+ if (dataAvbByMIdAndTime.size()<25){
+ for (int j = 1; j < 25; j++) {
+ Map<String,Object> map = null;
+ for (int q = 0; q < dataAvbByMIdAndTime.size(); q++) {
+ if (dataAvbByMIdAndTime.get(q).get("time").toString().length() > 2){
+ continue;
+ }else {
+ // System.out.println(Integer.parseInt(dataAvbByMIdAndTime.get(q).get("time").toString())+"==="+j);
+ String sj="";
+ if (j<10){
+ sj="0"+j;
+ }else {
+ if (j==24){
+ sj="00";
+ }else {
+ sj=""+j;
+ }
+ }
+ if (!timeList.contains(sj)){
+ map = new HashMap<>();
+ map.put("time",sj);
+ Map map1 = new HashMap();
+ map1.put("PM2_5","");
+ map1.put("NO2","");
+ map1.put("SO2","");
+ map1.put("O3","");
+ map1.put("PM10","");
+ map1.put("O3_8h","");
+ map1.put("CO","");
+ map.put("data",map1);
+ dataAvbByMIdAndTime.add(map);
+ break;
+ }else {
+ break;
+ }
+ }
+ }
+
+ }
+ }
+ Collections.sort(dataAvbByMIdAndTime, new Comparator<Map<String, Object>>(){
+ public int compare(Map<String, Object> o1, Map<String, Object> o2) {
+ String name1 =(String)o1.get("time");//name1���������list������������������������
+ String name2= (String)o2.get("time"); //name1���������list���������������������������name
+ return name1.compareTo(name2);
+ }
+ });
+ Map map1 = dataAvbByMIdAndTime.get(0);
+ dataAvbByMIdAndTime.remove(0);
+ dataAvbByMIdAndTime.add(map1);
+ for (int j = 0; j <dataAvbByMIdAndTime.size() ; j++) {
+ if (dataAvbByMIdAndTime.get(j).get("time").toString().length()>2){
+ Map map2 = dataAvbByMIdAndTime.get(j);
+ dataAvbByMIdAndTime.remove(j);
+ dataAvbByMIdAndTime.add(map2);
+ }
+ }
lists.add(dataAvbByMIdAndTime);
}
List<Map<String, Object>> dataByMonitorPoints = historyHourlyService.getDataByMonitorPoints(mIds, date1, date2);
lists.add(dataByMonitorPoints);
}
- return new ResultBean<List<List<Map<String,Object>>>>(lists);
+ List<Map<String, Object>> rList =new ArrayList<>();
+ if (lists.size()==1 && lists.get(0)!=null){
+ for (int i = 0; i < lists.get(0).size(); i++) {
+ List list = new ArrayList();
+ list.add(lists.get(0).get(i).get("data"));
+ Map map = new HashMap();
+ map.put("data",list);
+ map.put("time",lists.get(0).get(i).get("time"));
+ rList.add(map);
+ }
+
+ } else if (lists.size()==1 && lists.get(0)==null){
+ rList.add(null);
+ }else {
+ for (int j = 0; j < lists.get(0).size(); j++) {
+ Map<String,Object> listMap = new HashMap();
+ List<Map> mapList=new ArrayList<>();
+ if (lists.get(0).size()==1){
+ mapList.add(null);
+ }else {
+ mapList.add((Map) lists.get(0).get(j).get("data"));
+ }
+ for (int i = 1; i < lists.size(); i++) {
+ if (lists.get(i).size()==1){
+ mapList.add(null);
+ listMap.put("data",mapList);
+ }else {
+ if (lists.get(0).get(j).get("time").equals(lists.get(i).get(j).get("time"))){
+ mapList.add((Map) lists.get(i).get(j).get("data"));
+ }
+ listMap.put("time",lists.get(0).get(j).get("time"));
+ listMap.put("data",mapList);
+ }
+ }
+ rList.add(listMap);
+ }
+ }
+ return new ResultBean<List<Map<String,Object>>>(rList);
}
-}
+}
\ No newline at end of file
diff --git a/src/main/java/com/moral/service/impl/HistoryHourlyServiceImpl.java b/src/main/java/com/moral/service/impl/HistoryHourlyServiceImpl.java
index f2d502c..c5272bf 100644
--- a/src/main/java/com/moral/service/impl/HistoryHourlyServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/HistoryHourlyServiceImpl.java
@@ -325,14 +325,14 @@
int time1 = Integer.parseInt(mapList1.get(j).get("time").toString()) ;
if (time==time1){
Double O3_8h = Double.parseDouble(mapList1.get(j).get("O3_8h").toString());
- dataAvbByMIdAndTime.get(i).put("O3_8h",new BigDecimal(O3_8h).setScale(4,BigDecimal.ROUND_HALF_UP).doubleValue());
+ dataAvbByMIdAndTime.get(i).put("O3_8h",new BigDecimal(O3_8h).setScale(0,BigDecimal.ROUND_HALF_UP).doubleValue());
}
}
}
}
Map<String, Object> dailyAvgData = historyMapper.getDailyAvgData(mId, startTime);
if (dailyAvgData!=null){
- dailyAvgData.put("maxO3_8h",new BigDecimal(maxO3).setScale(4,BigDecimal.ROUND_HALF_UP).doubleValue());
+ dailyAvgData.put("maxO3_8h",new BigDecimal(maxO3).setScale(0,BigDecimal.ROUND_HALF_UP).doubleValue());
}
List<Map<String,Object>> finalList = new ArrayList<>();
for (int i = 0; i <dataAvbByMIdAndTime.size() ; i++) {
@@ -346,13 +346,12 @@
Map<String,Object> timeAndDate = new HashMap();
String time = startTime.substring(0,11);
timeAndDate.put("time",time);
- if (dailyAvgData==null){
+ if (dailyAvgData!=null){
//List<Map<String, Object>> dailyData = historyHourlyMapper.getDataAvbByMIdAndT;
- timeAndDate.put("data","");
- }else {
timeAndDate.put("data",dailyAvgData);
+ }else {
+ timeAndDate.put("data","");
}
-
finalList.add(timeAndDate);
Map<String,Object> map = new HashMap<>();
map.put("maxO3",maxO3);
@@ -427,7 +426,7 @@
int time1 = Integer.parseInt(mapList1.get(j).get("time").toString()) ;
if (time==time1){
Double O3_8h = Double.parseDouble(mapList1.get(j).get("O3_8h").toString());
- dataByMonitorPoints.get(i).put("O3_8h",new BigDecimal(O3_8h).setScale(4,BigDecimal.ROUND_HALF_UP).doubleValue());
+ dataByMonitorPoints.get(i).put("O3_8h",new BigDecimal(O3_8h).setScale(0,BigDecimal.ROUND_HALF_UP).doubleValue());
}
}
}
@@ -445,14 +444,28 @@
Map<String, Object> dailyAvgData = historyMapper.getDailyAvgDataByMIds(mIds, startTime);
if (dailyAvgData!=null){
Map<String,Object> timeAndDate = new HashMap();
- dailyAvgData.put("maxO3_8h",new BigDecimal(maxO3).setScale(4,BigDecimal.ROUND_HALF_UP).doubleValue());
+ dailyAvgData.put("maxO3_8h",new BigDecimal(maxO3).setScale(0,BigDecimal.ROUND_HALF_UP).doubleValue());
timeAndDate.put("data",dailyAvgData);
timeAndDate.put("time",startTime.substring(0,11));
finalList.add(timeAndDate);
}else {
Map<String, Object> avgDataByMIds = historyHourlyMapper.getAvgDataByMIds(mIds, startTime, endTime);
Map<String,Object> timeAndDate = new HashMap();
- avgDataByMIds.put("maxO3_8h",new BigDecimal(maxO3).setScale(4,BigDecimal.ROUND_HALF_UP).doubleValue());
+ if (avgDataByMIds == null){
+ avgDataByMIds = new HashMap<>();
+ avgDataByMIds.put("PM2_5","");
+ avgDataByMIds.put("NO2","");
+ avgDataByMIds.put("primary_pollutants","-");
+ avgDataByMIds.put("SO2","");
+ avgDataByMIds.put("O3","");
+ avgDataByMIds.put("PM10","");
+ avgDataByMIds.put("O3_8h","");
+ avgDataByMIds.put("CO","");
+ avgDataByMIds.put("AQI","");
+ }else {
+ avgDataByMIds.put("maxO3_8h",maxO3);
+ }
+
timeAndDate.put("data",avgDataByMIds);
timeAndDate.put("time",startTime.substring(0,11));
finalList.add(timeAndDate);
@@ -460,7 +473,7 @@
for (int i=0;i<finalList.size();i++) {
if (i!=finalList.size()-1){
Map<String, Object> map1 = AQICalculation2.hourlyAQI((Map<String, Object>) finalList.get(i).get("data"));
- if (Double.parseDouble(map1.get("AQI").toString())>=50.0){
+ if (Double.parseDouble(map1.get("AQI").toString())>50.0){
((Map<String, Object>) finalList.get(i).get("data")).put("primary_pollutants",map1.get("maxSensor"));
((Map<String, Object>) finalList.get(i).get("data")).put("AQI",map1.get("AQI"));
}else {
@@ -469,6 +482,9 @@
}
}else {
Map<String, Object> map1 = AQICalculation2.dayAQI((Map<String, Object>) finalList.get(i).get("data"));
+ if (map1.get("AQI").toString().equals("")){
+ break;
+ }
if (Double.parseDouble(map1.get("AQI").toString())>=50.0){
((Map<String, Object>) finalList.get(i).get("data")).put("primary_pollutants",map1.get("maxSensor"));
((Map<String, Object>) finalList.get(i).get("data")).put("AQI",map1.get("AQI"));
@@ -481,10 +497,74 @@
Map<String,Object> timeAndDate = new HashMap();
String time = startTime.substring(0,11);
timeAndDate.put("time",time);
- if (dailyAvgData==null){
- timeAndDate.put("data","");
- }else {
+ if (dailyAvgData!=null){
timeAndDate.put("data",dailyAvgData);
+ }else {
+ Map map = new HashMap<>();
+ Map map1 = new HashMap();
+ map1.put("PM2_5","");
+ map1.put("NO2","");
+ map1.put("primary_pollutants","-");
+ map1.put("SO2","");
+ map1.put("O3","");
+ map1.put("PM10","");
+ map1.put("O3_8h","");
+ map1.put("CO","");
+ map1.put("AQI","");
+ map.put("data",map1);
+ timeAndDate.put("data",map);
+ }
+ List timeList = new ArrayList();
+ for (int q = 0; q < finalList.size(); q++) {
+ timeList.add(finalList.get(q).get("time"));
+ }
+ if (finalList.size()<25){
+ for (int j = 1; j < 25; j++) {
+ Map<String,Object> map = null;
+ for (int q = 0; q < finalList.size(); q++) {
+ if (finalList.get(q).get("time").toString().length() > 2){
+ continue;
+ }else {
+ String sj="";
+ if (j<10){
+ sj="0"+j;
+ }else {
+ if (j==24){
+ sj="00";
+ }else {
+ sj=""+j;
+ }
+ }
+ if (!timeList.contains(sj)){
+ map = new HashMap<>();
+ Map map1 = new HashMap();
+ map1.put("PM2_5","");
+ map1.put("NO2","");
+ map1.put("primary_pollutants","");
+ map1.put("SO2","");
+ map1.put("O3","");
+ map1.put("PM10","");
+ map1.put("O3_8h","");
+ map1.put("CO","");
+ map1.put("AQI","");
+ map.put("time",sj);
+ map.put("data",map1);
+ finalList.add(map);
+ break;
+ }else {
+ break;
+ }
+ }
+ }
+
+ }
+ }
+ for (int j = 0; j <finalList.size() ; j++) {
+ if (finalList.get(j).get("time").toString().length()>2){
+ Map map2 = finalList.get(j);
+ finalList.remove(j);
+ finalList.add(map2);
+ }
}
return finalList;
}
diff --git a/src/main/java/com/moral/util/AQICalculation2.java b/src/main/java/com/moral/util/AQICalculation2.java
index 050afdd..d8cfe99 100644
--- a/src/main/java/com/moral/util/AQICalculation2.java
+++ b/src/main/java/com/moral/util/AQICalculation2.java
@@ -101,15 +101,18 @@
continue;
}
}
-
- Double AQIMAX = Collections.max(AQIList);
Map<String, Object> returnMap = new HashMap<>();
- Optional<Map.Entry<String, Integer>> max0 = maxMap.entrySet()
- .stream()
- .max(Map.Entry.comparingByValue());
-
- returnMap.put("maxSensor",max0.get().getKey());
- returnMap.put("AQI", AQIMAX);
+ if (AQIList.size()>0){
+ Double AQIMAX = Collections.max(AQIList);
+ Optional<Map.Entry<String, Integer>> max0 = maxMap.entrySet()
+ .stream()
+ .max(Map.Entry.comparingByValue());
+ returnMap.put("maxSensor",max0.get().getKey());
+ returnMap.put("AQI", AQIMAX);
+ }else {
+ returnMap.put("maxSensor","");
+ returnMap.put("AQI", "");
+ }
return returnMap;
}
static double PM2_5AQI(Double value) {
diff --git a/src/main/resources/mapper/HistoryHourlyMapper.xml b/src/main/resources/mapper/HistoryHourlyMapper.xml
index e74be91..ecfbebb 100644
--- a/src/main/resources/mapper/HistoryHourlyMapper.xml
+++ b/src/main/resources/mapper/HistoryHourlyMapper.xml
@@ -112,15 +112,15 @@
</select>
<select id="getDataAvbByMIdAndTime" resultType="java.util.Map">
select hh.time as `time`,
- AVG(hh.json->"$.e1[0]") as `PM2_5`,AVG(hh.json->"$.e2[0]") as 'PM10',AVG(hh.json->"$.e10[0]") as 'CO',
- AVG(hh.json->"$.e11[0]") as 'SO2',AVG(hh.json->"$.e16[0]") as 'NO2',AVG(hh.json->"$.e15[0]") as 'O3'
+ ROUND(AVG(hh.json->"$.e1[0]")) as `PM2_5`,ROUND(AVG(hh.json->"$.e2[0]")) as 'PM10',CAST(AVG(hh.json->"$.e10[0]") as DECIMAL(10,1)) as 'CO',
+ ROUND(AVG(hh.json->"$.e11[0]")) as 'SO2',ROUND(AVG(hh.json->"$.e16[0]")) as 'NO2',ROUND(AVG(hh.json->"$.e15[0]")) as 'O3'
from monitor_point mt,device d,history_hourly hh
where mt.id=d.monitor_point_id and d.mac=hh.mac and mt.id=#{mId} and hh.time>#{startTime} and hh.time<![CDATA[<=]]>#{endTime} GROUP BY hh.time;
</select>
<select id="getDataByMonitorPoints" resultType="java.util.Map">
select hh.time as `time`,
- AVG(hh.json->"$.e1[0]") as `PM2_5`,AVG(hh.json->"$.e2[0]") as 'PM10',AVG(hh.json->"$.e10[0]") as 'CO',
- AVG(hh.json->"$.e11[0]") as 'SO2',AVG(hh.json->"$.e16[0]") as 'NO2',AVG(hh.json->"$.e15[0]") as 'O3'
+ ROUND(AVG(hh.json->"$.e1[0]")) as `PM2_5`,ROUND(AVG(hh.json->"$.e2[0]")) as 'PM10',CAST(AVG(hh.json->"$.e10[0]") as DECIMAL(10,1)) as 'CO',
+ ROUND(AVG(hh.json->"$.e11[0]")) as 'SO2',ROUND(AVG(hh.json->"$.e16[0]")) as 'NO2',ROUND(AVG(hh.json->"$.e15[0]")) as 'O3'
from monitor_point mt,device d,history_hourly hh
where mt.id=d.monitor_point_id and d.mac=hh.mac and mt.id in
<foreach collection="mIds" item="id" index="index" open="(" close=")" separator=",">
@@ -129,14 +129,15 @@
and hh.time>#{startTime} and hh.time<![CDATA[<=]]>#{endTime} GROUP BY hh.time;
</select>
<select id="getAvgDataByMId" resultType="java.util.Map">
- SELECT AVG(hh.json->"$.e1[0]") as `PM2_5`,AVG(hh.json->"$.e2[0]") as 'PM10',AVG(hh.json->"$.e10[0]") as 'CO',
- AVG(hh.json->"$.e11[0]") as 'SO2',AVG(hh.json->"$.e16[0]") as 'NO2',AVG(hh.json->"$.e15[0]") as 'O3_day'
+ SELECT
+ ROUND(AVG(hh.json->"$.e1[0]")) as `PM2_5`,ROUND(AVG(hh.json->"$.e2[0]")) as 'PM10',CAST(AVG(hh.json->"$.e10[0]") as DECIMAL(10,1)) as 'CO',
+ ROUND(AVG(hh.json->"$.e11[0]")) as 'SO2',ROUND(AVG(hh.json->"$.e16[0]")) as 'NO2',ROUND(AVG(hh.json->"$.e15[0]")) as 'O3_day'
from monitor_point mt,device d,history_hourly hh where mt.id=d.monitor_point_id
and d.mac=hh.mac and mt.id =#{mId} and hh.time<![CDATA[<=]]>#{endTime} and hh.time>#{startTime}
</select>
<select id="getAvgDataByMIds" resultType="java.util.Map">
- SELECT AVG(hh.json->"$.e1[0]") as `PM2_5`,AVG(hh.json->"$.e2[0]") as 'PM10',AVG(hh.json->"$.e10[0]") as 'CO',
- AVG(hh.json->"$.e11[0]") as 'SO2',AVG(hh.json->"$.e16[0]") as 'NO2',AVG(hh.json->"$.e15[0]") as 'O3_day'
+ SELECT ROUND(AVG(hh.json->"$.e1[0]")) as `PM2_5`,ROUND(AVG(hh.json->"$.e2[0]")) as 'PM10',CAST(AVG(hh.json->"$.e10[0]") as DECIMAL(10,1)) as 'CO',
+ ROUND(AVG(hh.json->"$.e11[0]")) as 'SO2',ROUND(AVG(hh.json->"$.e16[0]")) as 'NO2',ROUND(AVG(hh.json->"$.e15[0]")) as 'O3_day'
from monitor_point mt,device d,history_hourly hh where mt.id=d.monitor_point_id
and d.mac=hh.mac and mt.id in
<foreach collection="mIds" item="id" index="index" open="(" close=")" separator=",">
diff --git a/src/main/resources/mapper/HistoryMapper.xml b/src/main/resources/mapper/HistoryMapper.xml
index 08223f6..91c1e7e 100644
--- a/src/main/resources/mapper/HistoryMapper.xml
+++ b/src/main/resources/mapper/HistoryMapper.xml
@@ -301,14 +301,14 @@
limit 0,1
</select>
<select id="getDailyAvgData" resultType="java.util.Map">
- SELECT AVG(hd.json->"$.e1[0]") as `PM2_5`,AVG(hd.json->"$.e2[0]") as 'PM10',AVG(hd.json->"$.e10[0]") as 'CO',
- AVG(hd.json->"$.e11[0]") as 'SO2',AVG(hd.json->"$.e16[0]") as 'NO2',AVG(hd.json->"$.e15[0]") as 'O3_day'
+ SELECT ROUND(AVG(hd.json->"$.e1[0]")) as `PM2_5`,ROUND(AVG(hd.json->"$.e2[0]")) as 'PM10',CAST(AVG(hd.json->"$.e10[0]") as DECIMAL(10,1)) as 'CO',
+ ROUND(AVG(hd.json->"$.e11[0]")) as 'SO2',ROUND(AVG(hd.json->"$.e16[0]")) as 'NO2',ROUND(AVG(hd.json->"$.e15[0]")) as 'O3_day'
FROM `history_daily` hd,monitor_point mt,device d where mt.id=d.monitor_point_id
and d.mac=hd.mac and mt.id=#{mId} and time=#{time}
</select>
<select id="getDailyAvgDataByMIds" resultType="java.util.Map">
- SELECT AVG(hd.json->"$.e1[0]") as `PM2_5`,AVG(hd.json->"$.e2[0]") as 'PM10',AVG(hd.json->"$.e10[0]") as 'CO',
- AVG(hd.json->"$.e11[0]") as 'SO2',AVG(hd.json->"$.e16[0]") as 'NO2',AVG(hd.json->"$.e15[0]") as 'O3_day'
+ SELECT ROUND(AVG(hd.json->"$.e1[0]")) as `PM2_5`,ROUND(AVG(hd.json->"$.e2[0]")) as 'PM10',CAST(AVG(hd.json->"$.e10[0]") as DECIMAL(10,1)) as 'CO',
+ ROUND(AVG(hd.json->"$.e11[0]")) as 'SO2',ROUND(AVG(hd.json->"$.e16[0]")) as 'NO2',ROUND(AVG(hd.json->"$.e15[0]")) as 'O3_day'
FROM `history_daily` hd,monitor_point mt,device d where mt.id=d.monitor_point_id
and d.mac=hd.mac and mt.id in
<foreach collection="mIds" item="id" index="index" open="(" close=")" separator=",">
--
Gitblit v1.8.0