難しそうな関数はどんなときに使えるかイメージがしにくいですよね。
今回は私の実務経験をもとにLAG関数の実践的な使い方をご紹介します。
サンプルデータ 〜ジムの入退室ログデータ〜
ここになおとくんの2023年7月のジムの入退出記録があります。
いわゆるログデータですね。
このログデータからなおとくんがジムを利用しなかった期間を求めましょう！
なおとくんがジムに来なかった期間を求める
「利用しなかった期間」は「退出時間 – 入室時間」で求められますね。
現在のレコードのまま「退出時間 – 入室時間」をしてしまうと「利用時間」が求まってしまい、意図した結果ではありません。
「利用しなかった期間」を求めるには、退出時間から次の入室時間を引けば良さそうです。
このような同じ行にないレコード同士を分析したいときにLAG関数が使えます。
LAG関数の基本形
LAG関数はこのように書きました。
LAG (対象カラム, nレコード分だけ下にずらす) OVER( [PARTITION BY ～] ORDER BY ～ )今回であれば
- 対象カラムは「退出時間」
- レコードを1ずらす
- 退出時間は昇順のまま
というように書き記せば良さそうですね。実際にやってみましょう！
SELECT
LAG(exit_time, 1) OVER(ORDER BY exit_time ASC) AS `LAG_退出時間`,
enter_time AS `入室時間`,
exit_time AS `退出時間`
FROM
`sql-book-384011.sample.20230725_gym_naoto`
ORDER BY
enter_time ASC次の入室時間と同じ行に前の退出時間を持ってこれました。
これで差分を取ってあげれば「利用しなかった期間」が求まりそうですね！
WITH lag_time AS (
SELECT
LAG(exit_time, 1) OVER(ORDER BY exit_time ASC) AS `LAG_退出時間`,
enter_time AS `入室時間`,
exit_time AS `退出時間`
FROM
`sql-book-384011.sample.20230725_gym_naoto`
)
SELECT
DATETIME_DIFF(`入室時間`, `LAG_退出時間`, DAY) AS `利用しなかった期間`,
`LAG_退出時間`,
`入室時間`
FROM
lag_time
ORDER BY
`入室時間` ASC「利用しなかった期間」を求められました！
なおとくん平均3, 4日くらいの頻度でジムに通っていてえらいですね（笑）
集計結果をビジネスに活かす
今回の集計結果をビジネスに活かすとすると
例えば
- 1ヶ月以上利用していないお客様へダイレクトメッセージを送り利用を促す
- 半年以上利用していないお客様は休会へ変更するのはどうかと連絡する
- 利用しない期間が長いお客様の属性を分析する
このような活用方法があると思いました。
まとめ
LAG関数を使った実用的な分析はいかがだったでしょうか。
利用した時間は簡単に求められても、利用しなかった時間を求めるにはちょっと工夫が必要なんですね。
LAG関数は計算に使いたい数値が同じレコードにないときに使えると分かりました。
同じような状況に出会ったときはLAG関数を思い出して使ってみましょう！
