昆山，沧州大数据预测O3

jinpengyong

2020-10-09 c60311f48e7ec07c56a4df31536f795ba62751ce

昆山，沧州大数据预测O3

 src/main/java/com/moral/entity/ForecastWeather.java

@@ -8,7 +8,6 @@

@Data
public class ForecastWeather {
    @Id
    private String cityCode;
    private Date time;
    private String json;

 src/main/java/com/moral/entity/HistoryWeather.java

New file
@@ -0,0 +1,12 @@
package com.moral.entity;

import lombok.Data;

import java.util.Date;

@Data
public class HistoryWeather {
    private String cityCode;
    private Date time;
    private String value;
}

 src/main/java/com/moral/entity/RealWeather.java

@@ -8,7 +8,6 @@

@Data
public class RealWeather {
    @Id
    private String cityCode;
    private Date time;
    private String json;

 src/main/java/com/moral/entity/RealWeatherDaily.java

@@ -8,7 +8,6 @@

@Data
public class RealWeatherDaily {
    @Id
    private String cityCode;
    private Date time;
    private String json;

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

@@ -11,6 +11,10 @@

    List<Map<String,Object>> getO3(Map<String,Object> params);

    String getO3ByTime(Map<String,Object> params);

    List<Map<String,Object>> getAqiByHour(Map<String,Object> params);

    String getO3FromHistory(Map<String,Object> params);

}

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

@@ -8,4 +8,6 @@
    int insertForecastWeather(List<Map<String,Object>> list);

    void deleteByTime(Map<String,Object> parameters);

    String getBeam(Map<String,Object> parameters);
}

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

@@ -19,4 +19,8 @@

    List<Map<String, Object>> getWeatherByHour(Map<String, Object> params);

    List<String> getSampleFromHistoryWeather(Map<String, Object> params);

    List<String> getSampleFromRealWeather(Map<String, Object> params);

}

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

@@ -4,6 +4,8 @@

public interface ForecastWeatherService {

    int insertForecastWeatherNew() throws ParseException;

    int insertForecastWeather() throws ParseException;

}

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

