ZhuDongming
2019-08-13 83941694ce880ee4c0e280ca08ab9361d4706dd9
新增无人机当天查询sql
1 files modified
276 ■■■■ changed files
src/main/resources/mapper/HistoryMapper.xml 276 ●●●● patch | view | raw | blame | history
src/main/resources/mapper/HistoryMapper.xml
@@ -1,111 +1,111 @@
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.moral.mapper.HistoryMapper">
    <select id="selectValueByMacAndTime" resultType="java.lang.String">
    <select id="selectValueByMacAndTime" resultType="java.lang.String">
        select `value` from history
        where  time = #{time} and mac = #{mac}
        limit 0,1
    </select>
    <select id="getAreaAllDataByAccount" resultType="java.util.LinkedHashMap">
        SELECT
        <if test="macKey != null">
            d.`name`,
        </if>
        <foreach collection="sensorKeys" separator="," item="sensorKey">
            AVG(h.`value` ->'$.${sensorKey}[0]') AS '${sensorKey}'
        </foreach>
        FROM
            history h,
            device d,
            monitor_point mp
        WHERE
            h.mac = d.mac
            AND d.monitor_point_id = mp.id
            <if test="areaCode != null">
                AND mp.area_code = #{areaCode}
            </if>
            <if test="start != null">
                AND h.time >= #{start}
            </if>
            <if test="end != null">
                AND h.time &lt; #{end}
            </if>
            <if test="monitorPointId != null">
                AND mp.id = #{monitorPointId}
            </if>
            <if test="orgIds != null and orgIds.size &gt; 0">
                AND mp.organization_id IN
                <foreach collection="orgIds" open="(" separator="," close=")"
                    item="listItem">
                    #{listItem}
                </foreach>
            </if>
        <if test="macKey != null">
            GROUP BY d.id
            ORDER BY ${macKey} desc
        </if>
    </select>
    <select id="getAreaAllDataByAccount" resultType="java.util.LinkedHashMap">
        SELECT
        <if test="macKey != null">
            d.`name`,
        </if>
        <foreach collection="sensorKeys" separator="," item="sensorKey">
            AVG(h.`value` ->'$.${sensorKey}[0]') AS '${sensorKey}'
        </foreach>
        FROM
        history h,
        device d,
        monitor_point mp
        WHERE
        h.mac = d.mac
        AND d.monitor_point_id = mp.id
        <if test="areaCode != null">
            AND mp.area_code = #{areaCode}
        </if>
        <if test="start != null">
            AND h.time >= #{start}
        </if>
        <if test="end != null">
            AND h.time &lt; #{end}
        </if>
        <if test="monitorPointId != null">
            AND mp.id = #{monitorPointId}
        </if>
        <if test="orgIds != null and orgIds.size &gt; 0">
            AND mp.organization_id IN
            <foreach collection="orgIds" open="(" separator="," close=")"
                     item="listItem">
                #{listItem}
            </foreach>
        </if>
        <if test="macKey != null">
            GROUP BY d.id
            ORDER BY ${macKey} desc
        </if>
    </select>
    <select id="getRegionRankingData" resultType="java.util.Map">
        SELECT
            AVG( h.${column} -> '$.${sensorKey}[0]' ) avg ,
            <if test="dimension == 'profession'">
                p.`name`
            </if>
            <if test="dimension == 'monitorPoint'">
                mp.`name`
            </if>
            <if test="dimension == 'region'">
                r.${nextLevel}_name AS 'name'
            </if>
        FROM
            <if test="dimension == 'profession'">
                profession p ,
            </if>
            <if test="dimension == 'region'">
                ${nextLevel} r,
            </if>
            device d,
            ${table} h,
            monitor_point mp
        WHERE
            d.mac = h.mac
            AND d.is_delete = '0'
            AND mp.is_delete = '0'
            AND d.monitor_point_id = mp.id
            AND h.time >= #{start}
            AND mp.${regionType}_code = #{regionCode}
            <if test="dimension == 'profession'">
                AND d.profession_id = p.id
            </if>
            <if test="dimension == 'region'">
                AND r.${nextLevel}_code = mp.${nextLevel}_code
            </if>
            <if test="orgIds != null and orgIds.size &gt; 0">
                AND mp.organization_id IN
                <foreach  collection="orgIds" open="(" separator="," close=")" item="listItem">
                    #{listItem}
                </foreach>
            </if>
        GROUP BY
            <if test="dimension == 'profession'">
                p.id
            </if>
            <if test="dimension == 'monitorPoint'">
                mp.id
            </if>
            <if test="dimension == 'region'">
                mp.${nextLevel}_code
            </if>
        ORDER BY
            avg DESC
        <if test="dimension == 'monitorPoint'">
        LIMIT 0,50
        </if>
    </select>
    <select id="selectNextLevelRegion" resultType="string">
    <select id="getRegionRankingData" resultType="java.util.Map">
        SELECT
        AVG( h.${column} -> '$.${sensorKey}[0]' ) avg ,
        <if test="dimension == 'profession'">
            p.`name`
        </if>
        <if test="dimension == 'monitorPoint'">
            mp.`name`
        </if>
        <if test="dimension == 'region'">
            r.${nextLevel}_name AS 'name'
        </if>
        FROM
        <if test="dimension == 'profession'">
            profession p ,
        </if>
        <if test="dimension == 'region'">
            ${nextLevel} r,
        </if>
        device d,
        ${table} h,
        monitor_point mp
        WHERE
        d.mac = h.mac
        AND d.is_delete = '0'
        AND mp.is_delete = '0'
        AND d.monitor_point_id = mp.id
        AND h.time >= #{start}
        AND mp.${regionType}_code = #{regionCode}
        <if test="dimension == 'profession'">
            AND d.profession_id = p.id
        </if>
        <if test="dimension == 'region'">
            AND r.${nextLevel}_code = mp.${nextLevel}_code
        </if>
        <if test="orgIds != null and orgIds.size &gt; 0">
            AND mp.organization_id IN
            <foreach collection="orgIds" open="(" separator="," close=")" item="listItem">
                #{listItem}
            </foreach>
        </if>
        GROUP BY
        <if test="dimension == 'profession'">
            p.id
        </if>
        <if test="dimension == 'monitorPoint'">
            mp.id
        </if>
        <if test="dimension == 'region'">
            mp.${nextLevel}_code
        </if>
        ORDER BY
        avg DESC
        <if test="dimension == 'monitorPoint'">
            LIMIT 0,50
        </if>
    </select>
    <select id="selectNextLevelRegion" resultType="string">
        SELECT
            ${nextLevel}_name AS 'name' 
        FROM
