아래 쿼리 속도향상 가능할까요
월별 마감데이터를 만들려고 하는데 1개월 insert 시 20분 정도가 걸립니다.
월별 TBL_MONTHLY 테이블 row 약 700만건 정도입니다.
SELECT '200901'MM, CUSTID
,CASE WHEN GRADE1+GRADE2 IN('상승상승') THEN '1. 고속성장형'
WHEN GRADE1+GRADE2 IN('유지상승','상승유지') THEN '2. 저속성장형'
WHEN GRADE1+GRADE2 IN('유지유지','상승하락','하락상승') THEN '3. 유지형'
WHEN GRADE1+GRADE2 IN('유지하락','하락유지') THEN '4. 저속하락형'
WHEN GRADE1+GRADE2 IN('하락하락') THEN '5. 고속하락형'
END 등급성장
FROM
(
SELECT A.CUSTID
, CASE WHEN A.grade ='5' AND B.grade BETWEEN '1' AND '4' THEN '상승'
WHEN A.grade ='4' AND B.grade BETWEEN '1' AND '3' THEN '상승'
WHEN A.grade ='3' AND B.grade BETWEEN '1' AND '2' THEN '상승'
WHEN A.grade ='2' AND B.grade BETWEEN '1' AND '1' THEN '상승'
WHEN A.grade ='1' AND B.grade = '1' THEN '유지'
WHEN A.grade ='2' AND B.grade = '2' THEN '유지'
WHEN A.grade ='3' AND B.grade = '3' THEN '유지'
WHEN A.grade ='4' AND B.grade = '4' THEN '유지'
WHEN A.grade ='5' AND B.grade = '5' THEN '유지'
WHEN A.grade ='1' AND B.grade BETWEEN '2' AND '5' THEN '하락'
WHEN A.grade ='2' AND B.grade BETWEEN '3' AND '5' THEN '하락'
WHEN A.grade ='3' AND B.grade BETWEEN '4' AND '5' THEN '하락'
WHEN A.grade ='4' AND B.grade BETWEEN '5' AND '5' THEN '하락'
END GRADE1
, CASE WHEN B.grade ='5' AND C.grade BETWEEN '1' AND '4' THEN '상승'
WHEN B.grade ='4' AND C.grade BETWEEN '1' AND '3' THEN '상승'
WHEN B.grade ='3' AND C.grade BETWEEN '1' AND '2' THEN '상승'
WHEN B.grade ='2' AND C.grade BETWEEN '1' AND '1' THEN '상승'
WHEN B.grade ='1' AND C.grade = '1' THEN '유지'
WHEN B.grade ='2' AND C.grade = '2' THEN '유지'
WHEN B.grade ='3' AND C.grade = '3' THEN '유지'
WHEN B.grade ='4' AND C.grade = '4' THEN '유지'
WHEN B.grade ='5' AND C.grade = '5' THEN '유지'
WHEN B.grade ='1' AND C.grade BETWEEN '2' AND '5' THEN '하락'
WHEN B.grade ='2' AND C.grade BETWEEN '3' AND '5' THEN '하락'
WHEN B.grade ='3' AND C.grade BETWEEN '4' AND '5' THEN '하락'
WHEN B.grade ='4' AND C.grade BETWEEN '5' AND '5' THEN '하락'
END GRADE2
FROM
(SELECT CUSTID,LEFT(grade,1)grade FROM TBL_MONTHLY WHERE MM = '200808' AND grade IS NOT NULL)A JOIN
(SELECT CUSTID,LEFT(grade,1)grade FROM TBL_MONTHLY WHERE MM = '200811' AND grade IS NOT NULL)B ON A.CUSTID=B.CUSTID JOIN
(SELECT CUSTID,LEFT(grade,1)grade FROM TBL_MONTHLY WHERE MM = '200901' AND grade IS NOT NULL)C ON A.CUSTID=C.CUSTID
)A
|