프로그래머스 유일한(?) LEVEL_5 문제입니다.

LEVEL_5 문제이자 마지막 문제입니다.

저는 이번 게시글에서

1.먼저 정답 코드 관련 문제 풀이 과정 공개
2.제가 처음에 접근한 풀이과정 방법(개념 설명 포함)

이 순서로 글을 진행할 예정입니다.

---

1. 문제 소개

이번 문제는 SQL 해결 로직이 복잡하기보다는
문제 자체가 복잡해서 난잡한 느낌입니다.

따라서, 추천 풀이방법은 문제에 나와있는 예시대로 진행하면 수월하다고 생각합니다.
(저 또한 이렇게 문제를 해결하였습니다.)

---

2. 문제 풀이

2-1. '문제 예시' 살펴보기

저는 일단은
'ONLINE_SALE_ID', 'USER_ID', 'PRODUCT_ID', 'SALES_AMOUNT', 'SALES_DATE'가 포함된 결과 테이블을 먼저 만들어야겠다고 생각이 들었습니다.

다만, 문제 조건이 '2021년에 등록된 사람 중'이므로,
JOIN을 통해서 이를 구해야한다고 판단했습니다.

＊조인은 두 테이블의 공통된 교집합만 뽑으면 되므로 INNER JOIN을, 공통된 열은 USER_ID로 선택했습니다.
(이전 게시글들과 마찬가지인 논리이므로 설명은 생략하겠습니다.)

그 다음은 연,월,일 별로 PARSE(구분하다)를 해야하므로,
SQL DATE PARSING 문법을 사용했습니다.

DATE_PARSE 문법은 위 사진에서 보이는 것과 같이,
연은 YEAR()로, 월은 MONTH(), 일은 DAY()로 PARSING하면 됩니다.

그 다음은 최종 답안에 맞게 설정을 해야합니다.

필요없는 SELECT 열들은 주석처리를 하고,
남은 로직을 작성해야합니다.

일단 먼저 최종 정답 코드를 보여드리고 설명하도록 하겠습니다.

<최종 정답 코드>

-- 코드를 입력하세요
SELECT
    -- OS.ONLINE_SALE_ID,
    -- OS.USER_ID,
    -- OS.PRODUCT_ID,
    -- OS.SALES_AMOUNT,
    -- OS.SALES_DATE
    YEAR(OS.SALES_DATE) AS YEAR,
    MONTH(OS.SALES_DATE) AS MONTH,
    -- DAY(OS.SALES_DATE) AS DAY,
    COUNT(DISTINCT OS.USER_ID) AS PURCHASED_USERS,
    ROUND(COUNT(DISTINCT OS.USER_ID) * 1.0 / 
         (SELECT COUNT(*) FROM USER_INFO
         WHERE JOINED BETWEEN '2021-01-01' AND '2021-12-31'),1) AS PURCHASED_RATIO
FROM USER_INFO UI INNER JOIN ONLINE_SALE OS
ON UI.USER_ID = OS.USER_ID
WHERE UI.JOINED BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY YEAR,MONTH
ORDER BY YEAR ASC, MONTH ASC

우리가 남은 로직 작성 부분은

1.GROUP BY + COUNT
2.ROUND

부분입니다.

2-1-1. 'GROUP BY + COUNT'

위 사진을 참고하면,
문제에서 '년,월' 별로 ~ 라는 말이 나와있습니다.

그럼 우리는 GROUP BY에 들어갈 열이 '년,월'임을 알 수 있습니다.

GROUP BY YEAR,MONTH

그리고 COUNT를 이어서 진행해야하는데,
여기서 COUNT(OS.USER_ID)가 아닌 COUNT(DISTINCT OS.USER_ID)임을 유심히 봐야합니다.

앞서 봤던 사진을 다시 첨부하겠습니다.
우리는 여기서 GROUP BY YEAR,MONTH로 (YEAR,MONTH)를 기준으로 그룹화를 진행합니다.

그리고 우리는 USER_ID를 COUNT하는데, 여기서 USER_ID는 동일한 게 중복이 될 수 있습니다.

문제에서도 나와있고 논리 상 다른 날에 똑같은 고객이 또 물품을 구매할 수 있기 때문입니다.

