여러 기간별 평균을 한 테이블에 합치는 방법을 알고싶어요 ㅜㅜ(union이 합치는 반대방향으로 합치기) 0 4 253

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
... ... .. .. .. ..
           

이렇게  리턴할 방법이 없을까요 ㅜㅜ

혹시 좋은 방법이 있다면 알려주시거나 힌트라도 주시면 감사하겠습니다

좋은 하루 되세요~
 

by 우리집아찌 [2018.08.10 08:55:02]
/* 제가 무슨 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 

 


by 음미 [2018.08.13 19:19:39]

답변감사합니다! 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.... 

와 같습니다 ㅋㅋ


by 마농 [2018.08.10 08:56:26]

왜 이걸 꼭 한줄로 옆으로 쭉 나열해야 하나요?
테이블 형태로 가져가면 보기에도 좋고 쿼리 작성도 편할 텐데요?

(1안)
gubun        | day | week | month
temperature |
humidity     |
(2안)
gubun | temperature | humidity
day    |
week  |
month |


by 음미 [2018.08.13 19:29:14]

감사합니다! 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');

좋은 조언 감사합니다

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