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;