글을 작성하게 된 계기
회사에서 GROUP BY를 사용하다가 에러가 발생했고, 알게 된 내용을 정리하기 위해 글을 작성하게 되었습니다.
사용한 데이터베이스는 MySQL 입니다.
1. 문제 상황
회사에서 가맹점 데이터를 조회할 때, GROUP BY를 사용해 특정 칼럼 값을 기준 으로 데이터를 그룹화 하고 있었습니다. 그랬더니 다음과 같은 에러를 만났는데요, 이는 name과 city 칼럼이 한 bizNo 그룹 안에 여러개 존재할 수 있기 때문입니다.
1
2
3
SELECT biz_no, name, city
FROM merchants
GROUP BY biz_no;
1
Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'merchants.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
예를 들어, bizNo가 12345678인 스타벅스는 Seoul과 Busan 두 도시를 가지고 있습니다. GROUP BY를 사용해 하나의 도시 만 표시하려면 반드시 어떤 도시를 대표로 보여줄지 정해야 하는 것이죠.
1
2
3
4
5
6
7
8
9
------------------------------------------------------
| id | biz_no | country | city | name |
|----|----------|---------|-----------|--------------|
| 1 | 12345678 | Korea | Seoul | 스타벅스 |
| 2 | 12345678 | Korea | Busan | 스타벅스 |
| 3 | 87654321 | Japan | Tokyo | 유니클로 |
| 4 | 87654321 | Japan | Osaka | 유니클로 |
| 5 | 11223344 | USA | New York | 애플스토어 |
------------------------------------------------------
GROUP BY가 올바르게 사용 됐다면 이렇게 나타나겠죠?
1
2
3
4
5
6
7
-------------------------------------------
| bizNo | merchantName | cities |
|----------|---------------|--------------|
| 12345678 | 스타벅스 | Seoul, Busan | # SEOUL, BUSAN이 하나의 칼럼에 묶임
| 87654321 | 유니클로 | Tokyo, Osaka | # TOKYO, OSAKA가 하나의 칼럼에 묶임
| 11223344 | 애플스토어 | New York | # NEW YORK이 하나의 칼럼에 묶임
-------------------------------------------
2. 문제 해결
이를 해결하기 위해서는 그룹마다 여러 개의 데이터가 존재하는 칼럼에 대해 집계 함수(Aggregate Function) 를 사용하여, 어떤 값을 대표로 표시할지 명확히 지정해야 합니다.
예를 들어, 가맹점 아이디인 bizNo를 기준으로 그룹화하면, 각 가맹점마다 여러 도시가 존재할 수 있기 때문에 이 도시들을 하나로 묶어 명확하게 표현하는 것입니다. 이를 위해 GROUP_CONCAT 등을 사용해 같은 그룹에 속한 여러 값들을 하나의 문자열 로 묶을 수 있습니다. 즉, 비집계 칼럼이 GROUP BY에 포함되지 않을 경우, 집계 함수로 그룹 내 데이터를 명확히 정리해야만 SQL이 어떤 값을 반환할지 결정할 수 있는 것이죠.
1
2
3
SELECT bizNo, name, GROUP_CONCAT(city) AS cities
FROM merchants
GROUP BY biz_no, name;
다른 방법으로 ONLY_FULL_GROUP_BY 설정을 끌 수도 있는데요, 그러나 이는 데이터 신뢰성/정확성을 보장할 수 없으므로 권장하지는 않습니다.
1
2
3
4
5
-- 현재 세션에만 적용(재접속시 원상복구)
SET @@sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '');
-- 전역적으로 적용(영구 적용, 재접속 후에도 유지됨)
SET GLOBAL sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '');
3. 정리
간단한(?) SQL 관련 문제였는데, GROUP BY를 잘 쓰지 않다보니 처음에 조금 의아했습니다. 원리를 알고 나니 또 재미있었는데, JPA를 사용하는 것 못지 않게 쿼리를 잘 만드는 것도 중요한 것 같습니다.
사실은 JDBC로 작성된 수 백 줄의 레거시 코드를 잘 파악하기 위해서요.