SQLのサブクエリ（副問い合わせ）は習うより慣れろ？どこにでも書けるから取り敢えず書いてみようの話

サブクエリはSQL文の中にさらに括弧に入ったSQL文がいるよってやつです。
そのサブクエリの使い方がよくわかりませんと言われます。

話を聞いていると、特に相関サブクエリ、つまり主クエリとサブクエリに関係があって、主クエリの結果に対してサブの結果が連動して作用するようなやつが特に苦手な人多いなぁと思います。

SQL、特にそういう複雑なものについては、一通り理屈を本などで勉強したら、あとはもう、たくさん書いて、人が書いたものもたくさん読んで、理屈で理解するよりかは、イメージを体染みにつけるのが結局近道だと思います。

まず、SQLって何？って方はSAS忘備録のSQL入門を1から順番に読んでいくことをお勧めします。
http://sas-boubi.blogspot.jp/2014/04/sql1select.html


でサブクエリですが、よく、SELECT句、WHERE句、FROM句、どこに書いたらいいかわからないと質問されますが、乱暴にいっちゃうと、取り敢えず、どこに書いても何とかなるよ！です。

例えば

data Q1;
GRPNAME='A';ID='001';VAL=100;output;
GRPNAME='A';ID='002';VAL=90;output;
GRPNAME='A';ID='003';VAL=120;output;
GRPNAME='A';ID='004';VAL=80;output;
GRPNAME='A';ID='005';VAL=150;output;
GRPNAME='B';ID='006';VAL=70;output;
GRPNAME='B';ID='007';VAL=60;output;
GRPNAME='B';ID='008';VAL=180;output;
GRPNAME='B';ID='009';VAL=110;output;
GRPNAME='B';ID='010';VAL=190;output;
GRPNAME='C';ID='011';VAL=60;output;
GRPNAME='C';ID='012';VAL=120;output;
GRPNAME='C';ID='013';VAL=130;output;
GRPNAME='C';ID='014';VAL=50;output;
GRPNAME='C';ID='015';VAL=200;output;
run;

上記のようなデータがあるとします。A、B、Cの3つのグループに5人ずつ人がいて、VALという何らかのスコアを持っているとします。

上記のデータから、自分の所属するグループの平均スコアより、高いスコアを持っている人を抽出しなさいという問題を考えます。

まず基本的な考え方として、グループごとの平均をだすSQLは

proc sql;
 select GRPNAME,avg(VAL) as AVGVAL
 from Q1
 group by GRPNAME;
quit; 

な感じで、結果は

これをサブクエリとしてどう使うかですが、

まずはWHERE句に突っ込むパターン

proc sql noprint;
 create table A1 as
  select GRPNAME,ID,VAL
  from Q1 as A
  where VAL>(select avg(VAL)
             from Q1 as B
             group by GRPNAME
             having A.GRPNAME=B.GRPNAME);
quit;

で結果は

同じテーブルをあたかも違うテーブルとみなす自己参照はQ1　as A やQ1 as Bのように仮名(alias)を付与します。ちなみにasは省略可能ですが、慣れないうちは逐一つけた方がいいです。
having A.GRPNAME=B.GRPNAMEがポイントで、これによって、主クエリののGROUPNAMEの値と同じ値をもったサブクエリの集約結果が条件式にはまります。


つづいてSELECT句に突っ込むパターン

proc sql noprint;
 create table A2 as
  select GRPNAME,ID,VAL
         ,(select avg(VAL)
           from Q1 as B
           group by GRPNAME
           having A.GRPNAME=B.GRPNAME) as AVGVAL
  from Q1 as A
  where VAL>calculated AVGVAL;
quit;

で結果は

抽出されているIDを見ると先ほどの結果と同じであることがわかります。
基本的に考え方は先ほどの例と同じですが、ポイントはwhere VAL>calculated AVGVAL;の
calculatedの部分ですね。
calculatedの説明はSAS忘備録の「SQLの小技CALCULATEDキーワード」http://sas-boubi.blogspot.jp/2013/12/sqlcalculated.htmlをみていただければわかりますが、基本select句で新規に定義した変数でwher句の条件はかけないのですが、calculatedをつけるとOKなわけです。


さて最後にFROM句に突っ込む場合

proc sql noprint;
 create table A3 as
  select A.GRPNAME,ID,VAL
    from Q1 as A 
    left outer join
    (select GRPNAME,avg(val) as AVGVAL
        from Q1
        group by GRPNAME) as B
     on A.GRPNAME=B.GRPNAME
  where A.VAL>B.AVGVAL;
quit;

結果は

と、やはり同じです。

これは特に説明の必要ないですね。集計した結果と元の結果を結合して、それをFROMで指定して、抽出してるだけです。


さて、このように、どこに書いても、同じ結果を導くことができました。
あまり悩まずにどんどん書いて、どんどん失敗もして、勉強していきましょう。

ちなみにの話ですが、サブクエリは、処理速度の面からいうと最善ではない場合も多いので
少し留意しておきましょう。