🐬 SQL/🏆 MySQL 문제풀이

🔓 07. VIP 고객 누적 주문 금액 도달 일자 추출 쿼리

nyamin9 2023. 11. 12. 22:39

모바일은 화면을 돌려 가로화면으로 보시는 게 읽으시기 편할 수 있습니다. 돌려서 보시는 걸 추천드릴게요!!

 

 

1. 문제 상황

2022년 한 해 동안 50만원 이상의 제품을 구매한 VIP 고객들에 대해서,

해당 고객들의 첫 구매 이후 누적 주문 금액이 50만원에 도달할 때까지 걸린 일수를 추출하는 쿼리 작성 

  

2. 풀이 계획

  1. 2022년도의 제품 구매 금액이 50만원 이상인 VIP 고객의 ID 추출
  2. 고객 별, 날짜 별 누적 구매 금액 추출
  3. 누적 구매 금액이 50만원이 넘는 일자 확인 후, 첫 구매일자로부터의 날짜 차이 추출 

 

3. 쿼리 작성

 

3.1. 2022년도의 제품 구매 금액이 50만원 이상인 VIP 고객의 ID 추출

SELECT USER_ID
FROM USED_GOODS_BOARD
WHERE CREATED_DATE LIKE '2022-%'
GROUP BY 1
HAVING SUM(PRICE) >= 500000
ORDER BY 1

 

이렇게 구해진 고객 ID를 사용하기 위해서, 위의 쿼리를 서브쿼리로 사용해 고객 별, 날짜 별 누적 금액을 추출하겠습니다. 

 

 

 

3.2. 고객 별, 날짜 별 누적 구매 금액 추출

 

누적합 연산을 위해, SUM( ) OVER ( PARTITION BY ~ ORDER BY ~ ) 구문을 사용합니다.

 

PARTITION BY는 OVER( ) 내에서 사용되는 구문으로, 각 그룹의 개별 수준을 하나로 취급하여 그룹화하는 GROUP BY와는 달리 각 그룹 내의 개별 수준을 따로 취급하여 각 개별 수준에 대한 연산을 해주는 구문입니다. GROUP BY와 비교한다면, 보다 세부적인 그룹 연산을 해주는 느낌입니다. PARTITION BY 뒤의 ORDER BY는 연산 결과를 정렬하는 기준입니다.

 

예를 들어, USER_ID 라는 열이 (1, 1, 1, 2, 2, 3, 3, 3)의 요소를 가지고 있다고 가정하겠습니다. 해당 열을 GROUP BY 로 묶어주는 경우, 추출 결과 USER_ID는 (1, 2, 3) 으로 그룹화됩니다. 하지만 OVER( PARTITION BY ) 를 사용하면 개별 수준을 살려 (1, 1, 1, 2, 2, 3, 3, 3)을 그대로 남긴 상태에서 각 수준 별 연산을 진행합니다. 본 문제의 경우에는 개별 유저 파티션에 대한 구매 가격의 합을 계산한다고 생각하면 됩니다.

-- 누적합 연산
SUM(PRICE) OVER(PARTITION BY USER_ID ORDER BY CREATED_DATE) AS PRICE_CUMSUM

 

앞서 작성한 VIP 고객의 ID를 추출하는 쿼리를 서브쿼리로 사용하면 다음과 같아집니다. 

SELECT USER_ID, PRICE, CREATED_DATE, 
       SUM(PRICE) OVER(PARTITION BY USER_ID ORDER BY CREATED_DATE) AS PRICE_CUMSUM -- 누적합
FROM USED_GOODS_BOARD
WHERE USER_ID IN
(
    SELECT USER_ID
    FROM USED_GOODS_BOARD
    WHERE CREATED_DATE LIKE '2022-%'
    GROUP BY 1
    HAVING SUM(PRICE) >= 500000
    ORDER BY 1
)
ORDER BY 1, 3

 

