parent: 738b5136 | patch | commit | show whitespace
jinpengyong
2020-11-09 a619ac8760ff7823c6f84ae01522ddd9bae9d377 
history 风向sql更正
1 files modified
64 ■■■■■ changed files
src/main/resources/mapper/HistoryMapper.xml 64 ●●●●● patch | view | raw | blame | history 
 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