この記事はアイスタイル Advent Calendar 2022 16日目の記事です。

はじめまして、株式会社アイスタイルのkuritahです。
2022/02からデータ分析システム部に所属していて、GCPデータ基盤の運用等に関わっています。

初投稿ですが、ささやかながらnullが入った時のdatabase(以後DB)毎のqueryへの影響についてまとめます。
今回はMySQLとPostgreSQL、BigQueryの3つを比較していきます。
そういえばこんな違いもあったねと、去り行く2022年と共に振り返って頂ければ幸いです。

検証環境

MySQL ver.8.0.31
PostgreSQL ver.14.5
GCP BigQuery StandardSQLを使用(2022/12/13時点)

本記事で述べたいこと

同じ関数、同じqueryを実行してもDB種別によって挙動が異なることがあります。
特にDB移行に伴うSQLの書き換えや、普段から複数のDBを使用している場合は、それぞれのDB特性を考慮したqueryの記述やテストケースの作成が必要になります。

null値を含むカラムを処理する際はDB毎に扱いに違いが出やすいため、より慎重な対応が求められます。
ここでは、文字列結合(concat), 比較(greatest), 並べ替え(order by), 順位付け(row_number), window関数(first_value)を題材に、それぞれのDBで実際に起きるnullによる影響をご紹介していきます。

Case1.文字列結合(concat)とnull

nullが入った時の挙動でDB毎に違いが起きることについて、比較的有名なのはconcatでしょうか。

MySQL : not nullのカラム同士を結合する例

mysql> select concat('con', 'cat') as c1;
+--------+
| c1     |
+--------+
| concat |
+--------+
1 row in set (0.00 sec)

本来は上記のようにnot nullのカラム同士の文字列を結合する関数ですが、nullの許容されたカラムを結合する場合、null値が入った時の挙動にDB毎に違いが生じます。

MySQL : nullとnot nullを結合するとnullが優先される

mysql> select concat('con', null) as c1;
+------+
| c1   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

PostgreSQL : nullとnot nullを結合するとnot nullが優先される

postgres=# select concat('con', null) as c1;
 c1
-----
 con
(1 row)

BigQuery : nullとnot nullを結合するとnullが優先される

$ bq query --use_legacy_sql=false "select concat('con', null) as c1;"
+------+
|  c1  |
+------+
| NULL |
+------+

レコードのサンプリングでは影響に気づきにくいため、特にPostgreSQLから他のDBに移行する際は気を付けたいですね。

Case2.比較(greatest)とnull

greatestやleastのような複数のカラムを比較する関数も注意が必要です。
横持ちのカラムを比較できる便利な関数ですが、最大値を求めるgreatest, 最小値を求めるleastどちらもPostgreSQLを除いてnull値が混入すると比較ができなくなってしまいます。

MySQL : greatestでnullとnot nullを含む複数のカラムを比較するとnullが優先される

mysql> select greatest(1, 2, null) as c1;
+------+
| c1   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

PostgreSQL : greatestでnullとnot nullを含む複数のカラムを比較するとnot nullの中で最も大きい値が優先される

postgres=# select greatest(1, 2, null) as c1;
 c1
----
  2
(1 row)

BigQuery : greatestでnullとnot nullを含む複数のカラムを比較するとnullが優先される

$ bq query --use_legacy_sql=false "select greatest(1, 2, null) as c1;"
+------+
|  c1  |
+------+
| NULL |
+------+

Case3.並べ替え(order by)とnull

最も大きな影響として、order byを使った時にnullが何番目に出てくるかという順序に影響があります。
※PostgreSQLにおいては、厳密にはorder byにnull first, null lastをつけることでnullの順序を制御することができますが、ここでは割愛します。

MySQL : nullが昇順最上位

mysql> select num from pre order by num asc;
+------+
| num  |
+------+
| NULL |
|    1 |
|    2 |
+------+
3 rows in set (0.00 sec)

PostgreSQL : nullが昇順最下位

postgres=# select num from pre order by num asc;
 num
-----
   1
   2

(3 rows)

BigQuery : nullが昇順最上位

