CodeZine(コードジン)

特集ページ一覧

分析関数の衝撃6(応用編)

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2008/10/31 14:00

ダウンロード SourceCode (3.0 KB)

目次

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 truefalseunknown)かを判定してます。

条件式
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のイメージ
SQLのイメージ

  • LINEで送る
  • このエントリーをはてなブックマークに追加

バックナンバー

連載:分析関数の衝撃

もっと読む

著者プロフィール

あなたにオススメ

All contents copyright © 2005-2021 Shoeisha Co., Ltd. All rights reserved. ver.1.5