count(*) over() と minus

2つのselect文の結果が一致するかの確認

テーブル定義が同じテーブル同士や、select文の結果同士のデータが一致するかの確認に、
count(*) over() と minus集合演算を組み合わせたSQLが使えます。

create table cmpA(ID number,Val number);
create table cmpB(ID number,Val number);

-- case1 (cmpAとcmpBが一致)
truncate table cmpA;
truncate table cmpB;
insert into cmpA values(10,111);
insert into cmpA values(20,222);
insert into cmpB values(10,111);
insert into cmpB values(20,222);

-- case2 (cmpAとcmpBが不一致)
truncate table cmpA;
truncate table cmpB;
insert into cmpA values(10,111);
insert into cmpB values(10,111);
insert into cmpB values(20,222);

-- cmpAとcmpBのデータが一致するか調べる
select a.*,count(*) over() from cmpA a
minus
select a.*,count(*) over() from cmpB a;

上記のselect文の結果が0件になるのは、以下の少なくとも1つが成り立つ場合です。

  • cmpAが空集合(レコードが0件)
  • cmpAとcmpBのデータが(重複行があれば重複を排除してから)比較して一致する

実際の業務において、空集合や重複行があるということは、まずないので上記のselect文の結果が0件なら、cmpAとcmpBのデータが同じと判定できます。

▲ ページTOPに戻る

max(Val) over(partition by PID)

指定した値で区切った中での最大値を取得

max(Val) over(partition by PID)は、指定した値で区切った(パーティションを切った)中での最大値を取得する時に使います。

create table MaxPIDSample(ID,Val) as
select 111,10 from dual union all
select 111,30 from dual union all
select 111,40 from dual union all
select 222,20 from dual union all
select 333,60 from dual union all
select 333,60 from dual;

IDごとのValの最大値を求めてみます。

select ID,Val,
max(Val) over(partition by ID) as maxVal
  from MaxPIDSample;
出力結果
 ID  Val  maxVal
---  ---  ------
111   10      40
111   30      40
111   40      40
222   20      20
333   60      60
333   60      60

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

max(Val) over(partition by ID)のイメージ

max関数の他に、min関数やsum関数やcount関数などでも同じような使い方ができます。

▲ ページTOPに戻る

count(distinct Val) over(partition by PID)

列値が何通りあるかを調べる

distinctオプションを指定した分析関数のcount関数は、列値が何通りあるかを調べる時などに使われます。

create table CntDisSample(ID,Val) as
select 111,1 from dual union all
select 111,1 from dual union all
select 111,2 from dual union all
select 222,4 from dual union all
select 222,5 from dual union all
select 222,6 from dual union all
select 333,7 from dual union all
select 333,7 from dual;

IDごとにValが何通りあるかを調べてみます。

select ID,Val,
count(distinct Val) over(partition by ID) as disCnt
  from CntDisSample;
出力結果
 ID  Val  disCnt
---  ---  ------
111    1       2
111    1       2
111    2       2
222    4       3
222    5       3
222    6       3
333    7       1
333    7       1

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

count(distinct Val) over(partition by PID)のイメージ

▲ ページTOPに戻る

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