select num from pre order by num asc; -- at [2:1]
+------+
| num  |
+------+
| NULL |
|    1 |
|    2 |
+------+

上記order byの順序が影響する箇所として、直ぐに思いつくのはlimit文との組み合わせですが、意外と忘れがちなのが集計関数やwindow関数との組み合わせです。
この後のCase4, Case5では集計関数やwindow関数におけるnullの影響をご紹介しますが、いずれも根本的な原因にはこのorder byにおけるnullの出現順序が関わってきます。

Case4.順位付け(row_number)とnull

maxやmin等予めnullを除外することが明示されている関数においては、どのDBで実行してもnullによる差異は発生しません。

maxやminにおいてはnullを除外することが予め定められている

mysql> select max(num) as max, min(num) as min from pre order by num asc;
+------+------+
| max  | min  |
+------+------+
|    2 |    1 |
+------+------+
1 row in set (0.01 sec)

一方で顕著に影響が発生するのは、row_numberやrankのような順位付け関数です。
特にnullが最大値に評価されるPostgreSQLにおいては、以下の例のように在庫数が多い順に順位付けしようとしたら、row_number = 1で在庫数がnull値の商品が当たってしまった・・・なんてことが無いようにnull値の制御を徹底したいですね。

order byでnull値が最上位となったrow_numberの例

postgres=# select row_number() over(order by quantity desc) as row_num,
postgres-#        quantity, name
postgres-#  from  pre;
 row_num | quantity |  name
---------+----------+--------
       1 |          | melon
       2 |        2 | banana
       3 |        1 | apple
(3 rows)

上記のrow_numberの例のようなケースでは、予め参照元テーブルにnot null制約を設定もしくはdefault値 = 0を設定してnull値が入らないようにする、あるいはwhere句でis not nullを指定してnull値が入りえないようにすることが有効になりそうです。

しかし、特に正規化がされていないテーブルを使用するシチュエーションや複数のテーブルを外部結合するシチュエーションにおいて、安易にwhere句のis not nullによる制御を入れられないケースも考えられます。
次のCaseでは、サンプルテーブルを用いてfirst_valueでnullによる影響を受けるケースを紹介します。

Case5.window関数(first_value)とnull

first_value使用時に、nullによる影響を受けるのは主に以下の3パターンです。

order byで並び替えを行う時に参照するカラムにnullが入るケース
first_valueで取得するカラムにnullが入るケース
partitionに指定されたカラムにnullが入るケース

3つめのpartitionにnullが入るケースも十分な注意が必要ですが(想定外のデータが集約されてしまうことがあるため)、ここではDB毎の挙動の違いという本題からずれるため割愛し、1と2について見ていきます。

5-1.order byで並び替えを行う時に参照するカラムにnullが入るケース

まずnullの並べ替え順序が関係するのはこのケースですが、多くの場合ifnullやcoalesceで穴埋めをすることで回避できそうですね。
実際にやってみます。

以下のorder_historyテーブルを参照して、顧客毎の購入金額の合計と、総購入金額のランキングを集計します。
消費税等細かな条件はここでは割愛します。

order_historyテーブルの定義とレコード

postgres=# \d order_history;
            Table "pg_temp_3.order_history"
   Column    |  Type   | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
 customer_id | integer |           | not null |
 item_id     | integer |           | not null |
 quantity    | integer |           | not null |
 price       | integer |           | not null |
Indexes:
    "order_history_pkey" PRIMARY KEY, btree (customer_id, item_id)

postgres=# select * from order_history order by 1, 2;
 customer_id | item_id | quantity | price
-------------+---------+----------+-------
        1111 |   55555 |        3 |   100
        1111 |   66666 |        5 |   300
        1111 |   77777 |        2 |   600
        2222 |   11111 |        1 |  1500
(4 rows)

order_historyテーブルを用いた顧客毎の購入金額とランキング

postgres=#  select  customer_id,
postgres-#          sum(price * quantity) as total,
postgres-#          rank() over(order by sum(price * quantity) desc) as total_rank
postgres-#   from   order_history
postgres-# group by customer_id
postgres-# order by 3 asc;
 customer_id | total | total_rank
