SQL문 기초에서중급까지(L7)CASE문

출처 : http://ryu1hwan.tistory.com/27

머릿말

오늘은 CASE문에 강의해보도록 하겠습니다.

이전에 DECODE에 대해서 설명드렸는데, DECODE와 동일한 기능을 하는 문장입니다.

DECODE보다 이해하기 쉽고, 사용하기 편한 방법입니다. 단, DECODE보다 코딩양은 약간 길어집니다.

그러므로, 만드는 SQL에서 필요한 상황에 따라 DECODE와 CASE를 섞어 쓰시는게 현명하다고 생각됩니다.

Lesson 7: CASE문

작성방법

SELECT T1.STORE_ID ,T1.STORE_ADDR ,T1.REGION_CD ,T2.REGION_GD ,CASE WHEN T2.REGION_GD = 'S' THEN 'High Grade' WHEN T2.REGION_GD = 'A' THEN 'High Grade' ELSE 'Low Grade' END NEW_REGION_GD FROM SQL_TEST.MA_STORE T1 ,SQL_TEST.CD_REGION T2 WHERE T2.REGION_CD = T1.REGION_CD

- STORE의 지역에 대한 등급을 High Grade와 Low Grade로 변경해서 출력한다.

- CASE WHEN을 기술 한 후 조건을 기술하고, THEN 다음에 조건을 만족할 경우의 결과를 적는다.

- CASE WHEN조건을 만족하지 않을 경우 처리한 내용을 ELSE절에 적는다.

중첩된 CASE문

- 여러개의 CASE문을 중첩해서 사용할 수 있다.

SELECT T1.STORE_ID ,T1.STORE_ADDR ,CASE WHEN T1.STORE_SIZE >= 100 THEN CASE WHEN T2.REGION_GD IN ('S') THEN 'High grade' WHEN T2.REGION_GD IN ('A','B') THEN 'Mid Grade' ELSE 'Low Grade' END WHEN T1.STORE_SIZE >= 50 THEN CASE WHEN T2.REGION_GD IN ('S', 'A') THEN 'High Grade' WHEN T2.REGION_GD IN ('B') THEN 'Mid Grade' ELSE 'Low Grade' END ELSE CASE WHEN T2.REGION_GD IN ('S', 'A', 'B') THEN 'High Grade' ELSE 'Low Grade' END END STORE_SIZE_GD FROM SQL_TEST.MA_STORE T1 ,SQL_TEST.CD_REGION T2 WHERE T1.REGION_CD = T2.REGION_CD

- STORE의 SIZE에 대한 등급을 STORE의 지역(REGION), STORE_SIZE별로 STORE SIZE등급을 구한다.

- 이러한 중첩된 CASE는 사용자가 원하는 다양한 결과를 얻어 내는데 큰 도움이 된다.

GROUP BY와 CASE/DECODE : GROUP BY컬럼에 CASE/DECODE사용

- GROUP BY를 정의하는 컬럼에 CASE나 DECODE를 사용하는 방법이다.

SELECT CASE WHEN T1.REGION_GD = 'S' THEN 'High Grade' WHEN T1.REGION_GD = 'A' THEN 'High Grade' ELSE 'Low Grade' END GRADE ,COUNT(*) REGION_CNT FROM SQL_TEST.CD_REGION T1 GROUP BY CASE WHEN T1.REGION_GD = 'S' THEN 'High Grade' WHEN T1.REGION_GD = 'A' THEN 'High Grade' ELSE 'Low Grade' END; SELECT DECODE(T1.REGION_GD,'S','High Grade','A','High Grade','Low Grade') GRADE ,COUNT(*) REGION_CNT FROM SQL_TEST.CD_REGION T1 GROUP BY DECODE(T1.REGION_GD,'S','High Grade','A','High Grade','Low Grade');

- CASE/DECODE를 통해 REGION_GD가 S나 A인 경우는 High Grade로 표시하고, 나머지는 Low Grade로 집계해서 Region(지역)별로 카운트를 한다.

- 주의 할 점은 GROUP BY에 명시한 내용 그대로 SELECT절에 적어야 한다는 것이다.

- 추가로 예제를 하나 더 보도록 하자.

SELECT CASE WHEN T1.STORE_SIZE >= 100 AND T2.REGION_GD IN ('S') THEN 'High' WHEN T1.STORE_SIZE >= 100 AND T2.REGION_GD IN ('A','B','C') THEN 'Normal' WHEN T1.STORE_SIZE < 100 AND T2.REGION_GD IN ('S','A') THEN 'High' WHEN T1.STORE_SIZE < 100 AND T2.REGION_GD IN ('B','C') THEN 'Low' END STORE_SIZE_GD ,COUNT(*) STORE_CNT FROM SQL_TEST.MA_STORE T1 ,SQL_TEST.CD_REGION T2 WHERE T1.REGION_CD = T2.REGION_CD GROUP BY CASE WHEN T1.STORE_SIZE >= 100 AND T2.REGION_GD IN ('S') THEN 'High' WHEN T1.STORE_SIZE >= 100 AND T2.REGION_GD IN ('A','B','C') THEN 'Normal' WHEN T1.STORE_SIZE < 100 AND T2.REGION_GD IN ('S','A') THEN 'High' WHEN T1.STORE_SIZE < 100 AND T2.REGION_GD IN ('B','C') THEN 'Low' END

