🔎
BigQueryである条件を満たす直近の行を抽出するSQL
ナビゲーション関数
ウィンドウ関数（分析関数）のうち、leadやlagといった別の行のカラムを抽出するようなものをナビゲーション関数といいます。
BigQueryに実装されているナビゲーション関数は以下のドキュメントにまとまっています。
first_value,last_value,nth_valueはウィンドウフレーム内の絶対的な位置を指定してその行を抽出する関数、
lead,lagはウィンドウフレーム内で現在の行からの相対的な位置を指定してその行を抽出する関数です。
先に結論
last_valueのignore nullsオプションと条件分岐を組み合わせることで、ある条件を満たす直近の行を抽出する操作が可能です。
last_value(if(condition, expr, null) ignore nulls) over (...)
解説
実は、last_valueなどいくつかのウィンドウ関数ではデフォルトのウィンドウフレームが最初の行〜現在の行となっています（参考）。
ここにignore nullsオプションをつけることで、last_valueは現在までの行の中で最後に現れるnull以外の値を抽出するという動作になります。
last_value(expr ignore nulls) over (...)
exprには単なるカラムだけではなく式を含めた任意のデータを入れることができるので、条件を満たす場合は抽出したいカラムを、満たさない場合はnullを返すような式を使うことで、ある条件を満たす直近の行を抽出することができます。
last_value(if(condition, expr, null) ignore nulls) over (...)
Discussion