MySQL - GROUP_CONCAT 여러 레코드의 값을 하나로 합치기

GROUP_CONCAT 함수는 그룹 함수로써, 해당 그룹의 지정된 컬럼 중에서 NULL이 아닌 값들을 하나로 합쳐줍니다.

사용방법은 다음과 같습니다.

SELECT dept, GROUP_CONCAT(name) as names
FROM mushrooms
GROUP BY dept

GROUP BY를 이용해 dept가 같은 것끼리 그룹핑한 다음, GROUP_CONCAT(name)으로 name 컬럼의 값들을 하나로 만들어줍니다. 결과는 다음과 같이 나오게 됩니다.

dept	names
영업	느타리, 영지
개발	송이, 새송이, 양송이

기본 구분자는 콤마(,) 이므로 변경을 원한다면 GROUP_CONCAT(name SEPARATOR ' - ') 와 같이 구분자를 명시해줄 수 있습니다. 

DISTINCT를 걸어주면 중복값은 제거됩니다. GROUP_CONCAT(DISTINCT name SEPARATOR '-')

정렬을 원한다면 ORDER BY를 사용합니다. GROUP_CONCAT(DISTINCT name ORDER BY id SEPARATOR '-')

 

1. 발단

 

저는 과일농사를 짓고 있습니다. 매일 아침 우리 집 앞으로 전국 각지에서 온 트럭들이 모여듭니다. 품질 좋은 과일을 받아가기 위해 줄을 서서 기다리는 겁니다. 저에게는 다음과 같은 트럭 테이블과 과일 테이블이 있습니다.

ID	Name	Destination
1	EuroTruck	Seoul
2	4DollarTruck	Busan

ID	Name	price
1	Apple	100
2	Banana	200
3	Strawberry	300
4	Pear	400

저는 문득 각 트럭이 어떤 과일들을 가져가는지 궁금해졌고 아래와 같은 맵핑 테이블을 만들었습니다.

Truck	Fruit
1	1
1	2
1	3
2	1
2	3
2	4

이것으로 만족하고 열심히 농사를 짓고 있었습니다. 그런데 어느날 전국 과일 트럭 협회에서 협회에 소속된 트럭들이 가져가는 과일의 명단을 정리해서 달라는 요청을 받았습니다.

ID	Name	Destination	Fruits
1	EuroTruck	Seoul	Apple, Banana, Strawberry
2	4DollarTruck	Busan	Apple, Strawberry, Pear

우리는 이런 결과를 얻을 수 있는 쿼리를 작성해야 합니다. 물론 데이터만 뽑아서 프로그래밍 언어로 가공을 할 수도 있지만 쿼리만으로 문제를 해결하려고 합니다.

 

2. 전개

 

SELECT 트럭.*, GROUP_CONCAT(과일.name) fruits
FROM 트럭 
LEFT JOIN 트럭과일 ON 트럭.id = 트럭과일.truckid 
LEFT JOIN 과일 ON 트럭과일.fruitid = 과일.id
GROUP BY 트럭.id
HAVING 트럭.id IN (1, 2)

위 쿼리는 트럭과일 테이블을 매개로 트럭 테이블과 과일 테이블을 조인하고, 트럭에 배정된 과일들을 fruits라는 컬럼으로 만들어줍니다. 이렇게 하면 우리가 원하는 결과를 얻을 수 있습니다.

 

3. 위기

 

문제가 생겼습니다. 트럭 협회에서는 위 쿼리에 조건절을 추가하는 방식으로 검색 기능을 제공하고 있습니다. 사용자가 'apple'을 입력하면 where fruit.name = 'apple' 이라는 where 절을 만들어 붙여준다는 거죠. 그런데 위와 같은 쿼리로는 검색이 원활하게 되지 않는다는 겁니다.

fruits는 별칭(alias)이므로 출력할 수는 있지만, where절에서 참조하는 게 불가능하기 때문입니다. 협회에서는 요구사항을 들어주지 않으면 소속 트럭들의 계약을 해지하겠다며 협박을 합니다. 어쩔 수 없이 새로운 방법을 강구해야하는 상황입니다.

 

4. 절정

 

고민 끝에 GROUP_CONCAT한 결과를 다시 JOIN해주는 방식으로 쿼리를 변경했습니다. 

SELECT 트럭.*, 과일들.fruits
FROM 트럭
LEFT JOIN (
           SELECT 트럭과일.트럭_id, GROUP_CONCAT(과일.name) fruits
           FROM 트럭과일
           LEFT JOIN 과일 ON 트럭과일.과일_id = 과일.id
           GROUP BY 트럭과일.truck_id
          ) 과일들 ON 트럭.id = 과일들.트럭_id

이렇게 하면, 과일들이라는 alias를 부여할 수 있고 WHERE절에서 특정 키워드를 포함하는 지 여부를 검사할 수 있게 됩니다.

WHERE 과일들.fruits LIKE '%apple%'

 

5. 결말

 

일단 위기는 넘겼지만 이게 맞는 방법인가 하는 의문은 여전히 남아있습니다.

아무것도 없는 빈 도화지에 그림을 그리는 게 아니라,

누군가가 그려놓은 그림 위에 덧칠을 계속 하다보면

화풍도 맞춰가야 하고 이런 저런 제약 사항도 생기고 무엇보다도 사고가 기존의 소스 안에 갇히는 느낌이 있어요.

그래서 이런 질문을 자주 하게 되는 것 같아요.

"이게 정말 맞는 걸까?"

어쨌든 문제는 해결을 했으니 이 건은 덮어두도록 하겠습니다.