따라서 우리는 동일 인물의 중복을 제거하고자 DISTINCT를 사용합니다.

---

2-1-2. 'ROUND'

ROUND는 반올림 함수입니다.

보통 ROUND(반올림할 대상, 반올림 후 나타내는 소수점 자릿 수) 이렇게 작성합니다.

ROUND(COUNT(DISTINCT OS.USER_ID) * 1.0 / 
         (SELECT COUNT(*) FROM USER_INFO
         WHERE JOINED BETWEEN '2021-01-01' AND '2021-12-31'),1) AS PURCHASED_RATIO

문제에서 비율의 정의를 다음과 같이 나타냈습니다.

비율: 2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)

즉, 분모는 서브쿼리를 활용하여,
'2021년에 가입한 전체 가입자 수'로 표현해야하고,

분자는 아까 (연,월)로 그룹화한 DISTINCT OS.USER_ID를 세면 됩니다.

저는 분자 부분에 혹시 몰라서 1.0을 곱했으나,
(밑에 첨부한 사진을 참고하면, 정수 나눗셈으로 결과가 나올 수도 있다고 판단하에 1.0을 곱했습니다.)

1.0을 딱히 안 넣어도 문제는 없었습니다.

---

2-2. 제가 처음에 접근한 풀이과정 방법(개념 설명 포함)

※추가로 보고 싶으신 분들만 보세요. 안 보셔도 무관합니다.

문제를 보면 구하는 과정을 이렇게 세분화할 수 있습니다.

＊문제
1.2021년에 가입한 전체 회원들 중에

2.상품을 구매한 회원수와 상품을 구매한 회원의 비율(2021년 가입한 회원 중 상품 구매한 회원 수(AND 논리로 가야하나?)/2021년 가입한 전체 회원 수) -> 완전 확률 문제네. 을 년,월,별로 출력

3.상품 구매한 회원 비율은 소수점 두번째 자리에서 반올림(ROUND), 결과는 년 기준 ASC, 월 기준 ASC

※TMI_개념_1

갑작스레 뜬금없지만 TMI로 개념 설명하고 가겠습니다.
문제 풀이와 연관되는 개념은 아닙니다.

'MYSQL에서 별칭 붙일 때 AS 써도 되고 안 써도 된다.(둘 다 맞음)'

※CF.) 예약어란?

---

그럼 이제 위의 세분화된 문제를 기반으로 문제 풀이를 진행하도록 하겠습니다.

---

2-2-1. 2021년에 가입한 전체 회원 구하기

이것은 간단합니다.

<정답 코드>

SELECT
    USER_ID,
    GENDER,
    AGE,
    JOINED
FROM USER_INFO
WHERE JOINED BETWEEN '2021-01-01' AND '2021-12-31'

이 과정은 나중에 서브쿼리를 통해 문제를 풀기 위한 빌드업이라고 생각하면 되겠습니다.

---

2-2-2. '상품을 구매한 회원 수' 구하기

우리는 현재 위 2-2-1번을 통해,
'2021년에 가입한 전체 회원들 중에' 라는 조건을 구했습니다.

그리고 우리는 이제 '상품을 구매한 회원 수'를 구할 차례입니다.

이 말은 즉, '우리는 해당 테이블의 전체 행의 갯수를 구해야한다.'라는 말과 같습니다.
->이 부분이 지금 당장 이해가 잘 안가신다면, 일단은 스킵하고 뒷부분을 읽다보면 이해가 되실 겁니다.

우리는 테이블의 전체 행의 갯수를 어떻게 구할 수 있을까요?

저는 2가지 방법을 생각해보았습니다.

1.윈도우 함수 ROW_NUMBER 활용 + MAX() 개념
2.서브쿼리 후, LIMIT절을 통해 GROUP_COUNT 갯수 1개만 가져오기

1번부터 설명하도록 하겠습니다.

SELECT
    ROW_NUMBER() OVER(ORDER BY USER_ID) AS ROW_INDEX,
    ONLINE_SALE_ID,
    USER_ID
    -- COUNT(*)
FROM ONLINE_SALE
GROUP BY USER_ID

우리는 먼저 GROUP BY USER_ID로 USER_ID열을 기준으로 그룹화를 진행하였고,
ROW_NUMBER() 윈도우 함수를 통해, 인덱스와 같은 개념(=대리키)으로 번호를 붙였습니다.

