SQL에서 Group By와 Partition By는 유사한 기능을 하지만 서로 다른 문법이다.
실제로 Group By와 Partition By의 차이점에 대해 구글링해보면, 다음과 같은 비교표를 쉽게 찾을 수 있다.
위의 표에 보충 설명을 좀 덧붙이자면,
group by는 SQL 쿼리에서 where 뒤에 위치하는데, where 조건에 의해 필터링된 데이터를 명시한 컬럼으로 그룹화한다.
반면에, partition by는 select절의 column 위치에 '윈도우 함수 over (partition by 기준 컬럼)'의 형식으로 사용된다.
Group By와 Partition By의 차이점은 위의 표에도 나와 있듯, 데이터를 그룹화하였을 때 최종적으로 반환되는 데이터의 행 개수가 줄어드느냐의 차이다.
Group By는 그룹화된 데이터 묶음을 집계함수를 이용하여 필요한 값을 계산하고, 이를 하나의 행으로 집약시킨다.
하지만, Partition By는 계산된 값을 그룹화된 데이터의 각각 행에 모두 별도의 컬럼으로 추가한다. 그래서 Partition By는 그룹에 속한 행의 개수만큼 계산된 값이 중복적으로 결과 데이터에 표현된다.
Partition By는 윈도우 함수와 함께 사용된다. 윈도우 함수는 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수인데, 기존에 Group By와 사용하던 집계함수(SUM, AVG, MAX, MIN, COUNT)를 포함하여, 순위함수(RANK, DENSE_RANK, ROW_NUMBER, 등), 행순서함수, 비율함수가 있다. 추가적인 자세한 내용은 다루지 않겠다.
오늘 이 글을 쓰게된 가장 근본적인 이유는 하나의 쿼리 내에서 Group By와 Partition By가 동시에 사용되는 케이스를 다루는 SQL 문제 때문이다. 문제에 대한 내용을 상세하게 설명하지는 않고, 문제 링크와 해당 문제에 대한 해설글 링크만 첨부하겠다.
문제링크 : 1596. The Most Frequently Ordered Products for Each Customer
해설링크 : https://velog.io/@dev_taehyun/TIL-2022.-05.-19.-MySQL-%EC%9C%88%EB%8F%84%EC%9A%B0-%ED%95%A8%EC%88%98Window-Function%EC%99%80-GROUP-BY-%EC%B2%98%EB%A6%AC-%EB%B0%A9%EB%B2%95
해설글의 중간을 보면 아래와 같은 SQL 쿼리가 있다.
SELECT
customer_id,
product_id,
product_name
FROM (
SELECT
Orders.customer_id,
Orders.product_id,
Products.product_name,
DENSE_RANK() OVER(PARTITION BY Orders.customer_id ORDER BY COUNT(Orders.order_date) DESC) AS order_amount_rank
FROM Orders
JOIN Products
USING (product_id)
GROUP BY Orders.customer_id, Orders.product_id
) AS OrderAmountRank
WHERE order_amount_rank = 1;그 중에서도 From 절에 위치하는 서브쿼리만 떼와보자.
SELECT
Orders.customer_id,
Orders.product_id,
Products.product_name,
DENSE_RANK() OVER(PARTITION BY Orders.customer_id ORDER BY COUNT(Orders.order_date) DESC) AS order_amount_rank
FROM Orders
JOIN Products
USING (product_id)
GROUP BY Orders.customer_id, Orders.product_id서브쿼리를 보면 customer_id 와 product_id로 그룹핑하고, select 절에서 DENSE_RANK를 이용해 순위를 매길 때, 다시 customer_id로 파티셔닝하고 있다. 이 부분이 어떤 방식으로 동작하는지가 이해가 안되어서 계속 붙잡고 있었다.
예를 들어, Orders와 Products가 조인된 테이블이 customer_id 와 product_id로 그룹핑되는 경우,
Group 1(사용자 A, 상품 A), Group 2(사용자 A, 상품 B), Group 3(사용자 A, 상품 C), Group 4(사용자 B, 상품 A), Group 5(사용자 B, 상품 B), Group 6(사용자 B, 상품 C), 총 6개의 그룹이 생성된다고 해보자.
이렇게 Group By에 의해 그룹핑된 데이터를 Partition By에 의해서 파티셔닝한다고 하면, 아래와 같을 것이다.
Group 1(사용자 A, 상품 A), Group 2(사용자 A, 상품 B), Group 3(사용자 A, 상품 C) -> 파티션 1
==============================================================
Group 4(사용자 B, 상품 A), Group 5(사용자 B, 상품 B), Group 6(사용자 B, 상품 C) -> 파티션 2
그럼 여기서 Partition By 뒤에 나오는 Order By에 의해 순위가 결정된다는 이야기인데, 다음과 같은 의문이 있었다.
- 각 파티션 안에서 순위를 결정하는 것인가? 즉, 파티션에 속한 데이터끼리 비교해서 순위를 매기는 것인가?
- 각 파티션 자체에 순위를 부여하는 것인가? 즉, 하나의 특정 파티션에 속한 모든 데이터는 모두 동일한 순위를 갖는 것인가?
처음에는 2번이 맞다고 생각했다.
2번이 맞다고 생각한 이유는 DENSE_RANK()와 같은 순위함수가 아닌 SUM, COUNT 같은 집계함수를 Partition By와 사용하는 경우에 특정 컬럼을 기준으로 파티셔닝되어 같은 파티션에 소속된 데이터들이 모두 동일한 집계 결과값을 갖기 때문이었다.
그런데, 실제 동작은 1번으로 동작해서 거기서 계속 헤맸다.
그러던 중 지인중에 데이터 분석관련 일을 하는 두 명의 지인에게 물어보고 내가 틀렸다는걸 알았다.
중요 포인트는 'Partition By에 의해 파티셔닝된 데이터가 어떤 함수 에 사용되는가?" 였다.
Partition By는 데이터를 주어진 기준으로 파티셔닝할 뿐인 거고, 파티셔닝된 데이터를 어떤 함수에 이용하는지에 따라 동작에 차이가 생기는 것이다.
- 집계 함수의 경우에는, 특정 파티션에 속한 데이터 전체를 갖고 집계를 하는 것이고,
- 순위 함수의 경우에는, 특정 파티션에 속한 데이터 전체에서 순위를 결정하는 것이다.
좀 더 예시로 풀어서 쓰면,
SUM은 파티셔닝된 각각의 파티션에서 총합을 구하는 것이고,
RANK는 파티셔닝된 각각의 파티션에서 ‘순위’를 매기는 것이다.
이렇게 생각하니, Partition By는 일관성있게 데이터를 파티셔닝하는 기능만 한다는 것을 이해할 수 있었다.
그리고 한 분의 지인께서 내가 고민하던 문제의 답을 알려주는 스택오버플로 글을 공유해주셨다.
스택오버플로 글 링크 : https://stackoverflow.com/questions/61298978/sql-use-dense-rank-and-group-by-together
스택오버플로 글의 답변은 다음과 같다.
스택오버플로 답변을 위 SQL 문제의 답변에 대입을 해보면,
SELECT tmp.*,
DENSE_RANK() OVER(PARTITION BY tmp.customer_id ORDER BY tmp.order_amount_rank desc) as rnk
FROM (SELECT
Orders.customer_id as customer_id,
Orders.product_id as product_id,
Products.product_name as product_name,
COUNT(Orders.order_date) AS order_amount_rank
FROM Orders
JOIN Products
USING (product_id)
GROUP BY Orders.customer_id, Orders.product_id) tmp;위와 같은 SQL을 얻을 수 있다.
스택오버플로 답변의 설명에도 나와있듯이, Group By가 먼저 적용되어서 데이터가 일단 그룹된 후, 그룹화된 데이터에서 COUNT를 이용해서 주문 개수를 뽑아내고, 뽑아낸 총 주문 개수를 정렬기준으로 이용해서 customer_id로 파티션을 나눈뒤에 각 파티션에 적용하여 순위를 매기는 것이다.
이렇게 하면 사용자별 가장 많이 주문한 제품의 정보를 갖고 올 수 있다.
그러므로, Group By와 Partition By는 동시에 사용할 수 있고, 그룹핑 또는 파티션의 기준이 되는 컬럼을 적절하게 선택하면 유용하게 써먹을 수 있다.
