今回はBigQueryでデータ分析をする際に非常に役立つLAG関数について紹介する。
データ分析をする際に、前月比を取得したり、前年比を取得するということは非常に多い。
「1つ前のレコードの値との差分を取得したい！」
と場合に便利なのが本記事で説明するLAG関数だ。
LAG関数とは？
LAG関数は、指定したカラムの「現在の行」の「前にある行」の値を取得してくれる関数だ。
このLAG関数は売上データの前月比を取得したり、前年比を取得したりする際に非常に便利な関数だ。
例えば、下記の月別の売上データがあるとする。
- テーブル
- sample_sales
- カラム
- month ：DATE型
- total_sales：INT64型
| month | total_sales |
|---|---|
| 2023-01-01 | 2000 |
| 2023-02-01 | 1800 |
| 2023-03-01 | 2200 |
| 2023-04-01 | 2100 |
| 2023-05-01 | 2400 |
| 2023-06-01 | 2300 |
| 2023-07-01 | 2500 |
| 2023-08-01 | 2600 |
| 2023-09-01 | 2700 |
| 2023-10-01 | 2800 |
| 2023-11-01 | 2900 |
| 2023-12-01 | 3000 |
上記データに対して、LAG関数を使用し、前月の売上（last_month_sales）を取得する場合のクエリとその結果は下記の通りだ。
|
1 2 3 4 5 6 7 8 |
SELECT month ,total_sales ,LAG(total_sales,1)OVER(ORDER BY month) AS last_month_sales FROM `sample_dataset.sample_table` ORDER BY month ; |
LAG関数を使用することにより、対象カラムの1行前の値（今回の例では前月の売上値）を取得出来ていることがお分かりいただけると思う。
もし前月比を取得したい場合は、前月比(%)＝(当月売上÷前月売上)*100なので、下記のように指定すればOKだ。
|
1 2 3 4 5 6 7 8 9 |
SELECT month ,total_sales ,LAG(total_sales,1)OVER(ORDER BY month) AS last_month_sales ,ROUND((total_sales/LAG(total_sales,1)OVER(ORDER BY month))*100,1) AS sales_mom_change FROM `sample_dataset.sample_table` ORDER BY month ; |
このようにLAG関数は前月比を取得する際などに非常に便利な関数だ。
上記例では小数点の桁数を1桁に収めるためにROUND関数も使用しているのでやや複雑に見えるが、それぞれの指定の意味は次の章で詳しく説明するので
「LAG関数は前月比や前年比を取得するのに便利なんだな」
と今時点ではざっくり把握しておいてもらえれば全然OKだ。
LAG関数の使い方
LAG関数は下記3点を指定して使う。
- 対象カラム
- 何レコード分前の値を取得するか？
- どのカラムを基準として並び替えるか？
先ほどの例でLAG関数の指定の仕方を詳しく確認しよう。
- テーブル
- sample_sales
- カラム
- month ：DATE型
- total_sales：INT64型
| month | total_sales |
|---|---|
| 2023-01-01 | 2000 |
| 2023-02-01 | 1800 |
| 2023-03-01 | 2200 |
| 2023-04-01 | 2100 |
| 2023-05-01 | 2400 |
| 2023-06-01 | 2300 |
| 2023-07-01 | 2500 |
| 2023-08-01 | 2600 |
| 2023-09-01 | 2700 |
| 2023-10-01 | 2800 |
| 2023-11-01 | 2900 |
| 2023-12-01 | 3000 |
上記データにおいて、「total_sales」の前月売上（last_month_sales）を取得する場合のクエリとLAG関数の具体的な指定は下記の通りだ。
|
1 2 3 4 5 6 7 8 |
SELECT month ,total_sales ,LAG(total_sales,1)OVER(ORDER BY month) AS last_month_sales FROM `sample_dataset.sample_table` ORDER BY month ; |
上記クエリの結果は下記となる。
LAG関数で並び替えの基準のカラムを指定する際に、上記例では並び替えを昇順（小さい順）を指定する asc の指定を省略している。
実際は下記のように指定することも可能だ。
ORDER BYで昇順に並び替える場合、ascは省略可能であるが、LAG関数で使用するORDER BYに関しても同様にascは省略可能だ。
LAG関数で降順を使用する場合はあまりないと思うが、もし降順に並び替えたい場合はdescを指定する必要がある。
PARTITION BYを加える場合
LAG関数ではPARTITION BYを加えて使うことも出来る。
例えば、単純な前月比ではなく、下記データにおいて
「product_id（製品ID）ごとの前月比を確認したい！」
という時にPARTITION BYを加えたLAG関数の出番となる。
- テーブル
- sample_sales_amount
- カラム
- date(日付)：DATE型
- product_id（製品ID）：STRING型
- sales_amount（販売額）：INT64型
|
date
|
product_id
|
sales_amount
|
|
2023-01-01
|
A
|
1000
|
|
2023-02-01
|
A
|
1200
|
|
2023-03-01
|
A
|
1100
|
|
2023-04-01
|
A
|
1300
|
|
2023-05-01
|
A
|
1050
|
|
2023-06-01
|
A
|
1150
|
|
2024-01-01
|
B
|
2000
|
|
2023-02-01
|
B
|
2100
|
|
2023-03-01
|
B
|
2200
|
|
2023-04-01
|
B
|
2050
|
|
2023-05-01
|
B
|
2150
|
|
2023-06-01
|
B
|
2250
|
上記データでORDER BYのみを使用したLAG関数を使用したクエリを実行し、
- 前月売上（lastmonth_sales_amount）
- 前月比(mom_sales_amount)
を取得すると下記のように意図しない結果となってしまう。
|
1 2 3 4 5 6 7 8 9 |
SELECT date ,product_id ,sales_amount ,LAG(sales_amount,1)OVER(ORDER BY product_id,date) AS lastmonth_sales_amount ,ROUND((sales_amount/(LAG(sales_amount,1)OVER(ORDER BY product_id,date)))*100,1) AS mom_sales_amount FROM `sample_dataset.sample_table` ORDER BY product_id,date |
本来はproduct_idごとの前月比を見たいのだが、Bの23年1月の前月売上の値をAの23年6月の売上から取得してしまっているため、前月比がおかしくなっている。
ORDER BYのみを使用したLAG関数は単純に指定したカラムの指定した行数分前の値（今回は1行前の値）を取得する。
そのため、上記のようにproduct_idの値に関係なく、1行前の値を取得しているので、Bの前月売上をその1行前のAの売上から取得してしまっているのだ。
これでは分析がうまく出来ない、、、
LAG関数でPARTITON BYを使うと上記のような問題を解消してくれる。
PARTITON BYを加えたLAG関数を使用したクエリとその結果は下記の通りだ。
|
1 2 3 4 5 6 7 8 9 |
SELECT date ,product_id ,sales_amount ,LAG(sales_amount,1)OVER(PARTITION BY product_id ORDER BY product_id,date) AS lastmonth_sales_amount ,ROUND((sales_amount/(LAG(sales_amount,1)OVER(PARTITION BY product_id ORDER BY product_id,date)))*100,1) AS mom_sales_amount FROM `sample_dataset.sample_table` ORDER BY product_id,date |
今度は意図したとおりに、product_idごとに前月売上と前月比を取得出来ていることがお分かりいただけると思う。
LAG関数にPARTITION BYを追加すると、下記の指定を追加したことになる。
- 対象カラム
- 何レコード分前の値を取得するか？
- どのカラムの値ごとにデータを区切るか？
- どのカラムを基準として並び替えるか？
今回の例だとPARTITION BYでproduct_idを指定しているので、LAG関数の指定内容は下記の通りとなる。
PARTITION BYを使用すると、PARTITION BYで指定したカラムの値ごとにデータを区切って、その区切ったデータの範囲内でLAG関数を適用することが出来る。
上記例では、PARTITION BYでproduct_idを指定しているので、product_id=Aの場合とproduct_id=Bの場合でデータが区切られて、それぞれの範囲内でsales_amountの1行前の値を取得しているのだ。
LAG関数におけるPARTITON BYの使い方を覚えると劇的にLAG関数を使用できる範囲が広がるので、是非覚えてみて欲しい。
デフォルト値の設定
LAG関数で1行前の値を取得する指定をした際に、1行目などは前の行を取得出来ないので値がnullになる。
もし値をnullにしたくない場合はあらかじめLAG関数でデフォルトを設定することも可能だ。
例えば、下記クエリではproduct_idごとの前月売上を取得する際に、前月データがない行に関してはnullにする代わりに0を設定している。
|
1 2 3 4 5 6 7 |
SELECT date ,product_id ,sales_amount ,LAG(sales_amount,1,0)OVER(PARTITION BY product_id ORDER BY product_id,date) AS lastmonth_sales_amount `sample_dataset.sample_table` ORDER BY product_id,date |
このクエリの結果は下記の通りだ。
前月データがない 2023-01-01 の行に関してはnullの代わりに0が入っていることがお分かりいただけると思う。
このようにLAG関数ではデフォルト値を設定することも可能だ。
まとめ
だいぶ長くなってしまったが、今回紹介したLAG関数の要点は下記の通りだ。
- LAG関数は、指定したカラムの「現在の行」の「前にある行」の値を取得してくれる関数
- 前月の値や前月比を取得する際にとても便利な関数
- PARTITION BYを指定すると指定したカラムの値ごとにデータを区切った上でLAG関数を適用可能
- 前の行の値がない場合、nullの代わりにデフォルト値を設定することも可能
他のBigQueryに関する記事は下記にまとめているので是非参考にして欲しい。