NULLIF 関数の実用的な使い方
NULLIF 関数って動作の説明は多いけど、それを何に使えばいいのかってあんまり理解されてないんじゃないかと思う。
ってことで、NULLIF 関数の実用的な使い方をいくつか紹介する。
NULLIF 関数の動作
まずは、NULLIF 関数の動作から。
NULLIF 関数は引数を 2 つとって、引数が等しい場合に NULL を返し、異なる場合に第一引数を返すだけの、動作自体はかなり簡単な関数。
例えば、
SELECT col_a, col_b, NULLIF(col_a, col_b) FROM SomeTable;
これを実行すると、次のような結果が得られる。
| col_a | col_b | NULLIF(col_a, col_b) |
|---|---|---|
| 10 | 10 | NULL |
| 10 | 0 | 10 |
| 0 | 10 | 0 |
| NULL | NULL | NULL |
また、プログラマのためのSQL 第2版では、CASE 式を用いて、
NULLIF(V1, V2) := CASE
WHEN (V1 = V2)
THEN NULL
ELSE V1
END;
プログラマのためのSQL 第2版 (P.50)
と同じと説明している。
更に、同書ではこの関数の特性として、
プログラマのためのSQL 第2版 (P.50)
- NULLIF(x, x) は、すべての x の値に対して、NULL を返します。(NULL = NULL) は、UNKNOWN になり、TRUE ではありませんが、NULL の場合も NULL を返します。
- NULLIF(0, (x - x)) は、NULL ではないすべての x の値を NULL に変換します。しかし NULL は、0 に変換されます。なぜなら、(NULL - NULL) は NULL になり、等値性のテストに失敗するからです。
- NULLIF(1, (x - x + 1)) は、NULL でないすべての x の値を NULL に変換します。しかし NULL は 1 に変換されます。これは、すべての数値データ型と、値に関して一般化することができます。
としている。
で、問題は、この関数は一体何に使えるのか、ということ。
ゼロ除算を避ける
おそらく NULLIF 関数の一番有名かつこれ以外の使い方は知られてないんじゃないか、と思うくらい有名なものがこれ。
ゼロ除算はエラーになってしまうので、ゼロを NULL に変換することでエラーを避け、結果を NULL にしてしまう、というもの。
SELECT col_a / NULLIF(col_b, 0) FROM SomeTable
基本形はこれだけど、結果が NULL だと嬉しくない場合も多いので、COALESCE 関数と併用することも多い。
SELECT -- col_bが0の場合、0になる -- COALESCE関数の第二引数を変更することで、-1とかにもできる COALESCE(col_a / NULLIF(col_b, 0), 0) FROM SomeTable
何かの比率を求める、ってのはよくあること*1なので、多分最も多く使う形。
これを CASE 式で書こうとすると、
SELECT CASE col_b WHEN 0 THEN 0 ELSE col_a / col_b END FROM SomeTable
長い・・・
無効値の NULL 化
NULL の使用を嫌って*2、NOT NULL を使っていると、無効値に 0 だとか -1 だとか、そういう値を用いることがある。
そして、それら無効値を NULL 化することで、COALESCE 関数を使って計算から無効値を取り除くことが出来る。
例えば -1 が無効値の場合、
SELECT COALESCE(NULLIF(col_a, -1), 1) * col_b , COALESCE(NULLIF(col_a, -1), 0) + col_c FROM SomeTable
このように記述することで、計算に影響を与えない形に変換できる。
これを CASE 式で記述すると、
SELECT CASE col_a WHEN -1 THEN col_b ELSE col_a * col_b END , CASE col_a WHEN -1 THEN col_c ELSE col_a + col_c END FROM SomeTable
やっぱり長い・・・
AVG や MIN の対象から除外する
これは使っている RDBMS にもよりそうな話なので、一応 SQL Server のみを対象としておく。
SELECT -- 0を集計対象外にしている -- 無効値を集計対象外にすることも容易 AVG(NULLIF(col, 0)) , MIN(NULLIF(col, 0)) FROM SomeTable
こうすると、0 の値を集計対象外に出来る。
まぁ、この場合、
SELECT AVG(col) , MIN(col) FROM SomeTable WHERE col <> 0
こうすればいいだけの話だけど、SQL Server では NULLIF を使ったほうがほんの少し高速になるケースが多いようだ*3。
プログラマのためのSQL 第2版で紹介されている例
52 ページから 53 ページで紹介されている例だけど、一部分しか載ってない上、一部間違っている・・・
SELECT custno , COALESCE( NULLIF(COALESCE(NULLIF(0, P.dec - P.dec), 'Dec.'), 0) , NULLIF(COALESCE(NULLIF(0, P.nov - P.nov), 'Nov.'), 0) , NULLIF(COALESCE(NULLIF(0, P.oct - P.oct), 'Oct.'), 0) , NULLIF(COALESCE(NULLIF(0, P.sep - P.sep), 'Sep.'), 0) , NULLIF(COALESCE(NULLIF(0, P.aug - P.aug), 'Aug.'), 0) , NULLIF(COALESCE(NULLIF(0, P.jul - P.jul), 'Jul.'), 0) , NULLIF(COALESCE(NULLIF(0, P.jun - P.jun), 'Jun.'), 0) , NULLIF(COALESCE(NULLIF(0, P.may - P.may), 'May.'), 0) , NULLIF(COALESCE(NULLIF(0, P.apr - P.apr), 'Apr.'), 0) , NULLIF(COALESCE(NULLIF(0, P.mar - P.mar), 'Mar.'), 0) , NULLIF(COALESCE(NULLIF(0, P.feb - P.feb), 'Feb.'), 0) , NULLIF(COALESCE(NULLIF(0, P.jan - P.jan), 'Jan.'), 0) ) AS MonthOfLastPayment FROM Payments P
この SQL で、顧客 No と顧客が最後に支払った月を取得できる*4。
これを CASE 式を使って書くと・・・
SELECT custno , COALESCE( CASE WHEN P.dec IS NOT NULL THEN 'Dec.' ELSE NULL END , CASE WHEN P.nov IS NOT NULL THEN 'Nov.' ELSE NULL END , CASE WHEN P.oct IS NOT NULL THEN 'Oct.' ELSE NULL END , CASE WHEN P.sep IS NOT NULL THEN 'Sep.' ELSE NULL END , CASE WHEN P.aug IS NOT NULL THEN 'Aug.' ELSE NULL END , CASE WHEN P.jul IS NOT NULL THEN 'Jul.' ELSE NULL END , CASE WHEN P.jun IS NOT NULL THEN 'Jun.' ELSE NULL END , CASE WHEN P.may IS NOT NULL THEN 'May.' ELSE NULL END , CASE WHEN P.apr IS NOT NULL THEN 'Apr.' ELSE NULL END , CASE WHEN P.mar IS NOT NULL THEN 'Mar.' ELSE NULL END , CASE WHEN P.feb IS NOT NULL THEN 'Feb.' ELSE NULL END , CASE WHEN P.jan IS NOT NULL THEN 'Jan.' ELSE NULL END ) AS MonthOfLastPayment FROM Payments P
もはや人間が書くものじゃない。
まとめ
NULLIF 関数は COALESCE 関数や極値関数、場合によっては CASE 式と合わせて使うことで、SQL を単純にできる。
