MySQL講座#05 テーブルを集約して検索する

集約関数

SQLでは「テーブル全体の行数を合計する」という計算を行うときは、COUNT関数を使用する。SQLには集計用の関数が多く用意されていますが、まずは次の5つを覚えておけば良い。

COUNT：テーブルのレコード数（行数）を数える
SUM：テーブルの数値列のデータを合計する
AVG：テーブルの数値列のデータを平均する
MAX：テーブルの任意の列のデータの最大値を求める
MIN：テーブルの任意の列のデータの最小値を求める

テーブルの行数を数える

まずはCOUNT関数。COUNT関数は、テーブルの列を入力すると、行数を出力する。

[sql] SELECT COUNT(*) FROM chojin; [/sql]

結果

[sql] +----------+ | COUNT(*) | +----------+ | 44 | +----------+ [/sql]

COUNT()の中のアスタリスクは、「すべての列」を意味する。この()の中がCOUNT関数の入力にあたる。

なお、入力のことを「引数」や「パラメータ」と呼び、出力のことを「戻り値」と呼ぶ。

NULLを除外して行数を数える

attribute（属性）列にあるようなNULLの行を除外して数えたい場合は、対象とする列を限定して引数に記述する。

[sql] SELECT COUNT(attribute) FROM chojin; [/sql]

結果

[sql] +------------------+ | COUNT(attribute) | +------------------+ | 42 | +------------------+ [/sql]

attribute列では2行がNULLのため、その行はカウントされない。COUNT関数は、引数にとる列によって動作が変わるので注意が必要。

COUNT関数で列名を引数とした場合、NULLを除外して数えますが、この特性はCOUNT関数だけに当てはまる特殊なものである。他の関数はそもそもアスタリスクを引数にとることができない。

合計を求める

合計を求めるSUM関数を使用して、正義超人の超人強度の合計を求めてみる。

[sql] SELECT SUM(power) FROM chojin WHERE category = '正義超人'; [/sql]

結果

[sql] +------------+ | SUM(power) | +------------+ | 653 | +------------+ [/sql]

正義超人の全レコードの合計なので、以下のpowerが合計されていることになる。

[sql] +--------------------------+-------+ | name | power | +--------------------------+-------+ | キン肉マン | 95 | | テリーマン | 95 | | ロビンマスク | 96 | | ウォーズマン | 100 | | ラーメンマン | 97 | | ブロッケンＪｒ | 90 | | ウルフマン | 80 | | スペシャルマン | 0 | | カナディアンマン | 0 | +--------------------------+-------+ [/sql]

次に、正義超人の身長（height）の合計と体重（weight）の合計を求める。

[sql] SELECT SUM(height), SUM(weight) FROM chojin WHERE category = '正義超人'; [/sql]

結果

[sql] +-------------+-------------+ | SUM(height) | SUM(weight) | +-------------+-------------+ | 1396 | 812 | +-------------+-------------+ [/sql]

以上の内訳の計算式には注意すべきところがある。具体的に中身の計算を見てみる。

[sql] +--------------------------+--------+--------+ | name | height | weight | +--------------------------+--------+--------+ | キン肉マン | 185 | 90 | | テリーマン | 190 | 95 | | ロビンマスク | 217 | 155 | | ウォーズマン | 210 | 150 | | ラーメンマン | 209 | 130 | | ブロッケンＪｒ | 195 | 90 | | ウルフマン | 190 | 102 | | スペシャルマン | 0 | 0 | | カナディアンマン | 0 | 0 | +--------------------------+--------+--------+ [/sql]

数値が不明でNULLになっているデータが2行ある。SUM関数には、これらも含めた形の合計が出ているが、次のような違和感を感じた人もいたはず。

「四則演算の中にNULLが含まれた場合、結果は問答無用でNULLになるはず。それなら上記の合計値もNULLなのでは？」

結果から言うと、すべての集約関数は、列名を引数にとった場合、計算前にNULLを除外することになっている。したがって、NULLは何個あろうと無視される。

集約関数はNULLを除隊する。ただし、「COUNT(*)」は例外的にNULLを除外しない。

平均値を求める

平均を求めるためにはAVG関数を使用する。構文はSUM関数と全く同じ。

[sql] SELECT AVG(height) FROM chojin WHERE category = '悪魔超人'; [/sql]

結果

[sql] +-------------+ | AVG(height) | +-------------+ | 215.4615 | +-------------+ [/sql]

