SQL:ALL述語と極値関数を用いた比較の挙動差異について

これも本を読みかえしていて、再発見した話です。

以下のように

data Q1;
do X=8,2,4,7,1;
 output;
end;
run;








data Q2;
do X=9,3,5,5,6;
 Y='A';
 output;
end;
run;









という二つのデータセットがあったとします。

そこで
Q2の全ての値より小さいQ1のオブザベーションを抽出せよ。という問題が出たとします。

その場合、

proc sql noprint;
create table A1 as
 select X
 from Q1
 where X<ALL(select X from Q2);
quit;

と書くことができます。
結果は






です。
ALLはこのように全ての値との比較を行うことができるキーワードです。

でも、恐らく多くの方がこう思われているはずです、最小値と比較すりゃええやん、と。

つまり
proc sql noprint;
create table A2 as
 select X
 from Q1
 where X<(select min(X) from Q2);
quit;

ということで、結果は先のコードと全く同じです。

さて、ではALLと極値関数比較に違いはないのでしょうか?
あるんです。

先ほどのQ1と、新しく以下のデータセットQ3に対して同じ処理を考えます

data Q3;
do X=.,3,5,5,6;
 Y='A';
 output;
end;
run;









に対して

proc sql noprint;
create table A3 as
 select X
 from Q1
 where X<ALL(select X from Q3);

create table A4 as
 select X
 from Q1
 where X<(select min(X) from Q3);
quit;

とすると

ALLを使って作成したデータセットA3は







minを使って作成したA4は





です。

つまりALLでは、nullが入ったサブクエリ結果をとると、正しくできないので注意ということです。
whereでnullを省けば、作成可能です。

続いて、以下のコードではどうでしょうか

proc sql noprint;
create table A5 as
 select X
 from Q1
 where X<ALL(select X from Q3 where Y='B');

create table A6 as
 select X
 from Q1
 where X<(select min(X) from Q3 where Y='B');
quit;

Q3に対してwhere Y='B'としていますが、Q3のYの値は全て'A'なので、このサブクエリは空集合です。
さてこのように空集合が引数となった場合のALLの結果は









つまり、条件がかからずQ1がそのまま抽出されます。

対してmin関数で作成したA6については








です。

つまりALLと極値関数比較の違いは

★サブクエリ結果にnullが含まれる場合
  ALL→×
  極値→○
★サブクエリ結果そのものが空集合である場合
  ALL→全オブザベーションがそのままでる
  極値→×

といった感じなので、そこを踏まえて使いましょうということでした。


0 件のコメント:

コメントを投稿