System iを愛するすべての人のためのコミュニティサイト

前回と前々回では、
FROM 句を書き換えるだけですっきり簡単にデータの検索範囲をひろげることができる、
ということについてお話しました。

考えてみれば、FROM 句でのテーブルの結合については QUERY/400 に慣れている人であれば理解するのは簡単ですよね?!

STRQRY と実行すると、
まず「ファイル（テーブル）の選択」があって、
「結合タイプの指定」があって、
と"データの集合"の定義からしか始められないようになっています。

RPG の F 仕様書も同じですが、AS/400, System i 経験者、というのは言ってみれば"データベース"の熟練者なわけですから、
そういう「昔ながらのやり方」に翻案してみると「ああ、なるほど！」ということはたくさんあるのではないでしょうか!? （ぜひ、ちょっと考えてみてください） 

SQL の場合、このテーブル（ファイル）の結合については、とりあえず以下の二点の書き方をおぼえておけば OK です。

- テーブルの結合は"テーブル名 JOIN テーブル名　ON 結合カラム名 = 結合カラム名"と書く
- ↑ の"テーブル名"のところには ↑ のように記述した結合テーブルを代入することもできる

今回は、SQL ではデータの集計についてどうやるの?、のお話です。

データの集計、つまりデータをどうやってまとめて扱うか、といったようにことについてはRPG 経験者であればサイクルなどでおなじみでしょうし、QUERY 経験者であれば「計算機能の選択」でおなじみですね!!

データを選択する、ということについては、SQLではWHERE句で指定するわけですが、
その指定のしかたについては QUERYでの「レコードの選択」などとほとんど同じです。
AS/400, System i 経験者の方々にとってはあまり目新しいものではなさそうなので、
今回の「素直なSQL」シリーズからははずすことにしました。

そこで今回は集計のお話、というわけです。

SQL でデータをまとめて扱うには GROUP BY句を使用します。
今回は例を通していろんなケースを見ていきたい、と思います。 

今回もサンプルのデータベースは CREATE_SQL_SAMPLE ストアド・プロシージャで作成したものを使用しています。
↓ の例のSQL はV5R4/V6R1 両方ともの DB2 for IBM i で実行できることを確認したものです。
ぜひ実際に実行して結果を確かめながら見てください。

まず最初の例ですが …

たとえば、従業員テーブルで部署ごとの給料の合計を出したい、としましょう。
これは↓のSQLひとつでデータを取ってくることができます。

SELECT workdept, SUM(salary) 
  FROM Sample/Employee 
 GROUP BY workdept  

平均を出したい時は SUM を AVGに変更するだけで OKです。

SELECT workdept, AVG(salary) 
  FROM Sample/Employee 
 GROUP BY workdept  

部署名も出したいとしましょう。前回、前々回の知識が活用できますね。
FROM句で部署テーブルを結合してやればいいわけです。

SELECT E.workdept, D.deptname , SUM(E.salary) 
  FROM (Sample/Employee E JOIN  Sample/Department D ON E.workdept = D.deptno) 
 GROUP BY E.workdept, D.deptname                          

GROUP BY には通常、SUM とかAVG といった集計関数といわれるものが一緒に指定されます。

よく使われるものを挙げてみると、

SELECT E.workdept, D.deptname , MAX(E.salary) 
  FROM (Sample/Employee E JOIN  Sample/Department D ON E.workdept = D.deptno) 
 GROUP BY E.workdept, D.deptname       

SELECT E.workdept, D.deptname , MIN(E.salary) 
  FROM (Sample/Employee E JOIN  Sample/Department D ON E.workdept = D.deptno) 
 GROUP BY E.workdept, D.deptname          

SELECT E.workdept, D.deptname , COUNT(E.salary) 
  FROM (Sample/Employee E JOIN  Sample/Department D ON E.workdept = D.deptno) 
 GROUP BY E.workdept, D.deptname                             

などといったところがメジャーどころですね。

もちろんソートも可能です。

SELECT E.workdept, D.deptname , SUM(E.salary) 
  FROM (Sample/Employee E JOIN  Sample/Department D ON E.workdept = D.deptno) 
 GROUP BY E.workdept, D.deptname 
 ORDER BY SUM(E.salary) DESC  

単純な集計だけでなく、その結果を使って判断したい、という場合がありますが、
そんなときに HAVING 句を使用します。
総計や平均がいくら以上のものだけを出したい、というようなこと、ありますよね?!

SELECT E.workdept, D.deptname , SUM(E.salary) 
  FROM (Sample/Employee E JOIN  Sample/Department D ON E.workdept = D.deptno)
 GROUP BY E.workdept, D.deptname                                   