ではSUM関数のときと同様、NULLを含む列についても平均値を求めてみる。

列heihtがのNOT NULL制約を外す。。

[sql] ALTER TABLE chojin MODIFY COLUMN height integer; [/sql]

スペシャルマン、カナディアンマンの身長をNULLにする。

[sql] UPDATE chojin SET height = NULL WHERE id = '0043'; UPDATE chojin SET height = NULL WHERE id = '0044'; [/sql]

NULLになっているか確認。

[sql] SELECT name, height FROM chojin WHERE id = '0043' OR id = '0044'; [/sql]

結果

正義超人の身長は現状、以下のようになっている。

[sql] +--------------------------+--------+ | name | height | +--------------------------+--------+ | キン肉マン | 185 | | テリーマン | 190 | | ロビンマスク | 217 | | ウォーズマン | 210 | | ラーメンマン | 209 | | ブロッケンＪｒ | 195 | | ウルフマン | 190 | | スペシャルマン | NULL | | カナディアンマン | NULL | +--------------------------+--------+ [/sql]

平均値を求めてみる。

[sql] SELECT AVG(height) FROM chojin WHERE category = '正義超人'; [/sql]

結果

[sql] +-------------+ | AVG(height) | +-------------+ | 199.4286 | +-------------+ [/sql]

SUM関数のときと同様、NULLは予め除去した形で計算される。

NULLを0とみなして算出したい場合は後述する。

最大値・最小値を求める

複数行の中から最大値または最小値を求めるには、それぞれMAXとMINという関数を使用する。

SUM関数と同様、引数に列を入力して使用する。

categoryが完璧超人の中で、身長が最も高い値、体重が最も軽い値を検索してみる。

[sql] SELECT MAX(height), MIN(weight) FROM chojin WHERE category = '完璧超人'; [/sql]

結果

[sql] +-------------+-------------+ | MAX(height) | MIN(weight) | +-------------+-------------+ | 302 | 95 | +-------------+-------------+ [/sql]

それぞれの列の最大値と最小値が取得される。

MAX/MIN関数とSUM/AVG関数の違いは、SUM/AVG関数が数値型の列に対してのみしか用いることができなかったのに対し、MAX/MINでは原則的にどんなデータ型の列にも適用可能な点。順序がつけられるデータであれば、最大値と最小値も自然と決まるため、この2つを適用することができる。一方、日付に対する平均や合計は、そもそも意味をなさないので、SUM/AVG関数を日付に適用することはできない。これは文字型についても言えることで、MAX/MIN関数は文字型に対して適用できるが、SUM/AVG関数は適用できない。

重複値を除外して集約関数を使う（DISTINCTキーワード）

category列やattribute列のデータを見ると、同じ値が複数行に現れているのがわかる。

例えば、悪魔超人の場合、以下のようになっている。attributeの場合、テーブル行数は13行だが、7人の悪魔超人：7行、悪魔六騎士：6行という分類なので、値の種類としては2種類ということになる。このような「値の種類」の個数を求めるにはどのようにしたら良いか。

そのためには、値の重複を除いて行数を数えればよい。DISTINCTキーワードをCOUNT関数の引数に対しても使うことができる。

では、値の重複を除いて行を数えてみる。

[sql] SELECT COUNT(DISTINCT attribute) FROM chojin WHERE category = '悪魔超人'; [/sql]

結果

[sql] +---------------------------+ | COUNT(DISTINCT attribute) | +---------------------------+ | 2 | +---------------------------+ [/sql]

DISTINCTは、COUNT関数に限らず集約関数ならどれにでも適用できる。例えば、DISTINCTを使わない場合と使う場合のそれぞれについて、SUM関数の動作を見てみる。

完璧超人の身長は以下のようになっている。

DISTINCTの有無による動作の違いを見てみる。

[sql] SELECT SUM(height), SUM(DISTINCT height) FROM chojin WHERE attribute = '完璧超人始祖'; [/sql]

結果

[sql] +-------------+----------------------+ | SUM(height) | SUM(DISTINCT height) | +-------------+----------------------+ | 2378 | 2158 | +-------------+----------------------+ [/sql]

DISTINCTを適用した右側の合計値は、左側より220少なくなっている。これは、heightが220の超人「ゴールドマン」と「シルバーマン」が2つテーブルに存在するため、その重複が解消されて1レコードという扱いになっているからである。

ひとり勉強ログ

ITエンジニアの勉強したことメモ