두분께서 말씀하신 대로 해도 글 수정해서 추가된 이미지 대로 피라미드가 나옵니다..
말씀하신 col1,col2가 컬럼이름을 말씀하시는거같아서 이렇게 만들었는데
제가 뭔가 부족해서 빠뜨린거같습니다
전문 올려드립니다 (__)
select s.date,s.rank, MIN(case s.date when '2016-03-01' then s.rank end) as '1', MIN(case s.date when '2016-03-02' then s.rank end) as '2', MIN(case s.date when '2016-03-03' then s.rank end) as '3', MIN(case s.date when '2016-03-04' then s.rank end) as '4', MIN(case s.date when '2016-03-05' then s.rank end) as '5', MIN(case s.date when '2016-03-06' then s.rank end) as '6', MIN(case s.date when '2016-03-07' then s.rank end) as '7', MIN(case s.date when '2016-03-08' then s.rank end) as '8', MIN(case s.date when '2016-03-09' then s.rank end) as '9', MIN(case s.date when '2016-03-10' then s.rank end) as '10', MIN(case s.date when '2016-03-11' then s.rank end) as '11', MIN(case s.date when '2016-03-12' then s.rank end) as '12', MIN(case s.date when '2016-03-13' then s.rank end) as '13', MIN(case s.date when '2016-03-14' then s.rank end) as '14', MIN(case s.date when '2016-03-15' then s.rank end) as '15', MIN(case s.date when '2016-03-16' then s.rank end) as '16', MIN(case s.date when '2016-03-17' then s.rank end) as '17', MIN(case s.date when '2016-03-18' then s.rank end) as '18', MIN(case s.date when '2016-03-19' then s.rank end) as '19', MIN(case s.date when '2016-03-20' then s.rank end) as '20', MIN(case s.date when '2016-03-21' then s.rank end) as '21', MIN(case s.date when '2016-03-22' then s.rank end) as '22', MIN(case s.date when '2016-03-23' then s.rank end) as '23', MIN(case s.date when '2016-03-24' then s.rank end) as '24', MIN(case s.date when '2016-03-25' then s.rank end) as '25', MIN(case s.date when '2016-03-26' then s.rank end) as '26', MIN(case s.date when '2016-03-27' then s.rank end) as '27', MIN(case s.date when '2016-03-28' then s.rank end) as '28', MIN(case s.date when '2016-03-29' then s.rank end) as '29', MIN(case s.date when '2016-03-30' then s.rank end) as '30', MIN(case s.date when '2016-03-31' then s.rank end) as '31' FROM ( SELECT DATE_FORMAT(DATE,'%Y-%m-%d') AS DATE, rank FROM( SELECT a.dt AS DATE, ROUND(COUNT(B.RANK)/10*100,2) AS rank FROM ( SELECT dt + INTERVAL lv-1 DAY dt FROM ( SELECT ordinal_position lv , CONCAT('201603', '01') dt FROM information_schema.columns WHERE table_schema = 'mysql' AND table_name = 'user' ) a WHERE lv <= DAY(LAST_DAY(dt)) ) a LEFT JOIN ( SELECT a.rank AS rank, DATE_FORMAT(rank_date, '%Y-%m-%d') AS rank_date,b.info_goalrank FROM keyword_record AS a JOIN client_collect_info AS b WHERE a.client_collect_seq = b.client_collect_seq AND a.client_collect_seq =544 AND HOUR(a.rank_date) >=9 AND HOUR(a.rank_date) <=18 AND a.rank <=b.info_goalrank AND a.rank !=0 ) b ON a.dt = b.rank_date GROUP BY A.DT) AS ex) s group by s.date,s.rank
지난번 질문에서 비효율적인 부분들을 개선한 쿼리를 제시해 드렸는데...
전혀 적용을 안하셨네요. ㅠ,.ㅜ
1. ROUND(COUNT(b.rank)/10*100,2) 에서
- COUNT(b.rank) 는 COUNT(*) 로 바꿔도 결과 동일하고요, 더 효율적이죠.
- /10*100 부분은 *10 과 동일하고요
- ROUND(, 2) 부분은 안해도 되는 것이죠.
- 결론은 COUNT(*)*10 으로 바꾸면 되는 거죠.
2. information_schema.columns 를 이용한 아우터 조인은?
- 날짜별로 아래로 나열할 때 필요한 스킬이었죠
- 옆으로 나열할 때는 사용할 필요가 없죠
SELECT COUNT(CASE dd WHEN '01' THEN 1 END)*10.00 "01" , COUNT(CASE dd WHEN '02' THEN 1 END)*10.00 "02" , COUNT(CASE dd WHEN '03' THEN 1 END)*10.00 "03" , COUNT(CASE dd WHEN '04' THEN 1 END)*10.00 "04" , COUNT(CASE dd WHEN '05' THEN 1 END)*10.00 "05" , COUNT(CASE dd WHEN '06' THEN 1 END)*10.00 "06" , COUNT(CASE dd WHEN '07' THEN 1 END)*10.00 "07" , COUNT(CASE dd WHEN '08' THEN 1 END)*10.00 "08" , COUNT(CASE dd WHEN '09' THEN 1 END)*10.00 "09" , COUNT(CASE dd WHEN '10' THEN 1 END)*10.00 "10" , COUNT(CASE dd WHEN '11' THEN 1 END)*10.00 "11" , COUNT(CASE dd WHEN '12' THEN 1 END)*10.00 "12" , COUNT(CASE dd WHEN '13' THEN 1 END)*10.00 "13" , COUNT(CASE dd WHEN '14' THEN 1 END)*10.00 "14" , COUNT(CASE dd WHEN '15' THEN 1 END)*10.00 "15" , COUNT(CASE dd WHEN '16' THEN 1 END)*10.00 "16" , COUNT(CASE dd WHEN '17' THEN 1 END)*10.00 "17" , COUNT(CASE dd WHEN '18' THEN 1 END)*10.00 "18" , COUNT(CASE dd WHEN '19' THEN 1 END)*10.00 "19" , COUNT(CASE dd WHEN '20' THEN 1 END)*10.00 "20" , COUNT(CASE dd WHEN '21' THEN 1 END)*10.00 "21" , COUNT(CASE dd WHEN '22' THEN 1 END)*10.00 "22" , COUNT(CASE dd WHEN '23' THEN 1 END)*10.00 "23" , COUNT(CASE dd WHEN '24' THEN 1 END)*10.00 "24" , COUNT(CASE dd WHEN '25' THEN 1 END)*10.00 "25" , COUNT(CASE dd WHEN '26' THEN 1 END)*10.00 "26" , COUNT(CASE dd WHEN '27' THEN 1 END)*10.00 "27" , COUNT(CASE dd WHEN '28' THEN 1 END)*10.00 "28" , COUNT(CASE dd WHEN '29' THEN 1 END)*10.00 "29" , COUNT(CASE dd WHEN '30' THEN 1 END)*10.00 "30" , COUNT(CASE dd WHEN '31' THEN 1 END)*10.00 "31" FROM (SELECT DATE_FORMAT(rank_date, '%d') AS dd FROM keyword_record AS a JOIN client_collect_inf.o AS b ON a.client_collect_seq = b.client_collect_seq AND a.rank <= b.info_goalrank WHERE a.client_collect_seq = 544 AND HOUR(a.rank_date) >= 9 AND HOUR(a.rank_date) <= 18 AND a.rank != 0 AND DATE_FORMAT(rank_date, '%Y%m') = '201603' ) a ;