[MySQL] GROUP_CONCAT으로 grouping 한 결과를 한줄로 출력하기

1. 알고자 하는 것

회사에서 특정 조건으로 데이터를 2개의 그룹으로 그룹핑해서 추출하고, 이를 각각 일괄적으로 업데이트 해야하는 일이 있었다.

즉, 다음과 같은 형태이다.

 

이 때, 여러 데이터에 대해 콤마(,)로 구분지어 입력값을 넣으면 일괄적으로 업데이트를 처리할 수 있도록 팀 내에서 유틸성 기능을 Admin Page에서 제공하고 있었고, 이로 인해 특정 컬럼으로 그룹핑 된 n개의 데이터를 콤마로 구분지어 추출해야 했다.

이 때 유용하게 활용했던 GROUP_CONCAT 기능을 알아본다.

 

• GROUP_CONCAT

 

 

 

2. 알게된 것

• 사용자(member)는 여러 물건(product)을 구매할 수 있다.
• 사용자 별로 구매한 물건을 그룹핑해 콤마로 구분지어 출력해보자.
• member 테이블의 데이터는 다음과 같다.

• product 테이블의 데이터는 다음과 같다.

• 우리가 원하는 출력 형태는 다음과 같이 각 사용자별로 구매한 상품 목록을 콤마로 구분지어 나열하는 것이다.

• 단순하게 다음과 같은 쿼리를 날리면 오류가 발생한다.

SELECT member_id, name FROM product GROUP BY member_id;

/**
[42000][1055] Expression #2 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 'study.product.name' which is not functionally dependent on 
columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
**/

 

• 집계될 수 없는(nonaggregated) name 컬럼을 SELECT 절에 넣었기 때문에 발생한 오류이다.
• COUNT(name)과 같이 name에 대해 집계하는 함수가 아닌 단순히 name 컬럼 자체를 SELECT 절에 넣었으므로, GROUP BY를 통해 집계할 수 없는 쿼리이기 때문이다.
• member_id 기준으로 그룹핑 된 name 목록을 특정 separator로 나열하고자 한다면, GROUP_CONCAT 함수를 사용한다.

SELECT member_id, GROUP_CONCAT(name) FROM product GROUP BY member_id;

 

• separator 미지정 시 기본 separator는 콤마(,)로 지정되며, 결과는 다음과 같이 원하던 대로 구매한 상품 목록이 콤마로 구분지어 나열된다.

 

• 특정 separator를 사용하고 싶으면 GROUP_CONCAT(column SEPARATOR '문자') 형태로 지정할 수 있다.

SELECT member_id, GROUP_CONCAT(name SEPARATOR '|') FROM product GROUP BY member_id;

 

 

• 중복을 제거해 출력하고 싶으면 GROUP_CONCAT(DISTINCT column) 형태로 제거할 수 있다.
• 정렬해 출력하고 싶으면 GROUP_CONCAT(column ORDER BY column) 형태로 정렬할 수 있다.

 

 

3. 정리

• GROUP BY를 통해 그룹핑 된 결과를 특정 separator를 지정해 나열하고자 할 때 GROUP_CONCAT을 사용할 수 있다.
• 미지정 시 기본 separator는 콤마(,)이며, 특정 separator 지정 시 GROUP_CONCAT(column SEPARATOR '문자') 형태로 지정한다.
• 중복을 제거해 출력하고 싶으면 GROUP_CONCAT(DISTINCT column) 형태로 제거할 수 있다.
• 정렬해 출력하고 싶으면 GROUP_CONCAT(column ORDER BY column) 형태로 정렬할 수 있다.