Bigquery

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

ぐぅ

ぐぅ

京大工学部▶︎京大大学院情報学研究科▶︎日系企業の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.2nullnull
2021-12-11田中9.010.2null
2021-12-12田中8.29.010.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.2nullnull
2021-12-11田中9.010.2null
2021-12-12田中8.29.010.2
2021-12-10佐藤11nullnull
2021-12-11佐藤9.411null
2021-12-12佐藤8.89.411

こういった場合は分けたいカラムを「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.29.08.2
2021-12-11田中9.08.2null
2021-12-12田中8.2nullnull
SELECT
  日付,
  人,
  タイム,
  LEAD (タイム, 1) OVER (ORDER BY 日付) AS 次の日のタイム
  LEAD (タイム, 2) OVER (ORDER BY 日付) AS 次の日のタイム
FROM
  `日付と50m走のタイム`;

まとめ

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

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

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

  • この記事を書いた人
ぐぅ

ぐぅ

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

-Bigquery

© 2022 はやぶさエンジニア Powered by AFFINGER5