PostgreSQLの分析関数の衝撃6
（window関数の応用例）

複数列のdistinctなcountなど

2. range指定のcount関数

　次は、range指定のcount関数を模倣するSQLです。まずは、テーブルのデータと出力結果を考えます。

ValTable

Val
1
2
3
5
7
8
9
11
12
13
15
17
18
20
21
22
23

　Valの昇順にソートした状態で、差が1のデータが前後の両方になければ出力します。たとえば、Val=2の行は、前にVal=1の行があり、後ろにVal=3の行があるので出力対象外となります。Val=8の行も、前にVal=7の行があり、後ろにVal=9の行があるので出力対象外となります。

出力結果

Val
1
3
5
7
9
11
13
15
17
18
20
23

　Valの昇順にソートした状態で、差が1のデータが前後の両方になければ出力ということで、 前後の値をLag関数とLead関数で取得し、その値を使って判断するのであれば下記のSQLとなります。

Lag関数とLead関数を使うSQL

select Val
from (select Val,
      Lead(Val) over(order by Val) as LeadVal,
      Lag(Val)  over(order by Val) as LagVal
        from ValTable) a
where (Val = LagVal+1
   and Val = LeadVal-1) is not true
order by Val;

　Lag関数は、ソートした前の行を返して、Lead関数は、ソートした後の行を返しますが、対象行がない時はnullを返します。そして、下記の条件式がis not true（falseかunknown）かを判定してます。

is not trueかを判定する条件式

    Val = LagVal+1
and Val = LeadVal-1

　LagValとLeadValの少なくとも1つがnullの場合は、上記の条件式はunknownになるので、is not trueを満たします。LagValとLeadValが両方とも非nullの場合は、LagVal+1=Val=LeadVal-1でなければ上記の条件式はfalseとなるので、is not trueを満たします。

　以上により、差が1のデータが前後の両方になければ出力しています。SQLのイメージは下記となります。Lead関数に対応する青線と、Lag関数に対応する黄緑線を引いてます。

SQLのイメージ

　前記のようにLag関数やLead関数を使うことによって、条件式にnullが入り込むと、3値論理で条件式を考えなくてはならないため、SQLが複雑になります。

　『分析関数の衝撃6 （応用編）』では、3値論理の条件式を避けるため、count関数でrange指定を使う下記のSQLを扱いましたが、PostgreSQL 8.4では文法エラーになるので使えません。

PostgreSQL 8.4では文法エラー

select Val
from (select Val,
      count(*) over(order by Val
                    range between 1 preceding
                              and 1 following) as cnt
      from ValTable) a
 where cnt < 3
order by Val;