parent: 2c42977a | patch | commit | show whitespace
kaiyu
2020-10-27 07a1946f06e317796c7e07807db11d8251b77655 
计算平均风向
4 files modified
125 ■■■■■ changed files
src/main/resources/mapper/HistoryFiveMinutelyMapper.xml 17 ●●●●● patch | view | raw | blame | history
src/main/resources/mapper/HistoryHourlyMapper.xml 22 ●●●●● patch | view | raw | blame | history
src/main/resources/mapper/HistoryMapper.xml 64 ●●●●● patch | view | raw | blame | history
src/main/resources/mapper/HistoryMinutelyMapper.xml 22 ●●●●● patch | view | raw | blame | history 
 src/main/resources/mapper/HistoryFiveMinutelyMapper.xml


@@ -1,13 +1,30 @@


<?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.HistoryFiveMinutelyMapper">




    <select id="getFiveMinutesSensorData" resultType="java.util.LinkedHashMap">


        select


        h.mac


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


            <choose >


              <when test="sensorKey=='e23'">


                  ROUND((CASE    WHEN AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))<![CDATA[>]]>0 AND AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI()))<![CDATA[>]]>0


                  THEN ATAN(AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))/AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI())))*180/PI()


                  WHEN AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))>0 AND AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI()))<![CDATA[<]]>0


                  THEN (ATAN(AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))/AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI())))*180/PI())+180


                  WHEN AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))<![CDATA[<]]>0 AND AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI()))<![CDATA[<]]>0


                  THEN (ATAN(AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))/AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI())))*180/PI())+180


                  ELSE (ATAN(AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))/AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI())))*180/PI())+360


                  END),3) AS 'AVG${sensorKey}',


                  ROUND(MIN(json->'$.${sensorKey}[1]'),3) AS 'MIN${sensorKey}',


                  ROUND(MAX(json->'$.${sensorKey}[2]'),3) AS 'MAX${sensorKey}'


              </when>


                <otherwise>


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


            ROUND(MIN(json->'$.${sensorKey}[1]'),3) AS 'MIN${sensorKey}',


            ROUND(MAX(json->'$.${sensorKey}[2]'),3) AS 'MAX${sensorKey}'


                </otherwise>


            </choose>


        </foreach>


        FROM


        history_minutely_${yearAndMonth} h




 src/main/resources/mapper/HistoryHourlyMapper.xml


@@ -15,9 +15,25 @@


        SELECT


        h.mac,


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


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


            MIN(json->'$.${sensorKey}[1]') AS 'MIN${sensorKey}',


            MAX(json->'$.${sensorKey}[2]') AS 'MAX${sensorKey}'


            <choose >


                <when test="sensorKey=='e23'">


                    ROUND((CASE    WHEN AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))<![CDATA[>]]>0 AND AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI()))<![CDATA[>]]>0


                    THEN ATAN(AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))/AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI())))*180/PI()


                    WHEN AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))>0 AND AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI()))<![CDATA[<]]>0


                    THEN (ATAN(AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))/AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI())))*180/PI())+180


                    WHEN AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))<![CDATA[<]]>0 AND AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI()))<![CDATA[<]]>0


                    THEN (ATAN(AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))/AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI())))*180/PI())+180


                    ELSE (ATAN(AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))/AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI())))*180/PI())+360


                    END),3) AS '${sensorKey}',


                    ROUND(MIN(json->'$.${sensorKey}[1]'),3) AS 'MIN${sensorKey}',


                    ROUND(MAX(json->'$.${sensorKey}[2]'),3) AS 'MAX${sensorKey}'


                </when>


                <otherwise>


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


                    ROUND(MIN(json->'$.${sensorKey}[1]'),3) AS 'MIN${sensorKey}',


                    ROUND(MAX(json->'$.${sensorKey}[2]'),3) AS '${sensorKey}'


                </otherwise>


            </choose>


        </foreach>


        FROM


        history_hourly h




 src/main/resources/mapper/HistoryMapper.xml


