こんにちは、TVerでデータ分析をしている高橋です。
こちらは TVer Advent Calendar 2023 の12日目の記事です。
弊社の分析業務は、主にBigQueryに蓄積されたデータを対象としています。データ処理の効率を向上させるため、データの前処理から集計までを一貫してSQLクエリで実施しています。この過程でNULL値の取り扱いは避けて通れない重要なテーマとなっています。
この記事では、（直近タスクでNULL含む処理の検証に多くの時間を溶かした筆者が）弊社で頻繁に使用されるSQLクエリの処理においてNULLがどのように扱われるかをまとめたのでご紹介します。

チートシート

今回調べた内容を整理すると以下の3パターンになりました。

種類	演算子、構文、関数など
`NULL`として扱われる	四則演算子, ビット演算子, 比較演算子, 論理演算子, ARRAY_AGG, FIRST_VALUE, LAST_VALUE, ORDER BY
無視される	AVG, MAX, MIN, SUM, STRING_AGG, LOGICAL_AND, LOGICAL_OR, COUNT
`FALSE` として扱われる	CASE, IF

以下、サンプルクエリとともに個別に見ていきます。

演算子

公式ドキュメントには、特に指定がない場合、すべての演算子において被演算子のいずれかが NULL の場合はNULL を返す、と記載されています。

例えば以下のクエリは全てNULL を返します。

SELECT
    -- NULL = 1 <-- これはエラーが発生する
    CAST(NULL AS INT64) = 1,  --> NULL
    CAST(NULL AS INT64) + 1,  --> NULL
    CAST(NULL AS INT64) < 1 , --> NULL

論理演算子の場合は3値論理に基づいて判定が行われます。
被演算子にNULLを含んでいながらNULLではない値が返ってくるケースがあるようです。

条件式

CASE文でFALSEとNULLは区別されないようです。
以下のクエリを実行すると、すべてのWHEN句でNULLとなるのでunknownが返ってきます。

SELECT
    CASE 
        WHEN NULL AND TRUE THEN "a"
        WHEN CAST(NULL AS INT64) = 1 THEN "b"
        ELSE "unknown"
    END --> "unknown"

IF文も同様です。以下クエリを実行すると2が返ってきます。

SELECT
    IF(NULL, 1, 2) AS col1 --> 2

集計関数

集計関数のうち、AVG, MAX, MIN, SUM, STRING_AGGはnon-NULL値を対象として処理が行われるようです。

SELECT
    AVG(val) AS avg_val,    --> 3.0
    MAX(val) AS max_val,    --> 4
    MIN(val) AS min_val,    --> 2
    SUM(val) AS sum_val,    --> 6
    STRING_AGG(CAST(val AS STRING)) AS str_vals --> "2,4"
FROM (
    SELECT
        val
    FROM
        UNNEST(ARRAY[2, 4, NULL]) AS val
)

LOGICAL_AND, LOGICAL_OR も同様にnon-NULL値を対象として処理が行われます。

SELECT
    LOGICAL_AND(flag), --> [TRUE, FALSE] の AND なので FALSE
    LOGICAL_OR(flag)   --> [TRUE, FALSE] の OR  なので TRUE
FROM
    UNNEST(ARRAY[TRUE, FALSE, NULL]) AS flag

COUNT は入力の行数または式がNULL以外の値に評価された行数を取得します。

SELECT
    COUNT(1),    --> 3
    COUNT(flag), --> 2
    COUNT(NULL)  --> 0
FROM
    UNNEST(ARRAY[TRUE, FALSE, NULL]) AS flag

ARRAY_AGG は配列にNULL要素が含まれているとエラーが発生します。
IGNORE NULLSで除外してあげましょう。

SELECT
    -- ARRAY_AGG(val) AS arr           --> Error
    ARRAY_AGG(val IGNORE NULLS) AS arr --> [2,4]
FROM (
    SELECT
        val
    FROM
        UNNEST(ARRAY[2, 4, NULL]) AS val
)

ナビゲーション関数

FIRST_VALUE, LAST_VALUEはNULL値を含んで計算されます。
例えば、ユーザーが最後に視聴した日付を日次で取得する処理を考えます。
この場合IGNORE NULLSオプションでNULLを無視することで所望の結果を得ることができます。

WITH
play_logs AS (
    SELECT
        "xxx" AS user_id,    
        day,
        logs.begin_d IS NOT NULL AS has_played,
    FROM
        UNNEST(GENERATE_TIMESTAMP_ARRAY(
            TIMESTAMP("2023-12-01 00:00:00"),
            TIMESTAMP("2023-12-05 00:00:00"),
            INTERVAL 1 DAY
        )) AS day
    LEFT OUTER JOIN (
        SELECT
            begin_d
        FROM
            UNNEST(ARRAY[
                TIMESTAMP("2023-12-01 00:00:00"), 
                TIMESTAMP("2023-12-02 00:00:00"), 
                TIMESTAMP("2023-12-05 00:00:00")
            ]) AS begin_d
    ) AS logs
    ON
        day = logs.begin_d
)

SELECT
    *,
    LAST_VALUE(IF(has_played, day, NULL)) OVER(PARTITION BY user_id ORDER BY day ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS day_last_played,
    LAST_VALUE(IF(has_played, day, NULL) IGNORE NULLS) OVER(PARTITION BY user_id ORDER BY day ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS day_last_played_ignore_nulls
FROM
    play_logs

ORDER BY句

昇順に並べるとNULLは一番先頭に来るようです。
順序は以下の通り（公式doc）。

NaN (Not a Number)は不正な演算を行った場合に返ってくる値のようです（SELECT IEEE_DIVIDE(0, 0)はNaNを返す）。
NULLS LASTオプションでNULLを末尾に追いやることが可能です。

SELECT
    val
FROM
    UNNEST(ARRAY[1, 3, 2, NULL]) AS val
ORDER BY 
    val NULLS LAST

まとめ

NULL含む論理演算と条件式は難しいのでNULL埋めしてあげたい
ORDER BY句のNULLS LASTは便利そう

最後に

ニッチ感の否めない記事となりましたが、このようにTVerではデータ分析を通じてより確からしい示唆を得ようと日々精進を重ねています。
もしご興味があれば以下よりご連絡ください。お待ちしております！

recruit.tver.co.jp

TVer Tech Blog

BigQueryのNULLの扱いまとめ

チートシート

演算子

条件式

集計関数

ナビゲーション関数

ORDER BY句

まとめ

最後に