그리고 ROW_INDEX 중 가장 큰 번호가 곧 갯수이니, MAX(ROW_INDEX)를 통해 SELECT를 합니다.

<정답 코드>

SELECT
    MAX(ROW_INDEX)
FROM
(SELECT
    ROW_NUMBER() OVER(ORDER BY USER_ID) AS ROW_INDEX,
    ONLINE_SALE_ID,
    USER_ID
    -- COUNT(*)
FROM ONLINE_SALE
GROUP BY USER_ID) AS T_1

서브쿼리를 통해, 앞에 작성한 코드를 서브쿼리에 넣고,
SELECT MAX(ROW_INDEX)를 통해 테이블의 전체 행의 갯수를 구해줍니다.

※TMI_개념_2

'COUNT(＊)' VS 'COUNT(특정 컬럼명)'의 차이점

즉,COUNT(＊)은 NULL까지 세고, COUNT(특정 컬럼명)은 NULL을 제외하고 센다.

---

그 다음 2번을 설명하도록 하겠습니다.

'2.서브쿼리 후, LIMIT절을 통해 GROUP_COUNT 갯수 1개만 가져오기'

먼저 코드부터 보여드리도록 하겠습니다.

<방법_1>

SELECT
    -- ROW_NUMBER() OVER(ORDER BY USER_ID) AS ROW_INDEX,
    ONLINE_SALE_ID,
    USER_ID,
    (SELECT COUNT(DISTINCT USER_ID) FROM ONLINE_SALE) AS GROUP_COUNT
    -- COUNT(*)
FROM ONLINE_SALE
GROUP BY USER_ID

우리는 GROUP BY USER_ID로 그룹화를 했고,
다만 'GROUP_COUNT' 부분은 우리가 따로 서브쿼리를 작성해서 구현했으므로, GROUP BY의 영향을 받지 않으므로, COUNT(DISTINCT USER_ID)를 통해, GROUP BY의 효과와 동일하게 중복행을 제거해줍니다.

<방법_2>

SELECT
    -- ROW_NUMBER() OVER(ORDER BY USER_ID) AS ROW_INDEX,
    ONLINE_SALE_ID,
    USER_ID,
    COUNT(*) OVER() AS GROUP_COUNT -- GROUP BY 후 행 개수!(윈도우 함수)
    -- COUNT(*)
FROM ONLINE_SALE
GROUP BY USER_ID

이는 또 다른 윈도우 함수를 사용해서 구현한 것입니다.

'방법_1'보다 더 간편하게 구현했습니다.

먼저 개념 설명부터 잠시 하고 이어서 설명하겠습니다.

개념_3. ※'윈도우 함수에서 'OVER()' 정의 제대로 알고 가기!

즉, 그냥 OVER()만 있는 것은 전체를 하나의 파티션(구분)으로 본다는 의미이다.

그럼 우리는 위 개념 설명에 이어서 풀이과정을 설명하자면,

COUNT(*) OVER() AS GROUP_COUNT

GROUP BY로 그룹화한 것을 하나의 테이블로 보고(=한 파티션으로 본다.), 이 테이블의 총 행의 갯수를 구한 것입니다.

그리고 우리는 이 여러 개의 'GROUP_COUNT' 중에서 단 1개만을 가져오고 싶습니다.

이는, '서브쿼리 & LIMIT 절'로 해결이 가능합니다.

---

'방법_2'의 코드에 위 '서브쿼리 & LIMIT절'을 적용해보도록 하겠습니다.

<정답 코드>

SELECT
    GROUP_COUNT 
FROM
(SELECT
    -- ROW_NUMBER() OVER(ORDER BY USER_ID) AS ROW_INDEX,
    ONLINE_SALE_ID,
    USER_ID,
    COUNT(*) OVER() AS GROUP_COUNT -- GROUP BY 후 행 개수!(윈도우 함수)
    -- COUNT(*)
FROM ONLINE_SALE
GROUP BY USER_ID
LIMIT 1) AS T_1

LINIT 1로 GROUP_COUNT를 1개만 선택하게 하고,
위 방법_2 코드를 서브쿼리로 감싸면 끝입니다.

---

