| | |
| | | hd.mac = vdi.mac
|
| | | AND vdi.monitor_point_id = mp.id
|
| | | AND vdi.device_tech = 1
|
| | | <if test="monitorPointId != null and monitorPointId!=''">
|
| | | AND mp.id = #{monitorPointId}
|
| | | </if>
|
| | | <if test="mac != null and mac!=''">
|
| | | AND hd.mac = #{mac}
|
| | | </if>
|
| | | <if test="start != null">
|
| | | AND hd.time >= #{start}
|
| | | </if>
|
| | |
| | | <select id="getOverproofData" resultType="java.util.Map">
|
| | | SELECT
|
| | | AVG( hd.`json` -> '$.${sensorKey}[0]' ) avg,
|
| | | ( SELECT DISTINCT limit_val ->> '$.${sensorKey}' FROM view_device_density WHERE monitor_point_id = vdd.monitor_point_id AND device_tech = vdd.device_tech ) `limit`,
|
| | | ( SELECT DISTINCT limit_val ->> '$.${sensorKey}' FROM view_device_density WHERE monitor_point_id =
|
| | | vdd.monitor_point_id AND device_tech = vdd.device_tech ) `limit`,
|
| | | ( SELECT mp.`name` FROM monitor_point mp WHERE mp.id = vdd.monitor_point_id ) `name`,
|
| | | vdd.device_tech
|
| | | FROM
|
| | |
| | | vdd.mac,
|
| | | vdd.monitor_point_id
|
| | | HAVING
|
| | | AVG( hd.`json` -> '$.${sensorKey}[0]' ) >= ( SELECT limit_val ->> '$.${sensorKey}' FROM view_device_density WHERE mac = vdd.mac ) * (1 + #{overproofRatio} * 1) |
| | | AVG( hd.`json` -> '$.${sensorKey}[0]' ) >= ( SELECT limit_val ->> '$.${sensorKey}' FROM view_device_density
|
| | | WHERE mac = vdd.mac ) * (1 + #{overproofRatio} * 1)
|
| | | <if test="overproofRatio != 0 and overproofRatio != 3">
|
| | | AND AVG( hd.`json` -> '$.${sensorKey}[0]' ) < ( SELECT limit_val ->> '$.${sensorKey}' FROM view_device_density WHERE mac = vdd.mac ) * (2 + #{overproofRatio} * 1) |
| | | AND AVG( hd.`json` -> '$.${sensorKey}[0]' ) < ( SELECT limit_val ->> '$.${sensorKey}' FROM
|
| | | view_device_density WHERE mac = vdd.mac ) * (2 + #{overproofRatio} * 1)
|
| | | </if>
|
| | | ) ta
|
| | | <where>
|