第119回　group_concat()を利用してgroup byを更に便利に使う

今回は、group byを使って集約を行った際に知っておくと少しだけ便利になるかもしれない集計関数の1つ、group_concat()という関数を紹介していきます。group_concat()を使うと、group byで同じデータを集約する際、文字列としてまとめて扱うことができるようになります。

検証環境

今回はDocker ImageのMySQL 8.0.19を使用しています。検証用のデータとしては、第2回　MySQLにはじめてのデータを入れてみるからKEN_ALL.CSVを利用して作成していきます。

検証データのロードの方法に関しては、第113回　anemoeaterを使ってスローログを可視化してみるに詳しく書いているので今回は省略します。

group_concat()とは？

MySQLに用意されている集計関数の一種で、group byで同じデータを集約した時に、NULL以外の値を含む文字列を連結して返してくれます。言葉で説明してもなかなかに難しいので、実例として実行結果を見ていきましょう。

$  mysql -h127.0.0.1  -uroot -pmy-secret-pw zipcode

まずは113回と同様にMySQLに入ります。そして以下のクエリを実行してみましょう。以下のクエリは町単位でgroup byを行ったときの郵便番号を1つの文字列として取得するというコードになります。

mysql> select town, group_concat(zip_code) from zipcode group by town\G

実行結果は以下の通りになります。

～省略～

*************************** 86624. row ***************************
                  town: 黄金町
group_concat(zip_code): 5128062,7940037,2310054,0750252,8360872,7530045,8000027,8990125,8660845
*************************** 86625. row ***************************
                  town: 黄金町東
group_concat(zip_code): 0730012

～省略～

86956 rows in set, 8 warnings (0.32 sec)

実行結果は8万6,000件と非常に大きいので、特色的なところを抜き出してみました。この黄金町という町は、郵便番号の散らばり具合から全国にありそうですね。蛇足とはなるのですが、見てみたら全国にあった模様です。

mysql> select * from zipcode where town = '黄金町'
    -> ;
+-------+-------------+----------+-----------------------+-----------------------------------------+-----------------------+--------------+-----------------------+-----------+
| code  | old_zipcode | zip_code | prefecture_kana       | city_kana                               | town_kana             | prefecture   | city                  | town      |
+-------+-------------+----------+-----------------------+-----------------------------------------+-----------------------+--------------+-----------------------+-----------+
| 01216 | 07502       | 0750252  | ﾎｯｶｲﾄﾞｳ               | ｱｼﾍﾞﾂｼ                                  | ｺｶﾞﾈﾁｮｳ               | 北海道       | 芦別市                | 黄金町    |
| 14104 | 231         | 2310054  | ｶﾅｶﾞﾜｹﾝ               | ﾖｺﾊﾏｼﾅｶｸ                                | ｺｶﾞﾈﾁｮｳ               | 神奈川県     | 横浜市中区            | 黄金町    |
| 24202 | 512         | 5128062  | ﾐｴｹﾝ                  | ﾖｯｶｲﾁｼ                                  | ｺｶﾞﾈﾁｮｳ               | 三重県       | 四日市市              | 黄金町    |
| 35203 | 753         | 7530045  | ﾔﾏｸﾞﾁｹﾝ               | ﾔﾏｸﾞﾁｼ                                  | ｺｶﾞﾈﾁｮｳ               | 山口県       | 山口市                | 黄金町    |
| 38202 | 794         | 7940037  | ｴﾋﾒｹﾝ                 | ｲﾏﾊﾞﾘｼ                                  | ｺｶﾞﾈﾁｮｳ               | 愛媛県       | 今治市                | 黄金町    |
| 40101 | 800         | 8000027  | ﾌｸｵｶｹﾝ                | ｷﾀｷｭｳｼｭｳｼﾓｼﾞｸ                           | ｺｶﾞﾈﾏﾁ                | 福岡県       | 北九州市門司区        | 黄金町    |
| 40202 | 836         | 8360872  | ﾌｸｵｶｹﾝ                | ｵｵﾑﾀｼ                                   | ｺｶﾞﾈﾏﾁ                | 福岡県       | 大牟田市              | 黄金町    |
| 43202 | 866         | 8660845  | ｸﾏﾓﾄｹﾝ                | ﾔﾂｼﾛｼ                                   | ｺｶﾞﾈﾁｮｳ               | 熊本県       | 八代市                | 黄金町    |
| 46208 | 89901       | 8990125  | ｶｺﾞｼﾏｹﾝ               | ｲｽﾞﾐｼ                                   | ｺｶﾞﾈﾏﾁ                | 鹿児島県     | 出水市                | 黄金町    |
+-------+-------------+----------+-----------------------+-----------------------------------------+-----------------------+--------------+-----------------------+-----------+
9 rows in set (0.07 sec)

ここで見ていただきたいのはzip_codeの部分で、黄金町でselectした時に出てきた値とgroup_concat()した時の値が一緒のものが出力されているという点です。このようにgroupで集約した際に、文字列を結合して表示することができました。

使用する上での注意点

さて、もの凄く便利そうに見えるgroup_concat()ですが、注意しないといけない点があります。以下のコマンドをMySQL上で実行してみましょう。

mysql> select town, group_concat(zip_code) from zipcode where town = '以下に掲載がない場合' ;

実行してみると以下のような結果が得られます。

