공부하는 습관

SQLD 시험 전에 공부해야 할 것5 : DECODE, CASE

안녕하세요! 오늘은 SQLD를 준비할 때 절대로 놓쳐서는 안 될 조건문에 대해 알아보려고 합니다. 데이터를 정제할 때 어떤 조건의 정보만 추출하고 싶을 때가 있습니다. 이 때 유용하게 사용될 수 있는 문법입니다. 

DECODE

SELECT DECODE(column_name, 'value1', 'result1', 'value2', 'result2', 'default_result') AS decoded_value
FROM table_name;

오라클에서 사용하는 문법입니다

컬럼명, 컬럼의 데이터, 출력할 값, 기본값 이렇게 4개의 정보가 필요합니다.

바로 예시로 들어가서 살펴보겠습니다!

예를 들어 한 회사에 여러 부서가 있습니다. 부서별로 번호를 부여하고 싶습니다. 마케팅팀은 10, 운영팀은 20, 영업팀은 30 이렇게요! 그럴 때 DECODE를 어떻게 사용하면 좋을까요?

DECODE('DEPT', '마케팅', '10', '운영팀', '20', '영업팀','30')

간단하죠ㅎㅎ

그런데 이 세개의 팀에 속하지 않는 팀이 있다면 에러가 생길 수 있습니다. 그럴 땐 default 값으로 뒤에 앞에 내용에 해당하지 않은 데이터들은 어떻게 처리할 지 적어주면 됩니다. 저는 세 팀에 속하지 않은 팀의 경우 100이라는 숫자를 부여하도록 하겠습니다.

DECODE('DEPT', '마케팅', '10', '운영팀', '20', '영업팀','30', '100')

CASE

case 문법은 정말정말 자주 사용되기 때문에 꼭 아셔야 하는 필수 문법입니다. case 또한 조건문을 만들 때 사용됩니다. 

SELECT 
    CASE 
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE default_result
    END AS case_result
FROM table_name;

이렇게 보면 복잡해 보이죠. 예시를 활용해서 하나하나 살펴보겠습니다!

예를 들면 한 동물원에 갔습니다. 입장권을 살려고 하는데 나이별로 할인 금액이 달릅니다. 예를 들면 3살 미만일 경우 무료이며 3살부터 12살 미만일 경우 50%의 할인 가격이 들어갑니다. 그 외에는 정상 가격으로 받습니다. 이를 sql로 코딩을 하면 다음과 같습니다.

CASE
WHEN 나이 < 3 THEN '무료'
WHEN 나이 >= 3 AND 나이 < 12 THEN '할인 가격'
ELSE '정상 가격'
END

이렇게 결과 표가 출력이 됩니다.

쉽죠ㅎㅎ

DECODE와 CASE의 차이

둘의 큰 차이는 바로 부등호 비교가 가능한지 여부입니다. 앞선 예문에서 보셨듯이 CASE는 등호비교(=), 부등호비교(<,>,<=,>=) 모두 사용 가능합니다. 하지만 DECODE는 등호비교만 가능합니다. 이 점 꼭 기억하시기 바랍니다:)

확실하게 이해했는지 확인하기 위해 테스트 문제를 제시해볼게요!

문제 

직급이 사장인 경우에는  null을 출력하고 팀장인 경우에는 7000, 과장은 6000, 대리는 5000을 출력하시오.

직급 컬럼 : JOB

연봉 컬럼 : SALARY

데이터명: EMP

정답

select JOB, DECODE(JOB,'사장',null,'팀장',7000,'과장',6000,'대리',5000) as SALARY
from EMP

어떠셨나요? 이제 조금 심화 과정으로 들어갈볼까요?

데이터들을 정렬할 때 사용하는 구문 중 하나가 바로 order by 컬럼명 desc/asc 입니다. decode를 사용할 때 order by를 사용해보면 이상한 점을 발견할 수 있습니다.

바로 숫자가 문자열로 암시적 형변환으로 일어나는 것인데요. 예시로 쉽게 알아보겠습니다.

아래와 같은 order_details라는 테이블이 있습니다. 각각의 상품의 id와 수량이 적혀있군요.

테이블 이름: order_details

| product_id | quantity |
|------------|----------|
| 1          | 10       |
| 2          | 5        |
| 3          | 15       |
| 4          | 8        |

이제 id 별로 quantity에 따라 등급을 나눠주려고 합니다. 10개 이상이면 A 나머지는 quantity를 적습니다. 그 다음 quantity 컬럼을 기준으로 내림차순으로 정렬을 시켜보았습니다.

SELECT 
    product_id, 
    quantity,
DECODE(product_id, 15, 'A',quantity) as quantity_code
FROM 
    order_details
ORDER BY 
    quantity DESC;

출력 결과는?

어라 이상합니다. quantity를 기준으로 내림차순으로 정렬 되게 하였습니다. 그렇다면 15인 1이 가장 위에 있어야 하는데 8이 가장 위에 있습니다.

왜 그럴까요?

암시적 형변환

decode의 가장 많이 발생하는 에러 중 하나가 바로 암시적 형변환입니다. 즉 숫자를 문자로 자기가 멋대로 바꿔버립니다. 세번째 변수가 A 였었죠. A는 문자형입니다. 그래서 그 다음 변수인 quantity가 영향을 받아 숫자를 문자로 바꾼것입니다. 

여기를 숫자로 바꾸려면?

to_number('A') 로 적어주면 됩니다.

to_number 함수를 활용해서 문자형을 숫자형으로 바꿔줍니다.

SELECT 
    product_id, 
    quantity,
DECODE(product_id, 15, number('A'),quantity) as quantity_code
FROM 
    order_details
ORDER BY 
    quantity DESC;

자 이렇게 오늘은 조건문인 DECODE, CASE를 배웠습니다. 혹시 어렵거나 헷갈렸던 부분이 있다면 언제든 댓글로 알려주세요. 이번 포스팅 도움이 되기를 바라며 이만 물러나보겠습니다. 좋은 하루 보내세요!