分析関数の衝撃

PostgreSQLの分析関数の衝撃（7）
――window関数の変わった使用例

order by句でのwindow関数など

2010/02/15 14:00

ポスト

4. Lag関数の引数に行コンストラクタ

　最後は、Lag関数の引数に行コンストラクタを使うSQLです。『PostgreSQLの分析関数の衝撃6』で、『4.2.12. 行コンストラクタ』を少し紹介しましたが、今度はLag関数の引数に行コンストラクタを使ってみます。サンプルを見てみましょう。

LagTest

SortKey	Val1	Val2
10	1	5
12	3	7
14	3	7
15	1	5
19	3	7
25	3	5
30	3	5

　SortKeyの昇順で、前の行とVal1が等しく、かつ前の行とVal2が等しいかをチェックした結果を列別名PrevIsSameとして表示します。

出力結果

SortKey	Val1	Val2	PrevIsSame
10	1	5	0
12	3	7	0
14	3	7	1
15	1	5	0
19	3	7	0
25	3	5	0
30	3	5	1

　case式とLag関数を組み合わせて、答えは下記となります。

Lag関数を2回使用

select SortKey,Val1,Val2,
case when Val1 = Lag(Val1) over(order by SortKey)
      and Val2 = Lag(Val2) over(order by SortKey)
     then 1 else 0 end as PrevIsSame
from LagTest;

　SQLのイメージは下記となります。Lag(Val1) over(order by SortKey)に対応する黄緑線と、Lag(Val2) over(order by SortKey)に対応する青線を引いてます。

SQLのイメージ

　Lag関数を2回ではなく、1回で済ませたいと思ったら、下記の行コンストラクタを使用したSQLの出番です。行コンストラクタは『C#の匿名型』と似たようなものと考えると分かりやすいかもしれません。

Lag関数の引数に行コンストラクタ

select SortKey,Val1,Val2,
case when Row(Val1,Val2)
    = Lag(Row(Val1,Val2)) over(order by SortKey)
     then 1 else 0 end as PrevIsSame
from LagTest;

　SQLのイメージは下記となります。Lag(Row(Val1,Val2)) over(order by SortKey)に対応する黄緑線を引いてます。

SQLのイメージ