그러나 우리는 위 과정을
'ONLINE_SALE' 테이블에서의 전체 행의 갯수를 구했습니다.

우리는 ONLINE_SALE 테이블이 아닌,
위 '2-2-1. 2021년에 가입한 전체 회원 구하기'의 조건에서의 결과 테이블에서의 전체 행의 갯수를 구해야합니다.

풀이 방법은 2가지가 있습니다.

1.여러 개의 서브쿼리로만 구현하기
2.조인을 이용해서 간단히 구현하기

저는 2번 과정부터 먼저 설명하도록 하겠습니다.

---

2-2-2-1. 조인을 사용해서 간단히 구현하기

조인은 INNER JOIN, 공통된 열(속성)은 USER_ID로 선택했습니다.
(이유는 생략하겠습니다. 똑같은 논리입니다.)

<정답 코드>

SELECT
    GROUP_COUNT 
FROM
(SELECT
    -- ROW_NUMBER() OVER(ORDER BY USER_ID) AS ROW_INDEX,
    OS.ONLINE_SALE_ID,
    OS.USER_ID,
    COUNT(*) OVER() AS GROUP_COUNT -- GROUP BY 후 행 개수!(윈도우 함수)
    -- COUNT(*)
FROM ONLINE_SALE OS INNER JOIN USER_INFO UI
ON OS.USER_ID = UI.USER_ID
WHERE UI.JOINED BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY USER_ID
LIMIT 1) AS T_1

서브쿼리 부분을 자세히 보면,
조인을 통해 구성되었음을 알 수 있고,
WHERE 조건절을 통해 2021년에 등록한 회원만을 추려내는 것을 알 수 있습니다.

그리고 메인 SELECT문에서는 GROUP_COUNT를 SELECT하여,
LIMIT 1 조건에 의해 1개만 구하는 것을 알 수 있습니다.

---

2-2-2-2. 여러 개의 서브쿼리로만 구현하기

<정답 코드>

SELECT
    GROUP_COUNT 
FROM
(SELECT
    -- ROW_NUMBER() OVER(ORDER BY USER_ID) AS ROW_INDEX,
    ONLINE_SALE_ID,
    USER_ID,
    COUNT(*) OVER() AS GROUP_COUNT -- GROUP BY 후 행 개수!(윈도우 함수)
    -- COUNT(*)
FROM ONLINE_SALE
WHERE USER_ID IN (
SELECT
   -- * -- 별 의미없으므로 *(ALL의 의미)로 함. WRONG. WHERE/IN 문법은 값을 비교하는 것이므로, 1개의 컬럼만 선택해야 함.
    USER_ID
FROM USER_INFO
WHERE JOINED BETWEEN '2021-01-01' AND '2021-12-31'
)
GROUP BY USER_ID
LIMIT 1) AS T_1

WHERE/IN 부분을 자세히 보시면 됩니다.
여기에 '2021년에 등록한 회원들'이라는 조건을 넣으면 됩니다.

유의할 점은,
제가 처음에 SELECT ＊이라고 작성했는데,
이는 틀렸습니다.

WHERE/IN 문법을 자세히 보면,
우리는 IN에 있는 값들에 속하는지 속하지 않는지를 판단합니다.

SELECT ＊은 모든 열을 다 선택하는 문법이므로,
논리에서 어긋납니다.

따라서, USER_ID 하나만을 선택하여,
해당 조건을 만족하는 USER_ID(2021년에 USER_INFO 테이블에 등록된 USER_ID) 값들 안에 포함되어 있는지 안 되어 있는지로 판단해야 합니다.

---

이렇게 해서 우리는

1. 2021년에 등록된 회원들 중
   2.물품을 구매한 회원 수 구하기

이 과정을 마무리했습니다.

하지만, 우리는 지금 '총 수'를 먼저 구했습니다.
문제 예시를 보면, 테이블의 속성들을 먼저 뽑아내야하므로,
맨 처음 풀이과정처럼 해결해야 합니다.

---

3. 마무리

이렇게 해서 프로그래머스 LEVEL_5 문제를 풀어보았습니다.

논리에서 어렵다기 보다는 문제가 복잡하게 되어있어서 헷갈렸던 거 같습니다.

이번에도 제 긴 글을 봐주셔서 감사합니다!! :) bb