SQL _ CASE식

--단순CASE식--
CASE sex
WHEN '1' THEN '남'
WHEN '2' THEN '여'
ELSE '기타'
END

--검색CASE식--
CASE WHEN sex = '1' THEN '남'
WHEN sex = '2' THEN '여'
ELSE '기타' END

CASE식의 서식은 위와 같이 단순CASE식과 검색CASE식 두가지가 있다. 단순 CASE식은 이름 그대로 간결하게 쓸 수 있으나 비교조건 밖에 가능하지 못한 반면 검색 CASE식은 다양한 비교조건을 사용할 수 있다.

1.기존 코드체계를 새로운 체계로 변경하여 집계하기

PopTbl 집계결과

pref_name	population		지방명	인구
인천	100		경기	650
수원	200	→	경북	600
광명	150		기타	450
안양	200
대구	300
경주	100
포항	200
부산	400
제주	50



왼쪽 테이블의 도시별 인구를 집계하는 SQL을 만들어보자.

SELECT CASEpref_name WHEN '인천' THEN '경기도' WHEN '수원' THEN '경기도' WHEN '광명' THEN '경기도' WHEN '안양' THEN '경기도' WHEN '대구' THEN '경북' WHEN '경주' THEN '경북' WHEN '포항' THEN '경북' ELSE '기타' END AS district, SUM(population) FROM PopTbl GROUP BY CASE pref_name WHEN '인천' THEN '경기도' WHEN '수원' THEN '경기도' WHEN '광명' THEN '경기도' WHEN '안양' THEN '경기도' WHEN '대구' THEN '경북' WHEN '경주' THEN '경북' WHEN '포항' THEN '경북' ELSE '기타' END; DISTRICT SUM(POPULATION) --------- ------------------ 경기도 450 경북 800 기타 450

2.다른 조건의 집계를 하나의 SQL로 행하기

pref_name	sex	population		지방명	남	여
인천	1	60		인천	60	40
인천	2	40		수원	100	100
수원	1	100		광명	100	50
수원	2	100		안양	100	100
광명	1	100	→	대구	100	200
광명	2	50		경주	20	80
안양	1	100		포항	125	125
안양	2	100		서울	250	150
대구	1	100
대구	2	200
경주	1	20
경주	2	80
포항	1	125
포항	2	125
서울	1	250
서울	2	150

SELECT pref_name, SUM( CASEWHEN sex = '1' THEN population ELSE 0 END) AS cnt_m, SUM( CASEWHEN sex = '2' THEN population ELSE 0 END) AS cnt_f FROM PopTbl2 GROUP BY pref_name; PREF_NAME CNT_M CNT_F ----------------------- ---------- ---------- 경주 20 80 포항 125 125 대구 100 200 광명 100 50 안양 100 100 서울 250 150 인천 60 40 수원 100 100

3. 조건을 분지시킨 UPDATE

name	salary
영희	300000
순희	270000
민희	220000
원희	290000

위의 테이블에 대해, 다음과 같은 조건으로 변경시켜보자

1.현재의 급료가 30만이상인 사원은 10%삭감한다.

2.현재의 급료가 25만이상 28미만인 사원은 20%인상한다.

단순히 생각하면 다음과 같이 UPDATE문을 2번 실행하면 될 것 같지만 이는 맞지 않다.

--조건１． UPDATE Personnel SET salary = salary * 0.9 WHERE salary >= 300000; --조건２． UPDATE Personnel SET salary = salary * 1.20 WHERE salary >= 250000 AND salary < 280000;

왜 이런 결과가 나올까? 예를 들어 현재 급료가 30만원인 사원의 경우 조건1의 UPDATE문에 의해 급료가 27만원으로 줄었다. 하지만 이것이 끝이 아니라 다음에 실행되는 조건2의 UPDATE문에 의해서 32만4000원으로 늘어나버리기 때문이다. 두가지 조건을 정확하게 반영하는 SQL문은 다음과 같다.

UPDATE Personnel SET salary = CASEWHEN salary >= 300000 THEN salary * 0.9 WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.20 ELSE salary END;

위의 SQL뮨은 정확한데다 한번의 실행으로 해결되기 때문에 속도도 빨라진다.

4. 테이블 간의 매칭

DECODE함수등과 비료해서 CASE식의 최대 장점은 식을 평가가능하다는 것이다. 이말은 즉, CASE식 중에 BETWEEN,LIKE,<,>등을 사용할 수 있다는 말이다. 그 중에서도 IN 과EXISTS는 서브쿼리를 인수로 취하기 때문에 매우 강력한 표현력을 가진다.

CourseMaster OpenCourses

course_id	course_name		month	course_id
1	피아노		200706	1
2	태권도		200706	3
3	미술		200706	4
4	컴퓨터		200707	4
			200708	2
			200708	4

위 테이블로 부터 다음과 같은 월별 개강 상태를 한눈에 알 수 있도록 표를 만들어보자.

COURSE_NAME 6월 7월 8월 ------------------ -- -- -- 피아노 ○ × × 태권도 × × ○ 미술 ○ × × 컴퓨터 ○ ○ ○

이는 즉, OpenCourses의 어느 달에 CourseMaster테이블의 강좌가 존재하는지를 체크하는 것이다. 이 매칭의 조건을 CASE식으로 쓰는 것이 가능하다.

