➕ Topic
회원 수 통계 배치를 개발하면서, 조건에 해당하는 값을 추출하는 것이 가장 어려웠다. 예를 들면, 정상/휴면 회원 수 혹은 마케팅 알림 설정을 동의/미동의한 회원 수 등을 구하는 것이다. 단일 테이블에 간단한 조건이었다면 쉽게 할 수 있었겠지만, 실무에서는 수많은 테이블을 참조하고 여러 복잡한 조건들을 걸어야 했다.
이런 조건 카운트를 할 때, ORACLE에서는 대표적으로 DECODE 와 CASE WHEN 을 사용한다. 오늘은 이 두 가지를 이용해 조건 카운트를 하는 방법과, 두 방법의 차이점에 대해 정리해보려고 한다.
➕ Contents
1. DECODE
DECODE(VALUE, IF1, THEN1, IF2, THEN2...)- VALUE 값이 IF1일 경우에 THEN1 값을 반환하고, VALUE 값이 IF2일 경우에는 THEN2 값을 반환한다. 즉, 각 조건과 등가 연산(=)을 하여 비교한다.
- DECODE 함수 안에 DECODE 함수를 중첩으로 사용 할 수 있다.
DECODE로 조건 카운트
DECODE와 SUM을 함께 사용하여 조건 카운트를 할 수 있다.
IF1이 참이라면 1, 거짓이라면 0을 반환하도록 하여, 이 값을 모두 더하면 조건에 맞는 행의 개수를 구하는 방법이다.
DECODE로 작성한 SQL을 간략하게 표현하면 다음과 같다.
SELECT SUM(DECODE(STAT_CD, '정상회원코드', 1, NULL)) AS 총정상회원수,
SUM(DECODE(STAT_CD, '휴면회원코드', 1, NULL)) AS 총휴면회원수,
SUM(DECODE(STAT_CD, '휴면회원코드', DECODE(TO_CHAR(B.DMCY_DTM, 'YYYYMMDD'), TO_CHAR(SYSDATE, 'YYYYMMDD'), 1, NULL), NULL)) AS 오늘휴면회원수
FROM TB_TEST_MBR_I ;- 총정상회원수
- 회원 상태 코드가 ‘정상회원코드’이면 1, 아니라면 NULL을 반환하여 SUM 연산을 한다.
- 총휴면회원수
- 회원 상태 코드가 ‘휴면회원코드’이면 1, 아니라면 NULL을 반환하여 SUM 연산을 한다.
- 오늘휴면회원수
- 회원 상태 코드가 ‘휴면회원코드’이고, 휴면 전환 날짜가 오늘이면 1, 아니라면 NULL을 반환하여 SUM 연산을 한다.
- 이런 식으로 DECODE 함수를 중첩으로 사용하면 여러 조건을 걸 수 있다.
2. CASE WHEN
CASE VALUE
WHEN IF조건1 THEN1
WHEN IF조건2 THEN2
....
ELSE
END- CASE는 IF.. THEN .. ELSE 구문과 비슷하다. WHEN절 다음에 여러 조건이 올 수 있다.
- CASE는 DECODE 보다 좀 더 자유롭게 비교 조건을 설정할 수 있다.
- DECODE에서 비교 연산을 수행하기 위해서는 GREATEST, LEAST등의 함수를 사용해야 하지만, CASE에서는 조건 연산자를 모두 사용 할 수 있다.
CASE WHEN으로 조건 카운트
CASE WHEN은 COUNT 함수를 함께 사용하여 조건 카운트를 할 수 있다.
위에서 DECODE로 작성한 구문을 CASE WHEN으로 수정하면 다음과 같다.
SELECT COUNT(CASE WHEN STAT_CD ='정상회원코드' THEN 1 ELSE NULL END) AS 총정상회원수,
COUNT(CASE WHEN STAT_CD ='휴면회원코드' THEN 1 ELSE NULL END) AS 총휴면회원수,
COUNT(CASE WHEN STAT_CD ='휴면회원코드' AND TO_CHAR(B.DMCY_DTM, 'YYYYMMDD'), TO_CHAR(SYSDATE, 'YYYYMMDD') THEN 1 ELSE NULL END) AS 오늘휴면회원수
FROM TB_TEST_MBR_I ;로직은 DECODE와 유사하게 지정된 조건에 맞으면 1, 아니면 NULL을 반환하여 카운트한다.
3. DECODE와 CASE WHEN 비교
가장 처음 접한 것은 CASE WHEN을 이용한 조건 카운트 방법이었다. 개인적으로 CASE WHEN이 IF문과 유사하게 느껴져 이해하기 수월해, 우선 CASE WHEN을 이용해 개발했다.
이후 DBA분과 이야기해보며 보통은 SUM DECODE를 많이 쓴다는 이야기를 들었다. 기능이나 성능에서 큰 차이를 찾지는 못했지만, 분명 다른 점이 있을 것 같아 더 찾아보기로 했다.
한 블로그에서 “DECODE는 function이고, CASE WHEN은 statement이다.” 라는 단서를 찾을 수 있었다. 무슨 말인지 이해하기 위해 Oracle Document를 좀 더 확인해보았다.
DECODE function
https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions049.htm#SQLRF00631
여기서는 DECODE를 SQL functions으로 분류하고 있다. SQL function은 아래와 같이 정의되어 있는데, 해석해보면 function은 Oracle에 내장된 기본 기능이라는 것이다.
SQL functions are built into Oracle Database and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user-defined functions written in PL/SQL
또한 이 문서에서 CASE WHEN에 대한 언급도 찾을 수 있었다.
"COALESCE" and "CASE Expressions", which provide functionality similar to that of
DECODE
CASE expression
https://docs.oracle.com/cd/E11882_01/server.112/e41084/expressions004.htm#SQLRF20037
DECODE 문서에서 타고 넘어온 문서에서는 CASE를 SQL expressions으로 정의하고 있다.
An expression is a combination of one or more values, operators, and SQL functions that evaluates to a value. An expression generally assumes the data type of its components.
예시들만 봐서는 function과 expression을 구분하기 어려웠다. 다만 정의에 따르면 expression이 좀 더 많은 값과 연산, SQL functions를 포함하고 있는 것으로 보인다. 즉, expression이 function에 비해 좀 더 복잡한 연산이 가능하다는 것이다.
그렇다면 CASE가 statement라는 의견은 어디에서 나오는 것일까?
CASE statement
https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/case_statement.htm#LNPLS01304
CASE statement에 대한 내용은 PL/SQL Document에서 확인할 수 있었다.
PL/SQL, the Oracle procedural extension of SQL, is a portable, high-performance transaction-processing language.
PL/SQL은 프로시저와 같은 절차적인 개발을 위해 고안된 별도의 언어라고 한다. SQL에서 확장되었지만 엄밀히 말하면 SQL과는 다른 언어이다. CASE statement에 대한 설명은 다음과 같다.
The
CASEstatement chooses from a sequence of conditions and runs a corresponding statement. The simpleCASEstatement evaluates a single expression and compares it to several potential values. The searchedCASEstatement evaluates multiple Boolean expressions and chooses the first one whose value isTRUE.
💡 결과적으로 CASE는 SQL에서는 expression, PL/SQL에서는 statement이다.
(따라서 여기서 사용된 CASE는 expression으로 보는 게 더 적합할 것 같다.)
CASE는 SUM, DECODE는 COUNT?
그럼 왜 CASE에서는 SUM을 DECODE에서는 COUNT를 사용할까? 이것도 굉장히 궁금했다.
결론부터 말하자면, SUM과 COUNT 모두 행의 건 수를 세는데 사용할 수 있다. 즉, SUM(1)로 되어 있는 부분을 COUNT(1)로 바꾸거나 그 반대도 가능하다는 것이다.
하지만 동작하는 방식은 다르기 때문에 잘 이해하고 사용해야 할 것 같다.
간단히 둘의 차이점만 짚고 넘어가자면, 조회된 데이터가 없을 때 반환 값이 다르다고 한다.
조회된 데이터가 없을 때, SUM(1)은 NULL을 COUNT(1)은 0을 반환한다.
4. 결론
DECODE는 특정 값에 대해 등가(=) 연산을 할 때 유용한 것 같다. CASE WHEN에 비해 간결해서, DECODE 방식으로 수정했을 때 코드 길이가 훨씬 줄어들었다. 비교해야 할 것이 적을 때는 오히려 더 직관적으로 보이는 것 같기도 하다.
하지만 복잡한 연산이 필요한 경우 CASE WHEN을 사용하는 것이 더 좋을 것 같다. 애초에 DECODE는 등가(=) 연산 이외의 연산은 불가능하고, 조건이 많아질수록 중첩 DECODE를 사용해야 해서 가독성이 떨어지기 때문이다.
➕ References
- Oracle Document
- DECODE, CASE WHEN 사용법
- 참고 링크: DECODE와 CASE
- DECODE, CASE WHEN으로 조건 카운트 하기
- DECODE, CASE WHEN의 차이
- 참고 링크: DECODE 와 CASE : 과연 그놈이 그놈일까
- SUM, COUNT의 차이
- 참고 링크: [Oracle] 오라클 SUM(1) 사용하는 이유
