CUBE SUGAR CONTAINER

技術系のこと書きます。

SQL: COALESCE() 関数で NULL を別の値に置き換える

RDB のスキーマ設計において、なるべくなら Nullable なカラムは無い方が良いけど、そうもいかないときがある。 そんなときは COALESCE() 関数を使うと NULL になっているフィールドを別の値に置換できる。 これは、特定の値を NULL に置換できる NULLIF() 関数と対になるものかな。

blog.amedama.jp

動作確認に使った環境は次の通り。

$ sw_vers 
ProductName:    Mac OS X
ProductVersion: 10.12.5
BuildVersion:   16F73
$ mysql --version
mysql  Ver 15.1 Distrib 10.2.6-MariaDB, for osx10.12 (x86_64) using readline 5.1

例えば次のようなテーブルを作っておく。 何らかの商品の購入履歴のテーブルで、元々の金額といくらディスカウントがあったかを記録する。

> CREATE TABLE purchases (
    ->   id INTEGER,
    ->   amount INTEGER,
    ->   discount INTEGER,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

サンプルとなるレコードをいくつか追加しておく。 ここでディスカウントの金額が NULL になっているものがあるところに注意する。 ディスカウントがなかったときは 0 ではなく NULL が入るということ。

> INSERT INTO
    ->   purchases
    -> VALUES
    ->   (1, 1000, NULL),
    ->   (2, 2000, 500),
    ->   (3, 3000, 1000);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

格納されるデータはこんな感じ。

> SELECT * FROM purchases;
+----+--------+----------+
| id | amount | discount |
+----+--------+----------+
|  1 |   1000 |     NULL |
|  2 |   2000 |      500 |
|  3 |   3000 |     1000 |
+----+--------+----------+
3 rows in set (0.00 sec)

何も考えずにディスカウントが適用された金額を計算してみよう。 これは、ただ単に金額からディスカウントを引くだけ。

> SELECT
    ->   id,
    ->   amount - discount AS discounted_amount
    -> FROM purchases;
+----+-------------------+
| id | discounted_amount |
+----+-------------------+
|  1 |              NULL |
|  2 |              1500 |
|  3 |              2000 |
+----+-------------------+
3 rows in set (0.00 sec)

するとディスカウントの値が NULL だったところの内容が NULL になってしまった。 これは NULL の伝搬と呼ばれるもので、計算内容に NULL が混入していると結果が全て NULL になってしまうというもの。

この NULL の伝搬を防ぐには NULL になっている値を何らかの値に置換してやれば良い。 今回の例であれば NULL0 に置き換えてしまえば大丈夫なはず。 そんなとき使えるのが今回紹介する COALESCE() 関数で、これは第一引数が NULL だったときに第二引数の値に置換できる。

先ほどのクエリに COALESCE() 関数を使ってみよう。

> SELECT
    ->   id,
    ->   amount - COALESCE(discount, 0) AS discounted_amount
    -> FROM purchases;
+----+-------------------+
| id | discounted_amount |
+----+-------------------+
|  1 |              1000 |
|  2 |              1500 |
|  3 |              2000 |
+----+-------------------+
3 rows in set (0.00 sec)

今度は結果が NULL にならず、ちゃんとした値になった。

まとめ

  • スキーマ定義にはなるべく Nullable なカラムはない方が良い
    • とはいえ、そうもいかないときがある
  • 計算内容に NULL が含まれると、それが伝搬してしまう
  • それを防ぐには COALESCE() 関数を使うと良い

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

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

  • 作者: 加嵜長門,田宮直人,丸山弘詩
  • 出版社/メーカー: マイナビ出版
  • 発売日: 2017/03/27
  • メディア: 単行本(ソフトカバー)
  • この商品を含むブログを見る