超電磁砲でデータベース（集合関数編）

集合関数

代表的な集合関数 COUNT を使って集合関数に慣れることから始めよう。COUNT は行数を数える関数である。関数なので引数を持つのは自然であろう。引数は列名(ここでは NAME, LEVEL など)が基本であるが、COUNT は COUNT(*) とすることもでき、データ数をカウントする。SELECT COUNT(*) FROM STATUS ; とすると 35 が表示され、35個のデータが存在することが分かる。COUNT の引数を変更してみると、以下の結果になった。

COUNT(*)	35
COUNT( SKILL )	26
COUNT( DISTINCT SKILL )	24

COUNT( SKILL ) は SKILL が NULL の行を除き、COUNT( DISTINCT SKILL ) は更に重複する行を除いている。WHERE 句と組み合わせて課題を解いてみよう。

解答例
(1) SELECT COUNT(*) FROM STATUS WHERE SKILL IS NULL ;

(2) SELECT COUNT( DISTINCT LEVEL ) FROM STATUS WHERE SKILL IS NULL ;

COUNT 以外に次の重要な集合関数がある。

• SUM　総和を求める
• AVG　平均を求める
• MAX　最大値を求める
• MIN　最小値を求める

解答例
(1) SELECT MIN( LEVEL ) AS MIN, MAX( LEVEL ) AS MAX FROM STATUS WHERE SKILL IS NULL ;

(2) SELECT AVG( LEVEL ) AS AVG FROM STATUS WHERE SKILL IS NULL ;

GROUP BY

GROUP BY を使って、データをグループ分けして処理することが出来る。集合関数はグループ分けと合わせて使うと効果を発揮する。例として、レベル別に人数を求め、レベルの降順に表示する。

SELECT LEVEL, COUNT(*) AS COUNT FROM STATUS GROUP BY LEVEL ORDER BY LEVEL DESC ;

この例ではデータがレベル別にグループに分類される。表示できる項目としては、グループ化に使われた属性(ここでは LEVEL)と各グループに適用される集合関数となる。結果は次のようになる。

初学者は WHERE 句、GROUP BY 句、ORDER BY 句の順序に悩むことがあるようだ。WHERE 句に従って抽出したデータを GROUP BY 句でグループ化して処理し、結果を ORDER BY 句で整える訳だから、WHERE 句、GROUP BY 句、ORDER BY 句の順に追加される。次の具体例で確認しよう。

SELECT LEVEL, COUNT(*) FROM STATUS WHERE SKILL IS NOT NULL GROUP BY LEVEL ORDER BY LEVEL DESC ;

最初に SELECT * FROM STATUS WHERE SKILL IS NOT NULL ; が実行される。これは STATUS の各行が WHERE 句の条件を満たすことをチェックし、満たしたものだけが抽出される。次に、SELECT LEVEL, COUNT(*) FROM STATUS WHERE SKILL IS NOT NULL GROUP BY LEVEL ; までが実行される。ここでは、前段の処理結果に対して、グループ化処理が行われる。最後に ORDER BY 句によって出力順序が整えられる。

解答例
(1) SELECT LEVEL, COUNT(*) AS COUNT FROM STATUS WHERE SKILL IS NULL GROUP BY LEVEL ORDER BY LEVEL DESC ;
(別解) SELECT LEVEL, COUNT( SKILL ) AS COUNT FROM STATUS GROUP BY LEVEL ORDER BY LEVEL DESC ;

(2) SELECT LEVEL, COUNT( DISTINCT SKILL ) AS COUNT FROM STATUS GROUP BY LEVEL ORDER BY LEVEL DESC ;

(3) SELECT SKILL, COUNT(*) AS COUNT FROM STATUS GROUP BY SKILL ORDER BY COUNT ;
(別解１) SELECT SKILL, COUNT(*) AS COUNT FROM STATUS GROUP BY SKILL ORDER BY COUNT(*) ;
(別解２) SELECT SKILL, COUNT(*) AS COUNT FROM STATUS GROUP BY SKILL ORDER BY 2 ;

(3) の解答例では最後に 9 という数字が表示されるが、これは NULL に対する総数である。これを除きたければ WHERE SKILL IS NOT NULL を挿入すればよい。また、ORDER BY での指定方法には悩まされるかもしれないが、(別解１) のようにすればよい。順序指定される項目( COUNT(*) ) が表示項目に含まれているので、本解答では AS を使って別名を利用、(別解２) では２番目の項目であることを利用した。

HAVING 句

グループ分けした後に適用する条件は WHERE 句ではなく、HAVING 句を用いる。HAVING 句は GROUP BY 句の直後に配置され、集合関数を使って条件が記述される。

解答例
(1) SELECT LEVEL, COUNT(*) AS COUNT FROM STATUS GROUP BY LEVEL HAVING COUNT > 1 ORDER BY LEVEL DESC ;

(2) SELECT SKILL FROM STATUS GROUP BY SKILL HAVING COUNT(*) = 1 ;