mysql 월별 및 주차별 카운트 0 1 1,994

by xcrew [SQL Query] mysql [2019.03.22 13:25:15]


데이터 입니다.

변호사 명 가입일 03월 답변수 03월 1주차 03월 2주차 03월 3주차 03월 4주차 03월 5주차
안진우 2017-11-08 16 8 4 4 0 0
강소영 2018-06-21 9 4 0 5 0 0
고봉주 2017-12-06 7 6 0 1 0 0
박영태 2018-10-04 6 2 4 0 0 0
최지희 2019-02-14 6 5 0 1 0 0

 

변호사별로 월별 답변수와 그 달의 각각의 주차별 카운트를 가져오는 쿼리 입니다.

위에 데이터를 가져오기 위해 아래와 같이 쿼리를 작성하였습니다.

 

select * from (
				select 
					y.lawyer_name,
					DATE_FORMAT(y.register_date, '%Y-%m-%d') as reg_date,
					(select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201902') as cnt,
					(select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201902' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 1) as cnt1,
					(select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201902' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 2) as cnt2,
					(select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201902' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 3) as cnt3,
					(select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201902' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 4) as cnt4,
					(select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201902' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 5) as cnt5,
					(select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201902' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 6) as cnt6
				from lawyer y
			where y.lawyer_status = 'N'
			) A order by A.cnt desc;

 

더좋은 쿼리 방법이 있을까요?

예를 들면 '201902'를 한번만 넣어서 위에 데이터와 똑같이 나오게 하거나

해당하는 월의 주차를  자동으로 구하게 하는게 가능 할까요?

 

by 마농 [2019.03.22 14:03:53]

1. 조건 줄 때 컬럼을 변형시켜 조건에 맞추지 말고, 조건을 변형시켜 컬럼에 맞추세요.
   - DATE_FORMAT(register_date, "%Y%m") = '201902'   (나쁜예)
2. 주차 계산 결과가 0부터 시작할 듯. --> 1을 더해줘야 할 듯 하네요.
3. 스칼라서브쿼리 반복 사용은 --> 한번만 조인하는 방식으로 개선하세요.
4. 정렬은
  - 중복 가능한 항목만으로 정렬하기 보다는
  - 최대한 중복이 발생되지 않도록 정렬 항목을 추가하세요.

SELECT lawyer_name
     , reg_date
     , COUNT(w) cnt
     , COUNT(CASE w WHEN 1 THEN 1 END) cnt1
     , COUNT(CASE w WHEN 2 THEN 1 END) cnt2
     , COUNT(CASE w WHEN 3 THEN 1 END) cnt3
     , COUNT(CASE w WHEN 4 THEN 1 END) cnt4
     , COUNT(CASE w WHEN 5 THEN 1 END) cnt5
     , COUNT(CASE w WHEN 6 THEN 1 END) cnt6
  FROM (SELECT y.lawyer_name
             , DATE_FORMAT(y.register_date, '%Y-%m-%d') reg_date
             , WEEK(x.register_date, 5)
             - WEEK(x.register_date - INTERVAL DAYOFMONTH(x.register_date) - 1 DAY, 5)
             + 1 w
          FROM lawyer y
          LEFT OUTER JOIN counsel_answer x
            ON x.lawyer_idx = y.lawyer_idx
           AND x.register_date >= DATE_ADD('20190201', INTERVAL 0 MONTH)
           AND x.register_date <  DATE_ADD('20190201', INTERVAL 1 MONTH)
         WHERE y.lawyer_status = 'N'
        ) z
 GROUP BY lawyer_name, reg_date
 ORDER BY cnt DESC, lawyer_name, reg_date
;

 

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