PostgreSQLでGROUP BY句と集計関数を使用して1つの列の複数の結果行を1つに連結し、別の列でグループ化する際の注意点
SQL、PostgreSQL、集計関数を使用して、1つの列の複数の結果行を1つに連結し、別の列でグループ化する
例題：
顧客テーブル customers があり、以下の列があります。
customer_id: 顧客IDname: 顧客名city: 顧客の居住都市
各都市に住む顧客の名前をカンマ区切りで連結したリストを取得したいと考えています。
方法1：GROUP BY 句と STRING_AGG 関数を使用する
STRING_AGG 関数は、複数の文字列を1つの文字列に連結するために使用できます。
SELECT city, STRING_AGG(name, ', ')
FROM customers
GROUP BY city;
このクエリは、まず city 列に基づいて結果をグループ化します。その後、STRING_AGG 関数を使用して、各グループ内の name 列の値をカンマ区切りで連結します。
結果：
city | name
------- | --------
東京 | 山田太郎, 佐藤花子, 田中一郎
大阪 | 鈴木二郎, 高橋三枝
京都 | 藤原四郎, 木村五郎
方法2：GROUP BY 句とサブクエリを使用する
サブクエリを使用して、各都市に住む顧客の名前をカンマ区切りで連結した文字列を取得することもできます。
SELECT c.city,
(SELECT STRING_AGG(name, ', ')
FROM customers
WHERE city = c.city) AS names
FROM customers AS c
GROUP BY c.city;
このクエリは、まず customers テーブルを c というエイリアスを使用して2回参照します。最初の customers テーブルは、city 列に基づいて結果をグループ化するために使用されます。2番目の customers テーブルは、サブクエリ内で使用され、各グループ内の name 列の値をカンマ区切りで連結します。
city | name
------- | --------
東京 | 山田太郎, 佐藤花子, 田中一郎
大阪 | 鈴木二郎, 高橋三枝
京都 | 藤原四郎, 木村五郎
どちらの方法を使用するかは、好みの問題です。
- 方法1は、より簡潔で読みやすいコードになります。
- 方法2は、サブクエリを使用して複雑な処理を行うことができます。
補足：
STRING_AGG関数は、PostgreSQL 9.0以降で使用できます。- 他のデータベースでは、
GROUP BY句と別の集計関数を使用して同様の結果を取得することができます。
-- テーブル作成
CREATE TABLE customers (
customer_id INT,
name VARCHAR(255),
city VARCHAR(255)
);
-- データ挿入
INSERT INTO customers (customer_id, name, city) VALUES
(1, '山田太郎', '東京'),
(2, '佐藤花子', '東京'),
(3, '田中一郎', '東京'),
(4, '鈴木二郎', '大阪'),
(5, '高橋三枝', '大阪'),
(6, '藤原四郎', '京都'),
(7, '木村五郎', '京都');
-- 方法1：GROUP BY句とSTRING_AGG関数を使用する
SELECT city, STRING_AGG(name, ', ')
FROM customers
GROUP BY city;
-- 方法2：GROUP BY句とサブクエリを使用する
SELECT c.city,
(SELECT STRING_AGG(name, ', ')
FROM customers
WHERE city = c.city) AS names
FROM customers AS c
GROUP BY c.city;
このコードを実行すると、以下の結果が出力されます。
city | name
------- | --------
東京 | 山田太郎, 佐藤花子, 田中一郎
大阪 | 鈴木二郎, 高橋三枝
京都 | 藤原四郎, 木村五郎
実行環境:
- PostgreSQL 14.2
注意事項:
- 上記のコードは、PostgreSQL 14.2 で動作確認しています。他のバージョンの PostgreSQL では、動作が異なる可能性があります。
- テーブル名、列名、データなどは、環境に合わせて変更してください。
1つの列の複数の結果行を1つに連結し、別の列でグループ化する他の方法
FOR XML PATH 句を使用して、XML形式で結果を生成し、XPathを使用して必要なデータを抽出することができます。
SELECT city,
(SELECT name
FROM customers
WHERE city = c.city
FOR XML PATH('')) AS names
FROM customers AS c
GROUP BY c.city;
このクエリは、まず city 列に基づいて結果をグループ化します。その後、FOR XML PATH 句を使用して、各グループ内の name 列の値をXML形式で生成します。最後に、XPathを使用して、XMLから必要なデータ (name 列の値) を抽出します。
GROUP BY 句とウィンドウ関数を使用する
PostgreSQL 9.5以降では、ウィンドウ関数を使用して、グループ内の値を処理することができます。
SELECT city,
STRING_AGG(name, ', ') OVER (PARTITION BY city) AS names
FROM customers;
PL/pgSQLを使用してカスタム関数を作成する
より複雑な処理を行う場合は、PL/pgSQLを使用してカスタム関数を作成することができます。
CREATE FUNCTION concat_names(city VARCHAR(255)) RETURNS VARCHAR(255)
AS
$$
DECLARE
names TEXT;
BEGIN
SELECT STRING_AGG(name, ', ') INTO names
FROM customers
WHERE city = $1;
RETURN names;
END;
$$ LANGUAGE plpgsql;
SELECT city, concat_names(city) AS names
FROM customers
GROUP BY city;
このクエリは、まず concat_names というカスタム関数を作成します。この関数は、city 列を引数として受け取り、その都市に住む顧客の名前をカンマ区切りで連結した文字列を返します。その後、GROUP BY 句を使用して city 列に基づいて結果をグループ化し、concat_names 関数を使用して各グループ内の名前を連結します。
- 简单的な連結であれば、
GROUP BY句とSTRING_AGG関数を使用するのが最も簡単です。 - より複雑な処理を行う場合は、
FOR XML PATH句、ウィンドウ関数、PL/pgSQLなどの方法を使用することができます。
sql postgresql aggregate-functions
