[ TIL ] 2022. 05. 19. MySQL 윈도우 함수(Window Function)와 GROUP BY 처리 방법

블로그를 이전 중이라 완료되기 전까지는 벨로그에 작성할 계획입니다.
이후 모든 글은 https://weekwith.me 에 작성 예정이니 다른 글이 궁금하시다면 해당 링크를 통해 방문해주세요.

도입

오늘은 LeetCode SQL 중 Medium 난이도의 문제인 1596. The Most Frequently Ordered Products for Each Customer을 풀었다.

우선 아래와 같이 Customers , Orders , Products 테이블이 존재한다. 사실 문제를 풀 때 Customers 테이블 자체는 필요 없긴 하다.

/*
Table: Orders
Primary Key: order_id
Description: No customer will order the same product more than once in a single day.
*/
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| customer_id   | int     |
| product_id    | int     |
+---------------+---------+

/*
Table: Products
Primary Key: product_id
*/
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| product_name  | varchar |
| price         | int     |
+---------------+---------+

사용자 별로 가장 많이 주문한 제품을 찾아 반환해야 하며 이때 주문의 수량이 동일할 경우 전부 반환해야 한다.

풀이

서브쿼리를 활용한 윈도우 함수

처음에 윈도우 함수(Window Function), 그 중에서도 DENSE_RANK 함수를 사용하는 방법을 생각했다. RANK 함수가 아닌 DENSE_RANK 함수를 사용한 이유는 이후의 확장성 때문이다. RANK 함수의 경우 동일한 순위가 존재하면 그 다음의 순위는 동일한 순위의 수만큼 건너뛴 다음 수가 시작되지만 DENSE_RANK 함수는 동일한 순위와 상관없이 계속해서 이어져서 수가 시작되기 때문이다.

윈도우 함수의 표현은 대략적으로 DENSE_RANK() OVER(PARTITION BY _ ORDER BY _) 와 같습니다.

OVER 내부에 어떤 필드를 기준으로 묶어서 해당 윈도우 함수를 수행하게 할 것인지, 어떻게 그 값들을 정렬해서 윈도우 함수를 수행하게 할 것인지 먼저 설정합니다. PARTITION BY 키워드의 역할은 GROUP BY , ORDER BY 키워드의 역할은 그대로 ORDER BY 와 동일하다고 생각하면 편합니다.

이외에도 RANGE 부분을 따로 설정할 수 있는데 이에 대한 내용이나 윈도우 함수 자체에 대한 자세한 내용은 MySQL 공식 문서 중 윈도우 함수 사용법 및 윈도우 함수의 프레임(Frame)에서 추가적으로 확인 가능합니다.

쉽게 윈도우 함수는 행과 행 사이의 관계를 표현하기 위해 사용한다고 생각하면 편합니다. 위 문제에서 결국 수량이 가장 많은 제품이 여러 개일 경우 해당 제품을 모두 반환해야 하기 때문에 행과 행 사이의 관계 중 순위를 매겨 해결하기 위해 DENSE_RANK 윈도우 함수를 사용하기로 결정한 것입니다.

확장성을 고려한 해결 방법은 좋았는데 문제는 PARTITION BY 부분이었다. 윈도우 함수에서 문제를 해결하기 위해서는 파티션을 설정해야 하기 때문에 GROUP BY 구를 같이 사용하지 못하는 걸로 생각했다. 그래서 아래와 같이 복잡하게 서브쿼리(Subquery)를 활용했다.

SELECT
	...
FROM Orders
JOIN (
    SELECT
        customer_id,
        product_id,
        DENSE_RANK() OVER(PARTITION BY customer_id ORDER BY order_amount DESC) AS order_amount_rank
    FROM (
        SELECT
            customer_id,
            product_id,
            COUNT(order_date) AS order_amount
        FROM Orders
        GROUP BY customer_id, product_id
    ) AS OrderAmount
) AS OrderAmountRank
ON (
    OrderAmountRank.order_amount_rank = 1
    AND
    Orders.customer_id = OrderAmountRank.customer_id
    AND
    Orders.product_id = OrderAmountRank.product_id
)
...
GROUP BY Orders.customer_id, Orders.product_id;

