从history获取数据接口更新为从history日表获取

jinpengyong

2020-11-18 e786ed5d01635555dac37c69003bdbbf757d69d8

从history获取数据接口更新为从history日表获取

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

@@ -74,12 +74,10 @@
        Map<String, Object> resultMap = new LinkedHashMap<String, Object>();

        accountService.setOrgIdsByAccount(parameters);

        LocalDateTime time = LocalDateTime.now();

        // 当前时间 -10分钟

        LocalDateTime start = time.minusMinutes(10);

        parameters.put("start", start);

        parameters.put("end", time);

        // 当前时间 -5分钟

        LocalDateTime end = time.minusMinutes(5);

        parameters.put("end", end);

        LocalDateTime start = time.minusMinutes(5);

        parameters.put("start", start);

        List<Sensor> Sensors = sensorMapper.getSensorsByCriteria(parameters);

        List<String> sensorKeys = new ArrayList<String>();

        for (Sensor sensor : Sensors) {

@@ -87,7 +85,7 @@
        }

        parameters.put("sensorKeys", sensorKeys);

        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyyMM");

        parameters.put("timeUnits", "minutely_" + df.format(start));

        parameters.put("timeUnits", "five_minutely_" + df.format(time));

        List<Map<String, Object>> list = historyMapper.getAreaAllDataByAccount(parameters);

        resultMap.putAll(list.get(0));

        return resultMap;

@@ -111,7 +109,6 @@
        parameters.put("sensorKeys", sensorKeys);

        parameters.put("timeUnits", "hourly");

        List<Map<String, Object>> list = historyMapper.getAreaAllDataByAccount(parameters);



        for (Map<String, Object> map : list) {

            result.put((String) map.get("name"), map.get(macKey.toString()));

        }

@@ -386,7 +383,6 @@
            resultMap.put(map.remove("sensor_key").toString(), map);

        }

        return resultMap;



    }



    @Override


 src/main/resources/mapper/HistoryMapper.xml

@@ -13,7 +13,7 @@
            d.`name`,

        </if>

        <foreach collection="sensorKeys" separator="," item="sensorKey">

            AVG(h.`value` ->'$.${sensorKey}') AS '${sensorKey}'

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

        </foreach>

        FROM

        history_${timeUnits} h,

@@ -164,7 +164,7 @@
    </select>



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

        SELECT AVG(JSON_EXTRACT(value,'$.e1[0]')) e1,AVG(JSON_EXTRACT(value,'$.e2[0]')) e2,AVG(JSON_EXTRACT(value,'$.e10[0]')) e10,AVG(JSON_EXTRACT(value,'$.e11[0]')) e11,AVG(JSON_EXTRACT(value,'$.e16[0]')) e16,AVG(JSON_EXTRACT(value,'$.e15[0]')) e15

        SELECT AVG(JSON_EXTRACT(value,'$.e1')) e1,AVG(JSON_EXTRACT(value,'$.e2')) e2,AVG(JSON_EXTRACT(value,'$.e10')) e10,AVG(JSON_EXTRACT(value,'$.e11')) e11,AVG(JSON_EXTRACT(value,'$.e16')) e16,AVG(JSON_EXTRACT(value,'$.e15')) e15

        FROM history_${timeUnits}

        WHERE mac = #{mac}

        AND time >= #{starttime}

@@ -196,7 +196,7 @@
    </select>



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

        SELECT mp.name, AVG(JSON_EXTRACT(h.value,'$.${sensor}[0]')) sensor

        SELECT mp.name, AVG(JSON_EXTRACT(h.value,'$.${sensor}')) sensor

        FROM device d, history_${timeUnits} h, monitor_point mp

        WHERE d.mac=h.mac

        And mp.id=d.monitor_point_id

@@ -206,11 +206,11 @@
        </foreach>

        AND h.time BETWEEN #{before5Time} AND #{endTime}

        GROUP BY d.monitor_point_id

        ORDER BY AVG(JSON_EXTRACT(h.value,'$.${sensor}[0]')) DESC

        ORDER BY AVG(JSON_EXTRACT(h.value,'$.${sensor}')) DESC

    </select>



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

        SELECT AVG(JSON_EXTRACT(value,'$.e1[0]')) e1,AVG(JSON_EXTRACT(value,'$.e2[0]')) e2,AVG(JSON_EXTRACT(value,'$.e10[0]')) e10,AVG(JSON_EXTRACT(value,'$.e11[0]')) e11,AVG(JSON_EXTRACT(value,'$.e16[0]')) e16,AVG(JSON_EXTRACT(value,'$.e15[0]')) e15

        SELECT AVG(JSON_EXTRACT(value,'$.e1')) e1,AVG(JSON_EXTRACT(value,'$.e2')) e2,AVG(JSON_EXTRACT(value,'$.e10')) e10,AVG(JSON_EXTRACT(value,'$.e11')) e11,AVG(JSON_EXTRACT(value,'$.e16')) e16,AVG(JSON_EXTRACT(value,'$.e15')) e15

        FROM device d,history_${timeUnits} h

        WHERE d.monitor_point_id = #{monitor_point_id}

        AND d.mac = h.mac