[QCC] 3회차

양승우·2024년 11월 25일

코드카타

목록 보기
33/58

해당 문제는 문제가 너무 길기도 하고, 그만큼 풀이 과정도 길기 때문에 새로 알게된 점을 중심으로 정리하고자 한다.

문제

당신은 이커머스 회사 ’루팡(주)’의 고객 코호트 분석팀에 속해 있습니다.
이번 분석의 목표는 2011년 12월에 첫 주문을 한 고객에 대해,
여러 연도별 매출 관련 지표를 집계하는 것입니다.
(링크)

문제 상세

  1. 첫 주문 날짜가 2011년 12월인 고객만 필터링합니다.
  2. 각 고객의 첫 주문과 마지막 주문 사이의 기간을 기준으로, 그 기간 동안의 연평균 총거래액(GMV)을 계산합니다. 소수점 2째자리까지 계산해주세요.
    • 총거래액, GMV(Gross Merchandise Value)는 다음과 같이 구합니다: 💸 **GMV = 제품 단가 (`unitPrice`) × 주문 수량 (`orderQty`)**
    • 특정 연도에 주문을 하지 않았다면, 그 연도의 GMV와 주문 수는 0으로 처리하고 계산합니다. 예:
      • 고객 A: 첫주문 2011년 ($100), 2012년 ($250), 마지막 주문 2013년 ($400)
        • 연평균 GMV = (100 + 250 + 400) / 3 = $250.00
      • 고객 B: 첫주문 2011년 ($100), 마지막 주문 2013년 ($200)
        • 연평균 GMV = (100 + 0 + 200) / 3 = $100.00 (2012년에는 주문이 없으므로 그 해의 총거래액(GMV)은 0으로 처리하여 평균 계산)
      • 고객 C: 첫주문 & 마지막 주문 2011년 ($100)
        • 연평균 GMV = 100 / 1 = $100.00
  3. 고객별 각 연도에서의 주문 수를 집계하고, 최대값과 그 최대값을 발생시킨 연도를 찾습니다.
    • 만약 최대 주문 수가 여러 연도에서 발생하면, 더 최근의 연도를 선택합니다. 예:
      • 고객 A: 2011년 (주문 5개), 2012년 (주문 10개), 2013년 (주문 11개)
        • 최대 주문수: 11개
        • 최대 주문수가 발생한 연도: 2013년
      • 고객 B: 2011년 (주문 10개), 2012년 (주문 5개), 2013년 (주문 10개)
        • 최대 주문수: 10개
        • 최대 주문수가 발생한 연도: 2013년 (2011년과 같은 주문 수지만 더 최근의 연도)
  4. 결과는 고객 ID(customerid) 기준으로 오름차순으로 정렬합니다.

알게된 점

(풀이과?정)

dim_years의 활용

주어진 테이블에서 JOIN을 하다보면 특정 컬럼값들이 제외되는 경우가 발생한다
해당 문제에서는 2011년 ~ 201n년간 고객 주문 데이터를 보여주는데, 고객이 주문을 하지 않은 연도의 경우 JOIN 과정에서 누락되게 된다.
하지만 동시에 문제에서는 주문이 없는 경우 주문수를 0으로 간주하고, 이를 통해 평균까지 계산을 요구하고 있다.
이럴 때 dim_years 테이블을 LEFT JOIN 하는 것으로 비어있는 컬럼값을 채워줄 수 있다.

고객별 first, last 주문 연도 구하기

SELECT 
	c.customerid 
	, concat(c.firstname, ' ', c.lastname) AS "customer_name"
	, min(YEAR(s.orderdate)) AS "first_orderdate"
	, max(YEAR(s.orderdate)) AS "last_orderdate"
FROM
	sales_order s
	INNER JOIN customer c ON c.customerid = s.customerid
GROUP BY
	c.customerid
HAVING 
	date_format(min(orderdate), '%Y-%m') >= '2011-12'
;

dim_years를 통해 누락된 모든 연도 붙이기

WITH find_minmax AS (
	-- 첫 주문과 마지막 주문 연도 계산
	-- 고객 이름 = 이름 + 성
	-- Filter: 2011년 12월에 첫 주문한 고객 
	SELECT
	    c.customerid,
	    CONCAT (c.firstname, ' ', c.lastname) customer_name,
	    MIN(YEAR (so.orderdate)) AS first_order_year,
	    MAX(YEAR (so.orderdate)) AS last_order_year
	FROM
	    qcc.customer c
	    INNER JOIN qcc.sales_order so ON c.customerid = so.customerid
	GROUP BY
	    c.customerid
	HAVING
	    DATE_FORMAT (MIN(so.orderdate), '%Y-%m') = '2011-12'
)
SELECT 
	fm.customerid
	, fm.customer_name
	, dy.`year` as "year"
FROM
	find_minmax fm
	LEFT JOIN dim_years dy
		ON dy.`year` BETWEEN fm.FIRST_order_year AND fm.last_order_year
