17/04/12

mysqlでデータを集計をする



 

今回は、データを集計する際によく使う集計関数(SUM、MAX等)と
GROUP BY句、HAVING句についてまとめます。

環境

mysql 5.7.17

mysql> create table Test(id integer, cost int, name varchar(255), type int);
mysql> insert into Test(id, cost, name, type) values(1, 300, "hoge",1),(2, 500, "fuga",2), (3, 100, "piyo",1), (4, NULL, "bar",1);

+------+------+------+------+
| id   | cost | name | type |
+------+------+------+------+
|    1 |  300 | hoge |    1 |
|    2 |  500 | fuga |    2 |
|    3 |  100 | piyo |    1 |
|    4 | NULL | bar  |    1 |
+------+------+------+------+

集計関数

よく使う集計関数をまとめると以下になります。

 

関数名 説明
COUNT 行数を求める
AVG 平均値を求める
SUM 合計値を求める
MAX 最大値を求める
MIN 最小値を求める

 

それぞれについて、実際に動かしてみます。

COUNT関数

レコード数の取得

*(ワイルドカード)を指定した場合はすべてのレコード数を取得します。

mysql> select count(*) from Test;
+----------+
| count(*) |
+----------+
|        4 |
+----------+

NULLを含まないレコード数の取得

引数に列名を指定した場合は,その列の値がNULL以外のレコード数を取得します.

mysql> select count("cost") from Test;
+-------------+
| count(cost) |
+-------------+
|           3 |
+-------------+

重複を含まないレコード数の取得

DISTINCT句を使用すると,重複した値を1つにまとめてからレコード数を取得します。

mysql> select count(DISTINCT "type") from Test;
+----------------------+
| count(DISTINCT type) |
+----------------------+
|                    2 |
+----------------------+

AVG関数

AVG関数を用いてcostカラムのを平均を求めます。
ただ、AVG関数はNULL値を無視します。

mysql> select
    ->     AVG(cost) AS "AVG(cost)",
    ->     AVG(IFNULL("cost", 0)) AS "AVG(cost, 0)"
    ->     FROM Test;
+-----------+--------------+
| AVG(cost) | AVG(cost, 0) |
+-----------+--------------+
|  300.0000 |     225.0000 |
+-----------+--------------+

IFNULL関数を用いて、costがNULLだった時0として計算するようにスクリプトを書いています。
よってAVG関数に無視されることなく計算できていることがわかります。

SUM関数、MAX関数、MIN関数

これは簡単なので一気にsqlを書いていきます。
SUM関数は合計値を
MAX関数は最小値を
MIN関数は最大値を求めていきます

mysql> select SUM("cost"), MAX("cost"), MIN("cost") from Test;
+-----------+-----------+-----------+
| SUM(cost) | MAX(cost) | MIN(cost) |
+-----------+-----------+-----------+
|       900 |       500 |       100 |
+-----------+-----------+-----------+

GROUP BY句

今までは、単一の値(コストの平均や、レコードの数など)を求めてきました。
しかし、それに付随する情報(idやname)を取得しようとすると

mysql> select "id",SUM("cost") from Test;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.Test.id'; this is incompatible with sql_mode=only_full_group_by

といった具合にエラーになります。
これを回避するためにGROUP BY句を利用します。

 

GROUP BY フィールド名 でどのフィールドの値に基づいてグループ分けされるかを記述します。
別の言い方をすると、集計しない、つまりSUM関数やMAX関数を利用しないカラムを記します。

mysql> select "id","name",SUM("cost") from Test GROUP BY "id","name";
+------+------+-----------+
| id   | name | SUM(cost) |
+------+------+-----------+
|    1 | hoge |       300 |
|    2 | fuga |       500 |
|    3 | piyo |       100 |
|    4 | bar  |      NULL |
+------+------+-----------+

HAVING句

WHERE句はSELECT句の結果から条件を指定する際に用いますが
HAVING句はGROUP BY句でグルーピングした結果から条件を指定する際に利用するものです。

 

例えば、
タイプの平均値が300以上だったら取得
を書いていきます

mysql> SELECT "type", AVG("cost")
    -> FROM Test
    -> GROUP BY "type"
    -> HAVING AVG ("cost") >= 300;
+------+-----------+
| type | AVG(cost) |
+------+-----------+
|    2 |  500.0000 |
+------+-----------+

処理の流れとしては、
1,GROUP BY句のtypeをキーにグループ分けを行い、type毎にAVG関数によって平均コストを計算する。
2,その結果をHAVING句の条件で指定した平均コストが300以上のものを出力。
となります。

 

以上です。

 

参考記事
【初級編⑪】SQLのGROUP BYでレコードのグループ化と集計を行う | SQLServer2008虎の巻

 

スポンサーリンク

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

youya66

だらけとびびり、それとちょっぴりのてきとーさ。

コアラになってだらだらしながら愛されたい。