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で指定して、抽出してるだけです。


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

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

頭の体操 集合に対する抽出

例えば

data Q1;
TEAM='A';ID='aさん';VAL=90;output;
TEAM='A';ID='bさん';VAL=35;output;
TEAM='A';ID='cさん';VAL=42;output;
TEAM='A';ID='dさん';VAL=56;output;
TEAM='A';ID='eさん';VAL=68;output;

TEAM='B';ID='fさん';VAL=40;output;
TEAM='B';ID='gさん';VAL=58;output;
TEAM='B';ID='hさん';VAL=62;output;
TEAM='B';ID='iさん';VAL=52;output;
TEAM='B';ID='jさん';VAL=68;output;

TEAM='C';ID='kさん';VAL=90;output;
TEAM='C';ID='lさん';VAL=88;output;
TEAM='C';ID='mさん';VAL=62;output;
TEAM='C';ID='nさん';VAL=32;output;
TEAM='C';ID='oさん';VAL=38;output;

run;

のようなデータがあったとします。

A B Cの3チームで、チーム内には複数のメンバーがいて変数VALには何らかの得点が
入っているとします。


ここで、チームの70%以上のメンバーが50点以上の得点であるグループのTEAMを抽出して
データセットにしたいとします。

さて、どうしますか？

if VAL>=50 then FL=1;とかデータステップで一度付与してからfreqなんかで集計するのも手ですが、この手の問題については、やはりべらぼうにSQLが強いです。集合指向言語の肩書は伊達じゃないです。


つまり

proc sql ;
create table A1 as 
 select TEAM
 from Q1
 group by TEAM
 having count(*)*0.7<=sum(case when VAL>=50 then 1 else 0 end);
quit;

で詰んでるんですね。

【追記】
しまった！真偽ルールを使えば

proc sql ;
create table A1 as 
 select TEAM
 from Q1
 group by TEAM
 having count(*)*0.7<=sum(VAL>=50);
quit;

これでいけるんだった。くそっ

SQLプロシジャで_N_やfirst. last.の処理を疑似的に表現する_列番号を返すmonotonicを利用して

SQLで_N_やfirst. last.を使えますか？と聞かれることがあります。

SQLは本来、データ格納の位置やソート順に縛られない言語なので
そういうのはデータステップでやってくださいなというところです。

実際、標準SQLには列番号を取得するような関数は意図的に存在しないのですが
実はSASのSQLプロシジャには、monotonic関数という独自関数があります。
こいつを使うことで、_N_やfirst. last.と結果が同じになる処理を行うことはできます。

本来のSQLの理念に反しているということで、賛否両論、批判的な人も多いですが
まあ、実際あるもんだから、知ってて損はないはずです。

以下のようなデータセットがあったとします。

data Q1;
 do X=1 to 5;
  do Y=1 to 3;
   output;
  end;
 end;
run;

if _N_=3で絞ってデータセットを作る処理、つまり3番目に格納されているobsのみ残すのを
をSQLプロシジャでやるなら

proc sql noprint;
 create table A1 as
  select X,Y
  from Q1
  where monotonic()=3;
quit;

となります。
monotonic()がいわゆる行番号を返します。

つまり

proc sql noprint;
 create table A2 as
  select X,Y,monotonic() as N
  from Q1;
quit;

の結果は

となります。

で、ここからは応用でfirst.Xまたはlast.Xのみを残す処理をかいてみます。
多分、すでに予想がついているかと思いますが、案の定group byとhaving及び、minまたはmax関数を使います

first.Xは

proc sql noprint;
 create table A3(drop=N) as
  select X,Y,monotonic() as N
  from Q1
  group by X
  having min(N)=N
;
quit;

で結果は

となりlast.Xは

proc sql noprint;
 create table A4(drop=N) as
  select X,Y,monotonic() as N
  from Q1
  group by X
  having max(N)=N
;
quit;

となります。

まあ、あくまで結果を同じにしているだけで、データステップと同じように動いているわけではないので注意。

【追記】
う～ん、matsu a  さんもリンク先で指摘されているので、あんまり頼らない方がいいかもしれませんね。。
http://sas-boubi.blogspot.jp/2014/02/sqlmonotonic.html