新增无人机当天查询sql

ZhuDongming

2019-08-13 83941694ce880ee4c0e280ca08ab9361d4706dd9

新增无人机当天查询sql

 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>