[프로그래머스]재구매가 일어난 상품과 회원 리스트 구하기, 오프라인/온라인 판매 데이터 통합하기

프로그래머스 SQL 고득점 Kit 문제

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

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

 

---

문제1. 오프라인/온라인 판매 데이터 통합하기

https://school.programmers.co.kr/learn/courses/30/lessons/131537

프로그래머스

자세히

문제 설명

다음은 어느 의류 쇼핑몰의 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블과 오프라인 상품 판매 정보를 담은 OFFLINE_SALE 테이블 입니다. 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 조합에 대해서는 하나의 판매 데이터만 존재합니다.

OFFLINE_SALE 테이블은 아래와 같은 구조로 되어있으며 OFFLINE_SALE_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 오프라인 상품 판매 ID, 상품 ID, 판매량, 판매일을 나타냅니다.

Column name	Type	Nullable
OFFLINE_SALE_ID	INTEGER	FALSE
PRODUCT_ID	INTEGER	FALSE
SALES_AMOUNT	INTEGER	FALSE
SALES_DATE	DATE	FALSE

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

---

문제

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

---

예시

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

ONLINE_SALE_ID	USER_ID	PRODUCT_ID	SALES_AMOUNT	SALES_DATE
1	1	3	2	2022-02-25
2	4	4	1	2022-03-01
4	2	2	2	2022-03-02
3	6	3	3	2022-03-02
5	5	5	1	2022-03-03
6	5	7	1	2022-04-06

OFFLINE_SALE 테이블이 다음과 같다면

OFFLINE_SALE_ID	PRODUCT_ID	SALES_AMOUNT	SALES_DATE
1	1	2	2022-02-21
4	1	2	2022-03-01
3	3	3	2022-03-01
2	4	1	2022-03-01
5	2	1	2022-03-03
6	2	1	2022-04-01

각 테이블의 2022년 3월의 판매 데이터를 합쳐서, 정렬한 결과는 다음과 같아야 합니다.

SALES_DATE	PRODUCT_ID	USER_ID	SALES_AMOUNT
2022-03-01	1	NULL	2
2022-03-01	3	NULL	3
2022-03-01	4	NULL	1
2022-03-01	4	4	1
2022-03-02	2	2	2
2022-03-02	3	6	3
2022-03-03	2	NULL	1
2022-03-03	5	5	1

---

풀이

해결 단계

