[Mysql] 여러행의 데이터를 한 행의 데이터로 합치기 "GROUP_CONCAT"

 

 

오늘은 Mysql에서 여러행의 데이터를 한 행의 데이터로 합치는 쿼리에 대해서 알아보자.

 

우선 각각의 테이블에 대해서 대략적으로 살펴보자.

 

 

blanc_story_log

 

 

blanc_keyword_news

 

 

위 두 테이블은 간단하게 설명하자면,

 

blanc_story_log 테이블은 뉴스의 정보가 담긴다고 보면 되고, ( "action" 컬럼에 "view" or "create" 존재)

 

blanc_keyword_news 테이블은 해당뉴스의 키워드가 담긴다. (같은 뉴스에 키워드 최대 5개)

 

 

이런구조에서 두 테이블을 조인해서 뉴스가 생성 되었을때,

 

뉴스의 키워드 정보를 한 개의 uuid로우에 여러 키워드를 담고 싶었다.

 

 

우선 정답을 보고 쿼리를 하나씩 파헤쳐 보자.

 

 

SELECT t1.uuid AS id_post,
 t1.user_uuid AS id_usr,
 t1.news_uuid AS cts, 
t1.press_uuid AS id_press, 
GROUP_CONCAT(DISTINCT t2.keyword SEPARATOR ', ') AS keywords, 
DATE_FORMAT(t1.created_at, '%Y%m%d%H') AS day_str 
FROM blanc_user_story_log t1 JOIN blanc_keyword_by_news t2 ON t1.news_uuid = t2.uuid 
WHERE t1.action = 'create' 
GROUP BY t1.uuid, t1.user_uuid, t1.news_uuid, t1.press_uuid, DATE_FORMAT(t1.created_at, '%Y%m%d%H');

 

 

우선 SELECT로 "id_post", "id_usr", "cts", "id_press" 4 개의 컬럼을 Alias해서 갖고오고

 

 

GROUP_CONCAT()을 활용하여 아래에서 GROUPING한 키워드를 한개의 로우로 합쳐셔 갖고 온다.

 

GROUP_CONCAT() 안에 있는 조건에 대해서 보면,

 

DISTINCT는 말그대로 혹시 모를 중복된 키워드가 들어옴을 방지하기 위해 사용했고,

 

SEPARATOR는 각각의 키워드의 구분기호로 보면 된다. 

 

요구사항이 ", " 이런식으로 키워드를 띄워야 했어서 사용했다.

 

 

 

DATE_FORMAT은 아래 사진처럼 시간 데이터가 오고있어서, 

 

기존 created_at 컬럼

 

'%Y%m%d%H' 이 형태대로 "년 월 일 시간" 네가지 데이터만 뽑았다.

 

그 다음 두 테이블을 t1의 target_uuid 와 t2의 uuid로 INNER JOIN을 했다.

 

그리고 WHERE절 에는 t1의 action이 "create"일때로 조건을 걸어준다.

 

 

마지막으로 GROUP BY를 사용하여 t2의 키워드 컬럼을 제외한 t1의 모든 컬럼을 그룹핑해준다.

 

한가지 컬럼이라도 그룹핑을 하지 않게 되면 다음과 같은 에러문구가 발생한다.

 

 

Error (모자이크 부분은 DB이름이다.)

 

 

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'next_newming_statistics.t1.uuid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

GROUP BY를 사용하는 경우, SELECT할 수 있는 컬럼은 GROUP BY에 나열된 컬럼과 SUM(), COUNT() 같은 집계 함수(Aggregation Function)으로 한정 sql 표준 문법은 group by 사용시 select 의 칼럼중 집계함수에 쓰이는 것을 제외한 모든 칼럼을 기입해야 한다.

 

이 에러의 경우 세가지 방법이 있는데,

 

 

첫째 내 쿼리대로 관련 컬럼을 모두 그룹핑을 해준다.

 

둘째 비 집계 칼럼에 ANY_VALUE() 함수 사용한다.

 

셋째 mysql 설정 변경 (sql_mode 에서 ONLY_FULL_GROUP_BY 설정을 뺀다.)

 

 

나는 협업 업체에 쿼리문을 줘야되는 상황이여서 1번 방법을 사용해서 관련 컬럼을 모두 작성 하였다.

 

이제 쿼리를 돌린 데이터를 확인해보자.

 

 

 

 

쿼리 결과가 내가 원하는 대로 keywords가 한개의 로우에 합쳐져서 나오는 걸 볼 수 있다.