[프로그래머스/MySQL/JOIN/LV.5] 상품을 구매한 회원 비율 구하기

문제

문제 설명

다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블 입니다. USER_INFO 테이블은 아래와 같은 구조로 되어있으며 USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.

Column name	Type	Nullable
USER_ID	INTEGER	FALSE
GENDER	TINYINT(1)	TRUE
AGE	INTEGER	TRUE
JOINED	DATE	FALSE

GENDER 컬럼은 비어있거나 0 또는 1의 값을 가지며 0인 경우 남자를, 1인 경우는 여자를 나타냅니다.

ONLINE_SALE 테이블은 아래와 같은 구조로 되어있으며 ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다.

Column name	Type	Nullable
ONLINE_SALE_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
PRODUCT_ID	INTEGER	FALSE
SALES_AMOUNT	INTEGER	FALSE
SALES_DATE	DATE	FALSE

동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.

---

문제

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

---

예시

예를 들어 USER_INFO 테이블이 다음과 같고

USER_ID	GENDER	AGE	JOINED
1	1	26	2021-06-01
2	NULL	NULL	2021-06-25
3	0	NULL	2021-06-30
4	0	31	2021-07-03
5	1	25	2022-01-09
6	1	33	2022-02-14

ONLINE_SALE 이 다음과 같다면

ONLINE_SALE_ID	USER_ID	PRODUCT_ID	SALES_AMOUNT	SALES_DATE
1	1	54	1	2022-01-01
2	1	3	2	2022-01-25
3	4	34	1	2022-01-30
4	6	253	3	2022-02-03
5	2	31	2	2022-02-09
6	5	35	1	2022-02-14
7	5	57	1	2022-02-18

2021년에 가입한 회원은 USER_ID가 1, 2, 3, 4 인 회원들로 총 4명 입니다. ONLINE_SALE 테이블에서 해당 회원들에 대한 판매 데이터는 다음과 같습니다.

ONLINE_SALE_ID	USER_ID	PRODUCT_ID	SALES_AMOUNT	SALES_DATE
1	1	54	1	2022-01-01
2	1	3	2	2022-01-25
3	4	34	1	2022-01-30
5	2	31	2	2022-02-09

그러므로 년, 월 별로 상품을 구매한 회원수와 상품을 구매한 회원의 비율을 구하고 결과를 정렬하면 다음과 같아야 합니다.

YEAR	MONTH	PURCHASED_USERS	PUCHASED_RATIO
2022	1	2	0.5
2022	2	1	0.3

출처: 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/challenges

---

✔️  문제접근

이 문제는 DISTINCT 키워드를 사용해 중복을 제거하기, WITH 절을 사용하여 쿼리 재사용성 늘리는 것이 관건인 문제였습니다 🧐

1. USER_INFO 테이블의 JOINED 컬럼에서 2021년도에 가입한 유저만 추리는 쿼리를 WITH 절로 구현한다.
2. 상품을 구매한 회원수를 구하기 위해 COUNT(DISTINCT USER_ID)를 사용한다.
3. 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)를 구하기 위해 기존에 만들어둔 CTE에 ROUND 연산을 활용하여 계산을 진행한다.
4. 메인 쿼리에서 2021년 회원 정보만 추리기 위해 만들어둔 CTE과 EXISTS 연산을 활용한다.

⭐️   내 정답

WITH USERS_INFO_2021 AS (
    SELECT USER_ID
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
)

SELECT 
    YEAR(SALES_DATE) AS SALES_YEAR,
    MONTH(SALES_DATE) AS SALES_MONTH,
    COUNT(DISTINCT USER_ID) AS PUCHASED_USERS,
    ROUND(COUNT(DISTINCT USER_ID) / (SELECT COUNT(USER_ID) FROM USERS_INFO_2021),1) AS PUCHASED_RATIO
FROM ONLINE_SALE AS OS
WHERE EXISTS (
    SELECT 1 
    FROM USERS_INFO_2021 AS UI
    WHERE OS.USER_ID = UI.USER_ID
)
GROUP BY SALES_YEAR, SALES_MONTH
ORDER BY SALES_YEAR, SALES_MONTH;

---

🔎  짚고 넘어가기

WITH 절??

WITH 절, 일반적으로 "Common Table Expressions" (CTEs)라고 불리는 기능은 SQL에서 일시적인 결과 집합을 정의하는 데 사용됩니다. 이는 쿼리의 가독성을 높이고 복잡한 쿼리를 더 구조화된 방식으로 작성하는 데 도움이 됩니다.

CTE (Common Table Expression)란?

CTE는 쿼리의 일부로 일시적인 결과 집합을 정의하여 주 쿼리에서 사용할 수 있게 합니다. CTE는 WITH 키워드로 시작하며, 쿼리 내에서 여러 번 참조될 수 있습니다.

기본 구조

WITH CTE_name AS (
    SELECT_statement
)
SELECT * 
FROM CTE_name;

예제

1. 단순한 CTE 사용

WITH UserCTE AS (
    SELECT USER_ID, USER_NAME
    FROM USERS
    WHERE JOINED_YEAR = 2021
)
SELECT *
FROM UserCTE;

이 예제에서는 UserCTE라는 이름의 CTE를 정의하고, USERS 테이블에서 2021년에 가입한 사용자들을 선택합니다. 이후, 주 쿼리에서 이 CTE를 참조하여 결과를 가져옵니다.

2. 복수의 CTE 사용

여러 개의 CTE를 정의하고 사용할 수도 있습니다.

