문제
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년에 가입한 고유한 사용자의 수가 정확하게 출력된다.
'코딩 테스트 > SQL' 카테고리의 다른 글
| [MYSQL] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (0) | 2025.04.13 |
|---|---|
| [MYSQL] 물고기 종류 별 대어 찾기 (0) | 2025.03.29 |
| [MYSQL] 입양 시각 구하기(2) (2) | 2025.02.19 |
| [MYSQL] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (0) | 2025.02.04 |
| [MYSQL] 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 (0) | 2024.11.04 |