【初心者向け解説】MySQLでGROUP_CONCAT関数を使ってカンマ区切りリストを作成する方法

MySQLにおけるGROUP_CONCAT関数とカンマ区切り文字

カンマ区切り文字は、GROUP_CONCAT関数で連結された値の間に挿入される文字を指定します。デフォルトではカンマ (,) が使用されますが、他の文字に変更することも可能です。

このチュートリアルでは、以下の内容について説明します。

• GROUP_CONCAT関数の基本的な構文
• カンマ区切り文字を変更する方法

SELECT
  group_column,
  GROUP_CONCAT(column_list [ORDER BY expression ASC|DESC] [SEPARATOR separator_character]) AS concatenated_column
FROM table_name
GROUP BY group_column;

説明:

• group_column: グループ化の対象となる列を指定します。
• column_list: 連結する列をカンマ区切りで指定します。
• ORDER BY expression ASC|DESC: 連結する列の並び順を昇順 (ASC) または降順 (DESC) で指定します。省略可。
• SEPARATOR separator_character: カンマ区切り文字を指定します。省略するとカンマ (,) が使用されます。
• table_name: 対象となるテーブル名を指定します。

例:

SELECT
  country,
  GROUP_CONCAT(city SEPARATOR ' - ') AS cities
FROM customers
GROUP BY country;

このクエリは、customers テーブルの country 列でグループ化し、各国の city 列の値を「 - 」で区切って連結した文字列を cities 列として返します。

GROUP_CONCAT関数の SEPARATOR オプションを使用して、カンマ区切り文字を変更することができます。

SELECT
  department,
  GROUP_CONCAT(employee_name SEPARATOR ' & ') AS employees
FROM employees
GROUP BY department;

例1：各商品カテゴリーの販売個数の合計と、そのカテゴリーに属する商品IDをカンマ区切りで表示

SELECT
  category,
  SUM(quantity) AS total_sales,
  GROUP_CONCAT(product_id SEPARATOR ', ') AS product_ids
FROM order_details
GROUP BY category;

例2：各ユーザーの注文日と、その日に注文した商品IDをカンマ区切りで表示

SELECT
  user_id,
  order_date,
  GROUP_CONCAT(product_id SEPARATOR ', ') AS product_ids
FROM orders
JOIN order_details ON orders.order_id = order_details.order_id
GROUP BY user_id, order_date;

• GROUP_CONCAT関数は、返す文字列の長さに制限があります。この制限は、group_concat_max_len システム変数で設定できます。
• GROUP_CONCAT関数は、パフォーマンスに影響を与える可能性があります。そのため、大量のデータに対して使用する場合は注意が必要です。
• GROUP_CONCAT関数は、GROUP BY句で指定された列にインデックスが張られていない場合、パフォーマンスが低下する可能性があります。

このチュートリアルが、MySQLにおけるGROUP_CONCAT

GROUP_CONCAT 関数のサンプルコード

顧客テーブルの例

この例では、customers テーブルを使用して、各国の顧客の名前をカンマ区切りで連結したリストを取得します。

SELECT
  country,
  GROUP_CONCAT(name SEPARATOR ', ') AS customer_names
FROM customers
GROUP BY country;

このクエリを実行すると、次の結果が得られます。

| country | customer_names                               |
|---------|----------------------------------------------|
| USA     | John Doe, Jane Doe, Peter Jones           |
| Canada  | Emily Smith, David Johnson                |
| France   | Marie Dubois, Paul Dupont                 |

この例では、orders テーブルを使用して、各ユーザーの注文 ID と注文日、およびその注文に含まれる商品 ID をカンマ区切りで連結したリストを取得します。

SELECT
  user_id,
  order_date,
  GROUP_CONCAT(product_id SEPARATOR ', ') AS product_ids
FROM orders
JOIN order_details ON orders.order_id = order_details.order_id
GROUP BY user_id, order_date;

