[빅쿼리(BigQuery)] STRING_AGG 문자열 연결하는 집계함수

빅쿼리의 STRING_AGG함수는 MySQL의 GROUP_CONCAT과 같은 기능을 수행한다. STRING_AGG 함수는 그룹별로 null이 아닌 값들만 연결하여 하나의 문자열로 반환한다. (값들을 이어 붙일 때 null은 제외됨!!)

 

 

예시1

market_products

 

취급하는 카테고리가 다른 마켓이 여러 개(A, B, C, D)있다고 하자. 아직 마켓 C와 D는 오픈 예정인 곳이라 판매하는 카테고리가 없는 상태고, B도 최근 1개의 카테고리의 판매를 종료한 상황이라고 해보자.

-- 데이터 임시로 만들기
WITH market_products AS (
  SELECT * FROM UNNEST([
    STRUCT('A' AS market_name, 'clothes' AS category_name),
    STRUCT('A' AS market_name, 'beauty' AS category_name),
    STRUCT('A' AS market_name, 'food' AS category_name),
    STRUCT('B' AS market_name, 'electronics' AS category_name),
    STRUCT('B' AS market_name, 'furniture' AS category_name),
    STRUCT('B' AS market_name, NULL AS category_name),
    STRUCT('C' AS market_name, NULL AS category_name),  -- 마켓C는 아직 상품 카테고리가 없음
    STRUCT('C' AS market_name, NULL AS category_name),  -- 마켓C는 아직 상품 카테고리가 없음
    STRUCT('D' AS market_name, NULL AS category_name)   -- 마켓D는 아직 상품 카테고리가 없음
  ])
)

-- 마켓별 카테고리 리스트
SELECT market_name
     , STRING_AGG(DISTINCT category_name ORDER BY category_name) AS categories
FROM market_products
GROUP BY market_name
ORDER BY market_name;

 

- B에 포함된 null은 STRING_AGG 함수에서 제외되어 B는 오직 'electronics,furniture'만 출력에 포함되었다. 

- 그룹핑 기준에 C, D도 포함은 되지만, C와 D에는 모두 null만 존재하며 STRING_AGG 함수는 null을 제외한 값들만 연결하기 때문에 집계 결과 반환할 값이 없어서 null이다. (※ null 이 연결되어 null로 출력된 것이 아님을 주의하자.)

 

 

OUTPUT:

 

 

 

예시2

위 예시 데이터에 존재하는 null을 다른 문자열로 치환해서 집계 결과에 포함시키려면 COALESCE 함수를 이용할 수 있다. COALESCE 함수를 이용해 null을 'no_value' 라는 문자열로 치환한 후 STRING_AGG 함수를 적용했다. 그 결과 B에 'no_value' 가 추가되었고, C와 D에는 null대신 'no_value'가 나타난다. 

SELECT market_name
     , STRING_AGG(DISTINCT COALESCE(category_name, 'no_value') ORDER BY COALESCE(category_name, 'no_value')) AS categories
FROM market_products
GROUP BY market_name
ORDER BY market_name;

 

OUTPUT:

 

 

[참고]

COALESCE 함수 설명

https://limhm4907.tistory.com/258

[SQL] COALESCE( ) 는 합치는 함수다. (NULL이 아닌 값 중에서 처음 등장한 값으로)