GROUP BY + CASE/DECODE : 집계함수 내에 CASE/DECODE사용

- GROUP BY를 사용해서 값을 집계 할 때, CASE/DECODE를 사용해서, 특정 경우에만 값이 집계되도록 하는 방법이다.

SELECT T1.STORE_ID ,SUM(CASE WHEN TO_CHAR(TO_DATE(T1.ORDER_YMD,'YYYYMMDD'),'D')='1' THEN 1 END) SUN_ORD ,SUM(CASE WHEN TO_CHAR(TO_DATE(T1.ORDER_YMD,'YYYYMMDD'),'D')='2' THEN 1 END) MON_ORD ,SUM(CASE WHEN TO_CHAR(TO_DATE(T1.ORDER_YMD,'YYYYMMDD'),'D')='3' THEN 1 END) TUE_ORD ,SUM(CASE WHEN TO_CHAR(TO_DATE(T1.ORDER_YMD,'YYYYMMDD'),'D')='4' THEN 1 END) WED_ORD ,SUM(CASE WHEN TO_CHAR(TO_DATE(T1.ORDER_YMD,'YYYYMMDD'),'D')='5' THEN 1 END) THU_ORD ,SUM(CASE WHEN TO_CHAR(TO_DATE(T1.ORDER_YMD,'YYYYMMDD'),'D')='6' THEN 1 END) FRI_ORD ,SUM(CASE WHEN TO_CHAR(TO_DATE(T1.ORDER_YMD,'YYYYMMDD'),'D')='7' THEN 1 END) SAT_ORD FROM SQL_TEST.HI_ORDER T1 WHERE T1.ORDER_YMD LIKE '200901%' GROUP BY T1.STORE_ID ORDER BY T1.STORE_ID; SELECT T1.STORE_ID ,SUM(DECODE(TO_CHAR(TO_DATE(T1.ORDER_YMD,'YYYYMMDD'),'D'),'1',1,0)) SUN_ORD ,SUM(DECODE(TO_CHAR(TO_DATE(T1.ORDER_YMD,'YYYYMMDD'),'D'),'2',1,0)) MON_ORD ,SUM(DECODE(TO_CHAR(TO_DATE(T1.ORDER_YMD,'YYYYMMDD'),'D'),'3',1,0)) TUE_ORD ,SUM(DECODE(TO_CHAR(TO_DATE(T1.ORDER_YMD,'YYYYMMDD'),'D'),'4',1,0)) WED_ORD ,SUM(DECODE(TO_CHAR(TO_DATE(T1.ORDER_YMD,'YYYYMMDD'),'D'),'5',1,0)) THU_ORD ,SUM(DECODE(TO_CHAR(TO_DATE(T1.ORDER_YMD,'YYYYMMDD'),'D'),'6',1,0)) FRI_ORD ,SUM(DECODE(TO_CHAR(TO_DATE(T1.ORDER_YMD,'YYYYMMDD'),'D'),'7',1,0)) SAT_ORD FROM SQL_TEST.HI_ORDER T1 WHERE T1.ORDER_YMD LIKE '200901%' GROUP BY T1.STORE_ID ORDER BY T1.STORE_ID;

- 데이터를 STORE_ID별로 GROUP BY한다.

- CASE/DECODE를 사용해서 ORDER_DT의 요일값(1(일요일)~7(토요일))에 따라 주문 건수를 집계

- 예를 들어 ORDER_DT가 요일 값ㅣ 1(일요일)일 때는 숫자 1을 아닌 경우는 0으로 변경해서 합계를 구해 SUN_ORD를 만든다.

- 이와 같은 유형은 데이터 분석 화면을 만들때 많이 사용되는 유형입니다.

- 추가로 하나의 예제를 보도록 하자.

