MySQL 과 PostgreSQL GroupBy 차이
최근 사이드 프로젝트를 진행하면서 MySQL을 사용하고 있는데요. 당연하게 PostgreSQL을 주로 사용하다보니 이런 식으로 groupBy를 할 수 없어라고 생각하고 쿼리를 작게 나누었었는데, 테스트를 해보다 보니 MySQL에서는 문제없이 동작하는 것을 보고 차이를 정리해놓으려고 합니다.
예제
테이블
간단하게 테이블 구조를 확인해보면 아래와 같습니다.
Slot이 pictureId를 nullable하게 가지고 있고, PictureTagRelation이 pictureId와 pictureTagId를 가지고 있는 구조입니다.
쿼리
원했던 쿼리는 아래와 같습니다. 앨범에 존재하는 모든 Slot을 조회하는데 Slot이 Picture를 가지고 있다면, Picture 데이터를 가져오는 구조였습니다. 이때, Picture 데이터를 가져올 때, PictureTagRelation과 PictureTag와 함께 조인해 사진에 유효한 태그의 이름들만 뽑아오길 원했습니다.
select s1_0.id,
s1_0.layout,
s1_0.location,
s1_0.page,
p1_0.id,
p1_0.path,
p1_0.content,
p1_0.pictured_at,
group_concat(p3_0.name)
from slot s1_0
left join
picture p1_0
on s1_0.picture_id = p1_0.id
left join
picture_tag_relation p2_0
on p2_0.picture_id = p1_0.id
and p2_0.deleted_at is null
left join
picture_tag p3_0
on p2_0.tag_id = p3_0.id
and p3_0.deleted_at is null
where s1_0.album_id = 1
and s1_0.deleted_at is null
group by s1_0.id
order by s1_0.page
위 쿼리는 정상동작할까요?
PostgreSQL
PostgreSQL에서 위 쿼리를 실행해보면 아래와 같은 에러가 발생합니다.
ERROR: column "p1_0.id" must appear in the GROUP BY clause or be used in an aggregate function
pg에서는 select하는 칼럼이 group by 절안에 포함되거나 집계함수를 통해 포함되어야 하는데, 그렇지 않아서 발생하는 에러입니다.
그럼 왜 s1_0.layout 에서는 예외가 발생하지 않았을까요?
아래와 같이 pg 문서에서는 안내하고 있습니다.
it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.
간단하게 "select 절에 집계 함수 또는 그룹화한 칼럼들에 대해 함수적 종속을 갖는 그룹화되지 않은 칼럼을 제외한 칼럼이 언급될 수 없다. 왜냐하면, 그룹화되지 않은 칼럼이 하나 이상의 값을 반환할 수 있기 때문이다."를 의미합니다.
여기서 이야기하는 함수적 종속은 다음과 같습니다.
어떤 테이블 R 에 존재하는 필드들의 부분집합을 각각 X와 Y라고 할 때, X의 한 값이 Y에 속한 오직 하나의 값에만 사상될 경우에 "Y는 X에 함수 종속 (Y is functionally dependent on X)"이라고 하며, X→Y라고 표기한다.
즉 PK인 slot.id 에 대해 slot테이블의 모든 칼럼들은 하나만 존재하기 때문에 함수적 종속이 존재하기 때문에 slot 테이블에 대한 조회에 대해서는 문제가 발생하지 않습니다.
PostgreSQL에서는 함수적 종속성을 어디까지 판별해줄까요?
PostgreSQL - Functional Dependency에는 아래와 같이 group by 내에 pk가 존재할 때만 함수적 종속성을 인지한다고 설명하고 있습니다.
PostgreSQL recognizes functional dependency (allowing columns to be omitted from GROUP BY) only when a table's primary key is included in the GROUP BY list.
그럼 어떻게 해결해야할까요?
1. group by 절에 p1_0.id를 추가한다.
위에서 이야기한 것 처럼 pk를 기준으로 함수적 종속성을 판별하기 때문에 picture의 PK인 p1_0.id를 group by 절에 추가해주면 쿼리가 정상적으로 수행됩니다.
select s1_0.id,
s1_0.layout,
s1_0.location,
s1_0.page,
p1_0.id,
p1_0.path,
p1_0.content,
p1_0.pictured_at,
string_agg(p3_0.name, ',')
from slot s1_0
left join
picture p1_0
on s1_0.picture_id = p1_0.id
left join
picture_tag_relation p2_0
on p2_0.picture_id = p1_0.id
and p2_0.deleted_at is null
left join
picture_tag p3_0
on p2_0.tag_id = p3_0.id
and p3_0.deleted_at is null
where s1_0.album_id = 1
and s1_0.deleted_at is null
group by s1_0.id, p1_0.id
order by s1_0.page;
2. 쿼리를 분리해 애플리케이션 로직을 통해 결과를 합친다.
아래와 같이 slot 조회 쿼리와 사진 상세 정보 조회 쿼리를 쪼개어 조회하고 어플리케이션 로직에서 합쳐 사용하는 방법이 있습니다.
select
s1_0.id,
s1_0.album_id,
s1_0.created_at,
s1_0.deleted_at,
s1_0.layout,
s1_0.location,
s1_0.page,
s1_0.picture_id,
s1_0.updated_at
from
slot s1_0
where
s1_0.album_id=?
and s1_0.deleted_at is null
order by
s1_0.page
select
p1_0.id,
p1_0.content,
p1_0.path,
p1_0.pictured_at,
group_concat(p3_0.name)
from
picture p1_0
left join
picture_tag_relation p2_0
on p1_0.id=p2_0.picture_id
and p2_0.deleted_at is null
left join
picture_tag p3_0
on p2_0.tag_id=p3_0.id
and p3_0.deleted_at is null
where
p1_0.id in (?,?,?)
and p1_0.deleted_at is null
group by
p1_0.id3. distinct on과 subquery를 활용한다.
아래와 같이 distinct on과 subquery를 사용해 해결할 수 있습니다. distinct on 은 MySQL 5.7.5 이전 ONLY_FULL_GROUP_BY 가 꺼져있는 경우에 사용되던 group by와 유사하게 동작합니다. 내용은 아래 MySQL 설명과 함께 다루겠습니다.
select distinct on(slot.id, slot.page)
slot.id,
slot.layout,
slot.location,
slot.page,
slot.picture_id,
pictureAlias.path,
pictureAlias.content,
pictureAlias.pictured_at,
pictureAlias.tags
from slot
left join (select picture.id,
picture.path,
picture.content,
picture.pictured_at,
string_agg(pictureTag.name,',') as tags
from picture
left join picture_tag_relation pictureTagRelation
on picture.id = pictureTagRelation.picture_id and
pictureTagRelation.deleted_at is null
left join picture_tag pictureTag
on pictureTagRelation.tag_id = pictureTag.id and pictureTag.deleted_at is null
where picture.deleted_at is null
group by picture.id) as pictureAlias on slot.picture_id = pictureAlias.id
where slot.album_id = 1
and slot.deleted_at is null
order by slot.page asc;MySQL
MySQL에서 첫 쿼리를 수행해 보면 정상동작하는 걸 볼 수 있습니다.
MySQL 5.7.5 버전 이후부터는 ONLY_FULL_GROUP_BY라는 sql mode가 기본적으로 활성화되어 있습니다. 이 옵션은 함수적 종속을 탐지여부에 대한 옵션으로 활성화되어있다면, group by 사용 시 집계함수를 사용하지 않더라도 함수적 종속이 탐지되는 항목에 대해선 조회가 가능합니다.
ONLY_FULL_GROUP_BY 옵션이 꺼져있다면, 함수적 종속을 판단하지 않고 조회하기 때문에 값이 여러 개가 되는 것에 관계없이 첫 번째 값이 반환되게 됩니다.
MySQL의 함수적 종속 탐지 예제는 다음 문서에서 확인할 수 있습니다. 함수적 종속 탐지 예제
위 예시에 적용해 보면, 아래와 같은 이유로 함수적 종속이 탐지되어 조회가 가능하고, tagName에 대해선 집계함수를 사용하고 있기 때문에 쿼리가 정상적으로 수행되게 됩니다.
{slot.id} -> {slot.*}
{slot.picture_id} -> {picture.id} -> {picture.*}
결론
같은 쿼리더라도 함수적 종속성을 어디까지 판별하느냐에 따라 쿼리 수행이 달라질 수 있습니다. PostgreSQL의 경우 group by 절의 PK가 포함되어 있을 때만 함수적 종속성을 판별하고, MySQL에서는 join, unique not null 등 까지 함수적 종속성을 판단하게 됩니다.
참고
MySQL-group-by
PostgreSQL-group-by
PostgreSQL-distinct-on
PostgreSQL - Functional Dependency