예를 들면, 아래와 같은 테이블이 되겠네요.

USER ID PRICE PRICE_CUMSUM
1 1000  
1 2000 3000
1 5000 8000
1 10000 18000

 

 

 

3.3. 누적 구매 금액이 50만원이 넘는 일자 확인 후, 첫 구매일자로부터의 날짜 차이 추출

 

A. 

누적 구매 금액이 50만원을 넘는 경우는 아래의 두 가지 경우입니다.

 

1. 첫 구매 가격이 50만원 이상인 경우

2. 첫 구매 이후 지속적인 구매를 거친 후, 누적 구매 가격이 50만원 이상인 경우

 

두 번째 경우에 해당하는 유저에 대해서, 누적 구매 금액이 50만원이 넘어가는 시점을 계산하려면 이전 시점의 누적 금액이 필요합니다. MySQL은 동일 행의 비교에 특화되어 있기 때문에, 각 행에 이전 행의 거래 누적합을 추가하기 위해 LAG( 열 별칭, STEP ) OVER( PARTITION BY ~ ORDER BY ~ ) 구문을 사용할 것입니다. LAG( ) 를 통해, 이전 행의 데이터를 현재 행의 새로운 열에 추가해 줄 수 있습니다.

-- 이전 행의 누적합을 현재 행의 새로운 열에 할당하는 쿼리
LAG(PRICE_CUMSUM, 1) OVER(PARTITION BY USER_ID ORDER BY CREATED_DATE) AS LAG_CUMSUM

 

예를 들면, 아래와 같은 테이블이 되겠네요.

USER ID PRICE PRICE_CUMSUM LAG_CUMSUM
1 1000 1000  
1 2000 3000 1000
1 5000 8000 3000
1 10000 18000 8000

 

유저별 첫 구매의 경우, 이전 시점의 누적합이 NULL로 들어가 있는 것을 확인할 수 있습니다.

또한 시점을 알기 위해, 테이블에서 거래 일자도 추출해야 합니다. 

 

지금까지 사용한 쿼리를 보면, 여러 가지 함수도 사용했고 서브쿼리도 사용한 상황이라서 꽤나 복잡한 상황입니다. 각 열에 대한 연산의 편의성을 위해, WITH 구문을 사용해서 지금까지의 쿼리를 하나의 임시 테이블로 저장해두겠습니다.