+--------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| town                           | group_concat(zip_code)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+--------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 以下に掲載がない場合           | 0600000,0010000,0650000,0030000,0620000,0050000,0630000,0040000,0060000,0040000,0400000,0470000,0700000,0500000,0850000,0800000,0900000,0680400,0680000,0930000,0770000,0530000,0970000,0720000,0750000,0670000,0791100,0940000,0950000,0960000,0682100,0870000,0660000,0730000,0730100,0730400,0740000,0760000,0590000,0611400,0520000,0611100,0613200,0490100,0610200,0681100,0491500,0491300,0491100,0490400,0411100,0411400,0492300,0493100,0493500,0430000,0490600,0431100,0430100,0431400,0494300,0494500,0480600,0480400,0480100,0481300,0481500,0481600,0481700,0440200,0440100,0440000,0482200,0450000,0450200,0450300,0460200,0460100,0482400,0460000,0460500,0690200,0790300,0730200,0691200,0691300,0691500,0610500,0610600,0731100,0790500,0782100,0782600,0782500,0782200,0711200,0711500,0781300,0780300,0781400,0781700,0711400,0710200,0710500,0710700,0792400,0792200,0980100,0980300,0981200,0982200,0982500,0982800,0740400,0770200,0783300,0783700,0784100,0784400,0983500,0983300,0986200,0985700,0985500,0985800,0984100,0971200,0970400, |
+--------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.06 sec)

注目して欲しいところとしては、結果のgroup_concat(zip_code)カラムの一番最後の0970400,なのですが、最後が,となっていて何やら続きそうな雰囲気があります。それに最後のwarningが発生している事もわかります。

warningsを見るために、第43回　MySQLの準結合（セミジョイン）についてと第117回　MySQL 8.0のオプティマイザーヒントにも登場したSHOW WARNINGSコマンドを利用して見ましょう。注意点としては、SHOW WARNINGSコマンドは1つ前に実行されたSQLのwarningが見れるコマンドなので、スペルを間違えた場合はスペルミスのエラーになってしまいます。注意してください。もしミスをしてしまった場合は、再度group_concat()のSQLを実行してみてください。

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------+
| Level   | Code | Message                           |
+---------+------+-----------------------------------+
| Warning | 1260 | Row 129 was cut by GROUP_CONCAT() |
+---------+------+-----------------------------------+
1 row in set (0.00 sec)

メッセージを読んでみると、129行目がGROUP_CONCAT()によって切り捨てられたと書かれています。これはなぜ起こってしまったのかをMySQL公式のgroup_concat()のドキュメントで確認してみましょう。

結果は、group_concat_max_len システム変数で指定された最大長まで切り捨てられました。その変数のデフォルト値は1024です。

ということで、1024文字で切り捨てを行う模様です。今回は郵便番号を利用しています。郵便番号は7文字で、行末のカンマを含めると8文字です。8×128でピッタリ1024となり、129行目が切り落とされる結果になりました。

さて、切り落とされてしまう謎は解明されたのですが、どうしても結果を全件返したい場合もあると思います。その場合の対処方法として、group_concat_max_lenで最大長を変更するという方法があります。とりあえず、SET SESSION構文で設定をしていきたいのですが、何文字いるのかがわかりません。とりあえず、何カラムあるのかを計測してみましょう。

mysql> select town, count(zip_code) from zipcode where town = '以下に掲載がない場合' ;
+--------------------------------+-----------------+
| town                           | count(zip_code) |
+--------------------------------+-----------------+
| 以下に掲載がない場合           |            1874 |
+--------------------------------+-----------------+
1 row in set (0.06 sec)

1874カラムあることがわかりました。先頭から最後の1つ前の文字までは郵便番号とカンマを入れて8文字が必要で、最後の郵便番号は終端にカンマがいらないため、計算式は1873×8＋7＝14991 となり、14991文字取得できれば良いことがわかります。SET SESSION構文を使って設定してみましょう。

mysql> set session group_concat_max_len=14991;
Query OK, 0 rows affected (0.00 sec)
mysql> select town, group_concat(zip_code) from zipcode where town = '以下に掲載がない場合' ;

～省略～

1 row in set (0.06 sec)

結果が大きすぎるため省略させていただきますが、末尾の実行結果から、警告が消えていることがわかります。ちなみに本当に合っているか、14990文字を設定して確認してみましょう

mysql> set session group_concat_max_len=14991;
Query OK, 0 rows affected (0.00 sec)
mysql> select town, group_concat(zip_code) from zipcode where town = '以下に掲載がない場合' ;

～省略～

1 row in set, 1 warning (0.05 sec)

こちらは警告が出ているので、正しい値が設定できたことがわかります。

ちなみに、このgroup_concat_max_lenの最大値は64bitのプラットフォームであれば18446744073709551615文字、32bitのプラットフォームであれば4294967295文字と、とんでもなく大きな数字が設定できます。

ということなら、最初から最大値を入れてしまえばよいのではないかと考える人もいるかもしれません。しかし、このシステム変数に最大値を雑に適応してしまうと、サーバに負荷がかかってしまうためおすすめできません。どうしても大きな数字を設定する必要がある場合でも、max_allowed_packetの上限に引っ掛かってしまう場合があるので、合わせて設定することを忘れないようにしましょう。もし、データ量が増えていくシステムでgroup_concat()を利用したいとすれば、将来に向けてプログラムでの処理への置き換えを検討していく必要がある点には注意しましょう。

また、今回は文字列の結果がおかしかったことで気がつくことができましたが、データの取得できる量が足りないというのは開発環境や本番環境でリリースされた後でも気がつくのが非常に難しいバグになります。この機能を使う際にはよく注意をして利用をしましょう。

まとめ

今回はgroup_concat()というgroup byをより便利に使う集計関数を紹介させていただきました。ORマッパーなどを利用している場合にはプログラムで解決したくなることもあるかもしれませんし、そちらがうまくいく場合もあるでしょう。しかし、MySQLにこういった機能があると知っていると実装をする際に取れる選択肢が増えるのと、コマンドラインから同様の事をしたい場合に便利です。

皆さんもぜひ一度試してみてはいかがでしょうか。