SAS忘備録: SQL: NATURAL JOIN

ラベル SQL: NATURAL JOIN の投稿を表示しています。すべての投稿を表示

2016年3月11日金曜日

SQLプロシジャの「NATURAL JOIN」で3つ以上のデータセットを結合すると意図した結果にならない。

タイトル通りのご指摘をいただきました。

「NATURAL JOIN」については以前紹介しています。
「SQLプロシジャで結合条件を省略する方法。」

というわけで試してみます。以下２つの例は3つのデータセットを「NATURAL JOIN」で結合しているプログラムです。
。。。FEEDBACKオプションでログにプログラムを展開してみると。。

例①

proc sql feedback;

create table OUT1 as
select * from
(select name from SASHELP.CLASS a where AGE=11)
natural full outer join
(select name from SASHELP.CLASS b where AGE=12)
natural full outer join
(select name from SASHELP.CLASS c where AGE=13)
;
quit;

ログ（環境・バージョンによって挙動が異なるかもしれません）

select COALESCE(C.Name, A.Name) as Name
from ( select A.Name
from SASHELP.CLASS A
where A.Age = 11
) full outer join
( select B.Name
from SASHELP.CLASS B
where B.Age = 12
) on B.Name = A.Name full outer join
( select C.Name
from SASHELP.CLASS C
where C.Age = 13
) on C.Name = A.Name;

例②

data DT1 DT2 DT3;
set SASHELP.CLASS;
run;

proc sql feedback;
create table OUT1 as
select * from
(select name from DT1 a where AGE=11)
natural full outer join
(select name from DT2 b where AGE=12)
natural full outer join
(select name from DT3 c where AGE=13)
;
quit;

ログ（環境・バージョンによって挙動が異なるかもしれません）

select COALESCE(C.Name, A.Name, B.Name) as Name
from ( select A.Name
from WORK.DT1 A
where A.Age = 11
) full outer join
( select B.Name
from WORK.DT2 B
where B.Age = 12
) on B.Name = A.Name full outer join
( select C.Name
from WORK.DT3 C
where C.Age = 13
) on (C.Name = A.Name) and (C.Name = B.Name);

２つの例は同じようなプログラムなのに、展開されるプログラムが異なっています。
（青字と赤字の部分）

マニュアルみても言及してないし、仕様なのかバグなのか不明です。何かご存知の方いたら教えてください。

てことで、「NATURAL JOIN」で 3つ以上のデータセットを結合しない方がよさそうです。

2014年4月11日金曜日

SQLプロシジャで結合条件を省略する方法。

(注意点もあるので最後までご覧ください)

SQLには「自然結合」という結合方法がある。
これはデータ間で共通する変数名を自動で結合条件に入れてくれます。

サンプルデータ作成

data DT1;
input A B;
cards;
1 10
2 20
;

data DT2;
input A C$;
cards;
1 AA
3 BB
;

例

例えば「FULL JOIN」でDT1とDT2を結合したいとする。

*** FULL JOIN ;

proc sql;

create table DT3 as

select coalesce( DT1.A, DT2.A ) as A,

DT1.B,

DT2.C

from DT1 full join DT2 on DT1.A = DT2.A;

quit;

DT3

A B C

1 10 AA

2 20

3 . BB

さて、ここで「FULL JOIN」を自然結合に置き換えてみましょう。

「FULL JOIN」は「NATURAL FULL JOIN」で自然結合になります。

データセット間で「共通する変数名」を自動で結合条件に設定してくれるので、FROMに結合条件は記述不要
ただし、データセット間で「共通する変数名」があってもデータセット間で型が異なっていると、ERRORになり、自然結合が失敗します。
SELECTも「SELECT *」でシンプルな記述にすることが出来ます。このように書くと、各データセットの全変数をSELECTしたうえで、データセット間で変数名が共通する場合はCOALESCE関数で1変数にまとめてくれます（よく分からんって方は、詳細を下の方でも解説してます）

*** NATURAL FULL JOIN ;

proc sql;

create table DT3 as

select *

from DT1 natural full join DT2;

quit;

DT3

A C B

1 AA 10

2 20

3 BB .

「FULL JOIN」と「NATURAL FULL JOIN」の結果を比較すると、変数の順番が違ってますが、それ以外の結果は同じですね。

解説

上の「NATURAL FULL JOIN」の例は、内部で以下のようなプログラムに置き換えられます。

proc sql ;
create table DT3 as
select coalesce(DT2.A, DT1.A) as A,
DT2.C,
DT1.B
from WORK.DT1 full outer join WORK.DT2 on DT2.A = DT1.A;
quit;

まずFROMの「NATURAL FULL JOIN」が「FULL OUTER JOIN (FULL JOIN)」に置き換わっています。
またデータセット間で共通する変数名AがFROMの結合条件に自動で設定されました。
次にSELECTでは、共通変数AをCOALESCE関数で1変数にまとめてくれてます。この関数は「引数のうち最初にNULL以外で登場する値」を返してくれる。

（余談：データステップでは、引数が全て数値の場合COALESCE関数、全て文字の場合COALESCEC関数、と使い分ける必要があるけど、SQLの場合は引数が全て数値または全て文字のどちらでもCOALESCE関数が使える）

ちなみに、

今回は「FULL JOIN」を自然結合に置き換える例でしたが、以下の通り他の結合方法も自然結合に置き換えることが出来ます。

「INNER JOIN」→「NATURAL INNER JOIN」
「LEFT JOIN」→「NATURAL LEFT JOIN」
「RIGHT JOIN」→「NATURAL RIGHT JOIN」
「FULL JOIN」→「NATURAL FULL JOIN」

お役立ち情報

以前紹介した「FEEDBACK」オプションを使って、自然結合がどのような文に展開されているのか、ログに展開することが出来ます。

proc sql feedback;

create table DT3 as

select *

from DT1 natural full join DT2;

quit;

ログ
select COALESCE(DT2.A, DT1.A) as A, DT2.C, DT1.B
from WORK.DT1 full outer join WORK.DT2 on DT2.A = DT1.A;

注意

自然結合は便利だけど、、もし一方のデータセットに変数を追加した場合、
もう一方のデータセットに同じ変数名が存在してると、新たにその変数も自動で結合条件に入ってしまい、当初と異なる動きをしてしまう。

対策として、

例えば、以下のように必要な変数のみKEEP等で保持しておけば、上記のような不測の事態にも備えることが出来ます。

proc sql;

create table DT3 as
select *
from DT1 (keep=A B) natural full join DT2 (keep=A C);

quit;

こういった不確定要素もあるので、自然結合を用いる際は、最大限の注意を払う必要があります。

注意その2

SQLプロシジャの「NATURAL JOIN」で3つ以上のデータセットを結合すると意図した結果にならない。

登録: 投稿 (Atom)

2016年3月11日金曜日

SQLプロシジャの 「NATURAL JOIN」 で3つ以上のデータセットを結合すると意図した結果にならない。

2014年4月11日金曜日

SQLプロシジャで結合条件を省略する方法。

SQLプロシジャの「NATURAL JOIN」で3つ以上のデータセットを結合すると意図した結果にならない。