【BigQuery】LAG関数，LEAD関数の使い方

概要

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

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

・LAG関数：前の行を持ってくる関数
・LEAD関数：後ろの行を持ってくる関数
使いこなせると，とても便利です。

今回のやること一覧
1.LAG関数，LEAD関数で前後のデータを持ってくる
2.LAG関数で前日比を出してみる
3.曜日ごとの前週比も出してみる

使用テーブル: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

１. LAG関数，LEAD関数で前後のデータを持ってくる

　SELECT句でLAG関数，LEAD関数を使うと，指定したカラムの行の前後のデータが得られます。
　試しにカラム「number」の両隣に1日前，1日後の「number」のデータを付与して比較できるようにしてみましょう。

試しに使ってみる

SELECT
  date,
  day,
  --LAG()内で，参照するカラム名とずらす行数を指定
  --1行の場合はLAG(number)と省略しても問題なし
  --BigQueryでは，OVER()内でどのカラムで並べるか（ORDER BY）の指定は必須，今回は日付順で並べ替え
  LAG (number, 1) OVER (ORDER BY date) AS lag_data,
  number,
  --LEAD()内で，参照するカラム名とずらす行数を指定
  --1行の場合はLEAD(number)と省略しても問題なし
  LEAD (number, 1) OVER (ORDER BY date) AS lead_data 
FROM
  `qiita.number_of_people`;

実行結果は…

上手くいきました。
カラム「lag_data」には，カラム「number」の1行目のデータが2行目に，2行目のデータが3行目に...という様に，それぞれの行の1つ前のデータが入っています。同様にカラム「lead_data」には，カラム「number」の2行目のデータが1行目に，3行目のデータが2行目に...こちらにはそれぞれ1つ後のデータが入っています。

　ここで，「1つ前の行」，「1つ後の行」が存在しないカラム「lag_data」の1行目とカラム「lead_data」の15行目には，それぞれnullが入っているのも分かります。
※参照できない場合には必ずnullが入りますので，注意しておいて下さい。

2. LAG関数で前日比(%)を出してみる

　LAG関数をうまく使えば，各日の前日比(%)を簡単に出すこともできます。
　前日比は「その日の人数」/「前日の人数」*100で算出します。ROUND関数も使って小数点第1位まで出してみましょう。

前日比を計算してみる

SELECT 
  date,
  day,
  number,
  LAG(number) OVER (ORDER BY date) AS lag_data,
  ROUND(number * 1.0/(LAG(number) OVER (ORDER BY date)) *100,1) AS number_rate 
FROM
  `qiita.number_of_people` ;

実行結果は...

カラム「number_rate」に前日比が算出されているのが分かりますね。
　クエリのLAGの部分をLEADに置き換えれば，前日比ではなく後日比（？）を出すこともできます。書き方は全部同様ですので，片方覚えてしまえば，もう片方も上手く使えるようになるでしょう。

3. 曜日ごとの前週比(%)

　LAG関数，LEAD関数のOVER()内では「ORDER BY」だけでなく「PARTITION BY」も使うことができます。
　試しに曜日ごとの前週比(%)を算出してみましょう。算出方法は先ほどと同様です。

前週比も出してみる

SELECT 
  date,
  day,
--BigQueryのEXTRACT関数で曜日ごとに並び替え用の番号を付与
  EXTRACT(DAYOFWEEK FROM date) AS date_number,
  number,
--PARTITION BYで「どのカラムの内容で区切るか」を指定　今回は曜日ごとに区切って処理をしてもらう
  LAG(number) OVER (PARTITION BY day ORDER BY date) AS lag_data,
  ROUND(number * 1.0/(LAG(number) OVER (PARTITION BY day ORDER BY date)) *100,1) 
  AS number_rate 
FROM
  `qiita.number_of_people` 
ORDER BY date_number;

実行結果は...

上手くいきました。
それぞれに曜日ごとの前週比が，算出できないところにはnullが，ちゃんと入ってるのが分かります。

まとめ

LAG関数，LEAD関数を使いこなせば，
　・文字型，日付型など（数字型以外でも）のデータを前後にずらす
　・データを1行だけでなく，2行や3行などでもずらす
などができるので，色々と試してみてください。様々な集計が容易になります！

参考サイト

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#lead
https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions?hl=ja#lag
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja#extract_1