🔎
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