分析関数の衝撃6（応用編）

CodeZineに掲載されたSQLを分析関数で記述する 6

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)
 where case when Val= all(LagVal+1,LeadVal-1)
            then 1 else 0 end = 0
order by Val;

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

条件式

Val = all(LagVal+1,LeadVal-1)

　LagValとLeadValの少なくとも1つがnullの場合は、上記の条件式はunknownになるので、is not trueを満たします。

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

　以上により、前後を差が1のデータで挟まれていなければ出力しています。

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

3値論理の条件式を避けたSQL

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

　上記のSQLは、2値論理の条件式(cnt < 3)になってます。count関数は、nullを返さないので、2値論理の条件式でいいのです。また、Lag関数とLead関数を使うSQLは、インラインビューのselect文が3列(Val,LeadVal,LagVal)ですが、上記のSQLは2列(Val,cnt)というふうに、シンプルなSQLになってます。

　分析関数のcount関数のover句で、Valの昇順でソート指定して、range指定で(その行のValの値-1)以上で(その行のValの値+1)以下を条件としています。

　以下のように解釈すると分かりやすいでしょう。

count(*) over(   --以下の範囲の行数を求める。
order by Val     --Valの昇順で、
   range between --値の範囲は、
         1 preceding  --1小さい値から
     and 1 following) --1大きい値まで

　このcount関数のrange指定では、下記のことが成り立ちます。

• (Val = その行のValの値-1) の行は、存在するかしないかである。
• current rowの行は、必ず存在する。
• (Val = その行のValの値+1) の行も、存在するかしないかである。

　なので、下記のcount関数の値は、1以上3以下となります。そして、3未満であれば、前後を差が1のデータで挟まれてないと判断しています。

1以上3以下となるcount関数

count(*) over(order by Val
              range between 1 preceding
                        and 1 following)

　Lead関数やLag関数の結果を条件式で使うSQLは、rows指定やrange指定の分析関数を使って、シンプルなSQLにできることが多いのです。

　SQLのイメージは下記となります。青線が1 followingのイメージで、緑線が1 precedingのイメージです。

SQLのイメージ