3－8・関数を使ってデータを取り出す

３－８・関数を使ってデータを取り出す
2004年6月3日(木) ０１T6010F
大北高広
関数とは、「引数」を呼ばれる値（数値もしく
は文字列）に計算などの処理を行い、｢戻り
値」である結果データを表示させるための処
理を実行する式のようなものです。結果を表
示させることは、結果を「返す」ともいいます。
◆関数とは
集計関数をDBで使用する場合、引数にカラム名を指定することで、その
カラムに所属するすべてのデータを対象に処理を行えます。
関数は関数名（引数）で用います。
関数の中には、（）内に引数を2つ以上指定することもあります。一方、
引数を取らない関数は、（）内にはなにも入れません。
関数の処理を行い、その戻り値を表示させる場合にもselectコマンドを
使用します。
◆集計関数
・平均値： avg()
avg()という関数は、引数の数値もしくはカラムに所属する
すべての数値の平均値を出します。そのカラムの数値を全
部足して、Null値ではないレコード数で割る、という処理を行
います。
mysql> select avg(AGE_C) from FRIENDS_T2;
+-------------------+
| avg(AGE_C) |
+-------------------+
|
34.3000 |
+-------------------+
1 row in set (0.00 sec)
カラム名の場所に関数が表示されていますが、asを使うことで、エイリア
ス名で表示させることもできます。
mysql> select avg(AGE_C) as ”平均年齢” from FRIENDS_T2;
+--------------+
| 平均年齢 |
+--------------+
| 34.3000 |
+--------------+
1 row in set (0.00 sec)
特定の条件に合うデータを対象にした関数計算は、where句を使って条
件の設定をします。where以下の条件によるデータが取り出されたあと、
そのデータに対して関数による計算がおこなわれます。
mysql> select avg(AGE_C) from FRIENDS_T2 where AGE_C >= 30;
+-------------------+
| avg(AGE_C) |
+-------------------+
|
42.0000 |
+-------------------+
1 row in set (0.00 sec)
・最大値：max()、最小値：min()
テーブルの中から年齢の最大値を取り出します。selectのあとに、年齢
を引数とし、最大数を出す関数を記述します。
mysql> select max(AGE_C) from FRIENDS_T2;
+--------------------+
| max(AGE_C) |
+--------------------+
|
56 |
+--------------------+
1 row in set (0.00 sec)
年齢の最小値を取り出します。
mysql> select min(AGE_C) from FRIENDS_T2;
+--------------------+
| min(AGE_C) |
+--------------------+
|
17 |
+--------------------+
1 row in set (0.00 sec)
・レコード数を集計：count()
レコード数を数える関数です。（＊を用いているのでNull値だけを持つレ
コードもカウントされます。）
mysql> select count(*) from FRIENDS_T2;
+--------------------+
|
count(*)
|
+--------------------+
|
10 |
+--------------------+
1 row in set (0.00 sec)
指定のカラム名(NAME_C)から、Null値ではないデータを数えます。
mysql> select count(NAME_C) from FRIENDS_T2;
+------------------------+
| count(NAME_C) |
+------------------------+
|
10 |
+------------------------+
1 row in set (0.00 sec)
・カラムの数値を集計：sum()
指定のカラムの数値を足した合計を返します。
mysql> select sum(AGE_C) from FRIENDS_T2;
+-----------------------+
|
sum(AGE_C) |
+-----------------------+
|
343 |
+-----------------------+
1 row in set (0.02 sec)
◆group by/ having句と集計関数
・平均値： avg()
group byを使ってレコードをグループ化し、その中で集計関数を行いま
す。
mysql> select BLOOD_C, avg(AGE_C) from FRIENDS_T2 group
-> by BLOOD_C;
+------------------+---------------------+
| BLOOD_C | avg(AGE_C) |
+------------------+---------------------+
| AB型
|
45.0000 |
| A型
|
34.5000 |
| B型
|
25.0000 |
| O型
|
36.6667 |
+------------------+---------------------+
4 rows in set (0.01 sec)
条件で絞り込んだデータをグループ化し、そのあと、集計関数で計算をし
ます。where句で年齢のカラムが30以上を指定し、その結果セットに対し
て血液型でグループ化して各グループでの平均年齢を出します。
mysql> select BLOOD_C, avg(AGE_C) from FRIENDS_T2 group where
-> AGE_C >= 30 group by BLOOD_C;
+------------------+---------------------+
| BLOOD_C | avg(AGE_C) |
+------------------+---------------------+
| AB型
|
45.0000 |
| A型
|
44.0000 |
| B型
|
33.0000 |
| O型
|
43.0000 |
+------------------+---------------------+
4 rows in set (0.00 sec)
グルーピングしたデータを集計関数で計算し、その結果を基に特定の条
件で絞り込みします。関数の戻り値にはwhere句は使えませんので、
having句を使用します。
mysql> select BLOOD_C, avg(AGE_C) from FRIENDS_T2 group
-> by BLOOD_C having avg(AGE_C) >= 30;
+------------------+---------------------+
| BLOOD_C | avg(AGE_C) |
+------------------+---------------------+
| AB型
|
45.0000 |
| A型
|
34.5000 |
| O型
|
36.6667 |
+------------------+---------------------+
3 rows in set (0.00 sec)
・最大値：max()
レコードをグループ化し、最大値を求める関数max()を使います。使い方
はavg()と同じです。
mysql> select BLOOD_C, max(AGE_C) from FRIENDS_T2 group
-> by BLOOD_C;
+------------------+---------------------+
| BLOOD_C | max(AGE_C) |
+------------------+---------------------+
| AB型
|
45 |
| A型
|
56 |
| B型
|
33 |
| O型
|
48 |
+------------------+---------------------+
4 rows in set (0.00 sec)
having句の中ではwhere句と同様、andやorを使うことができます。
mysql> select BLOOD_C, max(AGE_C) from FRIENDS_T2
-> group by BLOOD_C having BLOOD_C like “A%”
-> and max(AGE_C) >= 50;
+------------------+---------------------+
| BLOOD_C | max(AGE_C) |
+------------------+---------------------+
| A型
|
56 |
+------------------+---------------------+
1 row in set (0.00 sec)
andの代わりにorを使うと、「どちらかの条件を満たす」の意味になります。
mysql> select BLOOD_C, max(AGE_C) from FRIENDS_T2
-> group by BLOOD_C having BLOOD_C like “A%”
-> or max(AGE_C) >= 50;
+------------------+---------------------+
| BLOOD_C | max(AGE_C) |
+------------------+---------------------+
| AB型
|
45 |
| A型
|
56 |
+------------------+---------------------+
2 rows in set (0.00 sec)
・レコードの集計：count()
レコードをグループ化したうえで、count()を使って、グループ化されたレコード
の数を数えます。このとき、selectしたカラムがNull値のレコードはカウントされ
ません。
mysql> select BLOOD_C, count(*) from FRIENDS_T2
-> group by BLOOD_C;
+------------------+---------------------+
| BLOOD_C |
count(*)
|
+------------------+---------------------+
| AB型
|
1 |
| A型
|
4 |
| B型
|
2 |
| O型
|
3 |
+------------------+---------------------+
4 rows in set (0.00 sec)
mysql> update FRIENDS_T2 set AGE_C = null
-> where ID_C = 2;
Query OK, 1 row affected(0.00 sec)
rows matched: 1 Changed: 1 Warnings: 0
mysql> select BLOOD_C, count(AGE_C) from FRIENDS_T2
-> group by BLOOD_C;
+------------------+---------------------+
| BLOOD_C | count(AGE_C) |
+------------------+---------------------+
| AB型
|
0 |
| A型
|
4 |
| B型
|
2 |
| O型
|
3 |
+------------------+---------------------+
4 rows in set (0.00 sec)
having句を使った条件で絞り込むこともできます。エイリアスを作成して
having句の中でそのエイリアス名を使用します。
mysql> select BLOOD_C, count(AGE_C) as “人数” from
-> FRIENDS_T2 group by BLOOD_C having 人数 >= 3;
+------------------+---------------------+
| BLOOD_C | avg(AGE_C) |
+------------------+---------------------+
| AB型
|
4 |
| O型
|
3 |
+------------------+---------------------+
2 rows in set (0.00 sec)
・カラムの数値を集計：sum()
グループ化したデータに対して、そのグループごとに所属する値の合計値を
出します。
mysql> select BLOOD_C, sum(AGE_C) from FRIENDS_T2 group
-> by BLOOD_C;
+------------------+---------------------+
| BLOOD_C | sum(AGE_C) |
+------------------+---------------------+
| AB型
|
45 |
| A型
|
138 |
| B型
|
50 |
| O型
|
110 |
+------------------+---------------------+
4 rows in set (0.00 sec)
関数を２つ使った例として、sum()とcount()を使って血液型ごとの平均年
齢を出します。年齢の合計を数で割ると、平均年齢がでます。
mysql> select BLOOD_C, sum(AGE_C) / count(AGE_C) as
-> “平均年齢” from FRIENDS_T2 group by BLOOD_C;
+------------------+--------------------+
| BLOOD_C | 平均年齢
|
+------------------+--------------------+
| AB型
|
45.00 |
| A型
|
34.50 |
| B型
|
25.00 |
| O型
|
36.67 |
+------------------+--------------------+
4 rows in set (0.00 sec)
◆文字列関数
・大文字、小文字の操作：upper(),lower()
引数には数値だけでなく、文字列を指定して処理を行うこともあります。
文字列が引数となる場合には必ず“ ”で括ります。upper()とlower()は、
引数の中のアルファベットの小文字を大文字に、大文字を小文字にする
関数です。
mysql> select NAME_C, lower(BLOOD_C) from FRIENDS_T2;
+-----------------+-------------------------+
| NAME_C | lower(BLOOD_C) |
+-----------------+-------------------------+
| 村何友好
| o型
|
| 友竹大雪
| a型
|
| 友崎亜吐夢 | a型
|
| 来伴行友
| b型
|
| 沢山友人
| o型
|
・・・・・・・・・・
+------------------+-------------------------+
10 rows in set (0.00 sec)
selectコマンドにBLOOD_Cを追加すると、大文字の血液型も表示され
ます。
mysql> select NAME_C, lower(BLOOD_C), BLOOD_C from
-> FRIENDS_T2;
+-----------------+-------------------------+-----------------+
| NAME_C | lower(BLOOD_C) | BLOOD_C |
+-----------------+-------------------------+-----------------+
| 村何友好
| o型
| O型
|
| 友竹大雪
| a型
| A型
|
| 友崎亜吐夢 | a型
| A型
|
・・・・・・・・・・
関数を使って表示させるということは、テーブル中の値が小文字の値に
置き換えられるわけではなく、データを小文字に変換して表示しているだ
けである。
◆算術演算子を使った計算
・加減乗除
算術演算子を使って、取り出した数値データになんらかの計算を行って
結果を表示させることが可能です。
+
*
/
%
足し算(加算）
引き算（減算）
掛け算（乗算）
割り算（除算）
割り算の余り（剰余）
剰余演算に関しては、小数を使用する場合、整数に変換（四捨五入）して
から行います。
テーブル中の１つのカラムのデータで計算を行い、その計算結果を別の
カラムを設けて表示させます。
mysql> select ID_C, NAME_C, AGE_C, AGE_C+10 as “10年後”
-> from FRIENDS_T2;
+---------+------------------+------------+------------+
| ID_C | NAME_C
| AGE_C | 10年後 |
+---------+------------------+------------+------------+
|
10 | 村何友好
|
48 |
58 |
|
9 | 友竹大雪
|
27 |
37 |
|
8 | 友崎亜吐夢 |
56 |
66 |
|
7 | 来伴行友
|
33 |
43 |
|
6 | 沢山友人
|
24 |
34 |
|
5 | 是田良友
|
32 |
42 |
|
4 | 出雲友子
|
38 |
48 |
|
3 | 友田智香
|
17 |
27 |
|
2 | 小友達夫
|
45 |
55 |
|
1 | 大友達子
|
23 |
33 |
+---------+------------------+-------------+------------+
10 rows in set (0.00 sec)
西暦2000年から年齢を引いて、何年生まれか計算します。
mysql> select ID_C, NAME_C, AGE_C, 2000-AGE_C as “誕生年”
-> from FRIENDS_T2;
+---------+------------------+------------+------------+
| ID_C | NAME_C
| AGE_C | 誕生年 |
+---------+------------------+------------+------------+
|
10 | 村何友好
|
48 | 1952 |
|
9 | 友竹大雪
|
27 | 1973 |
|
8 | 友崎亜吐夢 |
56 | 1944 |
|
7 | 来伴行友
|
33 |
1967 |
|
6 | 沢山友人
|
24 |
1976 |
|
5 | 是田良友
|
32 |
1968 |
|
4 | 出雲友子
|
38 |
1962 |
|
3 | 友田智香
|
17 |
1983 |
|
2 | 小友達夫
|
45 |
1955 |
|
1 | 大友達子
|
23 |
1977 |
+---------+------------------+-------------+------------+
10 rows in set (0.00 sec)
割り算の例として、34.3という平均年齢で年齢を割り、対平均年齢の比
率を出してみます。
mysql> select ID_C, NAME_C, AGE_C, AGE_C / 34.3 as
-> “対平均年齢” from FRIENDS_T2;
+---------+------------------+------------+------------------+
| ID_C | NAME_C
| AGE_C | 対平均年齢 |
+---------+------------------+------------+------------------+
|
10 | 村何友好
|
48 |
1.399 |
|
9 | 友竹大雪
|
27 |
0.787 |
|
8 | 友崎亜吐夢 |
56 |
1.633 |
|
7 | 来伴行友
|
33 |
0.962 |
|
6 | 沢山友人
|
24 |
0.700 |
|
5 | 是田良友
|
32 |
0.933 |
・・・・・・・
+---------+------------------+-------------+------------+
10 rows in set (0.00 sec)
34.3という数字ではなく、avg(AGE_C)を使って計算すると、group byを使
用していないので、エラーになります。
算術演算子は、where句の中やhaving句の中でも使うことができます。
「計算結果がある条件を満たすかどうか」という指定が可能です。
10年後の年齢が40歳以下の人を取り出します。where句の中にAGE_C <= 40を記述しま
す。この場合、selectあとにAGE_C+10を記述しなければ、結果セットに10を足した数値のカ
ラムを表示することはできません。
mysql> select ID_C, NAME_C, AGE_C, from FRIENDS_T2 where
-> AGE_C+10 <= 40;
+---------+------------------+------------+
| ID_C | NAME_C
| AGE_C |
+---------+------------------+------------+
|
9 | 友竹大雪
|
27 |
|
6 | 沢山友人
|
24 |
|
3 | 友田知香
|
17 |
|
1 | 大友達子
|
23 |
+---------+-------------------|-------------+
4 rows in set(0.00)
条件句の中で計算結果を利用する方法ですが、今度は2つの条件を設
定します。これらはandを使って結びつけます。
現在30歳以上で、5年後に40歳以下の人を取り出します。where句で30歳以下を指定し、
その結果に対して5を足したものが40以下であるレコードを結果セットとします。
mysql> select NAME_C, AGE_C, from FRIENDS_T2
-> where AGE_C >= 30 and AGE_C+5 <= 40;
+------------------+------------+
| NAME_C
| AGE_C |
+------------------+------------+
| 来伴行友
|
33 |
| 是田良友
|
32 |
+-------------------|-------------+
2 rows in set(0.00 sec)
◆Null値の計算
算術演算子や関数を使って、Null値を含むデータを計算した場合は、
Null値が返ってきます。
mysql> select null+31;
+------------+
| null+31 |
+------------+
| NULL |
+------------+
1 row in set(0.00 sec)
mysql> select sin(null);
+------------+
| sin(null) |
+------------+
| NULL |
+------------+
1 row in set(0.00 sec)
◆計算結果をカラムに入力する方法
計算結果をカラムに追加します。
mysql> alter table FRIENDS_T2 add AGE10_C int;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> update FRIENDS_T2 set AGE10_C = AGE_C+10;
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10 Changed: 10 Warnings: 0
mysql> select * from FRIENDS_T2;
+---------+----------------+-----------------------------------+------------+-----------------+---------------+
| ID_C | NAME_C | ADD_C
| AGE_C | BLOOD_C | AGE10_C |
+---------+----------------+-----------------------------------+------------+-----------------+----------------+
|
10 | 村何友好 | 愛媛県愛媛市何処町4-2-5 |
48 | O型
|
58 |
|
9 | 友竹大雪 | 大阪府大阪市何処町4-2-5 |
27 | A型
|
37 |
・・・・・・
10 rows in set(0.00 sec)

Download Report