@@ -86,9 +86,25 @@


        SELECT



        h.mac,



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



            <choose >



            <when test="sensorKey=='e23'">



            ROUND((CASE    WHEN AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))<![CDATA[>]]>0 AND AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI()))<![CDATA[>]]>0



                THEN ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI()



                WHEN AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))>0 AND AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI()))<![CDATA[<]]>0



                THEN (ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI())+180



                WHEN AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))<![CDATA[<]]>0 AND AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI()))<![CDATA[<]]>0



                THEN (ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI())+180



                ELSE (ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI())+360



                END),3) AS '${sensorKey}',



                MAX(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MAX${sensorKey}',



                MIN(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MIN${sensorKey}'



            </when>



            <otherwise>



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



            MAX(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MAX${sensorKey}',



            MIN(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MIN${sensorKey}'



            </otherwise>



            </choose>



        </foreach>



        FROM



        history h



@@ -103,9 +119,25 @@


        SELECT



        h.mac,



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



            <choose >



                <when test="sensorKey=='e23'">



                    ROUND((CASE    WHEN AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))<![CDATA[>]]>0 AND AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI()))<![CDATA[>]]>0



                    THEN ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI()



                    WHEN AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))>0 AND AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI()))<![CDATA[<]]>0



                    THEN (ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI())+180



                    WHEN AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))<![CDATA[<]]>0 AND AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI()))<![CDATA[<]]>0



                    THEN (ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI())+180



                    ELSE (ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI())+360



                    END),3) AS '${sensorKey}',



                    MAX(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MAX${sensorKey}',



                    MIN(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MIN${sensorKey}'



                </when>



                <otherwise>



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



            MAX(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MAX${sensorKey}',



            MIN(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MIN${sensorKey}'



                </otherwise>



            </choose>



        </foreach>



        FROM



        history h



@@ -124,9 +156,25 @@


        SELECT



        h.mac,DATE_FORMAT(time,'%Y-%m-%dT%H:%i') time,



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



            <choose >



                <when test="sensorKey=='e23'">



                    ROUND((CASE    WHEN AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))<![CDATA[>]]>0 AND AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI()))<![CDATA[>]]>0



                    THEN ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI()



                    WHEN AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))>0 AND AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI()))<![CDATA[<]]>0



                    THEN (ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI())+180



                    WHEN AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))<![CDATA[<]]>0 AND AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI()))<![CDATA[<]]>0



                    THEN (ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI())+180



                    ELSE (ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI())+360



                    END),3) AS '${sensorKey}',



                    MAX(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MAX${sensorKey}',



                    MIN(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MIN${sensorKey}'



                </when>



                <otherwise>



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



            MAX(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MAX${sensorKey}',



            MIN(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MIN${sensorKey}'



                </otherwise>



            </choose>



        </foreach>



        FROM



        history h



@@ -145,9 +193,25 @@


        SELECT



        h.mac,DATE_FORMAT(time,'%Y-%m-%dT%H:%i') time,



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



            <choose >



                <when test="sensorKey=='e23'">



                    ROUND((CASE    WHEN AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))<![CDATA[>]]>0 AND AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI()))<![CDATA[>]]>0



                    THEN ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI()



                    WHEN AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))>0 AND AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI()))<![CDATA[<]]>0



                    THEN (ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI())+180



                    WHEN AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))<![CDATA[<]]>0 AND AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI()))<![CDATA[<]]>0



                    THEN (ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI())+180



                    ELSE (ATAN(AVG(value->'$.e18'*SIN((value->'$.e23'/180)*PI()))/AVG(value->'$.e18'*COS((value->'$.e23'/180)*PI())))*180/PI())+360



                    END),3) AS '${sensorKey}',



                    MAX(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MAX${sensorKey}',



                    MIN(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MIN${sensorKey}'



                </when>



                <otherwise>



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



            MAX(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MAX${sensorKey}',



            MIN(cast(h.value ->'$.${sensorKey}' as decimal(11,3))) AS 'MIN${sensorKey}'



                </otherwise>



            </choose>



        </foreach>



        FROM



        history h





 src/main/resources/mapper/HistoryMinutelyMapper.xml


@@ -25,9 +25,25 @@


        SELECT


        h.mac,


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


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


            MIN(json->'$.${sensorKey}[1]') AS 'MIN${sensorKey}',


            MAX(json->'$.${sensorKey}[2]') AS 'MAX${sensorKey}'


            <choose >


                <when test="sensorKey=='e23'">


                    ROUND((CASE    WHEN AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))<![CDATA[>]]>0 AND AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI()))<![CDATA[>]]>0


                    THEN ATAN(AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))/AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI())))*180/PI()


                    WHEN AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))>0 AND AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI()))<![CDATA[<]]>0


                    THEN (ATAN(AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))/AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI())))*180/PI())+180


                    WHEN AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))<![CDATA[<]]>0 AND AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI()))<![CDATA[<]]>0


                    THEN (ATAN(AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))/AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI())))*180/PI())+180


                    ELSE (ATAN(AVG(json->'$.e18[0]'*SIN((json->'$.e23[0]'/180)*PI()))/AVG(json->'$.e18[0]'*COS((json->'$.e23[0]'/180)*PI())))*180/PI())+360


                    END),3) AS '${sensorKey}',


                    ROUND(MIN(json->'$.${sensorKey}[1]'),3) AS 'MIN${sensorKey}',


                    ROUND(MAX(json->'$.${sensorKey}[2]'),3) AS 'MAX${sensorKey}'


                </when>


                <otherwise>


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


                    ROUND(MIN(json->'$.${sensorKey}[1]'),3) AS 'MIN${sensorKey}',


                    ROUND(MAX(json->'$.${sensorKey}[2]'),3) AS 'MAX${sensorKey}'


                </otherwise>


            </choose>


        </foreach>


        FROM


        history_minutely_${yearAndMonth} h