SELECT T1.ORDER_YMD ,SUM(CASE WHEN T2.PROD_SIZE_CD = 'L' THEN 1 END) LARGE_ORD_CNT ,SUM(CASE WHEN T2.PROD_SIZE_CD = 'L' THEN T2.ITEM_SALE_PRC END) LARGE_ORD_AMT ,SUM(CASE WHEN T2.PROD_SIZE_CD = 'M' THEN 1 END) MIDDLE_ORD_CNT ,SUM(CASE WHEN T2.PROD_SIZE_CD = 'M' THEN T2.ITEM_SALE_PRC END) MIDDLE_ORD_AMT ,SUM(CASE WHEN T2.PROD_SIZE_CD = 'S' THEN 1 END) SMALL_ORD_CNT ,SUM(CASE WHEN T2.PROD_SIZE_CD = 'S' THEN T2.ITEM_SALE_PRC END) SMALL_ORD_AMT FROM SQL_TEST.HI_ORDER T1 ,SQL_TEST.HI_ORDER_ITEM T2 WHERE T1.ORDER_YMD LIKE '200901%' AND T1.ORDER_NO = T2.ORDER_NO GROUP BY T1.ORDER_YMD ORDER BY T1.ORDER_YMD

- 주문일자별로 데이터를 GROUP BY한다.

- 주문된 제품의 SIZE별로 주문 건수를 카운트 한다.

GROUP BY + CASE/DECODE : 집계 함수 바깥에 CASE/DECODE를 사용

- GROUP BY와 집계함수를 먼저 사용하고, 집계 함수의 결과에 대해 CASE/DECODE를 사용한다.

SELECT T1.STORE_ID ,SUM(T2.ORDER_AMT) ORDER_AMT ,CASE WHEN SUM(T2.ORDER_AMT) > 15 THEN 'High' WHEN SUM(T2.ORDER_AMT) > 10 THEN 'Mid' WHEN SUM(T2.ORDER_AMT) > 0 THEN 'Low' ELSE 'Bad' END SALE_GD FROM SQL_TEST.MA_STORE T1 ,SQL_TEST.HI_ORDER T2 WHERE T2.STORE_ID = T1.STORE_ID(+) AND T2.ORDER_YMD(+) = '20090101' GROUP BY T1.STORE_ID

- STORE별 판매 금액 합계에 따라 판매 등급을 구한다.

- 집계뙨 금액 합계에 따라 15보다 크면 High, 10~15사이이면 Mid, 나머지는 Low로 판매등급을 구한다.

SELECT T3.REGION_GD ,T1.STORE_ID ,SUM(T2.ORDER_AMT) ORDER_AMT ,CASE WHEN T3.REGION_GD = 'S' THEN CASE WHEN SUM(T2.ORDER_AMT) > 15 THEN 'High' WHEN SUM(T2.ORDER_AMT) > 10 THEN 'Mid' ELSE 'Low' END WHEN T3.REGION_GD = 'A' THEN CASE WHEN SUM(T2.ORDER_AMT) > 12 THEN 'High' WHEN SUM(T2.ORDER_AMT) > 9 THEN 'Mid' ELSE 'Low' END WHEN T3.REGION_GD = 'B' THEN CASE WHEN SUM(T2.ORDER_AMT) > 9 THEN 'High' WHEN SUM(T2.ORDER_AMT) > 5 THEN 'Mid' ELSE 'Low' END WHEN T3.REGION_GD = 'C' THEN CASE WHEN SUM(T2.ORDER_AMT) > 5 THEN 'High' WHEN SUM(T2.ORDER_AMT) > 3 THEN 'Mid' ELSE 'Low' END END SALE_GD FROM SQL_TEST.MA_STORE T1 ,SQL_TEST.HI_ORDER T2 ,SQL_TEST.CD_REGION T3 WHERE T2.STORE_ID = T1.STORE_ID(+) AND T2.ORDER_YMD(+) = '20090101' AND T3.REGION_CD = T1.REGION_CD GROUP BY T3.REGION_GD, T1.STORE_ID

- 중첩된 CASE를 이용해서 REGION_GD에 따라 판매등급의 판매금액 기준을 다르게 적용

원래 컬럼을 변경하는 CASE, DECODE문이 자주 나오는 것은 성능상 문제가 있을 수 있다.

- 수작업으로 작성하는 1회성 리포트라면 크게 문제 없다.

- CASE, DECODE를 사용하지 않고 SQL을 작성할 수 있는 가를 고려한다.

- CASE, DECODE를 제거할 수 있도록 테이블을 변경 할 수 있는 가를 고려한다.

- 위의 상황이 불가피 할 경우 상급개발자나 DBAㅘ 함께 성능점검을 하도록 한다.

CASE를 사용할 것인가? DECODE를 사용할 것인가?

- 기본적인 구문은 DECODE가 간단하지만, 복잡한 조건에서는 표현하기가 어렵다.

- DECODE를 이용해 간단히 표현이 가능하면 DECODE를,

- 조건식이 너무 복잡할 경우는 CASE를 사용하도록 하자.

맺음말.

오늘도 저의 지루한 글 읽으시느라고 고생들 하셨습니다.^^

누가 보실지는 모르겠지만, 아무튼, 지금까지 따라 오셨다면,

이젠 SQL문법 자체는 거의 모두 익히신거네요.^^