10-261 前後を差が1のデータで挟まれていなければ出力

SQLパズル

ValTable
Val
---
  1  ← 出力対象
  2
  3  ← 出力対象
  5  ← 出力対象
  7  ← 出力対象
  8
  9  ← 出力対象
 11  ← 出力対象
 12
 13  ← 出力対象
 15  ← 出力対象
 17  ← 出力対象
 18  ← 出力対象
 20  ← 出力対象
 21
 22
 23  ← 出力対象

Valの昇順にソートした状態で、前後を差が1のデータで挟まれていなければ出力する。

出力結果
Val
---
  1
  3
  5
  7
  9
 11
 13
 15
 17
 18
 20
 23

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table ValTable(Val) as
select  1 from dual union all
select  2 from dual union all
select  3 from dual union all
select  5 from dual union all
select  7 from dual union all
select  8 from dual union all
select  9 from dual union all
select 11 from dual union all
select 12 from dual union all
select 13 from dual union all
select 15 from dual union all
select 17 from dual union all
select 18 from dual union all
select 20 from dual union all
select 21 from dual union all
select 22 from dual union all
select 23 from dual;

SQL

--■■■Lag関数とLead関数を使う方法■■■
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;

--■■■range指定のcount関数を使う方法■■■
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;

解説

range指定のcount関数を使うとSQLをシンプルにできます。

8-39 指定値の行から3行後まで出力
8-41 window指定の分析関数
10-263 最初に1が出現してから値が変化した行を取得

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