SQL: NULLIF() 関数を使ってゼロ除算を防ぐ

RDBMS によってはゼロ除算をするとエラーになってしまうものがある。

例えば PostgreSQL とかがそう。

$ psql --version
psql (PostgreSQL) 9.6.3

次のようにゼロ除算をするとエラーになって結果が得られない。 これは SQL を使って演算するときに問題となることがある。

# SELECT 1 / 0;
ERROR:  division by zero

そんなときは NULLIF() 関数を使って意図的に値を NULL にしてしまうことが考えられる。 数値と NULL の計算であれば NULL の伝搬によって結果もまた NULL となるのでエラーにはならない。

次のクエリでは NULLIF() 関数の引数が両方とも 0 になっているけど、本来なら最初の変数が何らかのカラムの名前になるはず。 結果が空っぽに見えるのは PostgreSQL はデフォルトで NULL を空白として表示するからみたい。

# SELECT 1 / NULLIF(0, 0);
 ?column?
----------

(1 row)

これはようするに第一引数の中身が第二引数と一致したら、それを NULL に置き換えるということ。

# SELECT NULLIF(0, 0);
 nullif
--------

(1 row)

# SELECT NULLIF(1, 0);
 nullif
--------
      1
(1 row)

ゼロ除算がエラーにならない RDBMS もある

ゼロ除算してもエラーにならない実装もあるようだ。 例えば MariaDB とか。

$ mysql --version
mysql  Ver 15.1 Distrib 10.2.6-MariaDB, for osx10.12 (x86_64) using readline 5.1

次のようにゼロ除算を発生させると結果が自動的に NULL になる。

> SELECT 1 / 0;
+-------+
| 1 / 0 |
+-------+
| NULL  |
+-------+
1 row in set, 1 warning (0.00 sec)

あとは厳密には RDBMS じゃないけど SQL の処理系という意味では Hive とか。

$ hive --version
Hive 1.2.2
Subversion git://vgumashta.local/Users/vgumashta/Documents/workspace/hive-git -r 395368fc6478c7e2a1e84a5a2a8aac45e4399a9e
Compiled by vgumashta on Sun Apr 2 13:12:26 PDT 2017
From source with checksum bd47834e727562aab36c8282f8161030

こちらもゼロ除算を発生させると結果が NULL になった。

> SELECT 1 / 0;
OK
NULL
Time taken: 0.094 seconds, Fetched: 1 row(s)

まとめ

• ゼロ除算するとエラーになる RDBMS がある
• そのときは NULLIF() 関数を使って結果を NULL にするとエラーを防げる
• そもそもゼロ除算がエラーにならず NULL になる処理系もある

ビッグデータ分析・活用のためのSQLレシピ

ちなみに反対に NULL を特定の値に置換するのには COALESCE() 関数を使う。

blog.amedama.jp