null 値を含むレコード同士で LEFT JOIN する

はじめに

null 値を含むレコード同士で LEFT JOIN することができないときに、どう対応したかまとめました。

実行環境

• BigQuery

例

流入元 と 流入元別の売上 をすでに算出している。 流入元 が分からなかったレコードは、流入元ID を null とした。 流入元 と 流入元別の売上 を join して、流入元 と 流入元の売上 を抽出したい。

• 流入元: inflows
  • 流入元ID: inflow_id
  • 流入元: inflow_name
• 流入元別の売上: inflow_sales
  • 流入元ID: inflow_id
  • 売上: sales

参照するデータ

流入元 と 流入元別の売上 を下記データとする。

with inflows as (
  select
    cast(null as int64) as inflow_id
    , 'others' as inflow_name
)

, inflow_sales as (
  select
    cast(null as int64) as inflow_id
    , 100 as sales
)

LEFT JOIN できないクエリ

下記だと、join できない。

select
  inflows.inflow_name
  , inflow_sales.sales
from inflows
left join inflow_sales
  on inflows.inflow_id = inflow_sales.inflow_id

LEFT JOIN ができない理由

null と何かを比較すると null になる。 null = null で null になり join することができないため、null のレコードが消えてしまう。

select
  cast(null as int64) = cast(null as int64)
  , cast(null as int64) = 1
  , 2 = cast(null as int64)
  , 1 = 1
  , 1 = 2
  , -1 = -1
  , '' = ''

LEFT JOIN できるように対応

-1 や 未入力 など、通常では入らないような値を入れて join する。

-- 参照するデータ
with inflows as (
  select
    cast(null as int64) as inflow_id
    , 'others' as inflow_name
)

, inflow_sales as (
  select
    cast(null as int64) as inflow_id
    , 100 as sales
)

-- stg_* の CTE を追加してデータクレンジング
-- * null の値を -1 に置き換える
, stg_inflows as (
  select
    coalesce(inflow_id, -1) as inflow_id
    , inflow_name
  from inflows
)

, stg_inflow_sales as (
  select
    coalesce(inflow_id, -1) as inflow_id
    , sales
  from inflow_sales
)

select
  stg_inflows.inflow_name
  , stg_inflow_sales.sales
from stg_inflows
left join stg_inflow_sales
  on stg_inflows.inflow_id = stg_inflow_sales.inflow_id

下記でも join できるが、分かりづらい。 上記クエリのようにベースのクエリで -1 などを入れたほうが可読性が高い。

select
  inflows.inflow_name
  , inflow_sales.sales
from inflows
left join inflow_sales
  on coalesce(inflows.inflow_id, -1) = coalesce(inflow_sales.inflow_id, -1)

まとめ

null 値を含むレコード同士で join したい場合は、なにか値を入れる必要がある。

参考

• BigQuery エンタープライズ向けデータ ウェアハウス