@@ -114,7 +114,7 @@
            ${regionType}_code = #{regionCode}
    </select>
    <select id="getDischargeByRegion" resultType="java.util.Map">
    <select id="getDischargeByRegion" resultType="java.util.Map">
        SELECT
            mp.`name`,
            dis.`value` -> '$.${sensorKey}' 'value'
@@ -126,8 +126,8 @@
            AND mp.${regionType}_code = #{regionCode}
            AND dis.`year` = #{year}
    </select>
    <select id="getValueByMacAndSize" resultType="java.util.Map">
    <select id="getValueByMacAndSize" resultType="java.util.Map">
        SELECT 
            value,
            time
@@ -139,34 +139,56 @@
            time DESC 
        LIMIT #{size}
    </select>
    <select id="getActualDataByRegion" resultType="java.util.Map">
        SELECT
        <foreach collection="sensorKeys" separator="," item="sensorKey">
            MAX( `value` ->> '$.${sensorKey}' ) * 1 AS '${sensorKey}'
        </foreach>
        FROM
            history h ,
            device d,
            monitor_point mp
        <where>
            h.mac = d.mac
            AND d.monitor_point_id = mp.id
        <if test="orgIds != null and orgIds.size &gt; 0">
            AND mp.organization_id IN
            <foreach  collection="orgIds" open="(" separator="," close=")" item="listItem">
                #{listItem}
            </foreach>
        </if>
            AND h.time >= #{start}
            AND h.time &lt; #{end}
        </where>
    </select>
    <select id="getAVGValueByMacAndTimeslot" resultType="java.util.Map">
    <select id="getActualDataByRegion" resultType="java.util.Map">
        SELECT
        <foreach collection="sensorKeys" separator="," item="sensorKey">
            MAX( `value` ->> '$.${sensorKey}' ) * 1 AS '${sensorKey}'
        </foreach>
        FROM
        history h ,
        device d,
        monitor_point mp
        <where>
            h.mac = d.mac
            AND d.monitor_point_id = mp.id
            <if test="orgIds != null and orgIds.size &gt; 0">
                AND mp.organization_id IN
                <foreach collection="orgIds" open="(" separator="," close=")" item="listItem">
                    #{listItem}
                </foreach>
            </if>
            AND h.time >= #{start}
            AND h.time &lt; #{end}
        </where>
    </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
        FROM history
        WHERE mac = #{mac}
        AND time BETWEEN #{starttime} AND #{endtime}
    </select>
    <!-- 无人机sensor当天查询 -->
    <select id="getSensorDataToday" resultType="java.util.LinkedHashMap">
        SELECT
        DATE_FORMAT(time,'%Y-%m-%d %H:%i') time,
        <foreach collection="sensorKeys" separator="," item="sensorKey">
            AVG(h.value ->'$.${sensorKey}') AS '${sensorKey}'
        </foreach>
        FROM
        history h
        <where>
            <if test="mac!=null">
                and h.mac=#{mac}
            </if>
            and h.time >= #{startTime}
            and h.time <![CDATA[<=]]> #{endTime}
        </where>
        GROUP BY
        DATE_FORMAT(time,'%Y-%m-%d %H:%i')
        ORDER BY
        time
    </select>
</mapper>