행단위로 월 데이터 뽑으려고 합니다. 0 2 701

by 일회용 [MySQL] [2018.03.14 20:20:14]


안녕하세요.

행단위로 월을 뽑으려니 각 행 데이터를 뽑은 뒤 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

 

by 마농 [2018.03.15 09:21:03]

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'
;

 


by 일회용 [2018.03.15 09:44:12]

네 , 검색조건으로 년도, 회사, 등록자, 부서등 검색조건들이 있어서 , b외에 다른 테이블도 들어갈꺼같습니다.

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