| user_id | order_date | product_ids                                 |
|---------|------------|-------------------------------------------------|
| 1       | 2023-10-05 | 16, 32, 48                                    |
| 1       | 2023-11-12 | 11, 25, 50                                    |
| 2       | 2023-10-19 | 10, 19, 38                                    |
| 3       | 2023-11-08 | 24, 31, 55                                    |

SELECT
  department,
  GROUP_CONCAT(employee_name SEPARATOR ' & ') AS employee_names
FROM employees
GROUP BY department;

| department | employee_names                               |
|---------|-------------------------------------------------|
| Sales     | John Doe & Jane Doe & Peter Jones           |
| Marketing | Emily Smith & David Johnson                |
| IT       | Marie Dubois & Paul Dupont                 |

ORDER BY オプションを使用する

SELECT
  country,
  GROUP_CONCAT(city ORDER BY population DESC SEPARATOR ' - ') AS cities
FROM customers
GROUP BY country;

| country | cities                                     |
|---------|---------------------------------------------|
| USA     | New York - Los Angeles - Chicago - Houston |
| Canada  | Toronto - Montreal - Vancouver - Calgary     |
| France   | Paris - Lyon - Marseille - Bordeaux        |

DISTINCT オプションを使用する

SELECT
  department,
  GROUP_CONCAT(DISTINCT title SEPARATOR ', ') AS job_titles
FROM employees
GROUP BY department;

| department | job_titles                               |
|---------|---------------------------------------------|
| Sales     | Sales Manager, Sales Associate, Account Manager |
| Marketing | Marketing Manager, Marketing Specialist      |
| IT       | Software Engineer, Web Developer, Network Engineer |

上記の例は、GROUP_CONCAT 関数の基本的な使用方法を示しています。この関数は、さまざまな方法で組み合わせることで、複雑なデータ処理を実行することができます。

GROUP_CONCAT 関数の代替方法

状況によっては、以下の方法がより適切で効率的な場合もあります。

サブクエリを使用して、グループごとに値を連結することができます。

SELECT
  group_column,
  (
    SELECT GROUP_CONCAT(column_list SEPARATOR ', ')
    FROM table_name
    WHERE group_column = outer_table.group_column
  ) AS concatenated_column
FROM table_name
GROUP BY group_column;

この方法は、GROUP_CONCAT 関数よりも柔軟性がありますが、複雑になる可能性があります。

FOR LOOP を使用して、グループごとに値を反復処理し、手動で連結することができます。

SELECT
  group_column,
  @list_var := ''
FROM table_name
GROUP BY group_column;

SELECT
  group_column,
  @list_var AS concatenated_column
FROM table_name
GROUP BY group_column;

UPDATE table_name
SET concatenated_column = @list_var
GROUP BY group_column;

この方法は、非効率的で冗長になる可能性があります。

ARRAY_AGG 関数を使用する (MySQL 8.0 以降)

MySQL 8.0 以降では、ARRAY_AGG 関数を使用して、グループごとに値の配列を返すことができます。

SELECT
  group_column,
  ARRAY_AGG(column_list) AS array_column
FROM table_name
GROUP BY group_column;

この方法は、GROUP_CONCAT 関数よりも新しく、より汎用性の高い方法です。ただし、MySQL 8.0 以降でのみ使用できます。

STRING_AGG 関数を使用する (PostgreSQL)

PostgreSQL では、STRING_AGG 関数を使用して、グループごとに値をカンマ区切りで連結した文字列を返すことができます。

SELECT
  group_column,
  STRING_AGG(column_list, ', ') AS concatenated_column
FROM table_name
GROUP BY group_column;

この方法は、PostgreSQL 独自の関数であり、MySQL では使用できません。

カスタム集計関数を使用する

複雑な要件がある場合は、カスタム集計関数を作成することができます。

この方法は、高度な知識と経験が必要となります。

最適な方法の選択

使用する方法は、データ構造、要件、および使用しているデータベースによって異なります。