블로그를 이전 중이라 완료되기 전까지는 벨로그에 작성할 계획입니다.
이후 모든 글은 https://weekwith.me 에 작성 예정이니 다른 글이 궁금하시다면 해당 링크를 통해 방문해주세요.
도입
오늘은 LeetCode SQL 중 Medium 난이도의 문제인 1555. Bank Account Summary을 풀었다.
우선 Users 및 Transactions 테이블이 아래와 같이 존재한다.
/*
Table: Users
Primary Key: user_id
*/
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| user_id | int |
| user_name | varchar |
| credit | int |
+--------------+---------+
/*
Table: Transactions
Primary Key: trans_id
Description: User with id (paid_by) transfer money to user with id (paid_to).
*/
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| trans_id | int |
| paid_by | int |
| paid_to | int |
| amount | int |
| transacted_on | date |
+---------------+---------+이때 각 사용자의 최종 amount 필드의 값을 구하고 만약 이것이 0 을 넘으면 credit_limit_breached 라는 필드를 새로 만들어 No 를, 넘지 못하면 Yes 를 입력해야 한다.
자연스레 LEFT JOIN , GROUP BY , SUM 등을 활용한 풀이 방법이 떠오르는데 간과한 사실이 하나 있었다.
NULL 값 연산
아래 쿼리의 결괏값은 과연 무엇이 나올까?
SELECT 2 + NULL;정답은 NULL 이다. 그래서 아무 것도 반환하지 않는다. 어떤 특정 수와 NULL 값을 더했을 때 NULL 값을 일종의 0 으로 취급하여 계산이 되지 않아 자연스레 기존의 수만 반환할 것이라 생각했다. 그런데 결론적으로 NULL 값을 반환하기 때문에 LEFT JOIN 을 활용해서 결합하여 amount 필드의 값이 NULL 인 경우에 대한 예외처리를 해줘야했다.
그래서 기존에는 아래와 같이 SUM 함수 내부에 IF를 사용했는데, CASE WHEN 으로 변경했다. 물론 IFNULL 함수를 바깥에 사용해서 문제를 해결할 수도 있지만 가독성 부분에 있어 CASE WHEN 이 훨씬 좋을 것이라 판단했다.
-- Before
SELECT
...,
credit + SUM(IF(Users.user_id = paid_by, -amount, amount)) AS credit -- NULL 값 때문에 원하는 답을 제대로 얻지 못한다.
FROM Users
...
GROUP BY Users.user_id;
-- After
SELECT
...,
credit + SUM(
CASE Users.user_id
WHEN paid_by THEN -amount
WHEN paid_to THEN amount
ELSE 0
END
) AS credit
FROM Users
...
GROUP BY Users.user_id;최적화
다음으로 최적화(Optimization) 부분이다.
반복되는 SUM 함수
먼저 credit_limit_breached 필드의 값을 구하기 위해 위와 같은 SUM 함수를 한번 더 사용해줬어야 했는데 모든 레코드를 한번 쭉 훑으면서 SUM 함수 내부의 조건까지 판단해야되서 이 자체가 효율이 별로 안 좋다.
그래서 아래와 같이 서브쿼리를 활용해 이 문제를 해결할 수 있다. SUM 함수를 한번만 사용하게 한 다음 그 결괏값만 바로 활용해서 credit_limit_breached 필드의 값을 구하면 되기 때문이다.
-- After
SELECT
...
credit,
IF(credit > 0, 'No', 'Yes') AS credit_limit_breached
FROM (
SELECT
...,
credit + SUM(
CASE Users.user_id
WHEN paid_by THEN -amount
WHEN paid_to THEN amount
ELSE 0
END
) AS credit
FROM Users
...
GROUP BY Users.user_id
) AS TrnasactionResult;LEFT JOIN 조건의 OR 키워드
끝으로 LEFT JOIN 의 조건에 대한 부분이 OR 키워드로 연결이 되어 있기 때문에 성능 최적화를 위해서는 LEFT JOIN 구를 두번으로 나누어 사용하는 방법도 고려해볼만 하다.
-- After
SELECT
...,
credit - minus_amount + plus_amount AS credit
IF((credit - minus_amount + plus_amount) > 0 'No', 'Yes') AS credit_limit_breached
FROM Users
LEFT JOIN (
SELECT
paid_by AS user_id,
SUM(amount) AS minus_amount
FROM Transactions
GROUP BY user_id -- MySQL에서는 GROUP BY 구 부분에 별칭(Alias)을 사용해도 문제 되지 않는다.
) AS MinusTransactions
USING (user_id)
LEFT JOIN (
SELECT
paid_to AS user_id,
SUM(amount) AS plus_amount
FROM Transactions
GROUP BY user_id
) AS PlusTransactions
GROUP BY Users.user_id;JOIN 의 대상이 되는 테이블이 많을 경우 비효율적일 수 있으나 결론적으로 두번만 결합하면 되고 또 결합의 대상이 되는 테이블의 레코드가 많을 수록 OR 키워드를 통해 조건을 내부적으로 따지는 것보다 단순히 결합하는 게 훨씬 성능이 좋을 수밖에 없다.
결론
-
정수형과
NULL값에 대한 더하기 결괏값은NULL값이 아닌 정수형을 그대로 반한화는 것이 아닌NULL이다. 따라서IFNULL등의 함수를 활용한 별도의 예외처리가 필요하다. -
SUM함수를 반복적으로 사용할 경우 결국 모든 레코드를 한번씩 조회해서 계산을 하는 것이기 때문에 비효율적이다. 이럴 때는 서브쿼리를 활용하여SUM함수는 한번만 사용하고 그에 대한 결괏값을 여러 번 사용하는 방식으로 최적화할 수 있다. -
JOIN에 대한 조건으로OR키워드 등의 여러 조건을 두는 것은 비효율적일 수 있다. 레코드 내 필드의 값을 하나씩 조회해야 하기 때문이다.JOIN의 대상이 되는 테이블의 갯수가 그렇게 많지 않다면 차라리 테이블을 나누어 여러 번JOIN을 실행하는 게 더 효과적일 수 있다.
