【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   |

![キャプチャ.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/390340/8896423d-ab33-4b45-3f86-3dd8c60cab5b.png)

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

```sql:試しに使ってみる
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`;
```
実行結果は…
![キャプチャ1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/390340/393eee72-3e91-7623-51d1-975f663539b9.png)
上手くいきました。
カラム「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位まで出してみましょう。

```sql:前日比を計算してみる
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` ;
```

実行結果は...
![キャプチャ2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/390340/958979c1-6513-8841-8360-79eaddadc864.png)
カラム「number_rate」に前日比が算出されているのが分かりますね。
　クエリのLAGの部分をLEADに置き換えれば，前日比ではなく後日比（？）を出すこともできます。書き方は全部同様ですので，片方覚えてしまえば，もう片方も上手く使えるようになるでしょう。

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

```sql:前週比も出してみる
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;
```
実行結果は...
![キャプチャ3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/390340/7cad452b-fb4e-5979-1d96-923fd020fb52.png)

上手くいきました。
それぞれに曜日ごとの前週比が，算出できないところには_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