;

LEFT JOIN 시 GROUP BY 기준 테이블 선정

A LEFT JOIN B라는 문법은, A 테이블을 기준으로 A 테이블에 있는 모든 값은 유지하면서, 이 중 B 테이블에만 있는 값을 추가하는 형태이다

다만 여기서 중요한 것은, GROUP BY나 WHERE 등 조건을 달아줄 때 A의 컬럼과 B의 컬럼 중 어느 것을 선택하는지에 따라 결과가 달라질 수 있다는 점이다
심지어 그것이 ON절을 통해 명시한 id컬럼일지라도 말이다.

올바른 케이스

WITH find_minmax AS (
	-- 첫 주문과 마지막 주문 연도 계산
	-- 고객 이름 = 이름 + 성
	-- Filter: 2011년 12월에 첫 주문한 고객 
	SELECT
	    c.customerid,
	    CONCAT (c.firstname, ' ', c.lastname) customer_name,
	    MIN(YEAR (so.orderdate)) AS first_order_year,
	    MAX(YEAR (so.orderdate)) AS last_order_year
	FROM
	    qcc.customer c
	    INNER JOIN qcc.sales_order so ON c.customerid = so.customerid
	GROUP BY
	    c.customerid
	HAVING
	    DATE_FORMAT (MIN(so.orderdate), '%Y-%m') = '2011-12'
),
match_dim_years AS (
	SELECT 
		-- dim_year와 left_join으로 연결하여 customerid마다 min부터 max 사이의 year를 부여
		fm.customerid
		, fm.customer_name
		, dy.`year` as "year"
	FROM
		find_minmax fm
		LEFT JOIN dim_years dy
			ON dy.`year` BETWEEN fm.FIRST_order_year AND fm.last_order_year
)
SELECT 
	# 고객별, 연도별 GMV와 주.문.수 계산
	dy.customerid
	, dy.customer_name
	, dy.year
	, COALESCE(sum(so.orderqty * so.unitprice), 0) AS "GMV"
	, coalesce(sum(so.orderqty), 0) AS "order_cnt"
FROM
	match_dim_years dy
	LEFT JOIN sales_order so
		ON dy.customerid = so.customerid 
			AND dy.YEAR = year(so.orderdate)
GROUP BY
	dy.customerid
	, dy.year
ORDER BY 
	1, 3
;

LEFT JOIN의 왼쪽에 match_dim_years dy가 위치해있고,
ON절에서는 dy.customerid = so.customerid를 지정해주었다.
동시에 GROUP BY에서 dy.customerid, dy.year를 명시해주었다.

이렇게 작성하면 dy의 year 값들을 전부 살리면서, B에는 없는 부분들에 대해서는 Null이 들어가게 된다 (-> 이를 COALESCE 함수를 통해 0으로 변경)

잘못된 케이스

하지만 딱 하나,
GROUP BY에서 dy 대신 so.customerid를 쓰면, 결과는 전혀 달라진다

WITH find_minmax AS (
	-- 첫 주문과 마지막 주문 연도 계산
	-- 고객 이름 = 이름 + 성
	-- Filter: 2011년 12월에 첫 주문한 고객 
	SELECT
	    c.customerid,
	    CONCAT (c.firstname, ' ', c.lastname) customer_name,
	    MIN(YEAR (so.orderdate)) AS first_order_year,
	    MAX(YEAR (so.orderdate)) AS last_order_year
	FROM
	    qcc.customer c
	    INNER JOIN qcc.sales_order so ON c.customerid = so.customerid
	GROUP BY
	    c.customerid
	HAVING
	    DATE_FORMAT (MIN(so.orderdate), '%Y-%m') = '2011-12'
),
match_dim_years AS (
	SELECT 
		-- dim_year와 left_join으로 연결하여 customerid마다 min부터 max 사이의 year를 부여
		fm.customerid
		, fm.customer_name
		, dy.`year` as "year"
	FROM
		find_minmax fm
		LEFT JOIN dim_years dy
			ON dy.`year` BETWEEN fm.FIRST_order_year AND fm.last_order_year
)
SELECT 
	# 고객별, 연도별 GMV와 주.문.수 계산
	dy.customerid
	, dy.customer_name
	, dy.year
	, COALESCE(sum(so.orderqty * so.unitprice), 0) AS "GMV"
	, coalesce(sum(so.orderqty), 0) AS "order_cnt"
FROM
	match_dim_years dy
	LEFT JOIN sales_order so
		ON dy.customerid = so.customerid 
			AND dy.YEAR = year(so.orderdate)
GROUP BY
	so.customerid
	, dy.year
ORDER BY 
	1, 3
;


분명 ON절에서 dy와 so의 id가 동일한 경우만을 추출했지만,
GROUP BY를 할 때 LEFT JOIN의 왼쪽에 있는 테이블의 컬럼을 사용하지 않으면 sales_order 테이블이 기준이 되어버리면서 match_dim_years 테이블의 값들이 소실되는 것이다.

