全称肯定,全称否定,存在肯定,存在否定

複数行にまたがった条件のチェック

• 全ての行が条件を満たすか？
• 全ての行が条件を満たさないか？
• 少なくとも1行が条件を満たすか？
• 少なくとも1行が条件を満たさないか？

といった複数行にまたがったチェックをしたい時には、分析関数のmin関数やmax関数と、case式を組み合わせると有効です。

create table BoolSample(ID,Val) as
select 111,3 from dual union all
select 111,3 from dual union all
select 111,3 from dual union all
select 222,3 from dual union all
select 222,1 from dual union all
select 333,0 from dual union all
select 333,4 from dual;

• check1 IDごとで、全ての行が Val=3 を満たすか？
• check2 IDごとで、全ての行が Val=3 を満たさないか？
• check3 IDごとで、少なくとも1つの行が Val=3 を満たすか？
• check4 IDごとで、少なくとも1つの行が Val=3 を満たさないか？

をチェックしてみます。

select ID,Val,
min(case when Val=3 then 1 else 0 end) over(partition by ID) as chk1,
min(case when Val=3 then 0 else 1 end) over(partition by ID) as chk2,
max(case when Val=3 then 1 else 0 end) over(partition by ID) as chk3,
max(case when Val=3 then 0 else 1 end) over(partition by ID) as chk4
  from BoolSample
order by ID,Val;

出力結果
 ID  Val  chk1  chk2  chk3  chk4
---  ---  ----  ----  ----  ----
111    3     1     0     1     0
111    3     1     0     1     0
111    3     1     0     1     0
222    1     0     0     1     1
222    3     0     0     1     1
333    0     0     1     0     1
333    4     0     1     0     1

SQLのイメージは下記です。partition by IDで、IDごとに区切る赤線を引いて、min関数とmax関数で黄緑線を引いてます。

ちなみに、IDごとで、少なくとも1つの行が Val=0を満たし、かつ、少なくとも1つの行が Val=4を満たすかをチェックするSQLは、下記となります。
掛け算で論理積を代用しています。

select ID,Val,
 max(case when Val=0 then 1 else 0 end) over(partition by ID)
*max(case when Val=4 then 1 else 0 end) over(partition by ID) as chk
  from BoolSample
order by ID,Val;

出力結果
 ID  Val  chk
---  ---  ---
111    3    0
111    3    0
111    3    0
222    1    0
222    3    0
333    0    1
333    4    1

参考リソース

SQLアタマアカデミー：第8回 SQLにおける論理演算
OracleSQLパズル 9-22 存在有無のブール値で論理演算

▲ ページTOPに戻る

ListAggとwmsys.wm_concat

文字列を連結してまとめる

MySQLのGroup_Concat関数のように文字列を連結する機能として、ListAgg関数やwmsys.wm_concat関数があります。

wmsys.wm_concat関数は、Oracle11gR2の段階でマニュアルに記載されていないので、注意して使う必要があります。
wmsys.wm_concat関数と似たような機能を持つListAgg関数は、Oracle11gR2で追加された関数です。

create table strAggSample(ID,Val) as
select 111,'a' from dual union all
select 111,'b' from dual union all
select 111,'c' from dual union all
select 222,'d' from dual union all
select 222,'e' from dual union all
select 222,'f' from dual;

select ID,Val,
wmsys.wm_concat(Val) over(partition by ID order by Val desc) as strAgg1,
wmsys.wm_concat(Val) over(order by Val) as strAgg2,
ListAgg(Val,',') withIn group(order by Val) over() as strAgg3,
ListAgg(Val,',') withIn group(order by Val) over(partition by ID) as strAgg4
  from strAggSample
order by ID,Val;

出力結果
 ID  Val  strAgg1  strAgg2      strAgg3      strAgg4
---  ---  -------  -----------  -----------  -------
111  a    c,b,a    a            a,b,c,d,e,f  a,b,c
111  b    c,b      a,b          a,b,c,d,e,f  a,b,c
111  c    c        a,b,c        a,b,c,d,e,f  a,b,c
222  d    f,e,d    a,b,c,d      a,b,c,d,e,f  d,e,f
222  e    f,e      a,b,c,d,e    a,b,c,d,e,f  d,e,f
222  f    f        a,b,c,d,e,f  a,b,c,d,e,f  d,e,f

▲ ページTOPに戻る

Range指定でInterVal型の使用

InterVal型で、ソートキーの範囲を指定

Range 2 Precedingで扱ったように、分析関数でのRange指定はソートキーの範囲を指定するのに使いますが、ソートキーがDate型やTimeStamp型であれば、InterVal型を使ってソートキーの範囲を指定できます。

create table dateRangeSample(dayCol) as
select to_date('2010-11-01 10:10','YYYY-MM-DD HH24:MI') from dual union
select to_date('2010-11-01 10:14','YYYY-MM-DD HH24:MI') from dual union
select to_date('2010-11-01 10:17','YYYY-MM-DD HH24:MI') from dual union
select to_date('2010-11-01 10:19','YYYY-MM-DD HH24:MI') from dual union
select to_date('2010-11-01 10:20','YYYY-MM-DD HH24:MI') from dual union
select to_date('2010-11-01 10:26','YYYY-MM-DD HH24:MI') from dual union
select to_date('2010-11-01 10:30','YYYY-MM-DD HH24:MI') from dual;

行ごとに、5分後以降で最小のdayColを求めます。

select dayCol,
min(dayCol)
over(order by dayCol
range between InterVal '5' minute Following
          and UnBounded Following) as after5minute
from dateRangeSample;

出力結果
dayCol            after5minute
----------------  ----------------
2010-11-01 10:10  2010-11-01 10:17
2010-11-01 10:14  2010-11-01 10:19
2010-11-01 10:17  2010-11-01 10:26
2010-11-01 10:19  2010-11-01 10:26
2010-11-01 10:20  2010-11-01 10:26
2010-11-01 10:26  null
2010-11-01 10:30  null

SQLのイメージは下記です。min関数で黄緑線を引いてます。

参考リソース

製品情報 - Oracle8i データウェアハウス
マニュアル --- 分析ファンクション

OracleSQLパズル 分析関数とmodel句
OracleSQLパズル Oracleの分析関数のサンプル集

▲ ページTOPに戻る

"図でイメージするOracle DatabaseのSQL全集" インデックスに戻る

山岸 賢治（やまぎし けんじ）
Oracle ACEの1人。
OracleSQLパズルの運営者。

• ＜ 前のページへ
• 1
• 2
• 3
• 4
• 5