Sql Postgresql Group By String Aggregation

PostgreSQL の GROUP BY クエリで文字列を連結する方法

データベース、特に PostgreSQL を扱う際に、レコードのグループ内で文字列を連結する必要がある一般的なシナリオに直面することがあります。これは通常、1 つのカテゴリ（同じ会社の従業員など）の複数のエントリが含まれるデータセットを持っているときに発生し、それらのエントリをプレゼンテーションまたは分析のために 1 つの文字列にまとめたい場合です。

このブログ投稿では、PostgreSQL の GROUP BY クエリを使用して文字列を連結する方法を説明します。最新のソリューションと、包括的な理解のために古いバージョンの PostgreSQL 用のアプローチをカバーします。

問題

たとえば、次の従業員テーブルを考えてみましょう：

ID	COMPANY_ID	EMPLOYEE
1	1	Anna
2	1	Bill
3	2	Carol
4	2	Dave

これらのエントリを COMPANY_ID でグループ化し、それぞれの会社に関連する従業員のリストを表示するように出力したいとします。期待される出力は次のようになります：

COMPANY_ID	EMPLOYEE
1	Anna, Bill
2	Carol, Dave

解決策

PostgreSQL 9.0 以降

PostgreSQL バージョン 9.0 以降を使用している場合、強力な組み込み関数 string_agg() を使用して、GROUP BY 句内で文字列を効果的に連結できます。

クエリは次のように記述できます：

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

順序を指定する場合

さらに、PostgreSQL バージョン 9.0 以降では集計関数内で ORDER BY をサポートしているため、従業員の順序を指定できます：

SELECT company_id, string_agg(employee, ', ' ORDER BY employee)
FROM mytable
GROUP BY company_id;

これにより、従業員名を特定の順序で連結できます。

PostgreSQL 8.4.x

PostgreSQL 8.4 を使用している場合、このサポートが終了したバージョンでは、array_agg() 関数と array_to_string() を組み合わせて同様の結果を達成できます。以下が使用する SQL クエリです：

SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id;

PostgreSQL 8.3.x およびそれ以前

PostgreSQL 8.3 およびそれ以前のバージョンを使用している場合、文字列を直接連結するための組み込み関数はありません。この制限を回避するためのカスタム実装を以下に示します：

textcat 関数を使用して新しい集計関数を作成します：

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

連結された文字列の間に , のような区切り文字を含めるために、カスタム関数を作成します：

CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$ 
BEGIN 
  IF acc IS NULL OR acc = '' THEN 
    RETURN instr; 
  ELSE 
    RETURN acc || ', ' || instr; 
  END IF; 
END; 
$$ LANGUAGE plpgsql;

これは、文字列を適切に連結します。

NULL または空の値に対して追加のカンマを削除したい場合、以下のより洗練されたバージョンを使用できます：

CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$ 
BEGIN 
  IF acc IS NULL OR acc = '' THEN 
    RETURN instr; 
  ELSIF instr IS NULL OR instr = '' THEN 
    RETURN acc; 
  ELSE 
    RETURN acc || ', ' || instr; 
  END IF; 
END; 
$$ LANGUAGE plpgsql;

結論

PostgreSQL での文字列の連結、特に GROUP BY 操作中は、意味のあるレポートやデータセットを生成するために重要です。最近のバージョンで string_agg() のような関数が導入されたことで、この作業は簡単になりました。

古いバージョンを使用している場合でも、ここで詳述したカスタム集計メソッドを使用すれば、同様の結果を得ることができます。新しい機能やセキュリティの改善を活用するために、PostgreSQL のバージョンをアップグレードすることを常に考慮してください。

クエリを楽しんでください！