꿈꾸는 개발자, DBA 커뮤니티 구루비

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

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]

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

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(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변