-------------+-------+------------
        1111 |  3000 |          1
        2222 |  1500 |          2
(2 rows)

ここまでは、元tableにnot null制約がかかっているため特に問題が起きることはなさそうです。
ここで、先ほどのorder_historyに以下のitem_categoryを結合して、顧客毎に購入履歴のある商品の中で最もratingの高いitem_idを取得するという要件を追加します。

item_categoryテーブルの定義とレコード

postgres=# \d item_category;
                Table "pg_temp_3.item_category"
  Column  |       Type        | Collation | Nullable | Default
----------+-------------------+-----------+----------+---------
 item_id  | integer           |           | not null |
 rating   | integer           |           | not null |
 category | character varying |           | not null |
Indexes:
    "item_category_pkey" PRIMARY KEY, btree (item_id)

postgres=# select * from item_category;
 item_id | rating | category
---------+--------+----------
   11111 |      3 | lotion
   55555 |      5 | lotion
   77777 |      1 | mascara
(3 rows)

item_categoryには上記の通りnot null制約がありますが、order_historyに存在するitem_id = 66666が廃番のためitem_categoryに存在しないという状況が発生したと仮定します。
ここでは顧客の購入総額に廃番済みitemを含めることを要件とすると、外部結合を使用する必要がある = ratingにnullが入るケースが発生します。

外部結合により元not nullのratingカラムにnullが入るイメージ

postgres=# select o.*, i.*
postgres-#  from  order_history as o
postgres-#  left outer join item_category as i on o.item_id = i.item_id;
 customer_id | item_id | quantity | price | item_id | rating | category
-------------+---------+----------+-------+---------+--------+----------
        1111 |   55555 |        3 |   100 |   55555 |      5 | lotion
        1111 |   66666 |        5 |   300 |         |        |
        1111 |   77777 |        2 |   600 |   77777 |      1 | mascara
        2222 |   11111 |        1 |  1500 |   11111 |      3 | lotion

では、first_valueを用いて顧客が購入した商品の中で最も高いratingを持つitem_idを取得をします。
customer_id = 1111の顧客において、最も高いrating = 5を持つitem_id = 55555が出てきてほしかったのですが、rating is nullとなるitem_id = 66666が取得されてしまいました。

order byでnull値が最上位に認識されたためにfirst_valueの結果がずれる例

