SQL의 string_agg 함수는 여러 행의 문자열을 다양한 옵션을 사용하여 하나의 문자열로 연결(합치기)하는 집계 함수입니다.
보고서에서 데이터 그룹별로 열의 값을 결합하고 싶을 때 특히 유용하며, 특히 다대다(many-to-many) 관계를 다룰 때 쿼리 결과를 더 읽기 쉽게 만들어 줍니다. 예를 들어, 각 주제에서 사용된 모든 태그를 표시하는 보고서를 만들려면 string_agg를 사용하여 각 주제별 모든 태그를 하나의 문자열로 연결할 수 있습니다.
구문
string_agg의 기본 구문은 다음과 같습니다:
STRING_AGG(expression, delimiter)
string_agg 함수는 두 가지 매개변수를 받습니다:
expression: 연결할 값.delimiter: 문자열 사이에 삽입할 구분자. 구분자는 문자열 또는 문자일 수 있습니다.
DE 쿼리에서의 사용 예시
string_agg를 사용하는 몇 가지 예제 쿼리를 살펴보겠습니다.
사이트의 모든 카테고리 나열
이 쿼리는 categories 테이블의 name과 id 필드를 하나의 문자열로 연결하며, 각 name과 id 쌍은 ’ : ’로, 각 쌍은 ', '로 구분됩니다. 쌍은 id 기준으로 정렬됩니다.
SELECT
-- STRING_AGG 함수는 'name'과 'id' 필드를 하나의 문자열로 연결합니다.
-- 각 '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를 하나의 문자열로 연결합니다. 각 태그는 쉼표와 공백으로 구분되며, 태그는 알파벳 순으로 정렬됩니다. 결과는 주제의 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'를 하나의 문자열로 연결합니다.
-- 각 태그는 쉼표와 공백으로 구분되며, 태그는 알파벳 순으로 정렬됩니다.
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 id와 titles, 각 주제에 게시한 사용자의 usernames, 그리고 각 주제에 게시한 고유 사용자 수(count of distinct users) 목록을 반환합니다. 결과는 사용자 수 기준으로 내림차순 정렬됩니다.
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 함수는 주제에 게시한 사용자의 사용자명을 하나의 문자열로 연결합니다.
-- 각 사용자명은 쉼표와 공백으로 구분되며, 사용자명은 알파벳 순으로 정렬됩니다.
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에 대한 질문이나 사용 예시가 있다면 아래에 자유롭게 공유해 주세요. 