@@ -5,6 +5,7 @@
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
@@ -66,6 +67,399 @@
    private HistoryHourlyMapper historyHourlyMapper;

    @Override
    public int insertForecastWeatherNew() throws ParseException {
        Calendar c = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String today = sdf.format(c.getTime());
        DecimalFormat df = new DecimalFormat("0.0");
        c.add(Calendar.DAY_OF_MONTH, 1);
        String nextDay = sdf.format(c.getTime());
        c.setTime(c.getTime());
        c.add(Calendar.DAY_OF_MONTH, 1);
        String nextNextDay = sdf.format(c.getTime()) + " 00";
        RestTemplate restTemplate = new RestTemplate();
        List<MonitorPoint> monitorPointList = monitorPointMapper.getMonitorPointList();
        Set<Map<String, Object>> hashSet = new HashSet<>();
        for (MonitorPoint monitorPoint : monitorPointList) {
            Map<String, Object> hashMap = new HashMap<>();
            Integer areaCode = monitorPoint.getAreaCode();
            Integer cityCode = monitorPoint.getCityCode();
            Integer provinceCode = monitorPoint.getProvinceCode();
            String parentName;
            String name1;
            Integer code;
            Integer parentCode;
            if (areaCode != null) {
                Area area = areaMapper.getAreaByAreaCode(areaCode);
                City city = cityMapper.getCityByCityCode(cityCode);
                name1 = area.getAreaName();
                parentName = city.getCityName();
                parentCode = cityCode;
                code = areaCode;
                if ("市辖区".equals(name1)) {
                    name1 = parentName;
                    Province province = provinceMapper.getProvinceByProvinceCode(provinceCode);
                    parentName = province.getProvinceName();
                    parentCode = provinceCode;
                    code = cityCode;
                }
            } else {
                if (cityCode != null) {
                    City city = cityMapper.getCityByCityCode(cityCode);
                    name1 = city.getCityName();
                    code = cityCode;
                    Province province = provinceMapper.getProvinceByProvinceCode(provinceCode);
                    parentName = province.getProvinceName();
                    parentCode = provinceCode;
                    if ("市辖区".equals(name1)) {
                        name1 = parentName;
                        code = provinceCode;
                    }
                } else {
                    code = provinceCode;
                    Province province = provinceMapper.getProvinceByProvinceCode(provinceCode);
                    name1 = province.getProvinceName();
                    parentCode = provinceCode;
                    parentName = name1;
                }
            }
            hashMap.put("name1", name1);
            hashMap.put("parentName", parentName);
            hashMap.put("cityCode", code);
            hashMap.put("parentCode", parentCode);
            hashSet.add(hashMap);
        }

        Map<String, Object> smap = new HashMap<>();
        smap.put("name1", "苏州市");
        smap.put("parentName", "江苏省");
        smap.put("cityCode", 320500);
        smap.put("parentCode", 320000);
        hashSet.add(smap);

        Map<String, Object> bmap = new HashMap<>();
        bmap.put("name1", "北京市");
        bmap.put("parentName", "北京市");
        bmap.put("cityCode", 110000);
        bmap.put("parentCode", 110000);
        hashSet.add(bmap);

        Map<String, Object> cz = new HashMap<>();
        Set<Map<String, Object>> set = new HashSet<>();
        cz.put("name1", "沧州市");
        cz.put("parentName", "河北省");
        cz.put("cityCode", 130900);
        cz.put("parentCode", 130000);
        set.add(cz);

        Map<String, Object> ks = new HashMap<>();
        ks.put("name1", "昆山市");
        ks.put("parentName", "苏州市");
        ks.put("cityCode", 320583);
        ks.put("parentCode", 320500);
        set.add(ks);

        List<Element> elements = Dom4jUtils.readDocument();
        String cityID;
        for (Map<String, Object> map : set) {
            String name1 = map.get("name1").toString();
            for (Element element : elements) {
                String name2 = element.element("name").getText();
                String parentName = map.get("parentName").toString();
                if (name2.equals("大丰市")) {
                    name2 = "大丰区";
                }
                if (name1.equals(name2)) {
                    cityID = element.element("Fweathercn").getText();
                    map.put("cityID", cityID);
                    break;
                }
                if (name2.endsWith(name1)) {
                    if (name2.startsWith(parentName)) {
                        cityID = element.element("Fweathercn").getText();
                        map.put("cityID", cityID);
                        break;
                    }
                }
            }
        }
        int count = 0;
        for (Map<String, Object> map : set) {
            String id = map.get("cityID").toString();
            Map<String, Object> dataMap = restTemplate.getForObject("https://api.heweather.net/v7/weather/72h?key=da05c6c4852d4f7aa3364a9236ee9e26&gzip=n&location={1}", Map.class, id);
            if (dataMap == null) {
                return count;
            }
            String json = JSONObject.toJSONString(dataMap);
            dataMap = (Map<String, Object>) JSONObject.parse(json);
            List<Map<String, Object>> nextDayList = new ArrayList<>();
            String string = dataMap.get("hourly").toString();
            List<Map<String, Object>> hourlyList = (List<Map<String, Object>>) JSONObject.parse(string);
            for (Map<String, Object> hourlyMap : hourlyList) {
                //逐小时预报时间
                String fxTime = hourlyMap.get("fxTime").toString().split("\\+")[0].replace("T", " ") + ":00";
                hourlyMap.remove("fxTime");
                //icon为不需要字段
                hourlyMap.remove("icon");
                hourlyMap.put("time", fxTime);
                //天气
                String text = hourlyMap.get("text").toString();
                //风速km/h->m/s
                Double windSpeed = Double.valueOf(hourlyMap.get("windSpeed").toString());
                windSpeed = windSpeed * 1000 / 3600;
                hourlyMap.put("windSpeed", df.format(windSpeed));
                String condition = "";
                if ("晴".equals(text)) {
                    condition = "100";
                } else if ("雾".equals(text)) {
                    condition = "90";
                } else if ("多云".equals(text)) {
                    condition = "80";
                } else if ("霾".equals(text)) {
                    condition = "70";
                } else if ("阴".equals(text) || "扬沙".equals(text) || "浮尘".equals(text) || "".equals(text)) {
                    condition = "60";
                } else if ("阵雨".equals(text)) {
                    condition = "45";
                } else if ("雷阵雨".equals(text)) {
                    condition = "40";
                } else if ("雷阵雨转小雨".equals(text) || "阵雨转小雨".equals(text)) {
                    condition = "30";
                } else if ("小雨".equals(text)) {
                    condition = "20";
                } else if ("雨".equals(text)) {
                    condition = "10";
                } else if ("雷阵雨转中雨".equals(text)) {
                    condition = "5";
                } else if ("雷阵雨转大雨".equals(text)) {
                    condition = "4";
                } else if ("中雨".equals(text) || "大雨".equals(text) || "暴雨".equals(text)
                        || "小雪".equals(text) || "中雪".equals(text) || "大雪".equals(text)
                        || "雨夹雪".equals(text) || "雨夹雪（伴有冰雹）".equals(text) || "暴雪".equals(text)
                ) {
                    condition = "0";
                } else {
                    condition = "50";
                }
                hourlyMap.put("condition", condition);

                String hour = fxTime.split(" ")[0];
                String hour1 = fxTime.split(":")[0];
                if (hour.equals(nextDay)) {
                    nextDayList.add(hourlyMap);
                }
                if (hour1.equals(nextNextDay)) {
                    nextDayList.add(hourlyMap);
                }
            }
            nextDayList.remove(0);
            map.put("data", nextDayList);
        }

        //today:今天
        c.setTime(sdf.parse(today));
        c.add(Calendar.YEAR, -3);
        Date start = c.getTime();
        Date end = sdf.parse(today);
        int timeUnits1 = c.get(Calendar.YEAR);
        int timeUnits2 = timeUnits1 + 1;
        int timeUnits3 = timeUnits1 + 2;
        int timeUnits4 = timeUnits1 + 3;
        //当天前15天 70%,当天前45天15%,其他 15%
        c.setTime(end);
        c.add(Calendar.DAY_OF_MONTH, -15);
        //前15天分割时间
        Date slicedTime1 = c.getTime();
        //前45天分割时间
        c.add(Calendar.DAY_OF_MONTH, -30);
        Date slicedTime2 = c.getTime();
        for (Map<String, Object> map : set) {
            String cityCode = map.get("cityCode").toString();
            ArrayList<Map<String, Object>> dataList = (ArrayList<Map<String, Object>>) map.get("data");
            for (Map<String, Object> dataMap : dataList) {
                Map<String, Object> hashMap = new HashMap<>();
                List<String> hours = new ArrayList<>();
                hashMap.put("cityCode", cityCode);
                hashMap.put("start", start);
                hashMap.put("end", end);
                hashMap.put("typeFormat", "%Y-%m-%d %H:%i:%s");
                Integer condition = Integer.valueOf(dataMap.get("condition").toString());
                Double temp = Double.valueOf(dataMap.get("temp").toString());
                Integer pressure = Integer.valueOf(dataMap.get("pressure").toString());
                String time = dataMap.get("time").toString();
                //当前小时
                hours.add(time.substring(11, 13));
                c.setTime(sdf1.parse(time));
                //前1小时
                c.add(Calendar.HOUR_OF_DAY, -1);
                hours.add(sdf1.format(c.getTime()).substring(11, 13));
                //前2小时
                c.add(Calendar.HOUR_OF_DAY, -1);
                String sHour = sdf1.format(c.getTime()).substring(11, 13);
                //后1小时
                c.add(Calendar.HOUR_OF_DAY, 3);
                hours.add(sdf1.format(c.getTime()).substring(11, 13));
                //后2小时
                c.add(Calendar.HOUR_OF_DAY, 1);
                String eHour = sdf1.format(c.getTime()).substring(11, 13);
                hashMap.put("hours", hours);
                hashMap.put("score", 50);
                if (condition > 50) {
                    hashMap.put("condition", 0);
                } else {
                    hashMap.put("condition", 1);
                }
                List<Integer> years = new ArrayList<>();
                Collections.addAll(years, timeUnits1, timeUnits2, timeUnits3, timeUnits4);
                List<String> samples = getSample(hashMap, years);
                if (samples.size() > 100) {
                    hashMap.put("startTemp", temp - 1.0);
                    hashMap.put("endTemp", temp + 1.0);
                    samples = getSample(hashMap, years);
                    if (samples.size() > 100) {
                        hashMap.put("startPressure", pressure - 5);
                        hashMap.put("endPressure", pressure + 5);
                        samples = getSample(hashMap, years);
                    }
                }
                hashMap.put("slicedTime1", slicedTime1);
                hashMap.put("slicedTime2", slicedTime2);
                hashMap.put("nowTemp", temp);
                hashMap.put("sHour", sHour);
                hashMap.put("eHour", eHour);
                Map<String, List<String>> sectionTimesMap = getSectionTimes(hashMap, samples, years);
                Map<String, Object> params = new HashMap<>();
                params.put("cityCode", cityCode);
                params.put("typeFormat", "%Y-%m-%d %H:%i:%s");
                List<String> times1 = sectionTimesMap.get("times1");
                List<String> times2 = sectionTimesMap.get("times2");
                List<String> times3 = sectionTimesMap.get("times3");
                Double avg1 = getAvgO3(params, times1);
                Double avg2 = getAvgO3(params, times2);
                Double avg3 = getAvgO3(params, times3);
                Double resultO3 = avg1 * 0.7 + avg2 * 0.15 + avg3 * 0.15;
                dataMap.put("O3C", String.valueOf(Math.round(resultO3)));
            }

            List<Map<String, Object>> resultList = new ArrayList<>();
            for (Map<String, Object> dataMap : dataList) {
                Map<String, Object> resultMap = new HashMap<>();
                String time = dataMap.remove("time").toString();
                Map<String, Object> params = new HashMap<>();
                params.put("cityCode", cityCode);
                params.put("time", sdf1.parse(time));
                String beam = forecastWeatherMapper.getBeam(params);
                if (beam != null) {
                    dataMap.put("beam",beam.replace("\"",""));
                }
                resultMap.put("cityCode", cityCode);
                resultMap.put("time", time);
                resultMap.put("json", JSONObject.toJSONString(dataMap));
                resultList.add(resultMap);
            }
            Map<String, Object> parameters = new HashMap<>();
            parameters.put("start",sdf1.parse(nextDay + " 01:00:00"));
            c.setTime(sdf1.parse(nextDay + " 01:00:00"));
            c.add(Calendar.DAY_OF_MONTH, 1);
            parameters.put("end", c.getTime());
            parameters.put("cityCode", cityCode);
            forecastWeatherMapper.deleteByTime(parameters);
            count = count + forecastWeatherMapper.insertForecastWeather(resultList);
        }
        return count;
    }

    private List<String> getSample(Map<String, Object> params, List<Integer> years) {
        List<String> samples = new ArrayList<>();
        if (years.size() > 0) {
            for (Integer year : years) {
                params.put("timeUnits", year);
                samples.addAll(realWeatherMapper.getSampleFromHistoryWeather(params));
            }
        }
        samples.addAll(realWeatherMapper.getSampleFromRealWeather(params));
        return samples;
    }

    private Double getAvgO3(Map<String, Object> params, List<String> times) {
        List<String> values = new ArrayList<>();
        for (String time : times) {
            String s = time.substring(0, 4);
            params.put("time", time);
            String O3 = aqiMapper.getO3ByTime(params);
            if (O3 == null) {
                params.put("timeUnits", s);
                O3 = aqiMapper.getO3FromHistory(params);
            }
            if (O3 != null) {
                values.add(O3.replace("\"", ""));
            }
        }
        Double sum = 0.0;
        for (String value : values) {
            sum += Double.valueOf(value);
        }
        return sum / values.size();
    }

    private Map<String, List<String>> getSectionTimes(Map<String, Object> params, List<String> samples, List<Integer> years) throws ParseException {
        Map<String, List<String>> hashMap = new HashMap<>();
        //当天前15天
        List<String> times1 = new ArrayList<>();
        //45-15
        List<String> times2 = new ArrayList<>();
        //其他
        List<String> times3 = new ArrayList<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date slicedTime1 = (Date) (params.get("slicedTime1"));
        Date slicedTime2 = (Date) (params.get("slicedTime2"));
        Double nowTemp = Double.valueOf(params.get("nowTemp").toString());
        ArrayList<String> hours = (ArrayList<String>) params.get("hours");
        for (String sample : samples) {
            Date samTime = sdf.parse(sample);
            if (samTime.getTime() >= slicedTime1.getTime()) {
                times1.add(sample);
            } else if (samTime.getTime() >= slicedTime2.getTime()) {
                times2.add(sample);
            } else {
                times3.add(sample);
            }
        }
        hashMap.put("times1", times1);
        hashMap.put("times2", times2);
        hashMap.put("times3", times3);
        for (String key : hashMap.keySet()) {
            List<String> values = hashMap.get(key);
            if (values.size() == 0) {
                params.put("startTemp", nowTemp - 2.0);
                params.put("endTemp", nowTemp + 2.0);
                if ("times1".equals(key) || "times2".equals(key)) {
                    years = new ArrayList<>();
                    values = getSample(params, years);
                } else {
                    values = getSample(params, years);
                }


                if (values.size() == 0) {
                    hours.add(params.get("sHour").toString());
                    hours.add(params.get("eHour").toString());
                    params.put("hours", hours);
                    if ("times1".equals(key) || "times2".equals(key)) {
                        years = new ArrayList<>();
                        values = getSample(params, years);
                    } else {
                        values = getSample(params, years);
                    }
                }
                hashMap.put(key, values);
            }
        }
        return hashMap;
    }

    @Override
    public int insertForecastWeather() throws ParseException {
        Calendar c = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

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

@@ -24,6 +24,7 @@
import com.moral.entity.Area;
import com.moral.entity.City;
import com.moral.entity.MonitorPoint;
import com.moral.entity.Province;
import com.moral.mapper.AreaMapper;
import com.moral.mapper.CityMapper;
import com.moral.mapper.MonitorPointMapper;
@@ -65,34 +66,48 @@
            Integer areaCode = monitorPoint.getAreaCode();
            Integer cityCode = monitorPoint.getCityCode();
            Integer provinceCode = monitorPoint.getProvinceCode();
            String parentName = "";
            String name1 = "";
            Integer code = 0;
            String parentName;
            String name1;
            Integer code;
            Integer parentCode;
            if (areaCode != null) {
                Area area = areaMapper.getAreaByAreaCode(areaCode);
                City city = cityMapper.getCityByCityCode(cityCode);
                name1 = area.getAreaName();
                parentName = city.getCityName();
                parentCode = cityCode;
                code = areaCode;
                if ("市辖区".equals(name1)) {
                    name1 = parentName;
                    parentName = provinceMapper.getProvinceByProvinceCode(provinceCode).getProvinceName();
                    Province province = provinceMapper.getProvinceByProvinceCode(provinceCode);
                    parentName = province.getProvinceName();
                    parentCode = provinceCode;
                    code = cityCode;
                }
            } else {
                if (cityCode != null) {
                City city = cityMapper.getCityByCityCode(cityCode);
                name1 = city.getCityName();
                parentName = provinceMapper.getProvinceByProvinceCode(provinceCode).getProvinceName();
                code = cityCode;
                    Province province = provinceMapper.getProvinceByProvinceCode(provinceCode);
                    parentName = province.getProvinceName();
                    parentCode = provinceCode;
                if ("市辖区".equals(name1)) {
                    name1 = parentName;
                    parentName = "";
                    code = provinceCode;
                    }
                } else {
                    code = provinceCode;
                    Province province = provinceMapper.getProvinceByProvinceCode(provinceCode);
                    name1 = province.getProvinceName();
                    parentCode = provinceCode;
                    parentName = name1;
                }
            }
            hashMap.put("name1", name1);
            hashMap.put("parentName", parentName);
            hashMap.put("cityCode", code);
            hashMap.put("parentCode", parentCode);
            hashSet.add(hashMap);
        }


 src/main/java/com/moral/task/ForecastWeatherInsertTask.java

