| | |
|---|
| | |
|
|---|
| | | <select id="getDevicesAvgDataToExcel" resultType="java.util.Map">
|
|---|
| | | SELECT
|
|---|
| | | h.mac,DATE_FORMAT(time, #{typeFormat}) time,
|
|---|
| | | d.name,DATE_FORMAT(time, #{typeFormat}) time,
|
|---|
| | | <foreach collection="sensorKeys" separator="," item="sensorKey">
|
|---|
| | | AVG(json->'$.${sensorKey}[0]') AS '${sensorKey}'
|
|---|
| | | </foreach>
|
|---|
| | | FROM
|
|---|
| | | history_${timeUnits} h
|
|---|
| | | history_${timeUnits} h,
|
|---|
| | | device d
|
|---|
| | | WHERE
|
|---|
| | | h.mac=d.mac
|
|---|
| | | AND
|
|---|
| | | h.time >= #{start}
|
|---|
| | | AND h.time <![CDATA[<=]]> #{end}
|
|---|
| | | AND h.mac in
|
|---|
| | | AND h.mac in
|
|---|
| | | <foreach collection="macs" open="(" separator="," close=")"
|
|---|
| | | item="mac">
|
|---|
| | | #{mac}
|
|---|
| | | </foreach>
|
|---|
| | | GROUP BY
|
|---|
| | | h.mac,DATE_FORMAT(time, #{typeFormat})
|
|---|
| | | h.mac,d.name,DATE_FORMAT(time, #{typeFormat})
|
|---|
| | | ORDER BY
|
|---|
| | | h.mac
|
|---|
| | | </select>
|
|---|
