[SQL/오답] 조건부 집계(CASE WHEN)와 집합 결합을 활용한 홀짝 거래액 산출 (LeetCode3220 Medium)

 

1. Problem

날짜별로 거래 금액(amount)의 홀수 합계와 짝수 합계를 각각 구해야 한다. 특정 날짜에 홀수 또는 짝수 거래가 없는 경우 0으로 표시해야 하며, 결과는 날짜 순으로 정렬한다. 단순히 그룹화하는 것을 넘어, 특정 조건에 부합하는 행들만 선택적으로 합산하는 로직이 핵심이다.

2. Solution

테이블 스캔 효율을 극대화한 CASE WHEN 방식과 구조적 명확성을 강조한 CTE 기반 JOIN 방식 두 가지로 접근 가능하다.

[방법 A] 최적화된 CASE WHEN 방식 (권장) 테이블을 단 한 번만 읽어 성능이 가장 뛰어나며 가독성도 좋다.

[방법 B] CTE와 JOIN을 활용한 구조적 방식 홀수와 짝수 집합을 분리하여 계산한 뒤, 전체 날짜 기준(all_dates)으로 결합하여 데이터 유실을 방지한다.

3. Takeaway (데이터 유실 방지와 효율성)

쿼리 설계 시 논리적 완결성을 확보하기 위해 다음의 기술적 포인트를 정립한다.

• 기준 테이블의 중요성: 홀수 거래만 있는 날과 짝수 거래만 있는 날이 섞여 있을 때, 어느 한쪽 테이블을 기준으로 LEFT JOIN을 하면 반대편 데이터가 누락될 수 있다. 모든 날짜를 가진 all_dates를 기준(Driver Table)으로 삼거나 UNION을 활용해 전체 집합을 유지해야 한다.
• Single Scan vs Multi Scan: JOIN 방식은 동일한 테이블을 여러 번 읽어야 하므로 자원 소모가 크다. 반면 SUM(CASE WHEN...)은 한 번의 스캔으로 여러 조건을 동시에 처리하므로 대용량 데이터 환경에서 훨씬 객관적인 우위를 점한다.
• NULL 처리: 집계 시 데이터가 없는 경우 NULL이 반환될 수 있으므로, IFNULL이나 COALESCE를 사용하여 문제 요구사항인 '0'을 정확히 출력해야 한다.

 

Odd and Even Transactions - LeetCode

 

Table: transactions

+------------------+------+
| Column Name      | Type | 
+------------------+------+
| transaction_id   | int  |
| amount           | int  |
| transaction_date | date |
+------------------+------+
The transactions_id column uniquely identifies each row in this table.
Each row of this table contains the transaction id, amount and transaction date.
Write a solution to find the sum of amounts for odd and even transactions for each day. If there are no odd or even transactions for a specific date, display as 0.

Return the result table ordered by transaction_date in ascending order.

The result format is in the following example.

 

Example:

Input:

transactions table:

+----------------+--------+------------------+
| transaction_id | amount | transaction_date |
+----------------+--------+------------------+
| 1              | 150    | 2024-07-01       |
| 2              | 200    | 2024-07-01       |
| 3              | 75     | 2024-07-01       |
| 4              | 300    | 2024-07-02       |
| 5              | 50     | 2024-07-02       |
| 6              | 120    | 2024-07-03       |
+----------------+--------+------------------+
  
Output:

+------------------+---------+----------+
| transaction_date | odd_sum | even_sum |
+------------------+---------+----------+
| 2024-07-01       | 75      | 350      |
| 2024-07-02       | 0       | 350      |
| 2024-07-03       | 0       | 120      |
+------------------+---------+----------+
  
Explanation:

For transaction dates:
2024-07-01:
Sum of amounts for odd transactions: 75
Sum of amounts for even transactions: 150 + 200 = 350
2024-07-02:
Sum of amounts for odd transactions: 0
Sum of amounts for even transactions: 300 + 50 = 350
2024-07-03:
Sum of amounts for odd transactions: 0
Sum of amounts for even transactions: 120
Note: The output table is ordered by transaction_date in ascending order.

 

 

---

1. 정답 쿼리 

사실 이 문제는 WITH 절이나 JOIN 없이 CASE WHEN을 이용한 집계가 성능과 가독성 면에서 가장 뛰어납니다. 테이블을 여러 번 읽지 않고 단 한 번만 훑기(Single Scan) 때문입니다.

 

SELECT 
    transaction_date,
    SUM(CASE WHEN amount % 2 = 1 THEN amount ELSE 0 END) AS odd_sum,
    SUM(CASE WHEN amount % 2 = 0 THEN amount ELSE 0 END) AS even_sum
FROM transactions
GROUP BY transaction_date
ORDER BY transaction_date ASC;

2. 오답 쿼리 분석

# Write your MySQL query statement below
WITH even_table AS (
SELECT transaction_date, SUM(amount) AS even_sum
FROM transactions
WHERE  amount%2=0     #짝수
GROUP BY transaction_date
),

odd_table AS (
SELECT transaction_date, SUM(amount) AS odd_sum
FROM transactions 
WHERE amount%2=1       #홀수
GROUP BY transaction_date
)

SELECT o.transaction_date, IFNULL(o.odd_sum,0) AS odd_sum, IFNULL(e.even_sum, 0) AS even_sum
FROM odd_table o 
LEFT JOIN even_table e ON o.transaction_date = e.transaction_date


UNION

SELECT o.transaction_date, IFNULL(o.odd_sum,0) AS odd_sum, IFNULL(e.even_sum, 0) AS even_sum
FROM even_table e
LEFT JOIN odd_table o ON o.transaction_date = e.transaction_date


ORDER BY transaction_date ASC;

현재 쿼리의 주요 문제점은 두 가지입니다.

1. UNION과 ORDER BY의 위치: UNION을 사용할 때 ORDER BY는 개별 쿼리가 아닌, 전체 합쳐진 결과의 가장 마지막에 한 번만 와야 합니다.
2. 데이터 유실: 2024-07-02처럼 홀수 거래가 아예 없는 날짜는 odd_table에 존재하지 않습니다. 첫 번째 SELECT에서 odd_table을 기준으로 LEFT JOIN을 하면 해당 날짜 자체가 누락될 위험이 있습니다.

3. 수정된 쿼리

WITH even_table AS (
    SELECT transaction_date, SUM(amount) AS even_sum
    FROM transactions
    WHERE amount % 2 = 0
    GROUP BY transaction_date
),
odd_table AS (
    SELECT transaction_date, SUM(amount) AS odd_sum
    FROM transactions 
    WHERE amount % 2 = 1
    GROUP BY transaction_date
),
all_dates AS (
    -- 모든 거래 날짜를 중복 없이 추출
    SELECT DISTINCT transaction_date FROM transactions
)

SELECT 
    d.transaction_date, 
    IFNULL(o.odd_sum, 0) AS odd_sum, 
    IFNULL(e.even_sum, 0) AS even_sum
FROM all_dates d
LEFT JOIN odd_table o ON d.transaction_date = o.transaction_date
LEFT JOIN even_table e ON d.transaction_date = e.transaction_date
ORDER BY transaction_date ASC;