![[メモ]BigQueryのGREATEST関数の対象からNULLを除外する](https://devio2023-media.developers.io/wp-content/uploads/2022/09/googlecloud-bigquery.png)
[メモ]BigQueryのGREATEST関数の対象からNULLを除外する
この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
クラスメソッド株式会社データアナリティクス事業本部所属のニューシロです。
今回はGoogle CloudのサービスであるBigQueryにおけるGREATEST関数の記事です。
前提
GREATEST関数とは
GREATEST関数は、複数の引数の中から最大の値を返す関数です。
逆に、複数の引数の中から最小の値を返す関数LEAST関数もあります。
ほぼ同じ挙動のため、今回はこちらもあわせて検証しています。
参考ブログ
GREATEST関数、LEAST関数については以下のブログが参考になりました。
GREATEST関数は、引数にNULLを1件でも含む場合はNULLを返します。
今回は、NULLを除く引数で比較できるように、少しSQLを変更してみます。
使ってみる
そのまま使用する
引数に1件でもNULLを含んでいるとNULLを返すことを確認します。
以下のSQLを使用して、4パターンの引数で比較してみます。
WITH master AS ( SELECT 10 AS col_1, 20 AS col_2, 30 AS col_3 UNION ALL SELECT NULL AS col_1, 20 AS col_2, 30 AS col_3 UNION ALL SELECT NULL AS col_1, NULL AS col_2,30 AS col_3 UNION ALL SELECT NULL AS col_1, NULL AS col_2, NULL AS col_3 ) SELECT *, GREATEST(col_1, col_2, col_3) AS greatest, LEAST(col_1, col_2, col_3) AS least FROM master
SQL実行結果
+-------+-------+-------+----------+-------+ | col_1 | col_2 | col_3 | greatest | least | +-------+-------+-------+----------+-------+ | 10 | 20 | 30 | 30 | 10 | | NULL | 20 | 30 | NULL | NULL | | NULL | NULL | 30 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | +-------+-------+-------+----------+-------+
ハイライト部分のレコードにおいて、GREATEST関数（またはLEAST関数）の引数にNULLが含まれているため、他の引数を無視してNULLを返しています。
NULLを除外した引数で比較した結果を返すように、少しSQLを変更してみます。
NULLを除外する
IFNULL関数を使用して、NULLを含む場合でも最大値を出力できるようにします。
GREATEST関数では、想定される引数の最小値をIFNULL関数の第二引数とします（LEAST関数では、想定される引数の最大値）。これにより、IFNULL関数によって置き換えられた値がGREATEST関数で最大値として選ばれることはありません。
WITH master AS ( SELECT 10 AS col_1, 20 AS col_2, 30 AS col_3 UNION ALL SELECT NULL AS col_1, 20 AS col_2, 30 AS col_3 UNION ALL SELECT NULL AS col_1, NULL AS col_2,30 AS col_3 UNION ALL SELECT NULL AS col_1, NULL AS col_2, NULL AS col_3 ) SELECT *, GREATEST(IFNULL(col_1, 0), IFNULL(col_2, 0), IFNULL(col_3, 0)) AS greatest, LEAST(IFNULL(col_1, 100), IFNULL(col_2, 100), IFNULL(col_3, 100)) AS least FROM master
SQL実行結果
+-------+-------+-------+----------+-------+ | col_1 | col_2 | col_3 | greatest | least | +-------+-------+-------+----------+-------+ | 10 | 20 | 30 | 30 | 10 | | NULL | 20 | 30 | 30 | 20 | | NULL | NULL | 30 | 30 | 30 | | NULL | NULL | NULL | 0 | 100 | +-------+-------+-------+----------+-------+
これで、NULLを除いた引数の内で最大値（最小値）を返すことができました。
しかし、最後のレコード（ハイライト箇所）ではGREATEST関数（またはLEAST関数）は引数が全てNULLですが、特定の値(0, 100)が返されてしまっています。
全ての引数がNULLならNULLを返すように、少しSQLを変更してみます。
NULLを除外し、かつ全ての引数がNULLならNULLを返す
IF文で条件分岐させましょう。
WITH master AS (
SELECT 10 AS col_1, 20 AS col_2, 30 AS col_3
UNION ALL
SELECT NULL AS col_1, 20 AS col_2, 30 AS col_3
UNION ALL
SELECT NULL AS col_1, NULL AS col_2,30 AS col_3
UNION ALL
SELECT NULL AS col_1, NULL AS col_2, NULL AS col_3
)
SELECT
*,
IF(col_1 IS NULL AND col_2 IS NULL AND col_3 IS NULL, NULL,
GREATEST(IFNULL(col_1, 0), IFNULL(col_2, 0), IFNULL(col_3, 0))
) AS greatest,
IF(col_1 IS NULL AND col_2 IS NULL AND col_3 IS NULL, NULL,
LEAST(IFNULL(col_1, 100), IFNULL(col_2, 100), IFNULL(col_3, 100))
) AS least
FROM master
SQL実行結果
+-------+-------+-------+----------+-------+ | col_1 | col_2 | col_3 | greatest | least | +-------+-------+-------+----------+-------+ | 10 | 20 | 30 | 30 | 10 | | NULL | 20 | 30 | 30 | 20 | | NULL | NULL | 30 | 30 | 30 | | NULL | NULL | NULL | NULL | NULL | +-------+-------+-------+----------+-------+
これにより、NULLを含む場合は他の引数で比較し、また全ての引数がNULLのときはNULLを返すことができました。
最後にまとめ
関数は便利ですが、ときに想定外の結果を返すこともありますね。
これからも挙動を逐一確認し、必要に応じて想定通りの結果が返ってくるように改修していきたいです。