1. ONLINE_SALE DB에서 조건에 맞는 데이터 추출 (A')
2. OFFLINE_SALE 에서 조건에 맞는 데이터 추출 (B')
3. A'와 B'를 합쳐주기 (UNION)

이 문제를 처음 봤을 땐 막막할 수 있다. 

UNION을 해주어야 하지만, ONLINE_SALE DB 와 OFFLINE_SALE DB가 칼럼이 다르다는 문제가 있다. 

 

OFFLINE_SALE 에 USER_ID를 추가해준 후에, UNION작업을 해야한다. 

 

빠른 이해를 위한 사진 첨부

 

 

 

-- 코드를 입력하세요
SELECT # ONLINE_SALE DB에서 데이터 추출
    DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, 
    PRODUCT_ID, 
    USER_ID,
    SALES_AMOUNT
FROM ONLINE_SALE
WHERE SUBSTR(SALES_DATE,1,7) = '2022-03'
UNION ALL ## 데이터 합치기
SELECT # OFFLINE_SALE DB에서 데이터 추출
    DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, 
    PRODUCT_ID,
    NULL AS USER_ID, # USER_ID 만들어주기
    SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SUBSTR(SALES_DATE,1,7) = '2022-03'
ORDER BY SALES_DATE ASC, PRODUCT_ID ASC, USER_ID ASC ;

 

더 나아가,

UNION과 UNION ALL의 차이는 무엇일까?

프로그래머스에선 두 함수 모두 정답으로  처리한다.

 

두 함수의 차이를 간단하게 짚고 넘어가자. 

 

UNION : 둘 DB를 합치지만, 중복된 값을 제거한다. 

UNION ALL : 중복된 값을 그대로 유지하면서, 두 DB를 합친다. 

---

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

https://school.programmers.co.kr/learn/courses/30/lessons/131536

프로그래머스

 

자세히

문제 설명

다음은 어느 의류 쇼핑몰의 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블 입니다. 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 조합에 대해서는 하나의 판매 데이터만 존재합니다.

---

문제

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

---

예시

예를 들어 ONLINE_SALE 테이블이 다음과 같다면

ONLINE_SALE_ID	USER_ID	PRODUCT_ID	SALES_AMOUNT	SALES_DATE
1	1	3	2	2022-02-25
2	1	4	1	2022-03-01
4	2	4	2	2022-03-12
3	1	3	3	2022-03-31
5	3	5	1	2022-04-03
6	2	4	1	2022-04-06
2	1	4	2	2022-05-11

USER_ID 가 1인 유저가 PRODUCT_ID 가 3, 4인 상품들을 재구매하고, USER_ID 가 2인 유저가 PRODUCT_ID 가 4인 상품을 재구매 하였으므로, 다음과 같이 결과가 나와야합니다.

USER_ID	PRODUCT_ID
1	4
1	3
2	4

---

TRY1

1. USER_ID와 PRODUCT_ID를 합쳐서 새로운 칼럼 만들기
2. 개수 세기
3. 서브쿼리 활용하여, 2번까지 기반으로 개수가 1개 초과인것들 추출

-- 첫 시도
SELECT # 중복 제거를 위한 쿼리
    DISTINCT * 
FROM (
    SELECT # PURCHASE_ID 값이 1초과 인것들 추출 / 중복되는 값들이 생긴다는 문제 발생
        USER_ID,
        PRODUCT_ID
    FROM (
        SELECT # USER_ID와 PRODUCT_ID를 합친 새로운 칼럼 PURCHASE_ID을 포함한 데이터 추출
            USER_ID,
            PRODUCT_ID,
            COUNT(*) OVER (PARTITION BY USER_ID, PRODUCT_ID ORDER BY USER_ID ASC, PRODUCT_ID DESC) AS PURCHASE_ID
        FROM ONLINE_SALE
    ) FIRST_TABLE
    WHERE PURCHASE_ID > 1 ) DUP_TABLE
;

 

첫 시도는 다음과 같다. 

 

이렇게 되면, 서브쿼리를 2번이나 사용하게 된다. 

WINDOW함수를 활용하여, 새로운 칼럼을 만들었다. 

 

이 문제에서  WINDOW함수를 사용하면 발생하는 문제는 중복값이 유지된다는 점이다. 

이로 인해, 마지막에 중복을 제거하는 절이 추가로 발생한다.

 

 이런 상황에선, WINDOW함수는 오히려 손해가 될 수 있다. 

 

TRY2(해답)

좀 더 단순한 풀이를 생각해보자.

쿼리를 너무 어렵게 생각하지 말고, Ad-hoc으로 완성시켜보자라고 생각을 하면서, 단순한 쿼리 로직을 발견하였다. 

 

GROUP BY를 통해서 그룹집계와 중복 제거를 동시에 해결할 수 있다.

 

풀이는 다음과 같이 단순한다. 

 

--  풀이
-- 코드를 입력하세요
SELECT 
    USER_ID,
    PRODUCT_ID
FROM ONLINE_SALE 
GROUP BY USER_ID, PRODUCT_ID 
HAVING COUNT(*) > 1
ORDER BY USER_ID ASC, PRODUCT_ID DESC 
;

 

쿼리시험 문제를 풀 때와 프로젝트를 진행하는 것과는 차이가 있는 것 같다. 

 

여러 문제를 풀어보며, 시험을 위한 쿼리에도 익숙해질 필요가 있다. 

 

하루에 3시간을 걸으면 7년 후에 지구를 한바퀴 돌 수 있다. 

-사무엘존슨-