이는 INNER JOIN에서는 발생하지 않는, LEFT JOIN이기에 발생한 문제 포인트이다
주로 INNER JOIN만 사용하기도 하고, ON절에서 customerid 조건을 걸어주어서 간과할 수 있는 포인트였지만, LEFT JOIN의 목적을 고려한다면 GROUP BY 또한 당연히 LEFT 테이블 기준으로 해야한다는 것을 기억할 필요가 있다.

주문수 : 주문 횟수와 주문 수량

튜터님의 코드와 내가 제출한 코드의 결과는 아래 캡쳐에서 볼 수 있는 1군데에서만 차이를 보인다.

count에 1의 차이가 존재하고, 이건 꽤나 중요한 문제라고 생각되어서,
어떤 부분에서 내가 답을 놓쳤는지 알기 위해 customerid=29858을 확인해보았다.

확인한 결과, 2012년 row수는 3이고, sum(orderqty)는 4이었다.

문제에서 요구한 바는 ‘주문수’
sales_order 테이블의 설명에서는 orderqty를 ‘주문 수량’이라고 명확하게 언급하고 있다

즉, 내가 구한 것은 ‘주문(한 횟)수’였으며,
문제에서 요구하는 ‘주문 수(량)’이 아니었기 때문에 차이가 발생한 것이다.

최종 코드

그 외 세부적인 내용은 문법적으로 크게 어렵거나, 새롭게 알게된 부분은 아니기에 여기서는 생략하고자 한다.
마지막으로 해당 문제의 최종적인 코드는 아래와 같다.
다소 더 축약할 수 있을지는 모르겠지만, 기본적인 틀은 아래와 같다고 볼 수 있을 것이다.

WITH find_minmax AS (
	-- 첫 주문과 마지막 주문 연도 계산
	-- 고객 이름 = 이름 + 성
	-- Filter: 2011년 12월에 첫 주문한 고객 
	SELECT
	    c.customerid,
	    CONCAT (c.firstname, ' ', c.lastname) customer_name,
	    MIN(YEAR (so.orderdate)) AS first_order_year,
	    MAX(YEAR (so.orderdate)) AS last_order_year
	FROM
	    qcc.customer c
	    INNER JOIN qcc.sales_order so ON c.customerid = so.customerid
	GROUP BY
	    c.customerid
	HAVING
	    DATE_FORMAT (MIN(so.orderdate), '%Y-%m') = '2011-12'
),
match_dim_years AS (
	SELECT 
		# dim_year와 left_join으로 연결하여 customerid마다 min부터 max 사이의 year를 부여
		fm.customerid
		, fm.customer_name
		, dy.`year` as "year"
	FROM
		find_minmax fm
		LEFT JOIN dim_years dy
			ON dy.`year` BETWEEN fm.FIRST_order_year AND fm.last_order_year
),
cal_gmv AS (
	SELECT 
		# 고객별, 연도별 GMV와 주.문.수 계산
		dy.customerid
		, dy.customer_name
		, dy.year
		, COALESCE(sum(so.orderqty * so.unitprice), 0) AS "GMV"
		, coalesce(sum(so.orderqty), 0) AS "order_cnt"
	FROM
		match_dim_years dy
		LEFT JOIN sales_order so
			ON dy.customerid = so.customerid 
				AND dy.YEAR = year(so.orderdate)
	GROUP BY
		dy.customerid
		, dy.year
	ORDER BY 
		1, 3
),
cal_ymv AS (
	# 고객별 GMV의 평균을 계산
	# 사전에 미리 COALESCE를 통해 0을 입력했기에 그냥 AVG 함수를 사용해도 제대로 계산 가능
	SELECT 
		customerid
		, customer_name
		, round(avg(gmv), 2) AS "YMV"
	FROM 
		cal_gmv
	GROUP BY
		customerid
),
cal_max_order AS (
	# 최고 주문수와 그 연도를 뽑기 위해 rank 함수를 사용
	SELECT 
		customerid
		, year
		, order_cnt
		, rank() OVER (PARTITION BY customerid ORDER BY order_cnt DESC, YEAR desc) AS "rnk"
	FROM 
		cal_gmv
)
SELECT
	y.customerid
	, y.customer_name
	, y.ymv AS "avg_yearly_gmv"
	, mo.YEAR AS "max_order_year"
	, mo.ORDER_cnt AS "max_order_count"
FROM
	cal_ymv y
	INNER JOIN (SELECT * FROM cal_max_order WHERE rnk = 1) mo
		ON y.customerid = mo.customerid
ORDER BY
	y.customerid
;
profile
양승우
어제보다 오늘 더
이전 포스트

[코드카타] 알고리즘 60번, 기사단원의 무기

다음 포스트

[코드카타] 알고리즘 63번, 짝꿍

0개의 댓글