[oracle] DECODE 활용하기

오라클에는 DECODE라는 내장 함수가 있다.

DECODE는 첫번째 파라미터 값과 이후 파라미터 값과 비교하여 파라미터로 정의한 값을 리턴해주는데 사용된다.

 

DECODE는 대개 다음과 같이 쓸 수 있다.

 - DECODE(COL01, 'A', 'B') 와 같은 함수는 첫번째 매개변수인 COL01의 값과 두번째 매개변수인 'A'값을 비교해서 같으면 'B'값을 리턴하고 다르면 NULL을 리턴한다.

 - DECODE(COL01, 'A', 'aaa', 'bbb') 와 같이 쓰면 COL01의 값이 'A'이면 'aaa'를 리턴하고, 아니면 'bbb'를 리턴하겠다는 의미이다.

 - DECODE(COL01, 'A', 'aaa', 'B', 'bbb', 'ccc') 와 같이 쓰면 COL01의 값이 'A'이면 'aaa'를 리턴하고, 'B'이면 'bbb'를 리턴하고 그도 아닐경우에 'ccc'를 리턴하겠다는 의미이다.

 

 

이 DECODE 함수를 조금 응용하면 다음과 같이 활용할 수도 있다.

 

1. DECODE를 활용하여 정렬시에 ORDER BY에서 오름차순, 내림차순 외에 다른 기준으로 정렬할 수 있다.

 

아래의 쿼리를 예로 들어보겠다.

WITH TEMP_TB AS

(

    SELECT 4 COL01 FROM DUAL

    UNION ALL SELECT 1 COL01 FROM DUAL

    UNION ALL SELECT 43 COL01 FROM DUAL

    UNION ALL SELECT 6 COL01 FROM DUAL

    UNION ALL SELECT 26 COL01 FROM DUAL

    UNION ALL SELECT 99 COL01 FROM DUAL

    UNION ALL SELECT 77 COL01 FROM DUAL

)

SELECT COL01

FROM TEMP_TB

--ORDER BY COL01 DESC; /*TESTCASE01*/

ORDER BY DECODE(COL01, 77, 9999, 4, 9998, COL01) DESC; /*TESTCASE02*/

 

위의 쿼리로  TESTCASE01과 TESTCASE02를 썼을경우 결과 값을 비교한다면, 아래와 같이 출력될 것이다.

TESTCASE01> 99, 77, 26, 43, 6, 4, 1

TESTCASE02> 77, 4, 99, 26, 43, 6, 1

DECODE를 통해 COL01이 77이면 9999 값을, 4면 9998값으로 치환하고 그 외는 그대로 사용하도록 해서 치환된 값을 가지고 정렬을하기 때문이다. 이를 업무에서 잘 활용하면 복잡하게 짤 쿼리를 좀 간결하게 짤 수 있게 된다.

 

 

2. 집계함수에서 DECODE를 활용하면 TYPE별 집계를 하나의 행으로 나타낼 수 있다.

 

A 반에 대한 과목별 점수 합계를 집계내린다고 예를 들어보자.

WITH TEMP_TB AS

(

    SELECT '국어' TYPE , 100 SCORE FROM DUAL

    UNION ALL '국어' TYPE , 30 SCORE FROM DUAL

    UNION ALL '영어' TYPE , 50 SCORE FROM DUAL

    UNION ALL '국어' TYPE , 44 SCORE FROM DUAL

    UNION ALL '수학' TYPE , 67 SCORE FROM DUAL

    UNION ALL '영어' TYPE , 50 SCORE FROM DUAL

    UNION ALL '국어' TYPE , 80 SCORE FROM DUAL

    UNION ALL '수학' TYPE , 80 SCORE FROM DUAL

    UNION ALL '미술' TYPE , 100 SCORE FROM DUAL

)

/*TESTCASE01*/

SELECT 

      TYPE, SUM(SCORE)

FROM TEMP_TB

GROUP BY TYPE

 

/*TESTCASE02*/

SELECT

      SUM(DECODE(TYPE,'국어',SCORE, 0)) /*국어점수*/

      , SUM(DECODE(TYPE,'수학',SCORE, 0)) /*수학점수*/

      , SUM(DECODE(TYPE,'영어',SCORE, 0)) /*영어점수*/

      , SUM(DECODE(TYPE,'미술',SCORE, 0)) /*미술점수*/

FROM TEMP_TB

 

TESTCASE01의 경우 결과값이 과목별로 4개의 행으로 출력될 것이지만, TESTCASE02의 경우, 결과값을 1개의 행으로 나타낼 수있다. TESTCASE01과 같은 결과값이 필요한 경우도 있지만, TESTCASE02와 같은 결과값이 필요한 경우도 있으니, DECODE를 활용하여 집계를 좀 더 다양하게 내릴수 있으니 알아두면 쿼리짤때 유용하게 쓰일수 있다.

 

최근에 안 사실인데, DECODE에서 NULL도 비교가 되더라군요 : ) NULL TYPE은 꼭 IS (NOT) NULL 로 비교해야한다고 배웠었는데 신박하더라구요 ㅎㅎ