아무래도 중첩된 서브쿼리로 인해 쿼리의 가독성이 떨어지는 것은 물론 성능도 그다지 좋지 않을 수밖에 없다.

그런데 다른 사람의 풀이를 보니 훨씬 간단하게 해결했다.

윈도우 함수와 GROUP BY 를 함께 사용

다른 사람의 풀이는 아래와 같았다. GROUP BY 구를 사용하고 나서 DENSE_RANK 함수 내에 PARTITION BY 및 COUNT 함수를 내부적으로 사용한 ORDER BY 구를 활용하여 문제를 풀었다.

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;

GROUP BY 구와 윈도우 함수를 무조건적으로 함께 사용하지 못한다고 잘못 생각해서 더 간단하고 좋은 풀이를 떠올리지 못한 것이다.

GROUP BY 구의 기준이 되는 필드가 결국 Orders 테이블의 customer_id 및 product_id 필드다. 쿼리의 실행 순서는 GROUP BY 구가 먼저 실행된 뒤에 윈도우 함수가 실행되기 때문에 그룹핑된 필드를 기준으로만 PARTITION BY 구를 사용하면 문제가 발생하지 않는다.

또한 표준 SQL의 경우 PARTITION BY 및 ORDER BY 구에 모두 컬럼명 밖에 오지 못한다. 하지만 MySQL의 경우 GROUP BY 와 마찬가지로 조금 더 확장하여 해당 부분에 컬럼이 아닌 것(Noncolumn)을 사용해도 문제되지 않는다. 따라서 COUNT 함수를 윈도우 함수 ORDER BY 구의 기준으로 사용한 것이다.

기타

확장된 GROUP BY

표준 SQL에서는 오로지 컬럼명이 GROUP BY 의 기준 또는 윈도우 함수 내부에 와야 하지만 MySQL은 이에 확장되어 컬럼이 아닌 것을 사용해도 괜찮다는 이야기를 했다.

예시와 함께 설명하자면 아래와 같이 GROUP BY 구 부분에 SELECT 구 부분에서 지정한 별칭( AS )을 사용해도 문제 없이 된다.

SELECT customer_id AS user_id
FROM Orders
GROUP BY user_id

또한 아래와 같이 GROUP BY 구의 대상 자체에 함수를 사용할 수도 있다. YEAR 함수를 사용하여 연도별로 그룹핑해 해당 연도에 주문한 고객의 수를 구하는 것이다.

SELECT COUNT(DISTINCT customer_id) AS customer_cnt
FROM Orders
GROUP BY YEAR(order_date)

더 자세한 내용은 MySQL 공식 문서 중 GROUP BY 구를 다루는 방법을 확인하면 된다. GROUP BY 구가 처리되는 방법에 대한 옵션이라 할 수 있는 ONLY_FULL_GROUP_BY 에 대한 설명을 확인할 수 있다.

ONLY_FULL_GROUP_BY 옵션의 경우 MySQL에서 제공하는 SQL 모드(SQL Mode)에 대한 부분으로 더 자세한 내용은 MySQL 공식 문서 중 서버 SQL 모드를 통해 확인할 수 있다.

결론

쿼리가 실행되는 순서는 결국 GROUP BY 구가 먼저 실행되고 윈도우 함수가 이후에 실행되기 때문에 GROUP BY 구에서 그룹핑한 필드를 위반하지 않게 윈도우 함수 내에서 PARTITION BY 구를 사용하면 정상적으로 사용할 수 있다는 걸 잊지 말자.

추가적으로 GROUP BY 구와 같이 MySQL은 윈도우 함수에서도 표준 SQL 문법을 확장하여 컬럼이 아닌 것(Noncolumn)까지 OVER 내부의 PARTITION BY 또는 ORDER BY 등에 사용할 수 있게 하기 때문에 적절히 활용하여 쿼리를 훨씬 간단하고 성능 좋게 만들 수 있다는 걸 잊지 말자.