WITH TEMP1 AS 
(
    SELECT USER_ID, PRICE, PRICE_CUMSUM, DATE_FORMAT(CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE,
           LAG(PRICE_CUMSUM, 1) OVER(PARTITION BY USER_ID ORDER BY CREATED_DATE) AS LAG_CUMSUM
    FROM
    (
        SELECT USER_ID, PRICE, CREATED_DATE, 
        SUM(PRICE) OVER(PARTITION BY USER_ID ORDER BY CREATED_DATE) AS PRICE_CUMSUM -- 누적합
        FROM USED_GOODS_BOARD
        WHERE USER_ID IN
        (
            SELECT USER_ID
            FROM USED_GOODS_BOARD
            WHERE CREATED_DATE LIKE '2022-%'
            GROUP BY 1
            HAVING SUM(PRICE) >= 500000
            ORDER BY 1
        )
        ORDER BY 1, 3
    ) AS CUMSUM_TABLE   
)

위 쿼리의 AS CUMSUM_TABLE에서 볼 수 있듯이, 서브쿼리를 FROM 절에 사용할 때는 테이블의 이름을 정의해주어야 합니다.

이렇게 하면 각 유저의 거래 일자, 거래 가격, 누적 거래 금액, 이전 시점의 누적 거래 금액을 확인할 수 있습니다. 

 

 

B.

이제는 첫 구매 시점과, 누적 거래액이 50만원이 넘는 시점 간의 차이를 구해주어야 합니다. MySQL은 동일 행 내의 연산을 주로 지원하기 때문에, 이전 시점의 누적합을 구할 때와 동일하게 LAG( ) 구문을 사용하겠습니다.

LAG(CREATED_DATE, 1) OVER(PARTITION BY USER_ID ORDER BY CREATED_DATE) AS LAG_DATE

 

이를 통해, 각 시점과 이전 시점의 날짜 차이를 구할 수 있습니다. 이때, 각 유저별 첫 구매인 경우에는 이전 시점과의 간격을 계산할 수 없기 때문에 데이터가 NULL로 채워집니다. 이러한 경우는 IFNULL 함수를 사용해 0으로 채워주겠습니다.

 

-- 이전 시점과의 날짜 차이 연산
IFNULL(
DATEDIFF(CREATED_DATE, LAG(CREATED_DATE, 1) 
OVER(PARTITION BY WRITER_ID ORDER BY CREATED_DATE)), 0) AS DIFF

 

 

C.

이제는 다시 누적 구매 금액이 50만원을 넘는 경우를 따져봐야 합니다.

 

1. 첫 구매 가격이 50만원 이상인 경우

2. 첫 구매 이후 지속적인 구매를 거친 후, 누적 구매 가격이 50만원 이상인 경우

 

1의 경우에는, 첫 구매 가격이 50만원을 넘는 경우를 판별해주어야 합니다. 이를 위해서는. 첫 구매를 판별할 수 있는 로직이 필요합니다. 앞서 유저의 첫 구매의 경우 누적 구매 금액이 NULL임을 확인 했기에, 이를 판별할 수 있는 조건문을 사용할 것입니다. 2의 경우에는, 이전 시점의 누적 금액은 50만원 미만이었으나 현재 시점의 거래 결과 누적 거래 금액이 50만원 이상이 되는 경우입니다. 결과적으로 사용한 조건문은 아래와 같은 형태입니다.

-- LAG_CUMSUM : 이전 시점까지의 누적 거래 금액
-- PRICE_CUMSUM : 현재 시점까지의 누적 거래 금액

WHERE (LAG_CUMSUM IS NULL)  -- 유저 첫 구매 행
OR (LAG_CUMSUM < 500000 AND PRICE_CUMSUM >= 500000) -- 누적 구매 금액이 50만원을 초과하는 행

 

위 조건을 사용하면, 첫 구매 일자와 50만원이 넘는 시점의 일자만을 추출하기 때문에 각 유저의 총 구매 기간을 추출할 수 있습니다. 하지만 아직 첫번째 구매 금액이 50만원 이상인 경우는 따지지 않았기 때문에, 추가 쿼리가 필요합니다. 사용한 구문 하나하나가 너무 길기 때문에, 일단 임시 테이블에 저장하고 마지막 쿼리를 짜겠습니다.

TEMP2 AS 
(
    SELECT *, 
           LAG(CREATED_DATE, 1) OVER(PARTITION BY USER_ID ORDER BY CREATED_DATE) AS LAG_DATE,
           IFNULL(
           DATEDIFF(CREATED_DATE, LAG(CREATED_DATE, 1) 
           OVER(PARTITION BY USER_ID ORDER BY CREATED_DATE)), 0) AS DIFF
    FROM TEMP1
    WHERE (LAG_CUMSUM IS NULL)  -- 유저 첫 구매 행
    OR (LAG_CUMSUM < 500000 AND PRICE_CUMSUM >= 500000) -- 누적 구매 금액이 50만원을 초과하는 행
)

 

위 임시 테이블 TEMP2 추출 시 다음과 같습니다.

ID PRICE SUM DATE LAG_SUM LAG_DATE DIFF
1 120000 120000 2022-10-14     0
1 450000 870000 2022-11-26 420000 2022-10-14 43
2 125000 125000 2022-10-05     0
2 380000 521000 2022-10-11 141000 2022-10-05 6
3 1500000 1500000 2022-10-05     0

 

유저 1, 2의 경우에는 첫 구매 일자와 누적 금액이 50만원이 넘는 시점의 일자 간의 차이를 구할 수 있습니다.

즉, <2. 첫 구매 이후 지속적인 구매를 거친 후, 누적 구매 가격이 50만원 이상인 경우>를 해결했습니다.

다만 첫 구매가 50만원 이상인 유저 3의 해결을 위해서는 추가적인 조건이 필요합니다.

 

 

D.

위 TEMP2 테이블에서 볼 수 있듯이, 유저 3은 첫 구매 가격이 50만원 이상이고 DIFF 열의 값이 0입니다. 또한 유저 1,2는 DIFF의 값이 0이 아닙니다. 이 두가지 조건을 사용해서, 최종 쿼리를 짜면 다음과 같습니다.

SELECT WRITER_ID, DIFF
FROM TEMP2
WHERE (PRICE_CUMSUM >= 500000 AND DIFF = 0) -- 첫 구매가 50만원 이상인 행
OR (DIFF != 0)

 

결과는 다음과 같겠네요😀.

ID DIFF
1 43
2 6
3 0

 

 

4. 전체 쿼리

WITH TEMP1 AS 
(
    SELECT USER_ID, PRICE, PRICE_CUMSUM, DATE_FORMAT(CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE,
           LAG(PRICE_CUMSUM, 1) OVER(PARTITION BY USER_ID ORDER BY CREATED_DATE) AS LAG_CUMSUM
    FROM
    (
        SELECT USER_ID, PRICE, CREATED_DATE, 
        SUM(PRICE) OVER(PARTITION BY USER_ID ORDER BY CREATED_DATE) AS PRICE_CUMSUM -- 누적합
        FROM USED_GOODS_BOARD
        WHERE USER_ID IN
        (
            SELECT USER_ID
            FROM USED_GOODS_BOARD
            WHERE CREATED_DATE LIKE '2022-%'
            GROUP BY 1
            HAVING SUM(PRICE) >= 500000
            ORDER BY 1
        )
        ORDER BY 1, 3
    ) AS CUMSUM_TABLE   
),

TEMP2 AS 
(
    SELECT *, 
           LAG(CREATED_DATE, 1) OVER(PARTITION BY USER_ID ORDER BY CREATED_DATE) AS LAG_DATE,
           IFNULL(
           DATEDIFF(CREATED_DATE, LAG(CREATED_DATE, 1) 
           OVER(PARTITION BY USER_ID ORDER BY CREATED_DATE)), 0) AS DIFF
    FROM TEMP1
    WHERE (LAG_CUMSUM IS NULL)  -- 유저 첫 구매 행
    OR (LAG_CUMSUM < 500000 AND PRICE_CUMSUM >= 500000) -- 누적 구매 금액이 50만원을 초과하는 행
)


SELECT USER_ID, DIFF
FROM TEMP2
WHERE (PRICE_CUMSUM >= 500000 AND DIFF = 0) -- 첫 구매가 50만원 이상인 행
OR (DIFF != 0)

 

 

저작자표시

'🐬 SQL > 🏆 MySQL 문제풀이' 카테고리의 다른 글

🔓 09. LeetCode SQL 50 Easy LEVEL 문제풀이  (2) 2023.11.26
🔓 08. 주요 구문 / 함수 정리  (4) 2023.11.20
🔓 06. 프로그래머스 LEVEL4, 5 문제풀이  (0) 2023.11.11
🔓 05. 프로그래머스 LEVEL3 문제풀이  (0) 2023.10.27
🔓 04. 프로그래머스 LEVEL2 문제풀이  (0) 2023.10.25

'🐬 SQL/🏆 MySQL 문제풀이'의 다른글

  • 현재글🔓 07. VIP 고객 누적 주문 금액 도달 일자 추출 쿼리

관련글

댓글

티스토리툴바