MYSQL 통계 질문드려요~ 0 2 607

by 성울 [MySQL] [2018.03.16 14:44:53]


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 값을 가져오려합니다. ㅠ

평균이요

하루동안 시간당 평균입니다. 

 

 

by 춤과파티 [2018.03.16 15:09:37]

case 쓰심 대지 않을까요?


by 마농 [2018.03.16 15:18:24]
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
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입