【BigQuery】LAG関数の使い方とは？レコードを比較して差分を求める方法を紹介

LAG関数を使うと分析の幅が広がります。

例えば「1つ前のレコードとの差分を求めたい」など複雑な分析に役立つでしょう。

初めてLAG関数を見ると引数が多くて苦手意識を持つかもしれません。
しかしよく観察するとそれほど複雑な関数でないことが分かります。

今回は1つ前のレコードと比較するLAG関数について紹介します。

サンプルデータ〜美術館の入場者数で分析する〜
LAG関数を使ってみる
1. 前日の入場者数カラムを追加する
2. 「前日と当日の入場者数の差」と「増減率」を求める
まとめ

サンプルデータ〜美術館の入場者数で分析する〜

ここに美術館の入場者数データがあります。
前日と比較して何人増減しているか把握できるカラムを追加しましょう。

LAG関数を使ってみる

LAG関数はこのように使います。

LAG (対象カラム, nレコード分だけ下にずらす) OVER( [PARTITION BY ～] ORDER BY ～ )

LAG関数を使うとn個だけレコード数をずらして対象カラムを追加できます。
それでは前日の入場者数が入ったカラムを追加しましょう。

前日の入場者数カラムを追加する

SELECT
  date AS `日付`,
  num AS `1日の入場者数`,
  LAG(num, 1) OVER(ORDER BY date ASC) AS `前日の入場者数`
FROM
  `sql-book-384011.sample.20230722_musium`
ORDER BY
  date asc

3列目に「前日の入場者数」が追加されました。
2行目を見ると、2023-07-20の「1日の入場者数」である「1000」が入っていますね。

続いて「前日と当日の入場者数の差分」と「増減率」を求めてみましょう。

「前日と当日の入場者数の差」と「増減率」を求める

SELECT
  date AS `日付`,
  num AS `1日の入場者数`,
  LAG(num, 1) OVER(ORDER BY date ASC) AS `前日の入場者数`,
  num - LAG(num, 1) OVER(ORDER BY date ASC) AS `前日と当日の入場者数の差分`,
  ROUND((num - LAG(num, 1) OVER(ORDER BY date ASC)) / (LAG(num, 1) OVER(ORDER BY date ASC)), 2) * 100 AS `増減率`
FROM
  `sql-book-384011.sample.20230722_musium`
ORDER BY
  date asc

複雑そうなクエリに見えますが