【Bigquery】LAGとLEAD関数で前後のデータを取得しよう

2021年12月11日

ぐぅ

京大工学部▶︎京大大学院情報学研究科▶︎日系企業のITエンジニア。研究室ではAI×通信の研究も行いAIにも知見あり。友人とプログラミング初学者向けブログ「はやぶさエンジニア」を運営。ノーコードで起業し、サブスク型サービスを運営。26歳大阪在住

★本記事は5分で読み終えることができます。

【本記事の信頼性】
現役エンジニアである筆者がBigqueryのLAG、LEAD関数について解説しています。

未経験くん

Bigqueryで1つ前のデータを取得したいんだけどどうやるの？

前後の行とデータを比較したい！！

未経験ちゃん

このような悩みを解説するための記事になります。

痒い所に手が届く「LAG」「LEAD」関数について、超シンプルな例と共に解説していきます！

こんな方におすすめ

LAG、LEAD関数の使い方を知りたい方
前、後ろのデータを取得したい方

LAG関数で前の行を抽出する

超シンプルな例で解説していきます。

体育の授業の田中くんの「日付と50m走のタイム」のデータがあるとします。
「前日のタイムより今日がどれだけ速くなったか」を知りたい時について考えます。

日付	人	タイム
2021-12-10	田中	10.2
2021-12-11	田中	9.0
2021-12-12	田中	8.2

「日付と50m走のタイム」のテーブル

欲しい最終結果は以下です。

日付	人	タイム	前日のタイム	2日前のタイム
2021-12-10	田中	10.2	null	null
2021-12-11	田中	9.0	10.2	null
2021-12-12	田中	8.2	9.0	10.2

では、早速コードを見ていきましょう。公式リファレンスはこちら

LAG関数の注意点

・LAG(取得したいカラム, 何行前のデータか)を指定する
・「ORDER BY」で並べる順番を指定するのは必須
・「PARTITION BY」で区切りたい場合は指定する（必須ではない）

  LAG (タイム, 1) OVER (PARTITION BY 人 ORDER BY 日付) AS 前日のタイム

欲しい結果を取得するコードは以下のようになります。

SELECT
  日付,
  人,
  タイム,
  LAG (タイム, 1) OVER (ORDER BY 日付) AS 前日のタイム,
  LAG (タイム, 2) OVER (ORDER BY 日付) AS 2日前のタイム
FROM
  `日付と50m走のタイム`;

では、複数人のデータの場合に、人ごとに前日のデータが欲しい時にはどうすれば良いでしょうか？

日付	人	タイム
2021-12-10	田中	10.2
2021-12-11	田中	9.0
2021-12-12	田中	8.2
2021-12-10	佐藤	11
2021-12-11	佐藤	9.4
2021-12-12	佐藤	8.8

欲しい最終結果は以下です。

日付	人	タイム	前日のタイム	2日前のタイム
2021-12-10	田中	10.2	null	null
2021-12-11	田中	9.0	10.2	null
2021-12-12	田中	8.2	9.0	10.2
2021-12-10	佐藤	11	null	null
2021-12-11	佐藤	9.4	11	null
2021-12-12	佐藤	8.8	9.4	11

こういった場合は分けたいカラムを「PARTTION BY」で指定してあげます。

SELECT
  日付,
  人,
  タイム,
  LAG (タイム, 1) OVER (PARTITION BY 人 ORDER BY 日付) AS 前日のタイム,
  LAG (タイム, 2) OVER (PARTITION BY 人 ORDER BY 日付) AS 2日前のタイム
FROM
  `日付と50m走のタイム`;

LEAD関数で後ろの行を抽出する

LAGと書き方は同じで、LAGをLEADにするだけなので詳細は省きます。

日付	人	タイム	次の日のタイム	2日後のタイム
2021-12-10	田中	10.2	9.0	8.2
2021-12-11	田中	9.0	8.2	null
2021-12-12	田中	8.2	null	null

SELECT
  日付,
  人,
  タイム,
  LEAD (タイム, 1) OVER (ORDER BY 日付) AS 次の日のタイム
  LEAD (タイム, 2) OVER (ORDER BY 日付) AS 次の日のタイム
FROM
  `日付と50m走のタイム`;

まとめ

いかがだったでしょうか。

SQLには痒いところに手が届く関数が実装されています。
知っているかどうかで大きく作業効率が変わりますね！！

どんどん調べて活用していきましょう！！

ぐぅ