@@ -28,4 +28,14 @@
            return new ReturnT(500, "插入失败");
        }
    }

    @XxlJob("forecastWeatherHourNew")
    public ReturnT insertForecastWeatherNew(String param) throws ParseException {
        int count = forecastWeatherService.insertForecastWeatherNew();
        if (count > 0) {
            return new ReturnT(200, "插入成功");
        }else {
            return new ReturnT(500, "插入失败");
        }
    }
}

 src/main/resources/mapper/AQIMapper.xml

@@ -24,6 +24,13 @@
        </foreach>
    </select>

    <select id="getO3ByTime" resultType="java.lang.String">
        select ifnull(aqi_json->'$.O3C',aqi_json->'$.O3') as 'O3'
        from hangzhou_aqi
        where city_code=#{cityCode}
        and DATE_FORMAT(time, #{typeFormat})=#{time}
    </select>

    <select id="getAqiByHour" resultType="java.util.Map">
        select DATE_FORMAT(time, '%Y-%m-%d %H:%i:%s') time,
        value
@@ -31,4 +38,11 @@
        where city_code=#{cityCode}
        and DATE_FORMAT(time, '%Y%H')=#{yearAndHour}
    </select>

    <select id="getO3FromHistory" resultType="java.lang.String">
        select value ->'$.O3C' as 'O3'
        from history_aqi_${timeUnits}
        where city_code=#{cityCode}
        and DATE_FORMAT(time, #{typeFormat})=#{time}
    </select>
</mapper>

 src/main/resources/mapper/ForecastWeatherMapper.xml

@@ -20,4 +20,11 @@
        AND time <![CDATA[<]]> #{end}
        and city_code=#{cityCode}
    </delete>

    <select id="getBeam" resultType="java.lang.String">
        select json->'$.beam' as 'beam'
        from forecast_weather
        where time=#{time}
        and city_code=#{cityCode}
    </select>
</mapper>

 src/main/resources/mapper/RealWeatherMapper.xml

@@ -85,5 +85,81 @@
            DATE_FORMAT(time, '%Y%H')=#{yearAndHour}
            or  DATE_FORMAT(time, '%Y%H')=#{yearAndHour1}
        )
    </select>

    <select id="getSampleFromHistoryWeather" resultType="java.lang.String">
        select DATE_FORMAT(time, #{typeFormat}) as time
        from history_weather_${timeUnits}
        <where>
            city_code=#{cityCode}
            <if test="condition == 0">
                and cast(value->'$.condition' as UNSIGNED integer) > #{score}
            </if>

            <if test="condition == 1">
                and cast(value->'$.condition' as UNSIGNED integer) <![CDATA[<=]]> #{score}
            </if>

            <if test="startTemp != null">
                and cast(value->'$.temp' as decimal(10, 1)) >= #{startTemp}
            </if>

            <if test="endTemp != null">
                and cast(value->'$.temp' as decimal(10, 1)) <![CDATA[<=]]> #{endTemp}
            </if>

            <if test="startPressure != null">
                and cast(value->'$.pressure' as UNSIGNED integer) >= #{startPressure}
            </if>

            <if test="endPressure != null">
                and cast(value->'$.pressure' as UNSIGNED integer) <![CDATA[<=]]> #{endPressure}
            </if>

            and time >= #{start}
            and time <![CDATA[<]]> #{end}
            and DATE_FORMAT(time, '%H') in
            <foreach collection="hours" open="(" separator="," close=")" item="hour">
                #{hour}
            </foreach>
        </where>
    </select>

    <select id="getSampleFromRealWeather" resultType="java.lang.String">
        select DATE_FORMAT(time, #{typeFormat}) as time
        from real_weather
        <where>
            city_code=#{cityCode}
            <if test="condition == 0">
                and cast(json->'$.condition' as UNSIGNED integer) > #{score}
            </if>

            <if test="condition == 1">
                and cast(json->'$.condition' as UNSIGNED integer) <![CDATA[<=]]> #{score}
            </if>

            <if test="startTemp != null">
                and cast(json->'$.temp' as decimal(10, 1)) >= #{startTemp}
            </if>

            <if test="endTemp != null">
                and cast(json->'$.temp' as decimal(10, 1)) <![CDATA[<=]]> #{endTemp}
            </if>

            <if test="startPressure != null">
                and cast(json->'$.pressure' as UNSIGNED integer) >= #{startPressure}
            </if>

            <if test="endPressure != null">
                and cast(json->'$.pressure' as UNSIGNED integer) <![CDATA[<=]]> #{endPressure}
            </if>

            and time >= #{start}
            and time <![CDATA[<]]> #{end}
            and DATE_FORMAT(time, '%H') in
            <foreach collection="hours" open="(" separator="," close=")" item="hour">
                #{hour}
            </foreach>
        </where>
    </select>
</mapper>