PostgreSQL: STRING_AGG関数 vs GROUP_CONCAT関数

2024-04-02

PostgreSQLにおけるGROUP_CONCATの代替方法

では、PostgreSQLで同様の処理を行うにはどうすれば良いのでしょうか？いくつか方法があります。

STRING_AGG関数を使う

PostgreSQLでは、STRING_AGG関数をGROUP_CONCATの代わりに使うことができます。STRING_AGG関数は、グループ化された行の列値を連結し、指定された区切り文字で区切ります。

SELECT
  column1,
  STRING_AGG(column2, ', ') AS column2_concat
FROM table
GROUP BY column1;

この例では、column1でグループ化し、column2の値をカンマとスペースで区切って連結しています。

サブクエリを使って、GROUP_CONCATと同様の処理を行うこともできます。

SELECT
  column1,
  (
    SELECT STRING_AGG(column2, ', ')
    FROM table AS t
    WHERE t.column1 = table.column1
  ) AS column2_concat
FROM table
GROUP BY column1;

SELECT
  column1,
  (
    SELECT column2
    FROM table AS t
    WHERE t.column1 = table.column1
    FOR XML PATH('')
  ) AS column2_concat
FROM table
GROUP BY column1;

PostgreSQLにはGROUP_CONCAT関数はありませんが、STRING_AGG関数、サブクエリ、FOR XML PATH('')を使って同様の処理を行うことができます。

それぞれの方法にはメリットとデメリットがあるので、状況に合わせて使い分けることが重要です。

CREATE TABLE IF NOT EXISTS customers (
  id SERIAL PRIMARY KEY,
  name TEXT,
  email TEXT,
  country TEXT
);

INSERT INTO customers (name, email, country) VALUES
  ('John Doe', '[email protected]', 'USA'),
  ('Jane Doe', '[email protected]', 'USA'),
  ('Peter Smith', '[email protected]', 'UK'),
  ('Sarah Jones', '[email protected]', 'UK');

STRING_AGG関数

SELECT
  country,
  STRING_AGG(name, ', ') AS names
FROM customers
GROUP BY country;

country | names
------- | --------
USA     | John Doe, Jane Doe
UK      | Peter Smith, Sarah Jones

サブクエリ

SELECT
  country,
  (
    SELECT STRING_AGG(name, ', ')
    FROM customers AS t
    WHERE t.country = customers.country
  ) AS names
FROM customers
GROUP BY country;

出力

country | names
------- | --------
USA     | John Doe, Jane Doe
UK      | Peter Smith, Sarah Jones

FOR XML PATH('')

SELECT
  country,
  (
    SELECT column2
    FROM customers AS t
    WHERE t.country = customers.country
    FOR XML PATH('')
  ) AS names
FROM customers
GROUP BY country;

country | names
------- | --------
USA     | John Doe, Jane Doe
UK      | Peter Smith, Sarah Jones

これらのサンプルコードは、PostgreSQLでGROUP_CONCATと同様の処理を行う方法を理解するのに役立ちます。

PostgreSQLでGROUP_CONCATと同様の処理を行うためのその他の方法

ARRAY_AGG関数は、グループ化された行の列値を配列にまとめます。

SELECT
  column1,
  ARRAY_AGG(column2) AS column2_array
FROM table
GROUP BY column1;

LATERAL JOINを使って、グループ化された行の列値を結合することができます。

SELECT
  t1.column1,
  t2.column2
FROM table AS t1
LATERAL JOIN (
  SELECT column2
  FROM table AS t2
  WHERE t1.column1 = t2.column1
) AS t2
GROUP BY t1.column1;

CREATE FUNCTION group_concat(text[] input, text delimiter) RETURNS text AS $$
DECLARE
  output text;
BEGIN
  FOREACH row IN ARRAY input LOOP
    output := output || row || delimiter;
  END LOOP;
  RETURN output;
END;
$$ LANGUAGE plpgsql;

SELECT
  column1,
  group_concat(column2, ', ') AS column2_concat
FROM table
GROUP BY column1;