-- 안녕하세요. -- 며칠동안 해당문제때문에 이곳에 문의 및 조언을 구했는데요 -- 원하는 형태의 결과값이 나오질 않아 또 문의를 드리네요 ㅡㅡ -- sise 관련테이블은 10만건 -- membership 테이블은 1500여건 정도 데이터가 있습니다. -- membership 테이블은 골프장의 회원권 데이터가 있습니다. -- sise테이블은 각 회원권의 날짜별 시세값이 있습니다. -- 제가 구하고자 하는것은 각 회원권의 저번달 시세평균, 이번달 시세평균을 구하는것인데요 -- 결과값은 나오는데 속도가 좀 느리네요.그렇다고 인덱스를 타는것도 아니고. -- 제가 쿼리를 잘못구현했는지, 조언좀 부탁드립니다. SELECT A.membership_name , A.leisureCode , A.region , A.seldate , B.pcode , (SELECT AVG(today_price) FROM sise WHERE membership_name = B.membership_name AND today_date BETWEEN '20180816' AND '20180916' -- 이번달시세평균 GROUP BY membership_name ) today_price , (SELECT AVG(today_price) FROM sise WHERE membership_name = B.membership_name AND today_date BETWEEN '20180716' AND '20180816' -- 저번달 시세평균 GROUP BY membership_name ) prev_price FROM sise A INNER JOIN membership B ON A.membership_name = B.membership_name WHERE B.info_open != 'N' AND B.pcode <> '' AND A.membership_name <> '' GROUP BY A.membership_name ORDER BY A.membership_name ASC
SELECT m.membership_name , s.today_price , s.prev_price FROM membership m LEFT OUTER JOIN (SELECT membership_name , AVG(CASE WHEN today_date >= '20180816' THEN today_price END) today_price , AVG(CASE WHEN today_date < '20180816' THEN today_price END) prev_price FROM sise WHERE today_date BETWEEN '20180716' AND '20180915' GROUP BY membership_name ) s ON m.membership_name = s.membership_name ;
SELECT m.membership_name , s.today_price , s.prev_price , (s.today_price - s.prev_price) AS 증감액 , (s.today_price - s.prev_price) / s.prev_price * 100 AS 증감율 FROM membership m LEFT OUTER JOIN (SELECT membership_name , AVG(CASE WHEN today_date >= '20180816' THEN today_price END) today_price , AVG(CASE WHEN today_date < '20180816' THEN today_price END) prev_price FROM sise WHERE today_date BETWEEN '20180716' AND '20180915' GROUP BY membership_name ) s ON m.membership_name = s.membership_name -- ORDER BY 증감액 DESC ORDER BY 증감율 DESC ;