postgres=#  select distinct o.customer_id,
postgres-#           sum(o.price * o.quantity) over(
postgres(#              partition by o.customer_id
postgres(#           ) as total,
postgres-#           first_value(o.item_id) over(
postgres(#              partition by o.customer_id
postgres(#              order by i.rating desc
postgres(#           ) as most_rated_item
postgres-#   from    order_history as o
postgres-# left outer join item_category as i on o.item_id = i.item_id
postgres-# order by  1 asc;
 customer_id | total | most_rated_item
-------------+-------+-----------------
        1111 |  3000 |           66666
        2222 |  1500 |           11111
(2 rows)

そこで、ratingのnull値にcoalesceで0を入れることで無事rating = 5を持つitem_id = 55555が取得できるようになります。

coslesceで予めorder byで参照するカラムを0埋めすることで元null値に紐づく値が最上位に来なくなる例

postgres=#  select distinct o.customer_id,
postgres-#           sum(o.price * o.quantity) over(
postgres(#              partition by o.customer_id
postgres(#           ) as total,
postgres-#           first_value(o.item_id) over(
postgres(#              partition by o.customer_id
postgres(#              order by coalesce(i.rating, 0) desc
postgres(#           ) as most_rated_item
postgres-#   from    order_history as o
postgres-# left outer join item_category as i on o.item_id = i.item_id
postgres-# order by  1 asc;
 customer_id | total | most_rated_item
-------------+-------+-----------------
        1111 |  3000 |           55555
        2222 |  1500 |           11111
(2 rows)

ついでに購買ランクを組み合わせるとこうなります。

購買金額に基づくランキングとの組み合わせ

postgres=# with pre as(
postgres(#  select distinct o.customer_id,
postgres(#           sum(o.price * o.quantity) over(partition by o.customer_id) as total,
postgres(#           first_value(o.item_id) over(
postgres(#              partition by o.customer_id
postgres(#              order by coalesce(i.rating, 0) desc
postgres(#           ) as most_rated_item
postgres(#   from  order_history as o
postgres(#  left outer join item_category as i on o.item_id = i.item_id
postgres(# )
postgres-# select customer_id, total,
postgres-#        rank() over(order by total desc) as total_rank,
postgres-#        most_rated_item
postgres-#  from  pre order by 1 asc;
 customer_id | total | total_rank | most_rated_item
-------------+-------+------------+-----------------
        1111 |  3000 |          1 |           55555
        2222 |  1500 |          2 |           11111
(2 rows)

5-2.first_valueで取得するカラムにnullが入るケース

最後にorder byによる並べ替えではなく、first_valueで値として取得するカラムにnullが入るケースを考えます。

先ほどのケースでは顧客毎に最も高いratingを持つ購買したitem_idを取得しましたが、今回は顧客毎に最も購買個数の多いcategoryを取得します。
先にcustomer_id, category毎の売り上げ金額と個数を集計してしまうと楽ですね。

顧客とカテゴリ毎の購買個数の集計

postgres=# create temporary table pre as
postgres-# select o.customer_id, i.category,
postgres-#        sum(o.price * o.quantity) as sub_total,
postgres-#        sum(o.quantity) as sub_total_quantity
postgres-#  from  order_history as o
postgres-# left outer join item_category as i on o.item_id = i.item_id
postgres-# group by o.customer_id, i.category;
SELECT 4
postgres=# select * from pre order by customer_id asc, category asc;
 customer_id | category | sub_total | sub_total_quantity
-------------+----------+-----------+--------------------
        1111 | lotion   |       300 |                  3
        1111 | mascara  |      1200 |                  2
        1111 |          |      1500 |                  5
        2222 | lotion   |      1500 |                  1
(4 rows)

顧客毎の購買総額と最も購買個数の多いカテゴリの集計(最上位レコードの値がnullであった場合の例)

postgres=# select distinct customer_id,
postgres-#           sum(sub_total) over(partition by customer_id) as total,
postgres-#           first_value(category) over(
postgres-#              partition by customer_id
postgres-#              order by sub_total_quantity desc
postgres-#           ) as favorite_category
postgres-#  from  pre;
 customer_id | total | favorite_category
-------------+-------+-------------------
        1111 |  3000 |
        2222 |  1500 | lotion
(2 rows)

ここでもcustomer_id = 1111の顧客において、購買個数が最も多い(sub_total_quantity=5)カテゴリはnullだったため、favorite_categoryの値にnullが入ってしまいました。

こんな時のために、first_valueにはignore nullsという便利なオプションが存在し、null値を飛ばして次点となる値を取得することができる・・のですが、MySQLとPostgreSQLは残念ながら、ignore nullsオプションに対応していません。

first_valueではデフォルトでrespect nullsというignore nullsと対となるオプションが適応されるため、order byによる順序で取得される値がnullであっても、自動で次点の値に繰り上がることはありません。

参考までに、PostgreSQLベースでもAmazon Redshiftではignore nullsオプションの使用が可能です。

BigQueryは、今回の3DBの中では唯一ignore nullsオプションの使用が可能です。
しかし、次にfirst_valueの参照するデフォルトのwindowフレームの問題が発生します。

first_valueが参照するデフォルトのフレーム範囲は、order byで並べ替えられた最初の値~現在の値までです。
今回の場合、preテーブルに置けるcustomer_id = 1111, category in('lotion', 'mascara')の2レコードにおいては、favorite_categoryの値に'lotion'を取得することができます。

customer_id = 1111、category = 'mascara'のレコード > sub_total_quantityカラムによるorder byで、favorite_category is nullのレコードを抜かすとcustomer_id = 1111、category = 'lotion'のレコードが最上位になる
customer_id = 1111、category = 'lotion'のレコード > sub_total_quantityカラムによるorder byで、favorite_category is nullのレコードを抜かすとレコード自身が最上位になる

しかし、customer_id = 1111, category is nullのレコードにおいては、自分より上位になるレコードが無いためfavorite_categoryの値を取得できません。

customer_id = 1111、category is nullのレコード > sub_total_quantityカラムによるorder byで、レコード自身が最上位になるため自身のnull値が取得される

windowフレームの影響で同一パーティションでも別レコードとfirst_valueの値が異なる例

# BigQuery :
select distinct customer_id, category, sub_total_quantity,
          sum(sub_total) over(partition by customer_id) as total,
          first_value(category ignore nulls) over(
             partition by customer_id
             order by sub_total_quantity desc
          ) as favorite_category
from pre
order by 1 asc; -- at [24:1]
+-------------+----------+--------------------+-------+-------------------+
| customer_id | category | sub_total_quantity | total | favorite_category |
+-------------+----------+--------------------+-------+-------------------+
|        1111 | mascara  |                  2 |  3000 | lotion            |
|        1111 | NULL     |                  5 |  3000 | NULL              |
|        1111 | lotion   |                  3 |  3000 | lotion            |
|        2222 | lotion   |                  1 |  1500 | lotion            |
+-------------+----------+--------------------+-------+-------------------+

結果、distinctをしてもcustomer_id = 1111に対し、favorite_category = 'lotion'とis nullの2種類のレコードができてしまいます。

パーティションに指定されたはずのcustomer_idが等しいレコードが複数できる例

select distinct customer_id,
          sum(sub_total) over(partition by customer_id) as total,
          first_value(category ignore nulls) over(
             partition by customer_id
             order by sub_total_quantity desc
          ) as favorite_category
from pre
order by 1 asc; -- at [29:1]
+-------------+-------+-------------------+
| customer_id | total | favorite_category |
+-------------+-------+-------------------+
|        1111 |  3000 | lotion            |
|        1111 |  3000 | NULL              |
|        2222 |  1500 | lotion            |
+-------------+-------+-------------------+

そこで、first_valueのフレーム参照範囲を変更し、特にフレームの最後尾の範囲を広げてorder byで同一パーティション中自分よりも下位になるレコードを参照できるように変更します。

・unbounded preceding : first_valueで参照する範囲の先頭をpartitionの最上位に指定する(このケースでは、オプション付与前と変わりはない)
・unbounded following : first_valueで参照する範囲の最後尾を、レコード自身からpartitionの最後尾に広げる

フレームの参照範囲を明示的に指定することでpartitionに紐づくfirst_valueの値を一意にできる例

select distinct customer_id, category, sub_total_quantity,
          sum(sub_total) over(partition by customer_id) as total,
          first_value(category ignore nulls) over(
             partition by customer_id
             order by sub_total_quantity desc
             rows between unbounded preceding and unbounded following
          ) as favorite_category
  from   pre
order by 1 asc; -- at [24:1]
+-------------+----------+--------------------+-------+-------------------+
| customer_id | category | sub_total_quantity | total | favorite_category |
+-------------+----------+--------------------+-------+-------------------+
|        1111 | mascara  |                  2 |  3000 | lotion            |
|        1111 | NULL     |                  5 |  3000 | lotion            |
|        1111 | lotion   |                  3 |  3000 | lotion            |
|        2222 | lotion   |                  1 |  1500 | lotion            |
+-------------+----------+--------------------+-------+-------------------+
select distinct customer_id, sum(sub_total) over(partition by customer_id) as total,
         first_value(category ignore nulls) over(
           partition by customer_id
           order by sub_total_quantity desc
           rows between unbounded preceding and unbounded following
         ) as favorite_category
  from   pre
order by 1 asc; -- at [33:1]
+-------------+-------+-------------------+
| customer_id | total | favorite_category |
+-------------+-------+-------------------+
|        1111 |  3000 | lotion            |
|        2222 |  1500 | lotion            |
+-------------+-------+-------------------+

これでようやく、顧客毎に最も購入数の多かった商品カテゴリを取得することができました。
　　
上記のような事態を避けるためにも、DBでnull or not nullをしっかりと定義し参照時にはテーブル定義を確認、結合中にも頭の中でレコードのpartitionやnull or not nullを正確に描けるようになりたいですね。

それでは皆様、素敵なクリスマスをお迎えください。