第4章 関数

集計関数

2009年8月17日

集計関数

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

集計関数には以下の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)
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演算子
集合問い合わせ

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

Comment

コメントを残す

メールアドレスが公開されることはありません。

リズムファクトリーはホームページの制作会社です。
ホームページ制作に関するご要望・ご相談はこちらからどうぞ。