MySQL GroupBy 사실과 오해 (feat: only_full_group_by, window function)

서론

---

진행중인 프로젝트에서 쿼리에 Group By 를 사용해야하는 기능이 있었다.

 

 

학부생 때, SQL 문제를 풀 때 Group By를 사용하면 Select 절에는 Group By의 기준 값이나, 집계함수만 사용할 수 있는줄 알았는데 MySQL 5.7 이후로는 사용 가능 여부를 설정이 가능했다 (하지만 사용시 주의해야 할 점이있다).

 

 

이 글에서는 해당 내용에 대한 간단한 설명과 내가 group by로 해결하지 못하고 Window Function으로 해결한 간단한 쿼리를 정리할 예정이다.

 

 

본론

---

GroupBy의 사실과 오해

 

간단한 예시 쿼리로 한번 설명해보자

select category, max(price), 
from item
group by category

 

 

아이템을 카테고으로 그룹화 하고 카테고리의 이름, 해당 카테고리중 가장 비싼 가격 을 반환하는 익숙한 쿼리이다.

 

 

 

select category, max(price), name
from item
group by category

 

해당 쿼리는 어떠한가? 오류가 나는 쿼리라고 생각할 수 있다, 실제로 대부분의 RDB, MySQL에서도 오류가 날 수 있는 쿼리이다.

 

 

이 말은 해당 쿼리가 정상적으로 작동이 가능할 수도 있다는 것이다. 어떻게 가능할까?  여기에는 2가지의 방법이 있다.

 

 

1. only_full_group_by

 

MySQL 5.7 부터 도입된 개념이다. 해당 설정 값을 on/off가 가능한데 off일 경우 2번째 예시 쿼리처럼 select절에 제한 없이 어떤 필드든 사용할 수 있고 on인 경우는 흔하게 알듯이 group by, 집계 함수만 select 절에서 사용할 수 있다.

 

 

2. any_value

 

또한 any_value 라는 명령어를 사용해서도 select 절에 group by와 관련없는 필드를 사용이 가능한데

select category, max(price), any_value(name)
from item
group by category

 

이런식으로 사용이 가능하다. 당연히 only_full_group_by가 on으로 되어있어도 any_value로 사용이 가능하다.

 

 

3.관련 없는 필드 사용시 주의해야 할 점

 

자 그러면 Group By를 사용한 쿼리에서 Select 절에서 Group By와 관련 없는 필드를 사용했다고 해보자

select category, max(price), name
from item
group by category

 

이러한 쿼리를 작성하고 이렇게 기대할 수 있다.

max(price) 했으니까 name도 알잘딱깔쎈으로 최고 가격을 가진 item 의 이름이 나오겠지? 히힣!

 

 

그렇지 않다.

 

 

나도 처음에는 저러한 느낌으로 쿼리를 실행했는데 내가 원하는 제일 최근 컬럼의 필드가 맵핑되어 대충 첫번째 컬럼이 선택되나 보다! 라고 생각하다가 불안해서 공식 문서를 찾아보았다.

This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group,
so unless they are the same, the values chosen are nondeterministic, which is probably not what you want.

 

 

이렇게 MySQL이 무작위로 값을 고르는 형태라고 나와있다, 해당 사실을 유념하고 이 기능을 사용하자.

 

 

 

window function (feat: row_number)

 

내가 group by를 사용했던 이유는 그룹화 시킨다음 그 그룹중 가장 최근 데이터를 모두 조회하기 위해서이다. (좀 더 복잡하지만 group by를 사용해야하는 부분은)

 

 

그래서 처음 생각했던 방식은

select *
from item
where (category, craeted_at) in 
(
    select category, max(created_at)
    from item
    group by category
)

이런 형태였다.

 

 

하지만 해당 방식에는 크나큰 문제가 있는데, created_at이 초단위 까지만 시간을 저장하고 있다는 것이다..

 

 

즉, 만약 1초안에 데이터가 여러개가 몰리면 메인 쿼리에서 해당 데이터들을 모두 조회하게 된다.

 

 

어플리케이션 내부로 가져와서 중복을 처리하기에는 페이징이나 다른 고려사항들 때문에 DB에서 처리하는 것이 제일 깔끔했다.

 

 

그래서 window function을 사용하기로 했다.

select *
from (
    select name, price,  
        row_number() over (partition by category order by item.created_at asc) item_row
    from item
)
where item_row = 1 

해당 쿼리를 간단히 설명하자면

1. partition by category -> group by와 유사하게 그룹화된 테이블을 만든다.
2. order by item.created_at -> 해당 그룹을 정렬한다.
3. row_number() -> 그 그룹에서 지금 컬럼이 몇번째인지 순서를 새로운 필드로 만든다.
4. where item_row = 1 -> 그렇게 그룹에서 제일 1번째로 있는 데이터만 필터링한다.

 

 

이렇게 하면 중복이 생기지 않고 category중 제일 최근에 만들어진 데이터만 1개씩 가져오는 것을 보장할 수 있다.

 

 

물론 window function은 MySQL 8.0 이상부터 지원하기 때문에 그 이전 버전은 다른 방식을 찾아야 한다.

 

 

다른 프로젝트에서도 해당 기능을 도입해야하는데 거기는 5.7을 사용하고 있다, 해결책이 뭐냐고?  여러가지가 있겠지만 우리는 쿼리를 2번 나눠서 쏘기로 하였다.  

 

1. 대표 데이터들 id만 가져오는 쿼리 

2. 해당 데이터 id 로 상세조회 쿼리

3. 어플리케이션에서 조합

 

 

결론

---

 

오랜만에 Mongo에서 벗어나 MySQL을 했더니 재밌었다, 오개념을 바로 잡고 익숙하지 않은 window function도 조금은 더 친해진 것 같다.

 

 

하지만 window function 없이도 해당 문제를 해결할 방법을 찾아야 한다. 좋은 기능이지만 특정 db에, 특정 버전에 의존적이게 된다는 단점이 있는 것 같다.. 실무에서는 최대한 지양하고 꼭 필요할 때만 사용할 카드로 남겨둘 것 같다..!