SELECT count(TA.TEMPERATURE_VAL) as cnt, date_format(TA.DEVICE_TIME,'%H') as time_h, SUM(TA.TEMPERATURE_VAL) as sum, avg(TA.TEMPERATURE_VAL) as avg FROM (SELECT * FROM tb_sensor_statistics WHERE DEVICE_ID ='TST-0003' AND SENSOR_NUM = '2' AND DEVICE_TIME BETWEEN '2018-03-16 00:00:00' AND '2018-03-16 23:59:59' -- 시간 범위 조건 )TA GROUP BY date_format(TA.DEVICE_TIME, '%H') ORDER BY TA.DEVICE_TIME asc
이렇게 해서 데이터 가져오고있는데요..
데이터 있는 시간만 나와서
없는시간.. 즉
1시, 3시 데이터가 있고 2시에 데이터가 없으면
현재는
01 | 10 |
03 | 10 |
원하는값은
01 | 10 |
02 | 0 |
03 | 10 |
입니다.
데이터 없는시간도
시간과 데이터 0 값을 가져오려합니다. ㅠ
평균이요
하루동안 시간당 평균입니다.
SELECT a.time_h , IFNULL(b.cnt, 0) cnt , IFNULL(b.sum, 0) sum , IFNULL(b.avg, 0) avg FROM (SELECT '00' time_h UNION ALL SELECT '01' UNION ALL SELECT '02' UNION ALL SELECT '03' UNION ALL SELECT '04' UNION ALL SELECT '05' UNION ALL SELECT '06' UNION ALL SELECT '07' UNION ALL SELECT '08' UNION ALL SELECT '09' UNION ALL SELECT '10' UNION ALL SELECT '11' UNION ALL SELECT '12' UNION ALL SELECT '13' UNION ALL SELECT '14' UNION ALL SELECT '15' UNION ALL SELECT '16' UNION ALL SELECT '17' UNION ALL SELECT '18' UNION ALL SELECT '19' UNION ALL SELECT '20' UNION ALL SELECT '21' UNION ALL SELECT '22' UNION ALL SELECT '23' ) a LEFT OUTER JOIN (SELECT DATE_FORMAT(device_time, '%H') AS time_h , COUNT(temperature_val) AS cnt , SUM (temperature_val) AS sum , AVG (temperature_val) AS avg FROM tb_sensor_statistics WHERE device_id ='TST-0003' AND sensor_num = '2' AND device_time BETWEEN '2018-03-16 00:00:00' AND '2018-03-16 23:59:59' GROUP BY DATE_FORMAT(device_time, '%H') ) b ON a.time_h = b.time_h ORDER BY a.time_h ;