From 3f667355c7013b459f3ab5ec0c1bc035d7e2d5aa Mon Sep 17 00:00:00 2001
From: cjl <909710561@qq.com>
Date: Wed, 31 Jan 2024 10:57:28 +0800
Subject: [PATCH] fix：sql查询时间调整
---
screen-job/src/main/java/com/moral/api/service/impl/CityAqiYearlyServiceImpl.java | 121 ++++++++++++++++++++++------------------
1 files changed, 66 insertions(+), 55 deletions(-)
diff --git a/screen-job/src/main/java/com/moral/api/service/impl/CityAqiYearlyServiceImpl.java b/screen-job/src/main/java/com/moral/api/service/impl/CityAqiYearlyServiceImpl.java
index 347ab89..92736ee 100644
--- a/screen-job/src/main/java/com/moral/api/service/impl/CityAqiYearlyServiceImpl.java
+++ b/screen-job/src/main/java/com/moral/api/service/impl/CityAqiYearlyServiceImpl.java
@@ -2,13 +2,13 @@
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
+import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.moral.api.entity.CityAqiDaily;
import com.moral.api.entity.CityAqiYearly;
import com.moral.api.mapper.CityAqiYearlyMapper;
import com.moral.api.service.CityAqiDailyService;
import com.moral.api.service.CityAqiYearlyService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
-import com.moral.constant.Constants;
import com.moral.util.AmendUtils;
import com.moral.util.ComprehensiveIndexUtils;
import com.moral.util.DateUtils;
@@ -19,14 +19,11 @@
import java.text.DecimalFormat;
import java.util.ArrayList;
-import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
-import java.util.OptionalDouble;
import java.util.stream.Collectors;
-import java.util.stream.DoubleStream;
/**
* <p>
@@ -48,8 +45,12 @@
@Override
public void insertCityAqiYearly() {
- //���������������������������
- List<String> sensors = Arrays.asList("PM2_5", "PM10", "SO2", "NO2");
+ /*
+ * ���������������
+ * PM2.5,PM10,CO,���������95���������
+ * SO2,NO2,���������98���������
+ * O3���������������90���������
+ * */
//���������������������1���
Date start = DateUtils.getFirstDayOfLastYear();
@@ -58,75 +59,86 @@
//���������������������1���
Date end = DateUtils.getDate(DateUtils.getDateAddYear(DateUtils.dateToDateString(start, DateUtils.yyyy), 1), DateUtils.yyyy);
+ //���������1���1���������������������������
+ //������1���1���������������������������������������������������������
+ String monthAndDay = DateUtils.dateToDateString(new Date(), DateUtils.MM_dd_EN);
+ UpdateWrapper<CityAqiYearly> cityAqiYearlyUpdateWrapper = new UpdateWrapper<>();
+ if ("01-01".equals(monthAndDay)) {
+ cityAqiYearlyUpdateWrapper.eq("time", start);
+ } else {
+ start = end;
+ end = DateUtils.getDate(DateUtils.getDateAddYear(DateUtils.dateToDateString(start, DateUtils.yyyy), 1), DateUtils.yyyy);
+ lastLastYear = DateUtils.getDate(DateUtils.getDateAddYear(DateUtils.dateToDateString(start, DateUtils.yyyy), -1), DateUtils.yyyy);
+ cityAqiYearlyUpdateWrapper.eq("time", start);
+ }
+ cityAqiYearlyMapper.delete(cityAqiYearlyUpdateWrapper);
- //������������������aqi������������
+
+ //������������������aqi���������
QueryWrapper<CityAqiDaily> wrapper = new QueryWrapper<>();
wrapper.select("city_code", "time", "value")
- .ge("time", DateUtils.dateToDateString(start))
- .lt("time", DateUtils.dateToDateString(end));
+ .ge("time", start)
+ .lt("time", end);
List<Map<String, Object>> monthlyData = cityAqiDailyService.listMaps(wrapper);
if (monthlyData.size() == 0) {
return;
}
//���city_code������
- Map<String, List<Map<String, Object>>> data = monthlyData.parallelStream().collect(Collectors.groupingBy(o -> o.get("city_code").toString()));
+ Map<String, List<Map<String, Object>>> data = monthlyData.stream()
+ .collect(Collectors.groupingBy(o -> o.get("city_code").toString()));
+ //������������
+ QueryWrapper<CityAqiYearly> queryWrapper = new QueryWrapper<>();
+ queryWrapper.select("city_code", "value")
+ .eq("time", lastLastYear);
+ //������������������
+ List<CityAqiYearly> lastCityAqiYearlyList = cityAqiYearlyMapper.selectList(queryWrapper);
+ Map<Integer, CityAqiYearly> lastYearData = new HashMap<>();
+ for (CityAqiYearly cityAqiYearly : lastCityAqiYearlyList) {
+ lastYearData.put(cityAqiYearly.getCityCode(), cityAqiYearly);
+ }
+
+
+ List<CityAqiYearly> cityAqiYearlyList = new ArrayList<>();
+
+ Date finalStart = start;
data.forEach((cityCode, value) -> {
- Map<String, Object> jsonMap = new HashMap<>();
CityAqiYearly cityAqiYearly = new CityAqiYearly();
+ Map<String, Object> jsonMap = new HashMap<>();
cityAqiYearly.setCityCode(Integer.parseInt(cityCode));
- cityAqiYearly.setTime(start);
+ cityAqiYearly.setTime(finalStart);
+ //PM2.5
+ Double pm25Avg = AmendUtils.getAvgOfYear(value, "PM2_5");
+ jsonMap.put("PM2_5", pm25Avg);
- Map<String, Object> params = new HashMap<>();
- List<Map<String, Object>> temp = new ArrayList<>();
+ //PM10
+ Double pm10Avg = AmendUtils.getAvgOfYear(value, "PM10");
+ jsonMap.put("PM10", pm10Avg);
- for (Map<String, Object> map : value) {
- Map<String, Object> sensorsValue = JSONObject.parseObject(map.get("value").toString(), Map.class);
- Map<String, Object> tempMap = new HashMap<>();
- tempMap.put(Constants.SENSOR_CODE_CO, sensorsValue.get("PM2_5"));
- tempMap.put(Constants.SENSOR_CODE_O3, sensorsValue.get("O3"));
- Map<String, Object> hashMap = new HashMap<>();
- hashMap.put("value", JSONObject.toJSONString(tempMap));
- temp.add(hashMap);
- }
- params.put("data", temp);
- //1. CO 95������������������������
- Map<String, Object> coAvgOfWeekOrMonth = AmendUtils.getCOAvgOfWeekOrMonth(params);
- if (!ObjectUtils.isEmpty(coAvgOfWeekOrMonth)) {
- jsonMap.put("CO", coAvgOfWeekOrMonth.get(Constants.SENSOR_CODE_CO));
- }
+ //SO2
+ Double so2Avg = AmendUtils.getAvgOfYear(value, "SO2");
+ jsonMap.put("SO2", so2Avg);
- //2. O3 90������������������������
- Map<String, Object> o3AvgOfWeekOrMonth = AmendUtils.getO3AvgOfWeekOrMonth(params);
- if (!ObjectUtils.isEmpty(o3AvgOfWeekOrMonth)) {
- jsonMap.put("O3", o3AvgOfWeekOrMonth.get(Constants.SENSOR_CODE_O3));
- }
+ //NO2
+ Double no2Avg = AmendUtils.getAvgOfYear(value, "NO2");
+ jsonMap.put("NO2", no2Avg);
- sensors.forEach(sensor -> {
- OptionalDouble optionalDouble = value.parallelStream().flatMapToDouble(v -> {
- Map<String, Object> dataValue = JSONObject.parseObject((String) v.get("value"), Map.class);
- double aDouble = Double.parseDouble(dataValue.get(sensor).toString());
- return DoubleStream.of(aDouble);
- }).average();
- if (optionalDouble.isPresent()) {
- //���������������������
- jsonMap.put(sensor, AmendUtils.sciCal(optionalDouble.getAsDouble(), 0));
- }
- });
+ //CO
+ Double coAvg = AmendUtils.getAvgOfYear(value, "CO");
+ jsonMap.put("CO", coAvg);
+
+ //O3
+ Double o3Avg = AmendUtils.getAvgOfYear(value, "O3");
+ jsonMap.put("O3", o3Avg);
//���������������������
Double compositeIndex = ComprehensiveIndexUtils.dailyData(jsonMap);
jsonMap.put("compositeIndex", compositeIndex);
- //���������������������������
- QueryWrapper<CityAqiYearly> queryWrapper = new QueryWrapper<>();
- queryWrapper.select("value")
- .eq("city_code", cityCode)
- .eq("time", DateUtils.dateToDateString(lastLastYear));
- //���������������������
- CityAqiYearly lastCityAqiYearly = cityAqiYearlyMapper.selectOne(queryWrapper);
+ //���������������������������
+ CityAqiYearly lastCityAqiYearly = lastYearData.get(Integer.parseInt(cityCode));
if (lastCityAqiYearly != null) {
Map<String, Object> map = JSONObject.parseObject(lastCityAqiYearly.getValue(), Map.class);
double lastCompositeIndex = Double.parseDouble(map.get("compositeIndex").toString());
@@ -135,9 +147,8 @@
jsonMap.put("yearContrast", format);
}
cityAqiYearly.setValue(JSONObject.toJSONString(jsonMap));
- cityAqiYearlyMapper.insert(cityAqiYearly);
+ cityAqiYearlyList.add(cityAqiYearly);
});
-
-
+ cityAqiYearlyMapper.insertCityAqiYearly(cityAqiYearlyList);
}
}
--
Gitblit v1.8.0