string_agg()の達人になる！結果を思い通りにソートするテクニック

2024-04-02

PostgreSQLにおけるstring_agg()の結果のソート方法

方法1: ORDER BY 句を使用する

最も簡単な方法は、ORDER BY 句を使用して結果をソートすることです。ORDER BY 句では、ソートする列と昇順/降順を指定できます。

SELECT
  string_agg(value, ', ') AS aggregated_value
FROM
  table
ORDER BY
  value ASC;

上記の例では、table テーブルの value 列を昇順に連結して aggregated_value という列に格納します。

方法2: サブクエリを使用する

ORDER BY 句でソートできない場合、サブクエリを使用して結果をソートすることができます。サブクエリでは、string_agg() 関数を使用して連結した文字列を列として生成し、その列をソートします。

SELECT
  aggregated_value
FROM
  (
    SELECT
      string_agg(value, ', ') AS aggregated_value
    FROM
      table
  ) AS t
ORDER BY
  aggregated_value ASC;

上記の例では、サブクエリで string_agg() 関数を使用して value 列を連結し、aggregated_value という列を生成します。その後、t というエイリアスでサブクエリを呼び出し、aggregated_value 列を昇順にソートします。

方法3: CASE 式を使用する

CASE 式を使用して、連結する文字列にソート順序を反映させることもできます。

SELECT
  string_agg(
    CASE
      WHEN value > 10 THEN value || ' (large)'
      ELSE value
    END,
    ', ') AS aggregated_value
FROM
  table;

上記の例では、value 列の値が10よりも大きい場合は "(large)" という文字列を付加し、それ以外はそのまま連結します。

string_agg() の結果をソートするには、ORDER BY 句、サブクエリ、CASE 式などの方法があります。それぞれの特徴を理解して、目的に合った方法を選択してください。

補足

PostgreSQL 14以降では、string_agg() 関数に ORDER BY 句を直接指定できるようになりました。

SELECT
  string_agg(value ORDER BY value ASC) AS aggregated_value
FROM
  table;

上記の例は基本的な方法を紹介していますが、より複雑なソート処理を行う場合は、ウィンドウ関数やその他の関数と組み合わせることもできます。

-- テーブル作成
CREATE TABLE test (
  id INT,
  value TEXT
);

-- データ挿入
INSERT INTO test (id, value) VALUES (1, 'a');
INSERT INTO test (id, value) VALUES (2, 'c');
INSERT INTO test (id, value) VALUES (3, 'b');

-- ORDER BY 句を使用する
SELECT
  string_agg(value, ', ') AS aggregated_value
FROM
  test
ORDER BY
  value ASC;

-- サブクエリを使用する
SELECT
  aggregated_value
FROM
  (
    SELECT
      string_agg(value, ', ') AS aggregated_value
    FROM
      test
  ) AS t
ORDER BY
  aggregated_value ASC;

-- CASE 式を使用する
SELECT
  string_agg(
    CASE
      WHEN value > 10 THEN value || ' (large)'
      ELSE value
    END,
    ', ') AS aggregated_value
FROM
  test;

-- PostgreSQL 14以降の場合
SELECT
  string_agg(value ORDER BY value ASC) AS aggregated_value
FROM
  test;

上記のコードを実行すると、以下の結果が出力されます。

aggregated_value
-----------------
a, b, c
a, b, c
a (large), b, c (large)
a, b, c

実行方法

上記のコードをテキストエディタなどに保存し、拡張子が.sql のファイルとして保存します。その後、PostgreSQLクライアントを使用してファイルを 실행합니다。

補足

上記のコードは、PostgreSQL 10.0以降で実行できます。
コード中のテーブル名や列名は、環境に合わせて変更してください。

PostgreSQLにおけるstring_agg() の結果をソートするその他の方法

DISTINCT キーワードを使用して、重複する値を削除してから連結することができます。

SELECT
  string_agg(DISTINCT value, ', ') AS aggregated_value
FROM
  table;

GROUP BY 句を使用して、グループごとに連結することができます。

SELECT
  group_by_column,
  string_agg(value, ', ') AS aggregated_value
FROM
  table
GROUP BY
  group_by_column;

外部結合を使用して、別のテーブルから値を取得して連結することができます。

SELECT
  t1.value,
  string_agg(t2.value, ', ') AS aggregated_value
FROM
  table1 AS t1
LEFT JOIN
  table2 AS t2
ON
  t1.id = t2.id
GROUP BY
  t1.id;

ウィンドウ関数を使用して、ソート処理をより柔軟に行うことができます。

SELECT
  id,
  value,
  string_agg(value ORDER BY value ASC) WITHIN GROUP (ORDER BY id) OVER (PARTITION BY group_by_column) AS aggregated_value
FROM
  table;