【BigQuery】LEAD関数とLAG関数の違いは？分析しつつ処理の違いを紹介

以前LAG関数の使い方に関する記事を書きました。

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

LAG関数を使うと分析の幅が広がります。例えば「1つ前のレコードとの差分を求めたい」など複雑な分析に役立つでしょう。初めてLAG関数を見ると引数が多くて苦手意識を持つかもしれません。しかしよく観察するとそれほど複雑な関数でないことが分かります。今回は1つ前のレコードと比較するLAG関数について紹介します。

tellingbook.com

2023.07.22

LAG関数と同じ使い方で逆の動きをするLEAD関数があります。
本日はLEAD関数の使い方についてご紹介します。

サンプルデータ 〜翌週の入場者数と比較する〜

LAG関数では前週の入場者数と比較してその増減数を出力しました。
LEAD関数を使うと翌週の入場者数と比較できその増減数を求められます。

美術館の入場者数データを元に分析してみましょう。
分かりやすいように曜日別に出力しました。

LEAD関数を使う

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

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

使い方はLAG関数と全く同じですね！
違うポイントはLAG関数が新しいカラムを1レコード下げて追加したのに対し、LEAD関数は新しいカラムを1レコード上げて追加します。

実際に「翌週の入場者数が入ったカラム」を追加しましょう。

「翌週の入場者数カラム」を追加する

SELECT
  date AS `日付`,
  day AS `曜日`,
  num AS `1日の入場者数`,
  LEAD(num, 1) OVER( PARTITION BY day ORDER BY day asc) AS `翌週の入場者数` 
FROM
  `sql-book-384011.sample.20230723_musium_partition_by_day`
ORDER BY
  day ASC,
  date asc

「翌週の入場者数カラム」を追加できました。
2行目（2023-07-01）を見ると「1日の入場者数：1000」の横に2023-07-08の入場者数である「650」が入ってますよね。

LAG関数では1行目の「1日の入場者数」の横は null でした。

このようにLEAD関数とLAG関数は似たような使い方ですが、正反対の動きをします。

LAG関数を使って「前週の入場者数カラム」を追加した場合

最後に「翌週との入場者数の差」と「増減率」を求めましょう！

「翌週との入場者数の差」と「増減率」を求める

WITH
  summary_result AS (
  SELECT
    date AS `日付`,
    day AS `曜日`,
    num AS `1日の入場者数`,
    LEAD(num, 1) OVER(PARTITION BY day ORDER BY day ASC) AS `翌週の入場者数`
  FROM
    `sql-book-384011.sample.20230723_musium_partition_by_day`
  ORDER BY
    day ASC,
    date ASC )
SELECT
  `日付`,
  `曜日`,
  `1日の入場者数`,
  `翌週の入場者数`,
  (`1日の入場者数` - `翌週の入場者数`) * -1 AS `翌週との入場者数の差`,
  ROUND(((`1日の入場者数` - `翌週の入場者数`) * -1) / `1日の入場者数`, 2) * 100 AS `増減率`
FROM
  summary_result

WITH句を使って計算式をシンプルにしました。
「翌週との差」と「増減率」が数値化でき、土曜日の第4週と第5週の差が1番大きいことが分かりました。

まとめ

LEAD関数を使うと指定した数のレコード分だけ上にずれて追加されます。
一方のLAG関数も使い方は同じでしたが、指定した数のレコード分だけ下にずれて追加されました。

それぞれ使い方は一緒なのに対照の動きをしていておもしろいですね。

「レコードの差分」を求めたいときは出力したい結果に合わせて適切な関数を使いましょう！