[mysql] NULLIF 함수의 활용

몇달전에 Mysql 에서 지원하는 함수목록을 정리할 일이 있었습니다. 이때 NULLIF 라는 함수를 발견하게 되었는데 어디다 활용하면 좋을 지 고민하다가 오늘 방법을 생각하게 되어서 감히 팁란에 올려봅니다.

NULLIF(expr1, expt2) 는 Mysql에서 제공하는 함수로써, expr1과 expr2가 서로 같으면 NULL을 리턴하고, 같지 않으면 첫번째 인자인 expr1을 리턴합니다.
(REF:http://www.mysql.com/doc/en/Control_flow_functions.html)

NULLIF 함수의 개요는 이쯤으로 해두고, 가장 쓰임세 있을 듯한 쇼핑몰을 예시로 들면서 생각해봅시다. 어느날 여러분은 여러분의 보스가 현재 당월의 주문완료가 되지않은 주문건수의 갯수를 구해오라고 지시하였다고 가정합니다. 이때 위의 NULLIF를 사용하면 다음과 같은 SQL문으로 쉽게 꺼내올수 있습니다.

mysql> SELECT count(NULLIF(주문상태, '주문완료'))
      FROM 주문테이블
      WHERE 주문일자 LIKE '200401%'
     
주문상태는 주문테이블의 컬럼이름이라고 가정합시다. 이때 주문상태가 '주문완료'가 되면 count()함수 안에 값은 NULL로 되어버립니다. 결국 이경우에는 count() 함수가 동작하지 않습니다. count() 함수는 NULL 값을 건너뛴다는 것은 알고 계시지요 ?

mysql> SELECT count(*)
      FROM 주문테이블
      WHERE 주문일자 LIKE '200401%'
      AND 주문상태 <> '주문완료'

물론 위의 SQL도 결과는 똑같이 나옵니다. 하지만 이 경우는 동시에 여러 컬럼을 가공할수 없습니다. 통계 화면 같은 SQL을 쓸때 쥐약이됩니다. NULLIF 를 사용하면 다음과 같은 경우도 가능합니다.

mysql> SELECT 상품코드, count(NULLIF(주문상태, '주문완료')) 주문중,
            count(*) 전체주문건수, 전체주문건수-주문중 완료건수
      FROM 주문테이블
      WHERE 주문일자 LIKE '200401%'
      GROUP BY 상품코드

위의 SQL문은 2004년 1월의 주문을 읽어 상품코드로 GROUP BY 하고 상품의 상태와 현재 주문완료 상태가 아닌, 즉 수행중인 갯수를 출력하는 예제입니다.

위의 예제 말고도 응용 분야는 더 있을 듯합니다. SUM()을 사용할때  특정 값일 경우 합산에서 제외시켜버린다던가, SIGN 과 NULLIF의 조합으로 특정 값보다 작은 값을 그 컬럼안에서만 제외한다던가등등...

여러분들도 뭔가 활용방안이 생기시면 알려주시기 바랍니다.



------------------------------------------------------------

명랑폐인
NULL값을 체크하는 함수류는 테이블 JOIN시에 매칭되지 않은 레코드들을 처리할때 사용하게 됩니다.
단일 테이블 처리에선 그냥 WHERE 절만 사용해도 거의 다 처리됩니다.

최근한
명량폐인/마지막에 쓰인 쿼리가 WHERE 절만 가지고는 안된다고 생각해서 예를 들었는데, 제가 모르는 방법이 있는지요 ?

상품코드 | 주문중 | 전체주문건수 | 주문완료건수

이런식으로 나오는 게 WHERE 절만 가지고 가능한가요 ? 있다면 지식을 좀 나누어주셨으면 합니다.  01/16 1:03:36 

 
 명랑폐인
 복합적인 정보를 가져오는 경우 JOIN과 함께 NULLIF문을 사용하면 효용성이 높아진다는 취지로 얘기를 드린겁니다. 특별히 딴지를 걸려는 의도는 아니었습니다.

만약에 날짜별로 주문내역과 주문개수를 다 표시해야 하는경우, 또는 날짜별로 주문한 수량과 주문금액, 결제수량, 결제총금액 을 표시해야 한다면?
이런 경우에 NULLIF와 JOIN절을 응용하면 아주 쉽게 처리가 될거라 봅니다.

SELECT 상품코드, count(IF(주문상태='주문중')) 주문중,
count(*) 전체주문건수, 전체주문건수-주문중 완료건수
FROM 주문테이블
WHERE 주문일자 LIKE '200401%'
GROUP BY 상품코드
과 같이 표현해도 될거 같네요.

테이블간에 일반 JOIN이나 OUTER JOIN을 하게 되면 필드값이 매칭이 안될때, 값이 NULL로 들어가게 됩니다. 이때는 NULLIF를 꼭 써야 되겠죠. 01/16 6:08:50 
 


 DB
일별 집계나 통계 같은 경우는 RAW DATA를 검색하지 않고 일별 집계를 테이블을 따로 만들어서 보는것이 런닝중인 테이블에 부하도 덜 주고 복잡하고 골치아픈 함수를 사용하지 않고도 집계 결과를 빠른 속도로 볼 수 있습니다.