BigQueryで値の補完をする

次のようなテーブルがあるとします。

id	name	flag	pre	producer
1	ミカン	on	XXX県産	Aさん
2	ミカン		不明	Aさん
3	リンゴ	off	不明	Cさん
4	リンゴ		YYY県産	Bさん
5	バナナ		不明	Cさん

以下のような形になるよう整形していきます。

id	name	flag	pre	producer
1	ミカン	on	XXX県産	Aさん
2	ミカン	on	XXX県産	Aさん
3	リンゴ	off	YYY県産	Cさん
4	リンゴ	off	YYY県産	Bさん
5	バナナ	off	YYY県産	Cさん

はじめに、flagの空欄を埋めます。
今回は、同じ行内で最後に登場した値を引き継ぐことにします。

table_flag AS (
  SELECT
    id,
    name,
    CASE
      -- flagがNULLならば、
      WHEN flag IS NULL
        -- id順で並べた際に、flagの行内で最後にNULLではなかった値を現在の行にコピーする。
        THEN LAST_VALUE(flag IGNORE NULLS) OVER (ORDER BY id)
      ELSE flag
    END AS flag0,
    pre,
    producer
  FROM base_table
)

※IGNORE NULLS：NULLを値に含まない
※LAST_VALUE ⇔ FIRST_VALUE

id	name	flag	pre	producer
1	ミカン	on	XXX県産	Aさん
2	ミカン	on	不明	Aさん
3	リンゴ	off	不明	Cさん
4	リンゴ	off	YYY県産	Bさん
5	バナナ	off	不明	Cさん

次に、preが不明になっている欄を補完します。
今回は以下の条件に沿って進めます。
（１）同じ果物なら同じ産地である。
（２）異なる果物でも、同じ生産者なら同じ産地である。
行の順番に依存している部分がありますが、説明のために用意したテーブルなので無視します。

（１）同じ果物なら同じ産地である。

table_pre1 AS (
  SELECT
    id,
    name,
    flag0 AS flag,
    CASE
      -- 現在の行が「不明」で、
      WHEN pre = "不明"
        -- 現在の行と1つ下の行の果物が同じで、
        AND name = LEAD (name, 1) OVER (ORDER BY id)
        -- 現在の行の1つ下の行の産地が「不明」でないなら、
        AND LEAD (pre, 1) OVER (ORDER BY id) != "不明"
        -- 現在の行の1つ下の行の産地を現在の行にコピーする。
        THEN LEAD (pre, 1) OVER (ORDER BY id)
      -- 現在の行が「不明」で、
      WHEN pre = "不明"
        -- 現在の行と1つ上の行の果物が同じで、
        AND name = LAG (name, 1) OVER (ORDER BY id)
        -- 現在の行の1つ上の行の産地が「不明」でないなら、
        AND LAG (pre, 1) OVER (ORDER BY id) != "不明"
        -- 現在の行の1つ上の行の産地を現在の行にコピーする。
        THEN LAG (pre, 1) OVER (ORDER BY id)
      -- どちらにも当てはまらないなら現在の値のまま
      ELSE pre
    END AS pre0,
    producer
  FROM table_flag
)

id	name	flag	pre	producer
1	ミカン	on	XXX県産	Aさん
2	ミカン	on	XXX県産	Aさん
3	リンゴ	off	YYY県産	Cさん
4	リンゴ	off	YYY県産	Bさん
5	バナナ	off	不明	Cさん

まだ1件「不明」が残っています。

（２）異なる果物でも、同じ生産者なら同じ産地である。

table_pre2 AS (
  SELECT
    id,
    name,
    flag,
    CASE
      -- table_pre1とほぼ同じなので説明省略
      WHEN pre0 = "不明"
        AND producer = LEAD (producer, 1) OVER (ORDER BY id)
        AND LEAD (pre0, 1) OVER (ORDER BY id) != "不明"
        THEN LEAD (pre0, 1) OVER (ORDER BY id)
      WHEN pre0 = "不明"
        AND producer = LAG (producer, 1) OVER (ORDER BY id)
        AND LAG (pre0, 1) OVER (ORDER BY id) != "不明"
        THEN LAG (pre0, 1) OVER (ORDER BY id)
      ELSE pre0
    END AS pre,
    producer
  FROM table_pre1
)

id	name	flag	pre	producer
1	ミカン	on	XXX県産	Aさん
2	ミカン	on	XXX県産	Aさん
3	リンゴ	off	YYY県産	Cさん
4	リンゴ	off	YYY県産	Bさん
5	バナナ	off	YYY県産	Cさん

これで完成です。