PostgreSQLのstring_agg関数：グループ化されたデータを直接出力
PostgreSQLのstring_agg関数は、グループ化された行のデータを文字列として結合し、指定された区切り文字で区切って出力する強力な集計関数です。複雑なレポート作成、データ分析、特定のフォーマットでのデータ出力など、様々な場面で活用できます。この記事では、string_agg関数の基本的な使い方から、応用的なテクニック、パフォーマンスに関する考慮事項まで、詳細に解説します。
1. string_agg関数の基本
string_agg関数は、PostgreSQL 9.0で導入された集計関数の一つで、グループ化された行の文字列値を連結するために使用されます。基本的な構文は以下の通りです。
sql
string_agg ( expression, delimiter [ order by expression ] )
expression: 連結したい文字列型の列または式。delimiter: 文字列を区切るための区切り文字。任意の文字列を指定できます（例：,,|,;,\nなど）。order by expression: 連結する文字列の順序を指定するオプションの句。指定しない場合、順序は保証されません。
例：
employeesテーブルがあるとします。
| employee_id | department | employee_name |
|---|---|---|
| 1 | Sales | Alice |
| 2 | Sales | Bob |
| 3 | Marketing | Charlie |
| 4 | Sales | David |
| 5 | Marketing | Eve |
以下のクエリは、部門ごとに従業員名をカンマ区切りで出力します。
sql
SELECT department, string_agg(employee_name, ', ') AS employees
FROM employees
GROUP BY department;
出力：
| department | employees |
|---|---|
| Marketing | Charlie, Eve |
| Sales | Alice, Bob, David |
この例では、employeesテーブルをdepartmentでグループ化し、各グループのemployee_nameをstring_agg関数を使用してカンマ区切りで連結しています。
2. string_agg関数の詳細な解説
2.1. expression：連結する値
expression引数には、連結したい文字列型の列、式、またはリテラルを指定できます。 expression が NULL の場合、その値は無視されます。つまり、NULL値は連結結果に含まれません。
例： NULL値の扱い
productsテーブルがあるとします。
| product_id | product_name | category | price |
|---|---|---|---|
| 1 | Product A | Electronics | 100 |
| 2 | Product B | Clothing | 50 |
| 3 | Product C | NULL | 25 |
| 4 | Product D | Electronics | 75 |
以下のクエリは、カテゴリごとに製品名をカンマ区切りで出力します。product_nameがNULLの場合は無視されることに注意してください。
sql
SELECT category, string_agg(product_name, ', ') AS products
FROM products
GROUP BY category;
出力：
| category | products |
|---|---|
| Clothing | Product B |
| Electronics | Product A, Product D |
| NULL | Product C |
NULLのカテゴリ自体はグループ化され、Product Cが表示されますが、product_nameがNULLであるProduct Cは連結結果に含まれていません。 NULLのカテゴリを除外するには、WHERE句を使用します。
sql
SELECT category, string_agg(product_name, ', ') AS products
FROM products
WHERE category IS NOT NULL
GROUP BY category;
2.2. delimiter：区切り文字
delimiter引数には、連結された文字列を区切るための任意の文字列を指定できます。カンマ（,）、パイプ（|）、セミコロン（;）、改行文字（\n）など、様々な区切り文字を使用できます。
例：様々な区切り文字の使用
“`sql
SELECT department, string_agg(employee_name, ‘ | ‘) AS employees_pipe
FROM employees
GROUP BY department;
SELECT department, string_agg(employee_name, E’\n’) AS employees_newline
FROM employees
GROUP BY department;
“`
これらのクエリは、それぞれパイプ（|）と改行文字（\n）を区切り文字として使用します。
2.3. order by expression：順序の指定
order by expression句を使用すると、連結する文字列の順序を指定できます。これは、特定の順序でデータを出力する必要がある場合に非常に便利です。
例：従業員名をアルファベット順に連結
sql
SELECT department, string_agg(employee_name, ', ' ORDER BY employee_name) AS employees
FROM employees
GROUP BY department;
出力：
| department | employees |
|---|---|
| Marketing | Charlie, Eve |
| Sales | Alice, Bob, David |
この例では、ORDER BY employee_name句を使用して、従業員名をアルファベット順に連結しています。
2.4. DISTINCTキーワードの使用
string_agg関数は、重複する値を排除するためにDISTINCTキーワードをサポートしています。
例：重複する従業員名を排除
employeesテーブルに同じ名前の従業員が複数いる場合を想定します。
| employee_id | department | employee_name |
|---|---|---|
| 1 | Sales | Alice |
| 2 | Sales | Bob |
| 3 | Marketing | Charlie |
| 4 | Sales | Alice |
| 5 | Marketing | Eve |
以下のクエリは、重複する従業員名を排除して、部門ごとに従業員名をカンマ区切りで出力します。
sql
SELECT department, string_agg(DISTINCT employee_name, ', ' ORDER BY employee_name) AS employees
FROM employees
GROUP BY department;
出力：
| department | employees |
|---|---|
| Marketing | Charlie, Eve |
| Sales | Alice, Bob |
この例では、DISTINCTキーワードを使用して、employee_nameの重複を排除しています。
3. string_agg関数の応用的なテクニック
3.1. 複雑な式の連結
string_agg関数は、単純な列だけでなく、複雑な式の結果も連結できます。
例：従業員名と部署名を連結
sql
SELECT
department,
string_agg(employee_name || ' (' || department || ')', ', ' ORDER BY employee_name) AS employee_department
FROM
employees
GROUP BY
department;
この例では、employee_nameとdepartmentを連結して、Employee Name (Department)のような形式で出力しています。
3.2. CASE式との組み合わせ
CASE式と組み合わせることで、条件に応じて異なる文字列を連結できます。
例：役職に応じて異なるメッセージを連結
employee_titlesテーブルがあるとします。
| employee_id | title |
|---|---|
| 1 | Manager |
| 2 | Developer |
| 3 | Manager |
| 4 | Analyst |
| 5 | Developer |
sql
SELECT
department,
string_agg(
CASE
WHEN et.title = 'Manager' THEN employee_name || ' (Manager)'
ELSE employee_name
END,
', '
ORDER BY
employee_name
) AS employees_with_titles
FROM
employees e
LEFT JOIN
employee_titles et ON e.employee_id = et.employee_id
GROUP BY
department;
この例では、役職がManagerの場合のみ、Employee Name (Manager)のような形式で出力しています。
3.3. サブクエリとの組み合わせ
string_agg関数は、サブクエリの結果を連結するためにも使用できます。
例：各部署の給与上位2名の従業員名を連結
sql
SELECT
department,
string_agg(employee_name, ', ' ORDER BY salary DESC) AS top_employees
FROM (
SELECT
e.department,
e.employee_name,
e.salary,
ROW_NUMBER() OVER (PARTITION BY e.department ORDER BY e.salary DESC) AS rn
FROM
employees e
) AS subquery
WHERE
rn <= 2
GROUP BY
department;
この例では、まずサブクエリで各部署の従業員を給与順にランク付けし、上位2名の従業員名をstring_agg関数で連結しています。
3.4. JSON形式での出力
PostgreSQLのJSON関数と組み合わせることで、連結されたデータをJSON形式で出力することも可能です。
例：従業員名をJSON配列で出力
sql
SELECT
department,
json_agg(employee_name ORDER BY employee_name) AS employees_json
FROM
employees
GROUP BY
department;
出力例：
json
{
"department": "Marketing",
"employees_json": ["Charlie", "Eve"]
},
{
"department": "Sales",
"employees_json": ["Alice", "Bob", "David"]
}
この例では、json_agg関数を使用して、従業員名をJSON配列として出力しています。 json_agg 関数は、string_aggとは異なり、JSON配列を作成する集計関数です。 string_aggでカンマ区切り文字列を作成し、それをJSON配列の文字列として扱っても良いですが、json_aggの方がより適切なソリューションです。
3.5. 配列としての出力
array_agg関数と組み合わせることで、文字列の配列として出力することも可能です。
例：従業員名を配列で出力
sql
SELECT
department,
array_agg(employee_name ORDER BY employee_name) AS employees_array
FROM
employees
GROUP BY
department;
出力例：
{
"department": "Marketing",
"employees_array": {"Charlie", "Eve"}
},
{
"department": "Sales",
"employees_array": {"Alice", "Bob", "David"}
}
この例では、array_agg関数を使用して、従業員名を文字列の配列として出力しています。こちらもstring_aggでカンマ区切り文字列を作り、それを配列に変換することも可能ですが、array_agg関数を使う方が効率的です。
4. string_agg関数のパフォーマンスに関する考慮事項
string_agg関数は非常に便利な関数ですが、大規模なデータセットで使用する場合は、パフォーマンスに注意する必要があります。
4.1. インデックスの活用
GROUP BY句で使用する列には、インデックスを作成することでパフォーマンスを向上させることができます。
例：department列にインデックスを作成
sql
CREATE INDEX idx_employees_department ON employees (department);
4.2. 大量のデータの連結
大量のデータを連結する場合、string_agg関数のパフォーマンスが低下する可能性があります。そのような場合は、以下のような対策を検討してください。
- データの分割: データをより小さなグループに分割して、
string_agg関数を複数回実行し、結果を結合する。 - マテリアライズドビューの使用: 頻繁に実行されるクエリの結果をマテリアライズドビューとして保存し、必要に応じてリフレッシュする。
- プログラミング言語での処理: データベースから必要なデータを取得し、アプリケーション層で文字列の連結処理を行う。
4.3. 連結する文字列の長さ
連結する文字列の長さが非常に長い場合、string_agg関数のパフォーマンスに影響を与える可能性があります。そのような場合は、文字列の長さを制限したり、圧縮したりすることを検討してください。
4.4. ORDER BY句の利用
ORDER BY句を使用すると、ソート処理が必要になるため、パフォーマンスが低下する可能性があります。 順序が重要でない場合は、ORDER BY句を省略することを検討してください。
5. string_agg関数の代替手段
string_agg関数以外にも、文字列を連結するためのいくつかの代替手段があります。
5.1. XML関数
PostgreSQLには、XML関数を使用して文字列を連結する方法があります。
例：XML関数を使用して従業員名を連結
sql
SELECT
department,
xmlagg(xmlelement(name employee, employee_name) ORDER BY employee_name).getcontent() AS employees_xml
FROM
employees
GROUP BY
department;
この例では、xmlagg関数とxmlelement関数を使用して、従業員名をXML形式で連結しています。
5.2. PL/pgSQL関数
PL/pgSQLで独自の関数を作成して、文字列を連結することも可能です。
例：PL/pgSQL関数で従業員名を連結
“`sql
CREATE OR REPLACE FUNCTION concat_employees(department_name VARCHAR)
RETURNS TEXT AS $$
DECLARE
employees TEXT := ”;
employee_record RECORD;
BEGIN
FOR employee_record IN SELECT employee_name FROM employees WHERE department = department_name ORDER BY employee_name LOOP
employees := employees || employee_record.employee_name || ‘, ‘;
END LOOP;
— 末尾の”, “を削除
IF length(employees) > 0 THEN
employees := substring(employees, 1, length(employees) – 2);
END IF;
RETURN employees;
END;
$$ LANGUAGE plpgsql;
SELECT department, concat_employees(department) AS employees FROM (SELECT DISTINCT department FROM employees) AS departments;
“`
この例では、concat_employees関数を作成して、指定された部署の従業員名を連結しています。 この方法は柔軟性が高いですが、パフォーマンスはstring_agg関数よりも劣る可能性があります。
6. まとめ
string_agg関数は、PostgreSQLでグループ化されたデータを文字列として結合し、指定された区切り文字で区切って出力するための非常に強力な集計関数です。この記事では、string_agg関数の基本的な使い方から、応用的なテクニック、パフォーマンスに関する考慮事項、代替手段まで、詳細に解説しました。
string_agg関数を理解し、適切に活用することで、より複雑なレポート作成やデータ分析を効率的に行うことができます。 ぜひ、この記事を参考に、string_agg関数を様々な場面で活用してみてください。