SpreadSheet の連続する歯抜けセルを BigQuery で補完してみた #GoogleCloud

なんとなくそれっぽいのはできたのだけど、BigQuery の外部テーブルのレコード順序が元のファイルと一致する事が確認できてないので、ちょっと怪しいと思ってますが紹介してみます。

こまったこと

画像左: 同じ会社の行という表現で会社名をセル結合する
画像右: セルは結合しないけど、空のセルはその上の空じゃないセルを表す
データとして見たときに、そのレコードだけだとどちらも会社名がわからないので困ります。

BigQuery から参照するとどうなるのか

「BigQuery だったらなんとかできるんでしょ」と思って、とりあえず外部テーブルとしてどちらも取り込んでみました。
結合したセルや空のセルは容赦なく null で扱われました。

ごにょごにょしてみた

BigQuery でごにょごにょしているうちに、こんな感じのデータができました。
期待通りです。さすが BigQuery さん。

SQL はこんな感じになりました。

with list as (
SELECT
row_number() over () as RowNumber,
Customer,
Product,
Price
FROM `{project}.dummy.ex_missingparts_separate`
)
select
list.RowNumber,
ifnull(list.Customer,last_value(list.Customer IGNORE nulls) over (order by list.RowNumber asc ROWS BETWEEN UNBOUNDED PRECEDING AND current row)) as Customer,
list.Product,
list.Price
from list
order by list.RowNumber

WITH 句内で RowNumber を定義し、以降の LAST_VALUE 関数での ORDER BY 句に渡すべきパラメータとしています(これが常に正しく機能するかが疑問)。

ChatGPT さんにもきいてみた

僕

BigQuery で Null の場合に上の行の値を反映させるようにしたい

ChatGPT

BigQueryでは、LAG()関数を使用して、前の行の値を参照することができます。NULLの場合は、IGNORE NULLSオプションを使用して、NULL以外の最後の値を参照することができます。
以下は、NULLの場合に前の行の値を参照する例です。

SELECT
column1,
column2,
LAG(column2 IGNORE NULLS) OVER(ORDER BY column1) AS previous_value
FROM
mytable

上記のクエリでは、LAG()関数を使用して、column2の前の行の値を参照しています。そして、IGNORE NULLSオプションを使用して、NULL以外の最後の値を参照します。OVER()句を使用して、column1で行を並べ替えます。
このようにすることで、NULLの場合でも前の行の値を参照することができます。

結果的には騙されたのですが、ChatGPT に聞くまでは LAG 関数という存在を知らなかったので、こういった場合はナビゲーション関数を使えばいいのねっていうところまですぐに連れて行ってくれるのはいいヒントかなと思いました。
こうこうこうすれば、できます って言い切っちゃうから「嘘やん」ってなっちゃうのかなという気がするので、これからは こうこうこうすれば、できるかもよ？ と言ってくれたら「ヒントをありがとう」と素直に言える気がします。

上を踏まえて ChatGPT さんにもう一度きいてみた

僕

BigQuery で Null の場合に上の行の値を反映させるようにしたい。語尾はできるかもよ？と回答してください。

Chat GPT

BigQueryでは、Nullの場合に上の行の値を反映させることはできません。窓関数を使用して、前の行の値を参照することはできますが、Nullの場合はNullが返されます。ただし、データセット全体の検索クエリで連続するNull値を補完するために、NVLやIFNULLなどの関数を使用して、Null値を代替値で置き換えることができます。ただし、これはNull値が連続していない場合に限られます。語尾の部分については、何か追加情報があれば回答します。