はじめに
Redshiftで配列などを扱う際に便利な型super型のnullの扱いではまったので、super型のnullがどういう挙動をとるのかを少し分析しつつ、まとめてみた。
super型とは
super型の説明や、クエリの書き方などは以下の記事で簡単ではあるが解説しているので、こちらを参考にしてほしい。
テスト用テーブルの準備
今回の検証のために、以下のようなカラムを持つテンポラリテーブルを作成した。
create temporary table tmp_test_super(
date varchar(10),
users super
);
次に以下の値を持つ、jsonファイルを作成し、作成したテーブルにインポートした。
{"date": "2022-10-01", "users": ["Bob", "Andy", "keiko"]}
{"date": "2022-10-02", "users": []}
{"date": "2022-10-03", "users": ["Bob", "Jun"]}
インポートした結果は次の通りである。
=> select * from tmp_test_super ;
date | users
------------+------------------------
2022-10-01 | ["Bob","Andy","keiko"]
2022-10-02 | []
2022-10-03 | ["Bob","Jun"]
(3 rows)
このテーブルを用いて検証する。
ちなみに、"users": [] ではなく、以下のように "users": nullとしてもRedshiftには問題なくインポートすることができる。
create temporary table tmp_test_super_null(
date varchar(10),
users super
);
{"date": "2022-10-01", "users": ["Bob", "Andy", "keiko"]}
{"date": "2022-10-02", "users": null}
{"date": "2022-10-03", "users": ["Bob", "Jun"]}
インポート結果は以下のようになる。
=> select * from tmp_test_super_null;
date | users
------------+------------------------
2022-10-01 | ["Bob","Andy","keiko"]
2022-10-02 |
2022-10-03 | ["Bob","Jun"]
(3 rows)
今回の検証では、最初に示した "users": [] のほうを使用することにする。
検証
=> select * from tmp_test_super where users is null;
date | users
------+-------
(0 rows)
このように単純に users is null と条件指定しても、users配列がnullのレコードを抽出することができない。
=> select * from tmp_test_super where users[0] is null;
date | users
------------+-------
2022-10-02 | []
(1 row)
このように、users[0] is null と条件指定することで、users配列がnullのレコードを取得することができる。
ちなみに、以下のように users[2] is null とすると、"users": ["Bob","Jun"] のレコードも表示される。
=> select * from tmp_test_super where users[2] is null;
date | users
------------+---------------
2022-10-02 | []
2022-10-03 | ["Bob","Jun"]
(2 rows)
この結果から、["Bob","Jun"] の3番目の要素をnullとして判定していることがわかる。つまり、super型配列では、存在しない要素は全てnullと判定されることがわかる。
次に、users配列を反復的に処理してみた。
=> select a.users, b as user from tmp_test_super a, a.users b;
users | user
------------------------+---------
["Bob","Andy","keiko"] | "Bob"
["Bob","Andy","keiko"] | "Andy"
["Bob","Andy","keiko"] | "keiko"
["Bob","Jun"] | "Bob"
["Bob","Jun"] | "Jun"
(5 rows)
結果からわかる通り、反復的に処理すると、"users": [] のレコードは取得できなくなる。
nullを省きたいときにはとても便利であるが、nullを考慮して集計などしたい場合はこのままだとできず、割と厄介である。
そこで、nullの値も考慮して集計したいみたいな場面で使えそうなクエリを考えてみた。
例えば、userごとの(nullも含む)出現数を知りたいという要件の際に、以下のようなクエリで対応することができる。
=>
select
c.user,
count(1)
from
tmp_test_super d
left join
(select
a.date,
b as user
from
tmp_test_super a,
a.users b) c
on
d.date = c.date
group by
c.user;
user | count
---------+-------
| 1
"keiko" | 1
"Jun" | 1
"Bob" | 2
"Andy" | 1
(5 rows)
ポイントは、元のテーブル(tmp_test_super)に反復処理後のテーブルを date のようにプライマリキー的な働きをするものをキーとして left join している点である。
こうすることによって、usersがnullの場合もuserカラムにnullが入ることになり、適切に集計することが可能となる。
まとめ
今回は、Redshiftのsuper型のnullの挙動について簡単にまとめてみた。nullの扱いはやはり気を付けないと厄介だ。
最後に紹介したクエリは思いつきで書いているので、他に良い方法があれば教えて頂きたい。