[프로그래머스 스쿨 - MYSQL] 이커머스 문제 Lv.2 ~Lv.5

LV.2 재구매가 일어난 상품과 회원 리스트 구하기

문제

ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성해주세요. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요.

해설

추출해야할 것 user_id, product_id

조건 재구매 회원 

정렬 user_id , product_id DESC

 

1. 회원별, 제품별 구매 횟수를 COUNT하기 위해 GROUP BY를 해준다

2. HAVING으로 집계된 횟수가 2회 이상인 row만 가져온다

3. 알맞게 정렬한다

 

이를 구현한 코드는 아래와 같다

SELECT user_id, product_id
FROM Online_sale
GROUP BY user_id, product_id
HAVING  COUNT(*) >= 2
ORDER BY user_id, product_id DESC

 

LV.4 오프라인/온라인 판매 데이터 통합하기

문제

ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요. OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요. 결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요.

해설

추출해야할 것 sales_date, product_id, user_id, sales_amount

조건 "2022-03"에 해당하는 거래 건

정렬 sales_date, product_id, user_id

 

1. 오프라인과 온라인 판매를 같은 컬럼으로 붙이기 위해 UNION을 써준다

2. 이때 , 오프라인 테이블에는 user_id가 존재하지 않기 때문에, NULL로 표현한다

3. 2022-03에 해당하는 필드를 LIKE 구문을 통해서 가져온다

4. 알맞게 정렬하고 출력한다

 

이를 구현한 코드는 아래와 같다

SELECT * 
FROM (SELECT DATE_FORMAT(sales_date,"%Y-%m-%d") AS sales_date, product_id, user_id, sales_amount FROM Online_sale
     UNION ALL
     SELECT sales_date, product_id, NULL AS user_id, sales_amount FROM Offline_sale) AS A
WHERE sales_date LIKE "%2022-03%"
ORDER BY sales_date, product_id, user_id

 

LV.5 상품을 구매한 회원의 비율 구하기

문제

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

해설

추출해야할 것 sales_date의 년, 월, 구매 회원 수, 구매 회원 수의 비율

조건 joined = 2021, GROUP BY <YEAR, MONTH>

정렬 YEAR ASC, MONTH ASC

 

1. 2021년에 가입한 유저 테이블과 구매 이력을 추출하기 위한 두 개의 테이블을 JOIN한다

2. JOIN 시 가입한 년도가 2021년에 해당되는 유저의 정보만 가져온다

3. sales_date의 년과 월을 기준으로 GROUP BY 해준다 

4. 년과 월을 답안 같이 SELECT 문에 넣어준다.

5. 구매한 회원 수를 추출한다

<주의! 구매 건 수가 아니기 때문에 중복 집계를 하지 않기 위해 DISTINCT를 써야함>

6. 회원 수의 비율을 구하기 위해 전체 회원 수 정보를 가지고 있는

User_info에서 스칼라 서브쿼리를 적어준다

7. 년과 월을 기준으로 오름차순 정렬한다

 

이를 구현한 코드는 아래와 같다

SELECT YEAR(o.sales_date) AS YEAR, MONTH(o.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 AS u RIGHT JOIN Online_sale AS o 
ON u.user_id = o.user_id AND YEAR(u.joined) = 2021
GROUP BY YEAR(o.sales_date), MONTH(o.sales_date)
ORDER BY YEAR, MONTH

-- 변수 저장하여 계속 불러오는 버전
SELECT COUNT(USER_ID) into @user_count FROM USER_INFO WHERE YEAR(JOINED) = 2021;
SELECT YEAR(o.sales_date) AS YEAR, MONTH(o.sales_date) AS MONTH, 
COUNT(DISTINCT(u.user_id)) AS PURCHASED_USERS,
ROUND(COUNT(DISTINCT(u.user_id)) / @user_count,1) AS PURCHASED_RATIO
FROM User_info AS u JOIN Online_sale AS o USING(user_id) 
WHERE YEAR(u.joined) = 2021
GROUP BY YEAR(o.sales_date), MONTH(o.sales_date)
ORDER BY YEAR, MONTH

* 스칼라 서브쿼리를 이용한 첫번째 코드와 

변수를 생성하여 불러오는 두번째 코드를 비교해보자

같은 상수를 불러오는데, 스칼라 서브쿼리는 매번 SELECT 문을 호출하여

비효율적인 연산을 하게될 것이다.

반면, @user_count라는 변수에 카운트 값을 저장하여 호출하면

불필요한 SELECT 문을 매번 호출할 필요가 없어진다