안녕하세요.
행단위로 월을 뽑으려니 각 행 데이터를 뽑은 뒤 union all 해서 다 합쳐해야 하는지 어떤식으로 해야할지 방법을 얻고자 또다시 질문드립니다.
----------------------------------------------------------
구분 컬럼1 컬럼2 컬럼3
작년총합계 값1 값2 값3 -- 기간조건은 예를들어 2018년기준 -2년한 2016년 12월 16일 부터 17년 12월 15일까지 총합계 1년치
1월 값1 값2 값3 -- 1월은 2018년기준 17년 12월16일 부터 18년 1월 15일까지 합계
2월 ... -- 2월은 2018년기준 18년 1월 16일부터 18년 2월 15일까지 합계 이런식으로 12월까지
3월 ... .....
... ....
12월 값1 값2 값3
올해총합 값1 값2 값3
총누적합 값2 값2 값3
---------------------------------------------- 쿼리의 일부분입니다.
SELECT '전년이월' AS MM
,IFNULL(COUNT(A.REGIST_DATE),0) AS '총계'
,IFNULL(SUM(CASE WHEN A.STATUS_CODE IN ('PRSTAT002') THEN 1 ELSE 0 END),0) AS '미분류'
,IFNULL(SUM(CASE WHEN A.STATUS_CODE IN ('PRSTAT003','PRSTAT004','PRSTAT005') THEN 1 ELSE 0 END),0) AS '미심사'
,IFNULL(SUM(CASE WHEN IFNULL(A.PROP_LEVEL,0) =0 THEN 1 ELSE 0 END),0) AS '0단'
,IFNULL(SUM(CASE WHEN A.PROP_LEVEL = 1 THEN 1 ELSE 0 END),0) AS '1단'
,IFNULL(SUM(CASE WHEN A.PROP_LEVEL = 2 THEN 1 ELSE 0 END),0) AS '2단'
,IFNULL(SUM(CASE WHEN A.PROP_LEVEL >= 3 THEN 1 ELSE 0 END),0) AS '3단이상'
,IFNULL(ROUND((SUM(CASE WHEN A.PROP_LEVEL >= 2 THEN 1 ELSE 0 END)/COUNT(A.REGIST_DATE))* 100,1),0) AS '2단이상비율'
,IFNULL(ROUND((SUM(CASE WHEN A.PROP_LEVEL >= 3 THEN 1 ELSE 0 END)/COUNT(A.REGIST_DATE))* 100,1),0) AS '3단이상비율'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002','PRDIV003') THEN 1 ELSE 0 END),0) AS '해야할일총건'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') THEN 1 ELSE 0 END), 0) AS '총계'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') AND STATUS_CODE NOT IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS '미완료'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') AND STATUS_CODE IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS '완료'
,IFNULL(SUM(CASE WHEN A.PROP_CODE = 'PRDIV003' THEN 1 ELSE 0 END), 0) AS 'FM총계'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV003') AND STATUS_CODE NOT IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS 'FM미완료'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV003') AND STATUS_CODE IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS 'FM완료'
,IFNULL(round((IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') AND STATUS_CODE IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) / IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002','PRDIV003') THEN 1 ELSE 0 END),0) * 100),0),0) AS '처리율'
FROM ITEM_TD A
INNER JOIN USER_TD B ON A.REGISTER_ID = B.USER_ID
WHERE A.REGIST_DATE >= CONCAT('2016' , CONCAT('12' , '16'))
AND A.REGIST_DATE < CONCAT('2017',CONCAT('12','15'))
union all
SELECT '1월' AS MM
,IFNULL(COUNT(A.REGIST_DATE),0) AS '총계'
,IFNULL(SUM(CASE WHEN A.STATUS_CODE IN ('PRSTAT002') THEN 1 ELSE 0 END),0) AS '미분류'
,IFNULL(SUM(CASE WHEN A.STATUS_CODE IN ('PRSTAT003','PRSTAT004','PRSTAT005') THEN 1 ELSE 0 END),0) AS '미심사'
,IFNULL(SUM(CASE WHEN IFNULL(A.PROP_LEVEL,0) = 0 THEN 1 ELSE 0 END),0) AS '0단'
,IFNULL(SUM(CASE WHEN A.PROP_LEVEL = 1 THEN 1 ELSE 0 END),0) AS '1단'
,IFNULL(SUM(CASE WHEN A.PROP_LEVEL = 2 THEN 1 ELSE 0 END),0) AS '2단'
,IFNULL(SUM(CASE WHEN A.PROP_LEVEL >= 3 THEN 1 ELSE 0 END),0) AS '3단이상'
,IFNULL(ROUND((SUM(CASE WHEN A.PROP_LEVEL >= 2 THEN 1 ELSE 0 END)/COUNT(A.REGIST_DATE))* 100,1),0) AS '2단이상비율'
,IFNULL(ROUND((SUM(CASE WHEN A.PROP_LEVEL >= 3 THEN 1 ELSE 0 END)/COUNT(A.REGIST_DATE))* 100,1),0) AS '3단이상비율'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002','PRDIV003') THEN 1 ELSE 0 END),0) AS '해야할일총건'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') THEN 1 ELSE 0 END), 0) AS '총계'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') AND STATUS_CODE NOT IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS '미완료'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') AND STATUS_CODE IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS '완료'
,IFNULL(SUM(CASE WHEN A.PROP_CODE = 'PRDIV003' THEN 1 ELSE 0 END), 0) AS 'FM총계'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV003') AND STATUS_CODE NOT IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS 'FM미완료'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV003') AND STATUS_CODE IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS 'FM완료'
,IFNULL(round((IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') AND STATUS_CODE IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) / IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002','PRDIV003') THEN 1 ELSE 0 END),0) * 100),0),0) AS '처리율'
FROM ITEM_TD A
INNER JOIN USER_TD B ON A.REGISTER_ID = B.USER_ID
WHERE A.REGIST_DATE >= CONCAT('2017' , CONCAT('12' , '16'))
AND A.REGIST_DATE < CONCAT('2018',CONCAT('01','15'))
union all
SELECT '2월' AS MM
,IFNULL(COUNT(A.REGIST_DATE),0) AS '총계'
,IFNULL(SUM(CASE WHEN A.STATUS_CODE IN ('PRSTAT002') THEN 1 ELSE 0 END),0) AS '미분류'
,IFNULL(SUM(CASE WHEN A.STATUS_CODE IN ('PRSTAT003','PRSTAT004','PRSTAT005') THEN 1 ELSE 0 END),0) AS '미심사'
,IFNULL(SUM(CASE WHEN IFNULL(A.PROP_LEVEL,0) = 0 THEN 1 ELSE 0 END),0) AS '0단'
,IFNULL(SUM(CASE WHEN A.PROP_LEVEL = 1 THEN 1 ELSE 0 END),0) AS '1단'
,IFNULL(SUM(CASE WHEN A.PROP_LEVEL = 2 THEN 1 ELSE 0 END),0) AS '2단'
,IFNULL(SUM(CASE WHEN A.PROP_LEVEL >= 3 THEN 1 ELSE 0 END),0) AS '3단이상'
,IFNULL(ROUND((SUM(CASE WHEN A.PROP_LEVEL >= 2 THEN 1 ELSE 0 END)/COUNT(A.REGIST_DATE))* 100,1),0) AS '2단이상비율'
,IFNULL(ROUND((SUM(CASE WHEN A.PROP_LEVEL >= 3 THEN 1 ELSE 0 END)/COUNT(A.REGIST_DATE))* 100,1),0) AS '3단이상비율'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002','PRDIV003') THEN 1 ELSE 0 END),0) AS '해야할일총건'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') THEN 1 ELSE 0 END), 0) AS '총계'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') AND STATUS_CODE NOT IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS '미완료'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') AND STATUS_CODE IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS '완료'
,IFNULL(SUM(CASE WHEN A.PROP_CODE = 'PRDIV003' THEN 1 ELSE 0 END), 0) AS 'FM총계'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV003') AND STATUS_CODE NOT IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS 'FM미완료'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV003') AND STATUS_CODE IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS 'FM완료'
,IFNULL(round((IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') AND STATUS_CODE IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) / IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002','PRDIV003') THEN 1 ELSE 0 END),0) * 100),0),0) AS '처리율'
FROM ITEM_TD A
INNER JOIN USER_TD B ON A.REGISTER_ID = B.USER_ID
WHERE A.REGIST_DATE >= CONCAT('2018' , CONCAT('01' , '16'))
AND A.REGIST_DATE < CONCAT('2018',CONCAT('02','15'))
union all
SELECT '3월' AS MM
,IFNULL(COUNT(A.REGIST_DATE),0) AS '총계'
,IFNULL(SUM(CASE WHEN A.STATUS_CODE IN ('PRSTAT002') THEN 1 ELSE 0 END),0) AS '미분류'
,IFNULL(SUM(CASE WHEN A.STATUS_CODE IN ('PRSTAT003','PRSTAT004','PRSTAT005') THEN 1 ELSE 0 END),0) AS '미심사'
,IFNULL(SUM(CASE WHEN IFNULL(A.PROP_LEVEL,0) = 0 THEN 1 ELSE 0 END),0) AS '0단'
,IFNULL(SUM(CASE WHEN A.PROP_LEVEL = 1 THEN 1 ELSE 0 END),0) AS '1단'
,IFNULL(SUM(CASE WHEN A.PROP_LEVEL = 2 THEN 1 ELSE 0 END),0) AS '2단'
,IFNULL(SUM(CASE WHEN A.PROP_LEVEL >= 3 THEN 1 ELSE 0 END),0) AS '3단이상'
,IFNULL(ROUND((SUM(CASE WHEN A.PROP_LEVEL >= 2 THEN 1 ELSE 0 END)/COUNT(A.REGIST_DATE))* 100,1),0) AS '2단이상비율'
,IFNULL(ROUND((SUM(CASE WHEN A.PROP_LEVEL >= 3 THEN 1 ELSE 0 END)/COUNT(A.REGIST_DATE))* 100,1),0) AS '3단이상비율'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002','PRDIV003') THEN 1 ELSE 0 END),0) AS '해야할일총건'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') THEN 1 ELSE 0 END), 0) AS '총계'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') AND STATUS_CODE NOT IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS '미완료'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') AND STATUS_CODE IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS '완료'
,IFNULL(SUM(CASE WHEN A.PROP_CODE = 'PRDIV003' THEN 1 ELSE 0 END), 0) AS 'FM총계'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV003') AND STATUS_CODE NOT IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS 'FM미완료'
,IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV003') AND STATUS_CODE IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) AS 'FM완료'
,IFNULL(round((IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002') AND STATUS_CODE IN('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 ELSE 0 END), 0) / IFNULL(SUM(CASE WHEN A.PROP_CODE IN ('PRDIV001','PRDIV002','PRDIV003') THEN 1 ELSE 0 END),0) * 100),0),0) AS '처리율'
FROM ITEM_TD A
INNER JOIN USER_TD B ON A.REGISTER_ID = B.USER_ID
WHERE A.REGIST_DATE >= CONCAT('2018' , CONCAT('02' , '16'))
AND A.REGIST_DATE < CONCAT('2018',CONCAT('03','15'))
union all
1. 개선 방안
- 각각 가져와 따로 처리하지 말고, 조회 대상 집합 전체를 가져와 그룹별로 집계하면 됩니다.
- 합계는 ROLLUP 을 이용하시면 됩니다.
2. 잘못된 코드
- A.REGIST_DATE < CONCAT('2017',CONCAT('12','15'))
- 15 일은 포함되어야 하므로 '16' 으로 해야 합니다.
- Concat 은 여러 인자를 받을 수 있으므로 중첩해서 사용할 필요가 없구요.
3. 불필요한 코드들
- IFNULL(SUM(CASE WHEN THEN 1 ELSE 0 END), 0)
- Else 구문을 제거하고 count 로 바꾸면 ifnull 도 필요 없고 성능도 개선
- 개선 : COUNT(CASE WHEN THEN 1 END)
- IFNULL(COUNT(a.regist_date),0)
- a.regist_date 는 널이 아니므로 굳이 컬럼을 카운트 하지 않아도 됨
- Count 결과는 Null 이 나오지 않으므로 IfNull 은 불필요
- 개선 : COUNT(*)
4. 의심가는 코드
- b 에 대한 조인이 필요한가요?
SELECT IFNULL(yyyy, '전체') 연 , IFNULL(mm , '합계') 월 , COUNT(*) AS '총계' , COUNT(CASE WHEN status_code IN ('PRSTAT002') THEN 1 END) AS '미분류' , COUNT(CASE WHEN status_code IN ('PRSTAT003','PRSTAT004','PRSTAT005') THEN 1 END) AS '미심사' , COUNT(CASE WHEN prop_level = 0 THEN 1 END) AS '0단' , COUNT(CASE WHEN prop_level = 1 THEN 1 END) AS '1단' , COUNT(CASE WHEN prop_level = 2 THEN 1 END) AS '2단' , COUNT(CASE WHEN prop_level >= 3 THEN 1 END) AS '3단이상' , ROUND(COUNT(CASE WHEN prop_level >= 2 THEN 1 END) / COUNT(*) * 100, 1) AS '2단이상비율' , ROUND(COUNT(CASE WHEN prop_level >= 3 THEN 1 END) / COUNT(*) * 100, 1) AS '3단이상비율' , COUNT(v1) AS '해야할일총건' , COUNT(CASE WHEN v1 = 0 THEN 1 END) AS '총계' , COUNT(CASE WHEN v1 = 0 AND v2 = 0 THEN 1 END) AS '미완료' , COUNT(CASE WHEN v1 = 0 AND v2 = 1 THEN 1 END) AS '완료' , COUNT(CASE WHEN v1 = 1 THEN 1 END) AS 'FM총계' , COUNT(CASE WHEN v1 = 1 AND v2 = 0 THEN 1 END) AS 'FM미완료' , COUNT(CASE WHEN v1 = 1 AND v2 = 1 THEN 1 END) AS 'FM완료' , ROUND(COUNT(CASE WHEN v1 = 0 AND v2 = 1 THEN 1 END) / COUNT(v1) * 100, 0) AS '처리율' FROM (SELECT DATE_FORMAT(a.regist_date - INTERVAL 15 DAY + INTERVAL 1 MONTH, '%Y') yyyy , CASE WHEN a.regist_date < CONCAT('2017', '1216') THEN '00' ELSE DATE_FORMAT(a.regist_date - INTERVAL 15 DAY + INTERVAL 1 MONTH, '%m') END mm , a.status_code , a.prop_code , IFNULL(a.prop_level, 0) prop_level , CASE WHEN a.prop_code IN ('PRDIV001','PRDIV002') THEN 0 WHEN a.prop_code IN ('PRDIV003' ) THEN 1 END v1 -- FM여부 , CASE WHEN a.status_code NOT IN ('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 0 WHEN a.status_code IN ('PRSTAT000','PRSTAT006','PRSTAT007','PRSTAT008','PRSTAT009') THEN 1 END v2 -- 완료여부 FROM item_td a -- INNER JOIN user_td b -- ON a.register_id = b.user_id WHERE a.regist_date >= CONCAT('2016', '1216') AND a.regist_date < CONCAT('2018', '1216') ) a GROUP BY yyyy, mm WITH ROLLUP HAVING IFNULL(mm, '') != '00' ;