【SQL】LAG関数を使って、一つ前のレコードの値を抽出！

こんにちは、hosigakiです。
今日はウィンドウ関数の一つ、LAG関数の使い方を紹介します！
（ウィンドウ関数について分からない方は、こちらの記事で詳しく取り上げておりますので、ご参照ください！）

LAG関数って何ができるの？

LAG関数を使うと、一つ前のレコードを簡単に抽出することができます！
まずは具体例をもとに、LAG関数でできることのイメージをつけましょう！

例えば、太郎さんの購入レコードがあったとして...、

LAG関数を使うことで、太郎さんの一つ前の購入日 を抽出することができます！

LAG関数のおかげで、各レコードごとに 前回の購入日 と 今回の購入日 が揃いましたね。
最後に、これらの差分を計算することで、「前回の購入日から何日経過後に購入したのか」という有益な情報を算出できるようになりました！

このように、LAG関数を使うことで、一つ前のレコードの値を抽出することができます！

次は、LAG関数の使い方を見ていきましょう！

LAG関数をマスターしよう！

LAG関数の書き方

SELECT
    LAG(抽出したいカラム名) OVER(ORDER BY 並び替えたいカラム名)

LAG関数の処理の流れ

LAG関数を書くと、以下の流れで処理が行われて、値が抽出されます。

1. OVER句内の「ORDER BY 並び替えたいカラム名」によって、レコードが並び替えられる
2. 一つ前のレコードから、LAG関数内で指定したカラムの値が抽出される

LAG関数を使ったクエリの具体例

今回は、冒頭で取り上げた「太郎の購入レコード」を例に、クエリを考えてみましょう！

以下のクエリを実行すると、taro_purchasesテーブルの一番右に、before_purchased_dateカラムを追加できます！

SELECT
    *,
    LAG(purchased_date) OVER(ORDER BY purchased_date) AS before_purchased_date
FROM
    taro_purchases
ORDER BY
    purchased_date

ちなみに、もし冒頭の例のように、「前回の購入から何日経過しているか」を出したい場合は、以下のクエリで算出可能です！

SELECT
    *,
    LAG(purchased_date) OVER(ORDER BY purchased_date) AS before_purchased_date,
    DATE_DIFF(purchased_date, LAG(purchased_date) OVER(ORDER BY purchased_date), DAY) AS passed_days
FROM
    taro_purchases
ORDER BY
    purchased_date

【発展編】LAG関数は、2つ以上前のレコードも参照できる！

先ほど「LAG関数の書き方」にて、以下の書き方を取り上げました。

SELECT
    LAG(抽出したいカラム名) OVER(ORDER BY 並び替えたいカラム名)

↑ 実はこれ、LAG関数の2つ目の引数が省略された書き方なんです！

省略しないでちゃんと書くと、以下になります。

SELECT
    LAG(抽出したいカラム名, 1) OVER(ORDER BY 並び替えたいカラム名)

LAG関数の2つ目の引数に 1 が入っていますね。
実は、ここの引数を変えることで、その数だけ前のレコードを参照できるんです！

例えば2行前のレコードを参照したいときは、以下の書き方になります！

SELECT
    LAG(抽出したいカラム名, 2) OVER(ORDER BY 並び替えたいカラム名)

筆者は実業務上、一つ前のレコードを参照する機会しかないのですが、これも覚えておくと、いざという時に役立つかもしません！

今日はLAG関数について紹介しました！ぜひ使いこなしてみてください！

最後まで読んでいただきありがとうございました！
また次の記事でお会いしましょう(^^)/