by 음미 [SQL Query] [2018.08.09 21:49:27]
users_day 테이블에 여러가지 컬럼 데이터가 있는데요. 오늘 컬럼 데이터, 7일간 평균 낸 데이터, 1달간 평균 낸 데이터를 한 테이블에 넣어서 보내려고 하는데요. temperature 한 컬럼의 7일, 1달 평균을 내는 쿼리는 아래와 같이 작성했었는데요. SELECT (SELECT AVG(temperature) FROM users_day WHERE user_id=24 AND written_date='20180506') as day_temperature, (SELECT AVG(temperature) FROM users_day WHERE user_id=24 AND written_date>DATE('20180506')-INTERVAL 7 DAY AND written_date<=DATE('20180506')) as week_temperature, (SELECT AVG(temperature) FROM users_day WHERE user_id=24 AND written_date>DATE('20180506')-INTERVAL 1 MONTH AND written_date<=DATE('20180506')) as month_temperature;
생각해보니 temperature 말고 humidity 이런 컬럼까지 같이 넣을 수가 없더라고요. 물론 아래와 같이 만들수는 있지만
SELECT (SELECT AVG(temperature) FROM users_day WHERE user_id=24 AND written_date='20180506') as day_temperature, (SELECT AVG(temperature) FROM users_day WHERE user_id=24 AND written_date>DATE('20180506')-INTERVAL 7 DAY AND written_date<=DATE('20180506')) as week_temperature, (SELECT AVG(temperature) FROM users_day WHERE user_id=24 AND written_date>DATE('20180506')-INTERVAL 1 MONTH AND written_date<=DATE('20180506')) as month_temperature (SELECT AVG(humidity) FROM users_day WHERE user_id=24 AND written_date='20180506') as day_humidity, (SELECT AVG(humidity) FROM users_day WHERE user_id=24 AND written_date>DATE('20180506')-INTERVAL 7 DAY AND written_date<=DATE('20180506')) as week_humidity, (SELECT AVG(humidity) FROM users_day WHERE user_id=24 AND written_date>DATE('20180506')-INTERVAL 1 MONTH AND written_date<=DATE('20180506')) as month_humidity;
이 방식도 꽤 비효율적인 듯해서요 ㅜㅜ
UNION 처럼 아예 테이블을 합치는데 row 방향 말고 컬럼 방향으로 합칠 방법이 없을까요?
(SELECT AVG(temperature) as day_temperature, AVG(humidity) as day_humidityFROM users_day 일별),
(SELECT AVG(temperature) as week_temperature, AVG(humidity) as week_humidity FROM users_day 주별) ,
(SELECT AVG(temperature) as month_temperatrue, AVG(humidity) as month_humidity FROM users_day 월별) ,
이런식의 테이블을 합쳐서
day_temperature | week_temperature | month_temperature | day_humidity | week_humidity | month_humidity |
... | ... | .. | .. | .. | .. |
이렇게 리턴할 방법이 없을까요 ㅜㅜ
혹시 좋은 방법이 있다면 알려주시거나 힌트라도 주시면 감사하겠습니다
좋은 하루 되세요~
/* 제가 무슨 DB인지 몰라서 정확치 않습니다. 밑에 SQL 처럼 WHERE 조건을 CASE WHEN 으로 빼서 처리하시면 됩니다. */ SELECT AVG(CASE WHEN written_date='20180506' THEN temperature END ) day_temperature , AVG(CASE WHEN written_date>DATE('20180506')-INTERVAL 7 DAY AND written_date<=DATE('20180506') THEN temperature END ) day_temperature , AVG(CASE WHEN written_date>DATE('20180506')-INTERVAL 1 MONTH AND written_date<=DATE('20180506') ) day_temperature . . . FROM users_day WHERE user_id=24
답변감사합니다! CASE WHEN 절로 FROM WHERE을 줄일 수는 있겠군요! 그런데 제가 AVG 내야 할 항목이 temperature, humidity만 있는게 아니라 10가지 정도 되어서 SELECT 문에 3(일, 월, 주)*10가지 =30개의 아래와 같은 형식을 써야해서요.
AVG(CASE WHEN written_date='20180506' THEN temperature END ) day_temperature....
10가지를 한줄에 써서 3번만 적을 방법을 찾고 있었어요. 막 말도 안되게 적어본다면
AVG(CASE WHEN written_date='20180506' THEN temperature, humidity, facot1 END ) day_temperature,day_humidity,day_factor1....
와 같습니다 ㅋㅋ
감사합니다! UNION하면 row를 구분할수 없으니까 옆으로 쭐 나열할 생각을 했었는데요. 그냥 열을 만들면 되네요!!
SELECT 'day' as unit, AVG(temperature) as temperature, AVG(humidity) as humidity FROM users_day WHERE user_id=24 AND written_date='20180506' UNION ALL SELECT 'week' as unit, AVG(temperature),AVG(humidity) FROM users_day WHERE user_id=24 AND written_date>DATE('20180506')-INTERVAL 7 DAY AND written_date<=DATE('20180506') UNION ALL SELECT 'month' as unit, AVG(temperature),AVG(humidity) FROM users_day WHERE user_id=24 AND written_date>DATE('20180506')-INTERVAL 1 MONTH AND written_date<=DATE('20180506');
좋은 조언 감사합니다