集計関数
列のグループを対象にした演算機能のことを集計関数、もしくは集計関数と呼びます。選択したレコードの特定の列に対して、そのフィールドの値が数値であれば、最大値、最小値、平均値などの演算を行うことができます。
集計関数には以下の5つがあります。
SUM()
グループの合計値を求める関数<
AVG()
グループの平均値を求める関数
MAX()
グループの中で最大の値を返す関数
MIN()
グループの中で最小の値を返す関数
COUNT()
グループの行数を求める関数
GROUP BYを含まない文でグループ関数を使用すると、全てのレコードについてグループされたものとみなされます。
準備作業
ここでは、顧客テーブル(テーブル名=customer)を作成し、そのデータを使って説明していきます。まずは準備としてCREATE TABLEとINSERTでテーブルの作成とデータの挿入をしておきましょう。
顧客(customer)テーブルの作成とデータの挿入
# 顧客テーブルの作成 CREATE TABLE customer ( id_c int unsigned NOT NULL auto_increment, fullname varchar(40) NOT NULL, age tinyint unsigned NOT NULL , sex tinyint unsigned NOT NULL, email varchar(50), PRIMARY KEY (id_c) );
もしすでにテーブルがある場合は、以下のコマンドでテーブルを削除してから、テーブルを作成しなおします。
DROP TABLE customer;
最後に、作成したテーブルにデータを挿入します。
# データの挿入 INSERT customer SET fullname='エラ・フィッツジェラルド',age='48',sex='2',email='ella@exsample.jp'; INSERT customer SET fullname='トミー・ゲレロ',age='34',sex='1',email='tommy@exsample.jp'; INSERT customer SET fullname='マディ・ウォーターズ',age='55',sex='1',email='muddy@exsample.jp'; INSERT customer SET fullname='エディ・ロバーツ',age='28',sex='1'; INSERT customer SET fullname='マーサ・リーブス',age='38',sex='2',email='martha@exsample.jp'; INSERT customer SET fullname='スライ・ストーン',age='34',sex='1';
上記命令文で作成されるテーブルは次のようになります。
顧客(customer)テーブル
顧客ID (id_c) |
フルネーム (fullname) |
性別 (sex) |
年齢 (age) |
|
---|---|---|---|---|
1 | エラ・フィッツジェラルド | 2 | 48 | ella@exsample.jp |
2 | トミー・ゲレロ | 1 | 34 | tommy@exsample.jp |
3 | マディ・ウォーターズ | 1 | 55 | muddy@exsample.jp |
4 | エディ・ロバーツ | 1 | 28 | |
5 | マーサ・リーブス | 2 | 38 | martha@exsample.jp |
6 | スライ・ストーン | 1 | 34 |
AVG(X)
平均値の問い合わせ
AVG()は、引数Xで指定した列の平均値を返します。
SELECT AVG( フィールド名 ) FROM テーブル名
次のクエリは、顧客(customer)テーブルの全レコードを[性別(sex)]でグループ化し、[年齢(age)]フィールドの平均値を求めています。
SELECT sex, AVG(age) FROM customer GROUP BY sex; -> +-----+----------+ -> | sex | AVG(age) | -> +-----+----------+ -> | 1 | 37.7500 | -> | 2 | 43.0000 | -> +-----+----------+
BIT_AND(X)
ビットごとのAND集合の問い合わせ
BIT_AND()は、引数Xで指定したフィールドの値に含まれる全てのビットごとのAND集合を返します。64 ビットの精度で計算されます。一致する行がない場合は、18446744073709551615( すべてのビットが 1 に設定された、符号なしのBIGINT値です )を戻します。
SELECT BIT_AND(フラグ) FROM テーブル名;
BIT_OR(X)
ビットごとのOR集合の問い合わせ
BIT_OR()は、引数Xで指定したフィールドの値に含まれる全てのビットごとのOR集合を返します。64 ビットの精度で計算されます。一致する行がない場合は、0 を戻します。
SELECT BIT_OR(フラグ) FROM テーブル名;
BIT_XOR(X)
ビットごとのXOR集合の問い合わせ
BIT_XOR()は、引数Xで指定したフィールドの値に含まれる全てのビットごとのXOR集合を返します。64 ビットの精度で計算されます。一致する行がない場合は、0 を戻します。
SELECT BIT_XOR(フラグ) FROM テーブル名;
COUNT(X)
レコード件数の問い合わせ
COUNT()は、SELECT文により選択されたレコードの件数を返します。引数にアスタリスク( * )を指定した場合、すべてのレコードを集計する、ということを意味します。
SELECT COUNT( * ) FROM テーブル名
次の例では、顧客(customer)テーブルのレコード数を調べています。
SELECT COUNT(*) from customer; -> 6
NULLを含まないレコードを選択
引数にアスタリスク( * )を指定した際は全てのレコード件数をカウントしますが、引数に列名を指定した際は、その列の値がNULL以外という条件が追加されます。
SELECT COUNT( フィールド名 ) FROM テーブル名
次の例では、customerテーブルを対象に、COUNT(*)で全レコード数を、COUNT(email)でemailフィールドの値がNULL以外のレコード数を問い合わせています。
SELECT COUNT(*), COUNT(email) FROM customer; -> +----------+--------------+ -> | COUNT(*) | COUNT(email) | -> +----------+--------------+ -> | 6 | 4 | -> +----------+--------------+
COUNT(DISTINCT X,[X...])
COUNT()の引数でDISTINCTキーワードを使うと、NULL値以外を持つ値で、さらに重複した値を1つにまとめてから行数を返します。
次のように、DISTINCT無しで[性別(sex)]フィールドの値をカウントすると、NULLが無いので返却値は全レコード数と同じ6になります。
SELECT COUNT(*), COUNT(sex) FROM customer; -> +----------+--------------+ -> | COUNT(*) | COUNT(sex) | -> +----------+--------------+ -> | 6 | 6 | -> +----------+--------------+
DISTINCTキーワードを指定すると、重複した値がまとめてカウントされます。[性別(sex)]フィールドの値は1か2の2種類なので、返却値は2です。
SELECT COUNT(*), COUNT(DISTINCT sex) FROM customer; -> +----------+--------------+ -> | COUNT(*) | COUNT(sex) | -> +----------+--------------+ -> | 6 | 2 | -> +----------+--------------+
MIN(X)
/ MAX(X)
最大値と最小値の問い合わせ
MIN()は引数Xで指定した列の最小値、MAX()は引数Xで指定した列の最大値を返します。
SELECT MAX ( フィールド名 ) FROM テーブル名
次の例では、顧客(customer)テーブルの[年齢(age)]フィールドの最大値、最小値を問い合わせしています。
SELECT MIN(age), MAX(age) FROM customer; -> +----------+----------+ -> | MIN(age) | MAX(age) | -> +----------+----------+ -> | 28 | 55 | -> +----------+----------+
MIX()、MAX()以外で問い合わせする列名がある際は、GROUP BY句を使う必要があります。
SELECT sex, MIN(age), MAX(age) FROM customer GROUP BY sex; -> +-----+----------+----------+ -> | sex | MIN(age) | MAX(age) | -> +-----+----------+----------+ -> | 1 | 28 | 55 | -> | 2 | 38 | 48 | -> +-----+----------+----------+
下記のクエリは、HAVING句を使って、30歳以上だけを対象にして問い合わせしてます。
SELECT sex, MIN(age) FROM customer GROUP BY sex having MIN(age) >30; -> +-----+----------+ -> | sex | MIN(age) | -> +-----+----------+ -> | 2 | 38 | -> +-----+----------+
SUM(X)
値の合計値の問い合わせ
SUM()は、引数Xで指定した列の値の合計値を返します。
SELECT SUM( *フィールド名 ) FROM テーブル名
次の例では、顧客テーブルの[年齢(age)]フィールドの合計値を問い合わせしています。
SELECT SUM(age) FROM customer; -> 237
STD(X)
/ STDDEV(X) Oracle互換
標準偏差の問い合わせ
STD()は、引数Xで得られる値の標準偏差を返します。これはANSI SQLの拡張になっています。
SELECT STD( フィールド名 ) FROM テーブル名
次の例では、顧客テーブルの[年齢(age)]フィールドの標準偏差を問い合わせしています。
SELECT STD(age) FROM customer; -> 9.1969
標準偏差
標準偏差は、統計値や確率変数の散らばり具合を表す数値のひとつで σ や s で表します。
グループ化
GROUP BY句は、特定のフィールドを対象に、同じ値を持つデータごとにグループ化します。このグループに対して集計関数を適用したり、グループ化された情報に対して条件を設定し、その条件にマッチしたグループだけを抽出することもできます。
GROUP BY句
集計問い合わせ
GROUP BY句を使ってレコードをグループ化し、集計関数を適用してみましょう。顧客(customer)テーブルの性別でグループ化し、性別毎の最大年齢を計算してみます。最大値を求めるための集計関数はMAXです。
SELECT sex, MAX(age) FROM customer GROUP BY sex; -> +-----+----------+ -> | sex | MAX(age) | -> +-----+----------+ -> | 1 | 55 | -> | 2 | 48 | -> +-----+----------+
HAVING句
集計後の結果に対して抽出条件を設定
HAVING句は、集計後の結果に対して抽出条件が指定できます。
SELECT sex, AVG(age) AS avg FROM customer GROUP BY sex HAVING avg>40; -> +-----+---------+ -> | sex | avg | -> +-----+---------+ -> | 2 | 43.0000 | -> +-----+---------+
WHERE句とHAVING句は抽出条件を設定するという機能では同じですが、明確な違いがあります。WHERE句はSELECT文でレコードを選択するときの条件を設定しますが、HAVING句はWHERE句が設定した条件で選択されたレコードから、さらにグループを選択するための条件を設定します。そのため、WHERE句には集計関数を設定することができませんが、HAVING句では集計関数を設定することができます。
DISTINCT
重複レコード除去
DISTINCTを指定すると重複レコードを取り除いて問い合わせすることができます。
SELECT distinct age, fullname FROM customer GROUP BY age; -> +-----+--------------------------------------+ -> | age | fullname | -> +-----+--------------------------------------+ -> | 28 | エディ・ロバーツ | -> | 34 | トミー・ゲレロ | -> | 38 | マーサ・リーブス | -> | 48 | エラ・フィッツジェラルド | -> | 55 | マディ・ウォーターズ | -> +-----+--------------------------------------+
集合演算子
集合演算子は、複数のクエリから返えされる結果セットの列数が同じで、型に互換性がある場合、結果セットをマージする演算子です。
UNION演算子 / UNION ALL演算子
集合問い合わせ
SELECT文において、UNION句を使用すると、前後のクエリ結果から重複行が取り除かれて結果が返されます。UNION ALL句を使用すると、重複行も含めて返えします。
準備作業
UNION句を試すために、テスト用のテーブルを2つ作成します。
顧客(customer)テーブルの作成とデータの挿入
# テスト用テーブルの作成 CREATE TABLE tmp01 ( id int, data int ); CREATE TABLE tmp02 ( id int, data int );
作成したテーブルにデータを挿入します。
# データの挿入 INSERT INTO tmp01 VALUES(1, 10 ); INSERT INTO tmp01 VALUES(2, 20 ); INSERT INTO tmp01 VALUES(3, 30 ); INSERT INTO tmp02 VALUES(3, 30 ); INSERT INTO tmp02 VALUES(4, 40 ); INSERT INTO tmp02 VALUES(5, 50 );
上記命令文で作成されるテーブルは次のようになります。
tmp01テーブル
id | data |
---|---|
1 | 10 |
2 | 20 |
3 | 30 |
tmp02テーブル
id | data |
---|---|
3 | 30 |
4 | 40 |
5 | 50 |
UNION句の使い方と結果
tmp01テーブルの全レコードを選択するSELECT文と、tmp02テーブルの全レコードを選択するSELECT文をUNION句で接続し、2つのSELECTが返す結果をマージします。UNION句は重複行を1つにまとめるので、同じ値のtmp01テーブルのid=3とtmp02テーブルのid3は1つのレコードにまとめられます。
SELECT id, data FROM tmp01 UNION SELECT id, data FROM tmp02; -> +------+------+ -> | id | data | -> +------+------+ -> | 1 | 10 | -> | 2 | 25 | -> | 3 | 30 | -> | 4 | 45 | -> | 5 | 50 | -> +------+------+
UNION ALL句の使い方と結果
tmp01テーブルの全レコードを選択するSELECT文と、tmp02テーブルの全レコードを選択するSELECT文をUNION ALL句で接続し、2つのSELECTが返す結果をマージします。UNION ALL句は単純に2つのSELECTが帰る結果をマージします。
SELECT id,data FROM tmp01 UNION ALL SELECT id,data FROM tmp02; -> +------+------+ -> | id | data | -> +------+------+ -> | 1 | 10 | -> | 2 | 25 | -> | 3 | 30 | -> | 3 | 30 | -> | 4 | 45 | -> | 5 | 50 | -> +------+------+
UNION句を利用するためには、次の条件を満たしている必要があります。
- SELECT句に指定するフィールドのデータ型が同じか、互換性があること
- SELECT句で指定したフィールド数が同じこと