第4章関数

集計関数

2009年8月17日

sql 関数集計

集計関数

列のグループを対象にした演算機能のことを集計関数、もしくは集計関数と呼びます。選択したレコードの特定の列に対して、そのフィールドの値が数値であれば、最大値、最小値、平均値などの演算を行うことができます。

集計関数には以下の5つがあります。

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)	email
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

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句で指定したフィールド数が同じこと

Programming Language

Server Side

Blog

集計関数

集計関数

SUM()

AVG()

MAX()

MIN()

COUNT()

準備作業

顧客(customer)テーブルの作成とデータの挿入

顧客（customer）テーブル

AVG(X)
平均値の問い合わせ

BIT_AND(X)
ビットごとのAND集合の問い合わせ

BIT_OR(X)
ビットごとのOR集合の問い合わせ

BIT_XOR(X)
ビットごとのXOR集合の問い合わせ

COUNT(X)
レコード件数の問い合わせ

NULLを含まないレコードを選択

COUNT(DISTINCT X,[X...])

MIN(X)
/ MAX(X)
最大値と最小値の問い合わせ

SUM(X)
値の合計値の問い合わせ

STD(X)
/ STDDEV(X)　Oracle互換
標準偏差の問い合わせ

標準偏差

グループ化

GROUP BY句
集計問い合わせ

HAVING句
集計後の結果に対して抽出条件を設定

DISTINCT
重複レコード除去

集合演算子

UNION演算子 / UNION ALL演算子
集合問い合わせ

準備作業

顧客(customer)テーブルの作成とデータの挿入

tmp01テーブル

tmp02テーブル

UNION句の使い方と結果

UNION ALL句の使い方と結果

関連記事

CONTACT WITH RHYTHMFACTORY

集計関数

準備作業

顧客(customer)テーブルの作成とデータの挿入

顧客（customer）テーブル

AVG(X) 平均値の問い合わせ

BIT_AND(X) ビットごとのAND集合の問い合わせ

BIT_OR(X) ビットごとのOR集合の問い合わせ

BIT_XOR(X) ビットごとのXOR集合の問い合わせ

COUNT(X) レコード件数の問い合わせ

NULLを含まないレコードを選択

COUNT(DISTINCT X,[X...])

MIN(X) / MAX(X) 最大値と最小値の問い合わせ

SUM(X) 値の合計値の問い合わせ

STD(X) / STDDEV(X) Oracle互換 標準偏差の問い合わせ

標準偏差

GROUP BY句 集計問い合わせ

HAVING句 集計後の結果に対して抽出条件を設定

DISTINCT 重複レコード除去

UNION演算子 / UNION ALL演算子 集合問い合わせ

準備作業

顧客(customer)テーブルの作成とデータの挿入

tmp01テーブル

tmp02テーブル

UNION句の使い方と結果

UNION ALL句の使い方と結果

関連記事

CONTACT WITH RHYTHMFACTORY

AVG(X)
平均値の問い合わせ

BIT_AND(X)
ビットごとのAND集合の問い合わせ

BIT_OR(X)
ビットごとのOR集合の問い合わせ

BIT_XOR(X)
ビットごとのXOR集合の問い合わせ

COUNT(X)
レコード件数の問い合わせ

MIN(X)
/ MAX(X)
最大値と最小値の問い合わせ

SUM(X)
値の合計値の問い合わせ

STD(X)
/ STDDEV(X)　Oracle互換
標準偏差の問い合わせ

GROUP BY句
集計問い合わせ

HAVING句
集計後の結果に対して抽出条件を設定

DISTINCT
重複レコード除去

UNION演算子 / UNION ALL演算子
集合問い合わせ