超電磁砲でデータベース(集合関数編)

集合関数

代表的な集合関数 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) 能力名が登録されていない人物の人数を求めよ。
(2) 能力名が登録されていない人物がいるレベルの数を求めよ。

解答例
(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) 能力名が登録されていない人物がいるレベルの最大値、最小値を求めよ。
(2) 能力名が登録されていない人物のレベルの平均値を求めよ。

解答例
(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)と各グループに適用される集合関数となる。結果は次のようになる。

LEVELCOUNT
57
410
38
21
11
08

初学者は 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) レベル別に能力名が登録されている人数を求めよ。
(2) レベル別に登録されている能力名を重複なく求めよ。
(3) 能力名別に人数を求めよ。

解答例
(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 ;
(別解1) SELECT SKILL, COUNT(*) AS COUNT FROM STATUS GROUP BY SKILL ORDER BY COUNT(*) ;
(別解2) SELECT SKILL, COUNT(*) AS COUNT FROM STATUS GROUP BY SKILL ORDER BY 2 ;

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

HAVING 句

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

課題
(1) レベル別の人数を求め、複数人がいるレベルだけを人数とともに表示せよ。
(2) 一人だけが有する(複数が有しない)能力名のリストを表示せよ。

解答例
(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 ;