[프로그래머스/SQL] 재구매가 일어난 상품과 회원 리스트 구하기(Oracle)

문제 설명

다음은 어느 의류 쇼핑몰의 온라인 상품 판매 정보를 담은 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

 

풀이

1. ONLINE_SALE 테이블에서

2. 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여,

3. 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성해주세요.

4. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고

5. 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요.

SELECT USER_ID  -- 회원ID
      , PRODUCT_ID -- 상품ID
   FROM ONLINE_SALE
   GROUP BY USER_ID, PRODUCT_ID
   HAVING COUNT(PRODUCT_ID) > 1
   ORDER BY USER_ID, PRODUCT_ID DESC

 

코드풀이

SELECT USER_ID  -- 회원ID
      , PRODUCT_ID -- 상품ID
     -- , COUNT(PRODUCT_ID)
   FROM ONLINE_SALE
   GROUP BY USER_ID, PRODUCT_ID
   -- 회원 ID와 상품ID로 그룹핑
   HAVING COUNT(PRODUCT_ID) > 1
   ORDER BY USER_ID, PRODUCT_ID DESC -- 회원 ID오름차순 정렬, 상품ID 내림차순 정렬

동일 상품을 구매한 경우 동일 데이터로 출력되기 때문에 회원 ID와 상품 ID기준으로 그룹핑(GROUP BY) 해준다.

후에 상품 ID값에 대해 Counting하면 동일 상품을 구매한 횟수만큼 Count할 수 있는 HAVING절을 사용했다.
HAVING절은 GROUP BY와 함께 쓰이며 집계함수를 가지고 조건을 비교할 때 주로 사용한다.

 

 

다른 풀이

HAVING절을 쓰기 전에 분석함수를 쓰는 방법도 생각했어서 분석함수를 쓰는 방법으로도 문제를 풀어보았다.

SELECT DISTINCT 
       A.USER_ID
     , A.PRODUCT_ID
  FROM (SELECT USER_ID
              , PRODUCT_ID
              , COUNT(PRODUCT_ID) OVER(PARTITION BY USER_ID, PRODUCT_ID) AS CNT
           FROM ONLINE_SALE
           ORDER BY USER_ID, PRODUCT_ID DESC
      ) A
 WHERE CNT > 1;

 

우선 서브 쿼리문을 먼저 살펴보면, 해당 쿼리는 동일 회원이 동일 상품을 구매한 이력이 있는지 카운팅을 하는 쿼리이다.

SELECT USER_ID -- 회원ID
     , PRODUCT_ID -- 상품ID
     , COUNT(PRODUCT_ID) OVER(PARTITION BY USER_ID, PRODUCT_ID) AS CNT
      -- 동일 상품 구매한 경우 Counting
 FROM ONLINE_SALE
 ORDER BY USER_ID, PRODUCT_ID DESC -- 회원ID,상품ID 내림차순 순으로 정렬

구매 이력을 카운팅 하는 부분에 있어서는 COUNT OVER 분석함수를 이용하여 해당 쿼리를 완성해 보았다.

COUNT(Counting을 원하는 컬럼명) OVER(PARTITION BY 그룹핑할 컬럼 ORDER BY 정렬할 컬럼)

동일 회원이 동일상품을 구매한 이력을 카운팅 해야하기 때문에 상품ID와 회원ID기준으로 그룹핑 후 상품ID를 Count한다.
해당 결과 값은 다음과 같다.

---

해당 결과값을 보면 동일 회원이 동일 상품을 구매한 경우 CNT컬럼에 구매한 수 만큼 Counting되어서 보이는 것을 알 수 있다.

 

다시 전체코드로 돌아와서, 앞선 서브쿼리를 A라고 지정한다.
동일 상품을 구매한 경우 동일 데이터로 출력되고 있기 때문에 DISTINCT() 함수를 통해 중복된 데이터를 제거해준 후 동일한 상품을 1개 이상 구매한 경우를 출력하기 위해 조건절에 아래와 같은 조건을 걸어주면 끝~!!

SELECT DISTINCT -- 중복된 데이터를 제거
       A.USER_ID --회원 ID
     , A.PRODUCT_ID --상품 ID
  FROM (SELECT USER_ID
              , PRODUCT_ID
              , COUNT(PRODUCT_ID) OVER(PARTITION BY USER_ID, PRODUCT_ID) AS CNT
                -- 동일 상품 구매한 경우 Counting
           FROM ONLINE_SALE
           ORDER BY USER_ID, PRODUCT_ID DESC -- 회원ID,상품ID 내림차순 순으로 정렬
      ) A
 WHERE CNT > 1; -- 동일 상품을 1개 이상 구매한 경우

 

 

문제출처

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

프로그래머스

 

 

도움이 되셨다면 공감 버튼과 댓글 남겨주세요오♥