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つ以上のデータセットを結合すると意図した結果にならない。

      0 件のコメント:

      コメントを投稿