集計をする上で必要となるgroup byですが、
今回はMySQLの拡張されたgroup byについて説明していきたいと思います。
検証環境
今回は第125回 phpMyAdminでDockerで建てたMySQLにアクセスするで記載したdocker-composeを利用して作成します。手元で簡単に試せるように、
group byのMySQL拡張
今回の検証はMySQLの独自拡張を利用することになるため、
まずは今回紹介したいgroup by拡張ですが、
select * from zipcode group by zip_code;
SQLモードを設定せずに上記のSQLを現行のMySQLで実行すると、
mysql> select * from zipcode group by zip_code; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zipcode.zipcode.code' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SELECTのリストの中でGROUP BYの中に含まれていないカラムと集約関数を通してないカラムが含まれているためエラーになっていて、
では、
SQLモードを指定する方法は、
手元で試してみたい場合は、
docker-composeでmy.cnfを設定する
まずはじめに、
mysql> select @@sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+
以下のように、
$ tree
.
├── Dockerfile
├── README.md
├── docker-compose.yml
├── docker-entrypoint-initdb.d
│ ├── 1_ddl.sql
│ ├── 2_load_data_infile.sql
│ └── KEN_ALL_UTF8.CSV
└── mycnf
└── my.cnf
今回のmy.
[mysqld] sql_mode=""
docker-compose.
version: '3'
services:
mysql:
build: .
volumes:
- ./docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
- ./mycnf/my.cnf:/etc/mysql/my.cnf # my.cnfをマウントする
environment:
MYSQL_ROOT_PASSWORD: password
command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --secure-file-priv="/docker-entrypoint-initdb.d"
docker-composeを再起動して、
mysql> select @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+
ということで、
group byのクエリを実行してみる
さて、
mysql> select * from zipcode group by zip_code; 《 省略 》 | 47382 | 90718 | 9071801 | ｵｷﾅﾜｹﾝ | ﾔｴﾔﾏｸﾞﾝﾖﾅｸﾞﾆﾁｮｳ | ﾖﾅｸﾞﾆ | 沖縄県 | 八重山郡 | 与那国 | +-------+-------------+----------+-----------------------+--------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------------------------+--------------------------------------------------------------------------------------------------------------------+ 120189 rows in set (0.25 sec)
ということで、
さて、*で取れた行の値はどうやって決まるのでしょうか？
この問題を考える前に、
mysql> select zip_code, count(*) from zipcode group by zip_code having count(zip_code)> 1 order by count(zip_code); +----------+----------+ | zip_code | count(*) | +----------+----------+ | 0040000 | 2 | | 0680546 | 2 | 《 省略 》
0040000の地域は、
mysql> select * from zipcode where zip_code = '0040000' ; +-------+-------------+----------+-----------------------+--------------------------------------+-----------------------------------------------+------------+--------------------+--------------------------------+ | code | old_zipcode | zip_code | prefecture_kana | city_kana | town_kana | prefecture | city | town | +-------+-------------+----------+-----------------------+--------------------------------------+-----------------------------------------------+------------+--------------------+--------------------------------+ | 01108 | 004 | 0040000 | ﾎｯｶｲﾄﾞｳ | ｻｯﾎﾟﾛｼｱﾂﾍﾞﾂｸ | ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ | 北海道 | 札幌市厚別区 | 以下に掲載がない場合 | | 01110 | 004 | 0040000 | ﾎｯｶｲﾄﾞｳ | ｻｯﾎﾟﾛｼｷﾖﾀｸ | ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ | 北海道 | 札幌市清田区 | 以下に掲載がない場合 | +-------+-------------+----------+-----------------------+--------------------------------------+-----------------------------------------------+------------+--------------------+--------------------------------+
北海道札幌市の厚別区か清田区の
mysql> select * from zipcode where zip_code = '0040000' group by zip_code; +-------+-------------+----------+-----------------------+--------------------------------------+-----------------------------------------------+------------+--------------------+--------------------------------+ | code | old_zipcode | zip_code | prefecture_kana | city_kana | town_kana | prefecture | city | town | +-------+-------------+----------+-----------------------+--------------------------------------+-----------------------------------------------+------------+--------------------+--------------------------------+ | 01108 | 004 | 0040000 | ﾎｯｶｲﾄﾞｳ | ｻｯﾎﾟﾛｼｱﾂﾍﾞﾂｸ | ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ | 北海道 | 札幌市厚別区 | 以下に掲載がない場合 | +-------+-------------+----------+-----------------------+--------------------------------------+-----------------------------------------------+------------+--------------------+--------------------------------+ 1 row in set (0.07 sec)
今回は厚別区が出ましたが、
不定であることが望ましくない場合には、
実際は、
mysql> select zip_code, count(DISTINCT prefecture) from zipcode group by zip_code having count(DISTINCT prefecture) > 1; +----------+----------------------------+ | zip_code | count(DISTINCT prefecture) | +----------+----------------------------+ | 4980000 | 2 | | 6180000 | 2 | | 8710000 | 2 | +----------+----------------------------+ 3 rows in set (0.20 sec)
それぞれ愛知と三重、
mysql> select * from zipcode where zip_code in ('4980000', '6180000', '8710000');
+-------+-------------+----------+--------------------+-----------------------------------------------------+-----------------------------------------------+------------+-----------------------+--------------------------------+
| code | old_zipcode | zip_code | prefecture_kana | city_kana | town_kana | prefecture | city | town |
+-------+-------------+----------+--------------------+-----------------------------------------------------+-----------------------------------------------+------------+-----------------------+--------------------------------+
| 23235 | 498 | 4980000 | ｱｲﾁｹﾝ | ﾔﾄﾐｼ | ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ | 愛知県 | 弥富市 | 以下に掲載がない場合 |
| 24303 | 498 | 4980000 | ﾐｴｹﾝ | ｸﾜﾅｸﾞﾝｷｿｻｷﾁｮｳ | ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ | 三重県 | 桑名郡木曽岬町 | 以下に掲載がない場合 |
| 26303 | 618 | 6180000 | ｷｮｳﾄﾌ | ｵﾄｸﾆｸﾞﾝｵｵﾔﾏｻﾞｷﾁｮｳ | ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ | 京都府 | 乙訓郡大山崎町 | 以下に掲載がない場合 |
| 27301 | 618 | 6180000 | ｵｵｻｶﾌ | ﾐｼﾏｸﾞﾝｼﾏﾓﾄﾁｮｳ | ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ | 大阪府 | 三島郡島本町 | 以下に掲載がない場合 |
| 40642 | 871 | 8710000 | ﾌｸｵｶｹﾝ | ﾁｸｼﾞｮｳｸﾞﾝﾖｼﾄﾐﾏﾁ | ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ | 福岡県 | 築上郡吉富町 | 以下に掲載がない場合 |
| 44203 | 871 | 8710000 | ｵｵｲﾀｹﾝ | ﾅｶﾂｼ | ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ | 大分県 | 中津市 | 以下に掲載がない場合 |
+-------+-------------+----------+--------------------+-----------------------------------------------------+-----------------------------------------------+------------+-----------------------+--------------------------------+
6 rows in set (0.08 sec)
この状態で郵便番号から県名を取得する際に以下のようにしてしまうと、
mysql> select prefecture from zipcode group by zip_code having zip_code = '4980000'; +------------+ | prefecture | +------------+ | 愛知県 | +------------+ 1 row in set (0.13 sec)
このように、
また、
そのため、
まとめ
今回紹介したMySQLのgroup by拡張ですが、
MySQLでは、