2014年5月7日水曜日

SQLプロシジャ入門5:集計後にレコードを抽出する【HAVING】



5回目はHAVINGです。
WHEREと同じでレコードを抽出する機能なんだけど、抽出するタイミングが異なります。




サンプルデータ
data EXAM;
input NAME$ SUBJECT$ SCORE FLG;
cards;
一郎 国語 20 .
次郎 国語 50 .
三郎 国語 100 1
花子 国語 20 .
一郎 算数 80 .
次郎 算数 30 .
三郎 算数 10 1
花子 算数 99 .
;

NAME
SUBJECT
SCORE
FLG
 一郎  
 国語   
 20  
  .  
 次郎  
 国語   
 50
  .  
 三郎
 国語   
 100  
  1  
 花子  
 国語   
 20  
  .  
 一郎  
 算数   
 80  
  .  
 次郎  
 算数   
 30  
  .  
 三郎
 算数   
 10  
  1  
 花子
 算数   
 99  
  .  

上の例は、各生徒の科目別テストの点数を表してます。
ただし、カンニングした生徒(FLG=1)がいる。




1. 点数が50点未満のレコードを抽出する【HAVING】
proc sql;
   select  *
   from  EXAM
   having  SCORE < 50 ;
quit;


結果ビューア
 NAME 
SUBJECT
SCORE
FLG
 一郎  
 国語   
 20  
  .  
 花子  
 国語   
 20  
  .  
 次郎  
 算数   
 30  
  .  
 三郎
 算数   
 10  
  1  


解説
・HAVINGで抽出条件を指定。
・この例ではWHEREと同じに見えてしまうけど、違いは以下の通り。

  WHERE ・・・ FROMで指定したデータに対する抽出条件
  HAVING ・・・ 集計後のレコードへの抽出条件

具体例は次の例で。



2. 平均が50点未満の科目を調べたい(ただしカンニングした点数は含めない)
【WHERE、GROUP BY、HAVING】
proc sql;
   select     SUBJECT,  avg(SCORE) as AVG_SCORE
   from        EXAM
   where      FLG = .
   group by  SUBJECT
   having     AVG_SCORE < 50 ;
quit;

結果ビューア
SUBJECT
AVG_SCORE
 国語   
 30  


解説
・SELECTにあるAVG関数は平均を求める集計関数。
・具体的な各句の役割は以下の通り。

 ①FROMとWHEREで、データEXAMからカンニングしてないレコードを抽出
 ②SELECTとGROUP BYにより、科目毎の平均点を計算
 ③HAVINGを使って、科目毎の平均点が50点未満のレコードを抽出


このようにグループ化して集計を行ったデータに対して抽出が行えます。




SQLプロシジャ入門記事一覧
1.変数を選択する【SELECT】
2.レコードを並べ替える【ORDER BY】
4.グループ毎に集計する【GROUP BY】
5.集計後にレコードを抽出する【HAVING】
6.データセットを作成する【CREATE TABLE】
7.レコードを追加する【INSERT】
8.レコードを削除する【DELETE】

0 件のコメント:

コメントを投稿