T-SQL - lag関数/lead関数でn個前/n個後の行を取得する

lag関数とlead関数を知ったのでメモしておきます。

それぞれ一言で説明すると、

  • lag関数 ... n個前の行を取得する
  • lead関数 ... n個後の行を取得する

といった感じです。この2つもウィンドウ関数と言っていいのかな。

構文の簡単なパターンだと、関数の1つ目の引数には「取得するカラム名」を、2つ目の引数には「n個前/n個後」を指定します。

lag(カラム名, n個前) over(...)
lead(カラム名, n個後) over(...)

年ごとや四半期ごとの比較といった実用的なサンプルは上記ドキュメントを参照してもらうとして、 ここでは超簡単なサンプルクエリを書いて実行結果を残しておきます。

with Seq(Value)  -- CTE(共通テーブル式)
as(
    -- 1から10まで連番を作る
    select 1
    union all
    select Seq.Value + 1
    from Seq
    where Seq.Value + 1 <= 10
)
select
    Value,
    -- Valueを昇順に並べ替えて3つ前の行を取得
    lag(Value, 3) over(order by Value) as Lag,
    -- Valueを昇順に並べ替えて3つ後の行を取得
    lead(Value, 3) over(order by Value) as Lead
from Seq
order by Value;

/*
Value       Lag         Lead
----------- ----------- -----------
1           NULL        4
2           NULL        5
3           NULL        6
4           1           7
5           2           8
6           3           9
7           4           10
8           5           NULL
9           6           NULL
10          7           NULL
*/

結果セットを見ると例えばValueが4の場合に、lag関数は1、lead関数は7を取得できていることがわかります。

また、lag関数/lead関数を使わないで上記と同じ結果を取得するには、 次のような自己結合を使ったクエリになると思いますが、 lag/lead関数を使ったほうがわかりやすいかなと。

-- lag関数/lead関数の代わりに自己結合を使って、3つ前、3つ後の行を取得する
with Seq(Value)
as(
    select 1
    union all
    select Seq.Value + 1
    from Seq
    where Seq.Value + 1 <= 10
)
select
    Src.Value,
    Dst1.Value as Lag,
    Dst2.Value as Lead
from Seq as Src
    left outer join Seq as Dst1
        on Src.Value = Dst1.Value + 3
    left outer join Seq as Dst2
        on Src.Value = Dst2.Value - 3
order by Src.Value;