search
LoginSignup
38

More than 3 years have passed since last update.

posted at

updated at

【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

1. 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`;

実行結果は…
キャプチャ1.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位まで出してみましょう。

前日比を計算してみる
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
カラム「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;

実行結果は...
キャプチャ3.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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
38