데이터 입니다.
변호사 명 | 가입일 | 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'를 한번만 넣어서 위에 데이터와 똑같이 나오게 하거나
해당하는 월의 주차를 자동으로 구하게 하는게 가능 할까요?
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 ;