블로그를 이전 중이라 완료되기 전까지는 벨로그에 작성할 계획입니다.
이후 모든 글은 https://weekwith.me 에 작성 예정이니 다른 글이 궁금하시다면 해당 링크를 통해 방문해주세요.
도입
오늘은 LeetCode SQL 중 Medium 난이도의 문제인 1709. Biggest Window Between Visits을 풀었다.
우선 아래와 같이 UserVisits 테이블이 존재한다.
/*
Table: UserVisits
Description: No Primary Key
*/
+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id | int |
| visit_date | date |
+-------------+------+사용자 별로 가장 많이 주문한 제품을 찾아 반환해야 하며 이때 주문의 수량이 동일할 경우 전부 반환해야 한다.
의문
처음에 아래와 같이 문제를 풀었다. [ TIL ] 2022. 05. 19. MySQL 윈도우 함수(Window Function)와 GROUP BY 처리 방법에서 작성했던 것처럼 윈도우 함수(Window)랑 GROUP BY 를 같이 사용하더라도 윈도우 함수의 PARTITION BY 부분이 GROUP BY 를 위배하지 않으면 사용 가능할 것이라 생각했기 때문이다. 그런데 이 맥락에서 윈도우 함수를 사용하지 못한다는 오류를 반환했다. 왜 그런 걸까?
SELECT
user_id,
MAX(DATEDIFF(IFNULL(LEAD(visit_date) OVER(PARTITION BY user_id ORDER BY visit_date ASC), '2021-1-1'), visit_date)) AS biggest_window
FROM UserVisits
GROUP BY user_id
ORDER BY user_id ASC;그렇다면 집계 함수(Aggregate Function)인 MAX 를 빼고 아래와 같이 DATEDIFF 함수까지만 사용해보자. 그러면 정상적으로 값을 반환하는 것을 알 수 있다. 따라서 오류를 반환하는 부분은 윈도우 함수와 집계 함수를 함께 사용하려 하기 때문이다.
SELECT
user_id,
DATEDIFF(IFNULL(LEAD(visit_date) OVER(PARTITION BY user_id ORDER BY visit_date ASC), '2021-1-1'), visit_date) AS diff
FROM UserVisits
GROUP BY user_id
ORDER BY user_id ASC;우선 그 이유에 대해 설명하기 전에 GROUP BY 옵션에 대한 부분을 다시 한번 이야기해야겠다. 왜냐하면 애초에 DATEDIFF 함수도 실행이 안 됐어야 정상이기 때문이다.
MySQL의 ONLY_FULL_GROUP_BY 옵션
GROUP BY 를 사용할 때 주의해야 할 부분이 세 가지가 있다.
- 별칭(Alias)
- 컬럼이 아닌 표현(Noncolumn Expression)
- 집계되지 않은 컬럼(Nonaggregated Column)
별칭과 컬럼이 아닌 표현에 대해서는 이전 [ TIL ] 2022. 05. 19. MySQL 윈도우 함수(Window Function)와 GROUP BY 처리 방법에서 확인했다. 예를 들어 아래와 같은 쿼리 예시들이다.
SELECT customer_id AS user_id
FROM Orders
GROUP BY user_id -- 별칭(Alias) 사용
SELECT COUNT(DISTINCT customer_id) AS customer_cnt
FROM Orders
GROUP BY YEAR(order_date) -- 컬럼이 아닌 표현(Noncolumn Expression) 사용표준 SQL에서는 이러한 방법이 모두 불가능하지만 MySQL은 이를 확장하여 GROUP BY 가 SELECT 보다 먼저 실행됨에도 불구하고 가능하게 하는 것이 핵심이었다. 이것이 바로 기본적으로 ONLY_FULL_GROUP_BY 옵션이 켜져있을 때 - enabled - 의 상황이다.
만약 해당 옵션이 꺼져있다면 - Disabled - 여기에 한 가지가 더 추가된다. 바로 집계되지 않은 컬럼(Nonaggregated Column)이다. 말 그대로 GROUP BY 에 의해 그룹핑 된 이후 집계되지 않아 어떤 값을 선택할지 정해지지 않은 컬럼이 집계되지 않은 컬럼이다.
원래 해당 경우에 대해서는 오류를 반환해야 하지만 만약 ONLY_FULL_GROUP_BY 옵션이 꺼져있다면 쿼리는 임의의 값을 반환한다. 그래서 앞선 DATEDIFF 함수가 작동한 것이다.
다시 말해, 아래 쿼리에서 LEAD 윈도우 함수의 대상이 되는 visit_date 필드는 집계되지 않은 컬럼이기 때문에 임의의 값을 반환한다.
SELECT
user_id,
DATEDIFF(IFNULL(LEAD(visit_date) OVER(PARTITION BY user_id ORDER BY visit_date ASC), '2021-1-1'), visit_date) AS diff
FROM UserVisits
GROUP BY user_id
ORDER BY user_id ASC;실제로 아래 쿼리와 같이 GROUP BY 이후 집계되지 않은 컬럼인 visit_date 필드를 반환해보면 예상하지 못한 값을 아무렇게 반환한 것을 알 수 있다.
SELECT
user_id,
visit_date
FROM UserVisits
GROUP BY user_id
ORDER BY user_id ASC;
/*
-- Return
+---------+------------+
| user_id | visit_date |
+---------+------------+
| 1 | 2020-11-28 |
| 2 | 2020-10-5 |
| 3 | 2020-11-11 |
+---------+------------+
*/그래서 위와 같이 반환된 테이블을 기준으로 LEAD 윈도우 함수가 작동되고 DATEDIFF 함수가 사용된 것이다.
user_id 필드를 기준으로 PARTITION BY 구를 작동시키기 때문에 각 user_id 필드에 대한 visit_date 필드의 값은 이미 GROUP BY 구에 의해 하나씩 밖에 존재하지 않아 결론적으로 모두 NULL 이다. 그래서 LEAD 윈도우 함수에 대한 결괏값은 IFNULL 함수가 작동하여 2021-1-1이 된다.
이에 대한 쿼리를 아래와 같이 실행해보면 결괏값이 모두 2021-1-1 로 반환되는 것을 알 수 있다.
SELECT
user_id,
visit_date,
IFNULL(LEAD(visit_date) OVER(PARTITION BY user_id ORDER BY visit_date), '2021-1-1') AS next_date
FROM UserVisits
GROUP BY user_id
ORDER BY user_id ASC;
/*
-- Return
+---------+------------+-----------+
| user_id | visit_date | next_date |
+---------+------------+-----------+
| 1 | 2020-11-28 | 2021-1-1 |
| 2 | 2020-10-5 | 2021-1-1 |
| 3 | 2020-11-11 | 2021-1-1 |
+---------+------------+-----------+
*/추가로 PARTITION BY 구를 따로 사용하지 않으면 아래와 같이 다른 결괏값을 얻을 수 있다. LEAD 함수가 전체 레코드에 대한 visit_date 필드를 기준으로 오름차순 정렬하여 값을 반환하기 때문이다.
SELECT
user_id,
visit_date,
IFNULL(LEAD(visit_date) OVER(ORDER BY visit_date), '2021-1-1') AS next_date
FROM UserVisits
GROUP BY user_id
ORDER BY user_id ASC;
/*
-- Return
+---------+------------+-------------+
| user_id | visit_date | next_date |
+---------+------------+-------------+
| 1 | 2020-11-28 | 2021-1-1 |
| 2 | 2020-10-5 | 2020-11-11 |
| 3 | 2020-11-11 | 2020-11-28 |
+---------+------------+-------------+
*/결론적으로 원래 쿼리 자체가 실행되지 않았어야 했는데 LeetCode 사이트의 경우 ONLY_FULL_GROUP_BY 옵션이 꺼져있기 때문에 집계되지 않은 컬럼(Nonaggregated Column)의 값이 임의로 반환되었고 이에 대한 LEAD 윈도우 함수가 작동해서 결괏값이 나온 것이다.
그리고 MAX 함수는 GROUP BY 구에 의해 작동하는 집계 함수인데 LEAD 윈도우 함수은 GROUP BY 이후의 레코드에 대해 PARTITION BY 구나 ORDER BY 구 등을 기준으로 값을 반환하기 때문에 그 실행 순서가 맞지 않아 사용할 수 없는 것이다.
풀이
그래서 결국 아래와 같이 서브쿼리로 빼서 문제를 해결했다.
SELECT
user_id,
MAX(date_diff) AS biggest_window
FROM (
SELECT
user_id,
DATEDIFF(LEAD(visit_date, 1, '2021-1-1') OVER(PARTITION BY user_id ORDER BY visit_date ASC), visit_date) AS date_diff
FROM UserVisits
) AS UserVisitedDateDiff
GROUP BY user_id
ORDER BY user_id;한 가지 유용한 것은 LEAD 윈도우 함수 자체에 기본 값을 설정할 수 있다는 것이다.
첫 번째 인자로 대상이 되는 필드, 두 번째 인자로 간격, 끝으로 세 번째 인자로 기본 값을 받는다. 기본적으로 기본 값(Default)은 NULL 이지만 아래와 같이 2021-1-1 로 지정하면 만약 따로 필드의 다음 값이 존재하지 않을 경우 2021-1-1 을 반환한다. 결국 IFNULL 함수를 사용하지 않고 더 깔끔하게 문제를 해결할 수 있다. 관련해서는 MySQL 공식문서 중 12.21.1 윈도우 함수 설명을 통해 더 자세하게 확인 가능하다.
SELECT
user_id,
MAX(date_diff) AS biggest_window
FROM (
SELECT
user_id,
DATEDIFF(LEAD(visit_date, 1, '2021-1-1') OVER(PARTITION BY user_id ORDER BY visit_date ASC), visit_date) AS date_diff
FROM UserVisits
) AS UserVisitedDateDiff
GROUP BY user_id
ORDER BY user_id;결론
지난 [ TIL ] 2022. 05. 19. MySQL 윈도우 함수(Window Function)와 GROUP BY 처리 방법에 이어서 MySQL이 GROUP BY 를 처리하는 방법 중 ONLY_FULL_GROUP_BY 옵션이 꺼져 있는 경우에 대해 추가적으로 알 수 있었다.
결론적으로 MySQL에서 GROUP BY 를 사용할 때 고려해야 하는 사항은 아래와 같이 세 가지다.
- 별칭(Alias)
- 컬럼이 아닌 표현(Noncolumn Expression)
- 집계되지 않은 컬럼(Nonaggregated Column)
이 중에서도 집계되지 않은 컬럼의 경우 ONLY_FULL_GROUP_BY 옵션이 꺼져있으면 사용 가능하며 쿼리가 임의의 값을 반환한다.
추가로 윈도우 함수의 경우 모든 처리가 완료된 이후의 레코드를 대상으로 실행이 되기 때문에 윈도우 함수를 실행하고 나서 집계 함수를 실행하는 것은 불가능하다.
