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

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

  • 全ての行が条件を満たすか?
  • 全ての行が条件を満たさないか?
  • 少なくとも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関数で黄緑線を引いてます。

Range指定でInterVal型の使用のイメージ

参考リソース

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

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

▲ ページTOPに戻る

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

Copyright © 2011, Oracle Corporation Japan. All rights reserved.
無断転載を禁ず

この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。

Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。

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



Left Curve
図でイメージする
Oracle DatabaseのSQL全集
Right Curve