WITH CTE1 AS (
    SELECT USER_ID, JOINED_YEAR
    FROM USERS
    WHERE JOINED_YEAR = 2021
),
CTE2 AS (
    SELECT USER_ID, ORDER_ID
    FROM ORDERS
)
SELECT CTE1.USER_ID, CTE2.ORDER_ID
FROM CTE1
JOIN CTE2 ON CTE1.USER_ID = CTE2.USER_ID;

여기서는 두 개의 CTE를 정의하고, 이를 주 쿼리에서 조인하여 2021년에 가입한 사용자들의 주문 정보를 가져옵니다.

3. 재귀적 CTE

재귀적 CTE는 자기 참조를 통해 반복적인 쿼리를 수행할 수 있습니다. 주로 계층적 데이터를 처리할 때 유용합니다.

WITH RECURSIVE HierarchyCTE AS (
    -- Anchor member
    SELECT EMPLOYEE_ID, MANAGER_ID, EMPLOYEE_NAME
    FROM EMPLOYEES
    WHERE MANAGER_ID IS NULL

    UNION ALL

    -- Recursive member
    SELECT e.EMPLOYEE_ID, e.MANAGER_ID, e.EMPLOYEE_NAME
    FROM EMPLOYEES e
    INNER JOIN HierarchyCTE h ON e.MANAGER_ID = h.EMPLOYEE_ID
)
SELECT *
FROM HierarchyCTE;

이 예제에서는 HierarchyCTE라는 재귀적 CTE를 정의하여 직원들의 계층 구조를 가져옵니다. Anchor member는 계층 구조의 루트(즉, 매니저가 없는 직원들)이며, Recursive member는 계층 구조를 확장하여 모든 직원들을 포함합니다.

CTE의 장점

1. 가독성 향상: 복잡한 쿼리를 더 읽기 쉽게 작성할 수 있습니다.
2. 재사용성: CTE를 여러 번 참조할 수 있어 쿼리의 중복을 줄입니다.
3. 구조화된 쿼리: 복잡한 논리를 단계별로 나누어 작성할 수 있습니다.
4. 임시적인 이름 지정: 결과 집합에 일시적으로 이름을 지정하여 더 명확하게 사용할 수 있습니다.

결론

WITH 절을 사용한 CTE는 SQL 쿼리를 더 간결하고 이해하기 쉽게 만드는 유용한 도구입니다. 복잡한 쿼리를 작성할 때, 특히 동일한 하위 쿼리를 여러 번 참조해야 하는 경우, CTE를 활용하여 쿼리의 가독성과 유지보수성을 크게 향상시킬 수 있습니다.

---

IN vs EXISTS ??

IN과 EXISTS의 성능은 사용 상황에 따라 다를 수 있습니다. 두 연산자는 서로 다른 방식으로 작동하며, 각각의 성능은 데이터의 크기, 인덱스의 유무, 데이터베이스 시스템 등에 의해 영향을 받습니다.

1. IN 절

IN 절은 서브쿼리의 결과 집합에 대해 외부 쿼리의 값을 비교합니다. 서브쿼리의 결과가 작을 때 더 효율적일 수 있습니다.

예시:

SELECT *
FROM ANIMAL_INS
WHERE USER_ID IN (SELECT USER_ID FROM USERS_INFO_2021);

• 서브쿼리 결과가 작은 경우: IN 절이 더 나을 수 있습니다. 서브쿼리의 결과를 메모리에 저장하고, 외부 쿼리의 각 값과 비교합니다.
• 서브쿼리 결과가 큰 경우: 메모리 사용량이 증가하고 성능이 저하될 수 있습니다.

2. EXISTS 절

EXISTS 절은 서브쿼리의 결과가 존재하는지 여부를 확인합니다. 서브쿼리가 외부 쿼리의 조건에 따라 다르게 실행되므로, 큰 데이터 집합에서도 효율적일 수 있습니다.

예시:

SELECT *
FROM ANIMAL_INS a
WHERE EXISTS (
    SELECT 1
    FROM USERS_INFO_2021 u
    WHERE a.USER_ID = u.USER_ID
);

• 서브쿼리 결과가 큰 경우: EXISTS 절이 더 나을 수 있습니다. 서브쿼리는 외부 쿼리의 각 행에 대해 한 행을 찾으면 즉시 종료하므로, 많은 데이터를 처리하는 경우에도 효율적일 수 있습니다.
• 인덱스가 있을 경우: EXISTS 절은 인덱스를 효과적으로 사용할 수 있어 성능이 향상될 수 있습니다.

일반적인 성능 비교

1. 서브쿼리의 크기:

   • 작은 서브쿼리: IN 절이 더 빠를 수 있습니다.
   • 큰 서브쿼리: EXISTS 절이 더 빠를 수 있습니다.

2. 데이터베이스 인덱스:

   • 인덱스 사용: EXISTS 절이 인덱스를 잘 활용할 수 있습니다.

3. 최적화 방법:

   • 데이터베이스 엔진: 각 DBMS는 쿼리 최적화 방식을 다르게 처리할 수 있습니다. 최신 DBMS는 IN과 EXISTS 절을 유사하게 최적화할 수 있습니다.

결론

• 작은 데이터 집합: IN 절이 더 나을 수 있습니다.
• 큰 데이터 집합: EXISTS 절이 더 효율적일 수 있습니다.
• 인덱스 활용: EXISTS 절이 인덱스를 더 효과적으로 사용할 수 있습니다.