[MYSQL] 상품을 구매한 회원 비율 구하기

문제

USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

 

오답 쿼리

-- 연/월별 집계 후 전체 집계 과정에서 동일 사용자가 여러 번 카운트되는 문제 발생

SELECT
    YEAR,
    MONTH,
    PURCHASED_USERS,
    ROUND(PURCHASED_USERS / TOTAL_USERS,1) AS PURCHASED_RATIO
FROM(
SELECT
    YEAR(SALES_DATE) AS YEAR,
    MONTH(SALES_DATE) AS MONTH,
    COUNT(DISTINCT U.USER_ID) AS PURCHASED_USERS,
    SUM(COUNT(DISTINCT U.USER_ID)) OVER() AS TOTAL_USERS
FROM USER_INFO U
LEFT JOIN ONLINE_SALE O
ON U.USER_ID = O.USER_ID
WHERE YEAR(JOINED) = 2021
GROUP BY 1,2
) SUB
WHERE YEAR IS NOT NULL
ORDER BY 1,2;

오답 쿼리는 겉보기에는 잘 구성된 것처럼 보이지만, 전체 집계 과정에서 중복 집계가 발생하는 문제가 있다.

 

COUNT(DISTINCT U.USER_ID)

• 연/월별로 고유한 사용자 수를 집계할 때는 올바르게 작동한다.

SUM(COUNT(DISTINCT U.USER_ID)) OVER()

• 전체 집계 시 GROUP BY 절에 의해 연/월별로 분할된 결과를 다시 집계하는 과정에서 문제가 발생한다.
• 예를 들어, USER_ID 1번이 2022년 1월과 2월에 각각 한 번씩 구매했다면, 전체 집계에서는 한 명으로 처리되어야 하지만, 이 쿼리는 두 번 집계되어 중복 계산되는 문제가 있다.

 

정답 쿼리

-- 서브쿼리로 전체 집계 과정을 분리하여, USER_ID 기준의 고유 집계를 수행함

SELECT
    YEAR(SALES_DATE) AS YEAR,
    MONTH(SALES_DATE) AS MONTH,
    COUNT(DISTINCT U.USER_ID) AS PURCHASED_USERS,
    ROUND(COUNT(DISTINCT U.USER_ID) 
    / (SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED)=2021),1) AS PURCHASED_RATIO
FROM USER_INFO U
LEFT JOIN ONLINE_SALE O
ON U.USER_ID = O.USER_ID
WHERE YEAR(JOINED) = 2021 AND YEAR(SALES_DATE) IS NOT NULL
GROUP BY 1,2
ORDER BY 1,2

전체 집계 과정을 별도의 서브쿼리로 분리하여 처리하면 중복 집계 문제가 해결된다.

서브쿼리 활용

• 기존 쿼리는 연/월별로 집계된 사용자 수를 다시 전체 집계하는 과정에서 중복이 발생했으나, 서브쿼리를 사용하면 USER_ID가 PK(Primary Key)인 테이블에서 직접 집계할 수 있다.
• 그 결과, 예를 들어 2021년에 가입한 고유한 사용자의 수가 정확하게 출력된다.