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

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

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

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

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

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

user_namepurchase_itempurchased_date
太郎干し柿2022-1-1
太郎干し柿2022-1-3
太郎干し柿2022-1-4
太郎干し柿2022-1-7
太郎干し柿2022-1-12
太郎の購入レコード。干し柿が大好きなようだ。

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

user_namepurchase_itempurchased_datebefore_purchased_date
太郎干し柿2022-1-1
太郎干し柿2022-1-32022-1-1
太郎干し柿2022-1-42022-1-3
太郎干し柿2022-1-72022-1-4
太郎干し柿2022-1-122022-1-7
一番右のカラムに注目。
一つ前の購入レコードのpurchased_dateの値が入っている。

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

user_namepurchase_itempurchased_datebefore_purchased_datepassed_days
太郎干し柿2022-1-1
太郎干し柿2022-1-32022-1-12
太郎干し柿2022-1-42022-1-31
太郎干し柿2022-1-72022-1-43
太郎干し柿2022-1-122022-1-75
前回の購入からの経過日を算出。
太郎は頻繁に購入しているようだ。

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

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

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

LAG関数の書き方

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

LAG関数の処理の流れ

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

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

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

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

user_namepurchase_itempurchased_date
太郎干し柿2022-1-1
太郎干し柿2022-1-3
太郎干し柿2022-1-4
太郎干し柿2022-1-7
太郎干し柿2022-1-12
taro_purchases

以下のクエリを実行すると、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
user_namepurchase_itempurchased_datebefore_purchased_date
太郎干し柿2022-1-1
太郎干し柿2022-1-32022-1-1
太郎干し柿2022-1-42022-1-3
太郎干し柿2022-1-72022-1-4
太郎干し柿2022-1-122022-1-7
右側にbefore_purchased_dateカラムを追加できた。
1つ目のレコードには、1つ前のレコードがないため、値はNULLになる。

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

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
user_namepurchase_itempurchased_datebefore_purchased_datepassed_days
太郎干し柿2022-1-1
太郎干し柿2022-1-32022-1-12
太郎干し柿2022-1-42022-1-31
太郎干し柿2022-1-72022-1-43
太郎干し柿2022-1-122022-1-75
前回の購入からの経過日を算出。

【発展編】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関数について紹介しました！ぜひ使いこなしてみてください！

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

  • この記事を書いた人

えびふらい

Webディレクター歴6年 兼 PdMとして活動中。 SQLや各社アプリ考察など、webディレクターの方向けの記事を掲載していきます！

-SQL
-, , ,