【BigQuery】FIRST_VALUE関数，LAST_VALUE関数の使い方とその違い

概要

　本記事では，BigQueryのFIRST_VALUE関数とLAST_VALUE関数を紹介します。
以下公式ドキュメントより引用：
https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts?hl=ja#navigation-functions

ナビゲーション関数は現在の行からウィンドウ フレーム内の別の行に対して，いくつかの value_expression を計算します。OVER 句の構文は，ナビゲーション関数によって異なります。
※value_expressionは，式から返すことができる任意のデータ型を指定できます。

・FIRST_VALUE関数：指定カラムの先頭の行を持ってくる関数
・LAST_VALUE関数：指定カラムの末尾の行を持ってくる関数

　同じような事をしているFIRST_VALUE関数とLAST_VALUE関数ですが，適当に使っていると少し厄介な問題が生じてしまいます。2つの関数の結果の違いと問題，その対応についても確認していきましょう。

今回のやること一覧
1.FIRST_VALUE関数，LAST_VALUE関数でカラムの先頭，末尾のデータを持ってくる
2.週ごとの初日，末日が比較できるようにしてみる

使用テーブル:number_of_people

date	day	number
2019-04-01	Monday	200
2019-04-02	Tuesday	10000
2019-04-03	Wednesday	2000
2019-04-04	Thursday	4000
2019-04-05	Friday	500
2019-04-06	Saturday	600
2019-04-07	Sunday	800
2019-04-08	Monday	100
2019-04-09	Tuesday	100
2019-04-10	Wednesday	3000
2019-04-11	Thursday	298
2019-04-12	Friday	19
2019-04-13	Saturday	298
2019-04-14	Sunday	998
2019-04-15	Monday	3000

１．FIRST_VALUE関数，LAST_VALUE関数でカラムの先頭，末尾のデータを持ってくる

　SELECT句でFIRST_VALUE関数，LAST_VALUE関数を使うことで，指定したカラムの先頭，末尾のデータが得られます。
試しにカラム「number」のそれぞれの行の隣に先頭，末尾のデータを付与して比較できるようにしてみましょう。

試しに使ってみる

SELECT
  date,
  day,
  number,
  --FIRST_VALUE()内で，参照するカラム名を指定
  --BigQueryでは，OVER()内でどのカラムで並べるか（ORDER BY）の指定は必須。今回は日付順で並べ替え
  FIRST_VALUE(number) OVER (ORDER BY date) AS first_value,
  --LAST_VALUE()内で，参照するカラム名を指定
  LAST_VALUE(number) OVER (ORDER BY date) AS last_value
FROM
  `qiita.number_of_people`;

これで，「first_value」には「number」カラムの先頭である「200」が，「last_value」には末尾である「3000」が付与されるはずです。

しかし，実は上の書き方だと想定通りの結果を得ることができません。実行してみましょう。

実行結果は…

上手くいっていません。少しおかしいのが分かりますね。

「first_value」には予想通り，指定したカラム「number」の先頭のデータである「200」が入っていますが，「last_value」の方には末尾のデータである「3000」が入っておらず，その行の「number」のデータがそのまま付与されていますね。

　実は，ナビゲーション関数の一種であるFIRST_VALUE関数とLAST_VALUE関数では，OVER()内でWindow Frame句を指定しない場合，自動的に「RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW」が指定されてしまいます。
これは「先頭行から現在の行の範囲だけで処理をする」ことを意味しますので，カラム全体の先頭を出力したいFIRST_VALUE関数の方では上手くいっていても，LAST_VALUE関数ではカラム全体の末尾のデータを取得することができず，先のような結果になってしまうのです。
　
　それでは，OVER()内で適切なWindow Frame句を指定して，正しくカラムの末尾のデータが取得できるようにしましょう。

　WindowFrame句を指定してカラム全体の末尾が取れるようにする

SELECT
  *,
  FIRST_VALUE(number) OVER (ORDER BY date) AS first_value,
  --OVER()内で，先頭行から末尾行までを意味する「ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING」を指定する
  LAST_VALUE(number) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  AS last_value
FROM
  `qiita.number_of_people`; 

実行結果は...

今度は上手くいきました。「first_value」には先ほど同様カラムの先頭のデータである「200」が，そして「last_value」には末尾のデータである「3000」が正しく入っているのが分かります。

　このように，FIRST_VALUE関数，LAST_VALUE関数はやることは同じ様に見えますが，内部で何が起きているのかキチンと確認しないと思う通りの結果が得られない為，OVER()内で適切な対応が必要になります。

２．週ごとに初日，末日を比較してみる

　「ORDER BY」，「Window Frame」だけでなく，OVER()内では「PARTITION BY」を使うこともできます。これを使って，週ごとの初日と末日が比較できるようなカラムを追加しましょう。

週ごとの初日，末日を出してみる

SELECT
  *,
  --BigQueryのEXTRACT関数で週ごとに区切るための番号（その日付がその年の第何週目か）を付与
  EXTRACT(week FROM date) AS week_number,
  --PARTITION BYで「どのカラムの内容で区切るか」を指定　今回は上述のEXTRACT関数で付与した週番号ごとに区切って処理をしてもらう
  FIRST_VALUE(number) OVER (PARTITION BY (EXTRACT(week FROM date)) ORDER BY date) AS first_value,
  --Window Frame句で処理する行範囲の指定を忘れずに
  LAST_VALUE(number) OVER (PARTITION BY (EXTRACT(week FROM date)) ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM
  `qiita.number_of_people`; 

実行結果は...

上手くいきましたね。
「first_value」，「last_value」にはそれぞれの週ごとに区切った際の先頭行，末尾行である初日と末日のデータが正しく付与されていることが確認できます。これで，その日のデータと週の初日，末日との比較がしやすくなりました。

まとめ

　FIRST_VALUE関数とLAST_VALUE関数が使いこなせれば，テーブル内のデータを比較する為に便利なカラムが作成できるようになります。
　しかし，OVER()内での処理を適切に扱わないと，特にLAST_VALUE関数の場合には思う通りの結果が出せない場合がありますので，注意して取り扱いましょう。

参考サイト

https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts?hl=ja#navigation-functions
https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions?hl=ja#first_value
https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions?hl=ja#last_value
https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts?hl=ja#analytic-function-syntax
https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions?hl=ja#extract