SQL の string_agg 関数は、複数の行の文字列をさまざまなオプションで 1 つの文字列に連結（結合）する集計関数です。
レポートの各データグループの列の値を結合したい場合に特に便利で、特に多対多の関係を扱う場合にクエリ結果を読みやすくすることができます。たとえば、各トピックで使用されているすべてのタグを表示するレポートを作成したい場合、string_agg を使用して各トピックのすべてのタグを 1 つの文字列に連結できます。
構文
string_agg の基本的な構文は次のとおりです。
STRING_AGG(expression, delimiter)
string_agg 関数は 2 つのパラメータを取ります。
expression: 連結する値。delimiter: 文字列の間に挿入する区切り文字。区切り文字は文字列または文字にすることができます。
DE クエリでの使用例
string_agg を利用したクエリの例をいくつか見てみましょう。
サイト上のすべてのカテゴリを一覧表示
このクエリは、categories テーブルの name フィールドと id フィールドを 1 つの文字列に連結します。各 name と id のペアは ’ : ’ で区切られ、各ペアは ', ’ で区切られます。ペアは id で並べ替えられます。
SELECT
-- STRING_AGG 関数は、'name' フィールドと 'id' フィールドを 1 つの文字列に連結します。
-- 各 'name' と 'id' のペアは ' : ' で区切られ、各ペアは ', ' で区切られます。
-- ペアは 'id' で並べ替えられます。
STRING_AGG(name || ' : ' || id, ', ' ORDER BY id) AS category_list
FROM
categories -- データは 'categories' テーブルから選択されます。
結果例:
| category_list |
|---|
| Uncategorized : 1, Site Feedback : 2, Staff : 3, Lounge : 4, Email : 5, Event : 6, Parent Category : 7, Sub Category |
すべてのトピックと関連タグを一覧表示
このクエリは、topics テーブルから id を選択し、各トピックに関連付けられた tags を 1 つの文字列に連結します。各タグはコンマとスペースで区切られ、タグはアルファベット順に並べ替えられます。結果はトピック id でグループ化され、タグで並べ替えられます。トピックにタグがない場合、tags 列は NULL になります。
SELECT t.id topic_id,
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM topics t
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
LEFT JOIN tags ON tags.id = tt.tag_id
GROUP BY t.id
ORDER BY tags
結果は、フォーマットされたトピック ID のリストになり、それぞれに関連付けられたタグの文字列がアルファベット順に並べられます。たとえば次のようになります。
結果例:
| topic | tags |
|---|---|
| Welcome to the Lounge (3) | tag1, tag2 |
| A Very Safe for work post (3) | tag3, tag4, tag,5 |
| This is a blog tagged post (4) | tag3 |
| About the Lounge category (3) | tag5 |
| Welcome to your 14 day standard hosting trial! | NULL |
インラインコメント付きの詳細な説明
-- この SQL ステートメントは、'topics' テーブルと関連する 'tags' からデータを選択しています。
SELECT
t.id topic_id, -- 'topics' テーブルから 'id' を選択します。
-- STRING_AGG 関数は、各トピックに関連付けられた 'tags' を 1 つの文字列に連結します。
-- 各タグはコンマとスペースで区切られ、タグはアルファベット順に並べ替えられます。
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM
topics t -- データは 'topics' テーブルから選択されます。
-- 'topic_tags' テーブルは、'topics' テーブルの 'topic_id' で左結合されます。
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
-- 'tags' テーブルは、'topic_tags' テーブルの 'tag_id' で左結合されます。
LEFT JOIN tags ON tags.id = tt.tag_id
GROUP BY
t.id -- 結果はトピック 'id' でグループ化されます。
ORDER BY
"tags" -- 結果は 'tags' で並べ替えられます。
トピックに投稿したユーザー
このクエリは、topic ids と titles、各トピックに投稿したユーザーの usernames、および各トピックに投稿した distinct users の count のリストを返します。結果はユーザー数で降順に並べ替えられます。
SELECT
topics.id AS "topic_id",
topics.title AS "topic_title",
STRING_AGG(DISTINCT users.username, ', ' ORDER BY users.username) AS "users_posted",
COUNT(DISTINCT users.id) AS "user_count"
FROM
topics
JOIN
posts ON posts.topic_id = topics.id
JOIN
users ON users.id = posts.user_id
GROUP BY
topics.id, topics.title
ORDER BY
"user_count" DESC
結果例
| topic | topic_title | users_posted | user_count |
|---|---|---|---|
| Lets make a topic (10) | Lets make a topic | anonymous, user1, user2, user3 | 4 |
| Another Topic (3) | Another Topic | user3, user4, user5 | 3 |
| Discobot test (2) | Discobot test | user6 | 1 |
インラインコメント付きの詳細な説明
-- この SQL ステートメントは、'topics'、'posts'、および 'users' テーブルからデータを選択しています。
SELECT
topics.id AS "topic_id", -- 'topics' テーブルから 'id' を選択します。
topics.title AS "topic_title", -- 'topics' テーブルから 'title' を選択します。
-- STRING_AGG 関数は、トピックに投稿したユーザーのユーザー名を 1 つの文字列に連結します。
-- 各ユーザー名はコンマとスペースで区切られ、ユーザー名はアルファベット順に並べ替えられます。
STRING_AGG(DISTINCT users.username, ', ' ORDER BY users.username) AS "users_posted",
-- トピックに投稿したユニークユーザーの数をカウントします。
COUNT(DISTINCT users.id) AS "user_count"
FROM
topics -- データは 'topics' テーブルから選択されます。
-- 'posts' テーブルは、'topics' テーブルの 'topic_id' で結合されます。
JOIN posts ON posts.topic_id = topics.id
-- 'users' テーブルは、'posts' テーブルの 'id' で結合されます。
JOIN users ON users.id = posts.user_id
GROUP BY
topics.id, topics.title -- 結果はトピック 'id' と 'title' でグループ化されます。
ORDER BY
"user_count" DESC -- 結果はユーザー数で降順に並べ替えられます。
このチュートリアルは以上です！
string_agg をどのように使用したかについての質問や例があれば、以下で共有してください。