HAVING SUM(E.salary) > 100000 

女性のみの場合は、というように、
テーブル全部ではなくある条件の対象を調べることもできます。
その場合は WHERE 句を使用します。

SELECT E.workdept, D.deptname , SUM(E.salary) 
  FROM (Sample/Employee E JOIN  Sample/Department D ON E.workdept = D.deptno) 
 WHERE E.sex = 'F' 
 GROUP BY E.workdept, D.deptname                                   
HAVING SUM(E.salary) > 100000             

WHERE 句と HAVING 句の指定のしかた、ちょっと混乱してしまいませんか？
よくある間違いなのですが、↑ のような選択条件を HAVING で一緒に指定してしまうとエラーになります。

（実行不可） 
SELECT E.workdept, D.deptname , SUM(E.salary) 
  FROM (Sample/Employee E JOIN  Sample/Department D ON E.workdept = D.deptno)
 GROUP BY E.workdept, D.deptname                                   
HAVING SUM(E.salary) > 100000 AND E.sex ='F'

HAVING はグループ化された後に存在する列しか指定できないんですね。
↓ のように性別までグループ化の対象に含めてやれば ↑ の HAVING 句は有効になります。

SELECT E.workdept, D.deptname , E.sex, SUM(E.salary)               
  FROM (Sample.Employee E JOIN  Sample.Department D ON E.workdept = D.deptno)
 GROUP BY E.workdept, D.deptname, E.sex                             
HAVING SUM(E.salary) > 100000 AND E.sex ='F'                    

原則、グループ化の対象になっているカラム、つまり GROUP BY 句と SELECT 句に現れているカラムであれば HAVING に指定できる、と考えておけばいいでしょう。

SELECT E.workdept, D.deptname , SUM(E.salary)                     
  FROM (Sample/Employee E JOIN  Sample/Department D ON E.workdept = D.deptno)
 WHERE E.sex = 'F'                                                
 GROUP BY E.workdept, D.deptname                                   
HAVING SUM(E.salary) > 100000                                   
   AND E.workdept IN ('D11','E11') 

グループ化された行に存在し得るカラム、が HAVING 句に指定できるカラムの定義です。
ですから、SELECT 句にも GROUP BY 句にも指定されていなかったとしても、
集計関数であれば実は使用することができます。

SELECT workdept 
  FROM Sample/Employee 
 GROUP BY workdept 
HAVING COUNT(*) < 2            

SELECT workdept 
  FROM Sample/Employee 
 GROUP BY workdept 
HAVING COUNT(*) < 2 AND SUM(salary) > 100000

あらためて、ですが、上記のようなすべてのケースについて、
WHERE 句での指定を組み合わせることができますし、
FROM 句での拡張も組み合わせることができます。

SELECT workdept 
  FROM Sample/Employee 
 WHERE sex = 'F' 
 GROUP BY workdept 
HAVING COUNT(*) < 2                    

SELECT E.workdept, D.deptname 
  FROM (Sample/Employee E JOIN  Sample/Department D ON E.workdept = D.deptno)
 WHERE sex = 'F' 
 GROUP BY E.workdept ,D.deptname 
HAVING COUNT(*) < 2      

SELECT E.workdept, D.deptname                                     
  FROM (Sample/Employee E JOIN  Sample/Department D ON E.workdept = D.deptno) 
 WHERE sex = 'F'                                                   
 GROUP BY E.workdept ,D.deptname                                   
HAVING COUNT(*) < 2 AND SUM(E.salary) > 100000

さらに、条件に応じて分けて集約することもできます。
以下のように、フィールドの内容を見て集計を行う、なんてことができますね。

SELECT workdept,                                           
       SUM(CASE WHEN sex = 'M' THEN 1 ELSE 0 END) AS MALES,   
       SUM(CASE WHEN sex = 'F' THEN 1 ELSE 0 END) AS FEMALES   
  FROM Sample/Employee 
 GROUP BY workdept   
 ORDER BY MALES DESC  

SELECT workdept,                                             
       COUNT(CASE WHEN sex = 'M' THEN 1 ELSE NULL END) AS MALES, 
       COUNT(CASE WHEN sex = 'F' THEN 1 ELSE NULL END) AS FEMALES 
  FROM Sample/Employee                                         
 GROUP BY workdept                                           
 ORDER BY MALES DESC                                        

今回は、例を中心にデータの集約の使いかたを挙げてみました。

RPG  や Query の経験者の方であれば、意外とかんたんにできるということ、 わかっていただけたんじゃないでしょうか?!
また、コンパイルがいらないのですぐに結果が確認できる、ということもすごい大きなメリットですよね。