좀 어려운 쿼리문 안되는데 검토 부탁 드립니다.
본문
약 1분에 하나씩 데이타가 입력되는 myTable 이 있습니다.
쿼리문의 의도는 다음과 같습니다.
하루가 지난것을 시간대별로 평균값을 내서 축약 하고자 합니다.
즉 입력된 데이타를 하루가 지나면, 24개로 줄이고자 합니다.
myTable 주요컬럼은 아래와 같고
`temp` INT(2) NULL DEFAULT '0',
`hum` INT(2) NULL DEFAULT '0',
`wRST` INT(2) NULL DEFAULT '0',
`date` DATETIME NULL DEFAULT '0000-00-00 00:00:00',
다음과 같이 쿼리문을 작성 했습니다만 어떤 변화도 없네요
에러메세지도 없구요.
sql_query(" CREATE TEMPORARY TABLE temp_avg AS
SELECT DATE(date) AS date,
HOUR(date) AS hour,
AVG(temp) AS avg_temp,
AVG(hum) AS avg_humidity,
AVG(wRST) AS avg_resistance
FROM myTable
WHERE date BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW()
GROUP BY date, hour ");
sql_query(" UPDATE myTable AS t1
JOIN (
SELECT date, hour, MIN(date) AS min_dt
FROM temp_avg
GROUP BY date, hour
) AS t2
ON t1.date = t2.min_dt
SET t1.temp = t2.avg_temp,
t1.hum = t2.avg_humidity,
t1.wRST = t2.avg_resistance ");
sql_query(" DELETE FROM myTable WHERE date < DATE_SUB(NOW(), INTERVAL 1 DAY ");
왜 안되고 또 문제점을 지적 부탁드립니다.
답변 1
CREATE TEMPORARY TABLE temp_avg AS
SELECT DATE(date) AS date,
HOUR(date) AS hour,
AVG(temp) AS avg_temp,
AVG(hum) AS avg_humidity,
AVG(wRST) AS avg_resistance
FROM myTable
WHERE date BETWEEN NOW() - INTERVAL 1 DAY AND NOW()
GROUP BY hour;
UPDATE myTable AS t1
JOIN (
SELECT hour, MIN(date) AS min_dt
FROM temp_avg
GROUP BY hour
) AS t2
ON HOUR(t1.date) = t2.hour
AND t1.date = t2.min_dt
SET t1.temp = (SELECT avg_temp FROM temp_avg WHERE hour = HOUR(t1.date)),
t1.hum = (SELECT avg_humidity FROM temp_avg WHERE hour = HOUR(t1.date)),
t1.wRST = (SELECT avg_resistance FROM temp_avg WHERE hour = HOUR(t1.date));
DELETE FROM myTable WHERE date < NOW() - INTERVAL 1 DAY;
참고해보세요.