--IN 이용-- SELECT CM.course_name, CASEWHEN CM.course_id IN (SELECT course_id FROM OpenCourses WHERE month = 200706) THEN '○' ELSE '×' END AS "200706", CASEWHEN CM.course_id IN (SELECT course_id FROM OpenCourses WHERE month = 200707) THEN '○' ELSE '×' END AS "200707", CASEWHEN CM.course_id IN (SELECT course_id FROM OpenCourses WHERE month = 200708) THEN '○' ELSE '×' END AS "200708" FROM CourseMaster CM; --EXISTS 이용-- SELECT CM.course_name, CASEWHEN EXISTS (SELECT course_id FROM OpenCourses OC WHERE month = 200706 AND CM.course_id = OC.course_id) THEN '○' ELSE '×' END AS "200706", CASEWHEN EXISTS (SELECT course_id FROM OpenCourses OC WHERE month = 200707 AND CM.course_id = OC.course_id) THEN '○' ELSE '×' END AS "200707", CASEWHEN EXISTS (SELECT course_id FROM OpenCourses OC WHERE month = 200708 AND CM.course_id = OC.course_id) THEN '○' ELSE '×' END AS "200708" FROM CourseMaster CM;

IN 이나EXISTS 어느쪽을 써도 결과는 같지만 퍼포먼스면에서 EXISTS를 쓰는 편이 좋다. 서브쿼리에서 (month, course_id)라는 주키의 인덱스를 이용할 수 있기 때문에 특히나 OpenCourses테이블의 사이즈가 클 경우는 굉장히 우위에 서게 된다.

5.CASE식 중에 집약함수를 사용

std_id	club_id	club_name	main_club_flg
100	1	야구	Y
100	2	기타	N
200	2	기타	N
200	3	배드민턴	Y
200	4	축구	N
300	4	축구	N
400	5	수영	N
500	6	바둑	N

학생은 복수의 클럽에 소속되어있는 경우도 있으면 (100,200), 1개밖에 소속되지 않은 경우도 있다.(300,400,500). 복수의 클럽에 소속되어 있는 학생에 대해서는 주클럽이 어느것인지 나타내는 클럽열에 Y 혹은 N 의 값이 들어간다. 1개의 클럽에만 전념하는 학생은 N이 들어간다.

그럼, 위 테이블로부터 다음과 같은 조건으로 쿼리를 만든다.

1. 1개의 클럽에 소속된 학생에 대해서는 그 클럽의 ID를 취득한다.

2. 복수의 클럽에 소속된 학생에 대해서는 주요한 클럽의 ID를 취득한다.

단순히 생각하면 다음과 같은 두개의 조건에 대응하는 쿼리를 만들면 될 것이다.

--조건1 :1개의 클럽에 전념하는 학생을 선택-- select std_id, max(club_id) main_club from studentclub group by std_id having count(*) > 1; STD_ID MAIN_CLUB -------- ---------- 300 4 400 5 500 6 --조건2 : 복수 클럽에 소속되어 있는 학생을 선택-- select std_id, club_id main_club from studentclub where main_club_flg = 'Y' STD_ID MAIN_CLUB -------- ---------- 100 1 200 3

이것으로도 조건에 만족하는 결과를 얻을 수 있지만 열에 의해 복수의 SQL이 필요하다. CASE식을 쓰면, 다음과 같은 한개의 SQL만으로 쓸 수 있다.

select std_id, casewhen count(*) = 1 then max(club_id) else max(casewhen main_club_flg = 'Y' then club_id else null end) end main_club from studentclub group by std_id; STD_ID MAIN_CLUB ------- ---------- 100 1 400 5 500 6 300 4 200 3

CASE식 중에 집약함수를 써서, 거기다 그 안에 CASE식을 쓰는 어지러워보이는 네스트구조이지만 정리하자면 「한개 클럽에 전념하는가, 복수의 클럽에 소속되어 있는가」라는 조건분지를 CASE WHEN COUNT(*) _ 1 .....ELSE .....라는 CASE식으로 표현한 것이다. 이는 혁명적인 표현방법이다. 왜냐하면 우리가 처음 SQL입문을 배울 때 집계결과에 대한 조건은 HAVING구를 써서 설정한다고 배웠지만 CASE식을 쓰면 SELECT구에서도 동등 조건 분지를 쓸 수 있기 때문이다. 이 예문에서도 알 수 있듯이 CASE식은 SELECT구에서 집약함수 중에서도 밖에서도 쓸 수 있다. 이 자유도가 높은 점이 CASE식의 큰 매력이다.

------ 정리

SQL에서의 cASE식을 순차적언어의 CASE문에 연연해서 CASE「문」이라고 하는 경우가 있다. 하지만 정확히는 문이 아니라 1＋１이나 a/b와 같은 식의 동료이다. 「문」과「식」의 차이는 기능의 방식의 차이를 반영하는 중요한 포이트이다. 식이기 때문에 CASE식은 실행시에는 평가되어져 하나의 값으로 결정되어지고 (그렇기 때문에 집약함수 중에 쓸 수 있다), 식이기 때문에 SELECT구에서도 GROUP BY구에서도 WHERE구에서도 ORDER BY구에서도 쓸 수 있는 것이다. 쉽게 말하면 CASE식은 열명이나 정수를 쓸 수 있는 장소에서는 언제든지 쓸 수 있다.

[출처] SQL _ CASE식|작성자 미우