GREATESTおよびLEAST関数のNULLに関する仕様がBigQueryとRedshiftで全く違った件

どーも、データアナリティクス事業本部コンサルティングチームのsutoです。

今回は表題にあるとおり、BigQueryとRedshiftでGREATEST・LEAST関数の仕様に違いがあることを知る機会があったので備忘録として残すためにブログにしました。

GREATEST・LEAST関数のNULLの扱いについて

Redshiftの場合

以下のドキュメントにあるとおり、比較する引数にNULLがある場合は無視するとあります。

結果として、NULLを除く値が存在する引数のなかから結果を返す仕様となっています。

BigQueryの場合

以下のドキュメントにあるとおり、比較する引数にNULLが1つでもある場合、関数は結果としてNULLを返す仕様となっています。

実際に確認してみた

実際に簡単なテーブルを使ってBigQueryとRedshiftのそれぞれのGREATEST・LEAST関数の動きを確認してみます。

Redshiftではクエリエディタv2から作成したRedshiftクラスターに入り、以下のようなNULL値を含むデータを挿入したテーブル(greatest_least_test01)を準備しました。

同様にBigQueryの方も同じデータを挿入したテーブルを作成しました。

まずはRedshiftに以下のようなSQLを実行して、GREATEST・LEAST関数を使用したカラムと値を作成したTempテーブルを表示させてみると、

# WITH temp_created AS (
	SELECT
		id,
		number_1,
		number_2,
		GREATEST(number_1, number_2) AS number_g,
		LEAST(number_1, number_2) AS number_l
	FROM "dev"."public"."greatest_least_test01"
)
SELECT * FROM temp_created order by 1;

このように値が存在しているnumber_1カラムの方の値を返していることがわかります。

一方でBigQueryにも同様に以下のようなSQLを実行してTempテーブルを表示させてみると、

# WITH temp_created AS (
	SELECT
		undefinedid,
		number_1,
		number_2,
		GREATEST(number_1, number_2) AS number_g,
		LEAST(number_1, number_2) AS number_l
	FROM cm_suto.greatest_least_test01
)
SELECT * FROM temp_created order by 1;

NULLと比較している行はNULLが結果として返されています。

以上、GREATEST・LEAST関数におけるBigQueryとRedshiftの仕様の違いでした。

サービスとして比較される両者ですが、同じSQLを書いたとしても全く逆の結果を返すことになる関数があるんですね。