#概要
本記事では，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