SQL 과제 오답: WITH | WITH RECURSIVE | STEDDEV

2. 결과 방식별 통계 분석: payments 테이블
payments 테이블에서 각 결제 방식별 결제 금액의 합계와 해당 결제 방식이 전체 결제 금액에서 차지하는 비율 구하기

-- 내가 짠 쿼리
SELECT payment_type, 
	total_payment_value, 
	ROUND((total_payment_value / SUM(total_payment_value) OVER())*100, 2) payment_percentagee
FROM (
	SELECT payment_type, 
		SUM(payment_value) total_payment_value
	FROM payments
	GROUP BY payment_type
	) p
ORDER BY total_payment_value;

-- 정답: 훨씬 간단!
SELECT 
    payment_type,
    SUM(payment_value) AS total_payment_value,
    ROUND(SUM(payment_value) * 100.0 / (SELECT SUM(payment_value) FROM payments), 2) AS payment_percentage
FROM payments
GROUP BY 1
ORDER BY 2 DESC;

 

3. 유저별 결제 및 주문 분석: orders 테이블
배송된 주문을 기준으로 고유 고객 수, 총 주문 수, 총 결제 금액, 그리고 고객 1명당 평균 결제액 계산

-- 내가 짠 쿼리
SELECT cnt_users,
	cnt_orders,
	sum_payment,
	sum_payment/ cnt_users arppu
FROM (
	SELECT COUNT(o.customer_id) cnt_users, 
		COUNT(o.order_id) cnt_orders, 
		SUM(p.payment_value) sum_payment
	FROM orders o
	JOIN payments p ON o.order_id = p.order_id 
	WHERE o.order_status = 'delivered'
	) a;

-- 정답
-- 단일 쿼리로 처리되어 가독성이 높으며 성능 최적화 가능
-- cnt_users가 0인 경우를 처리하여 오류 발생 가능성x
SELECT 
    COUNT(DISTINCT o.customer_id) AS cnt_users,
    COUNT(o.order_id) AS cnt_orders,
    SUM(p.payment_value) AS sum_payment,
    CASE
        WHEN COUNT(DISTINCT o.customer_id) = 0 THEN 0
        ELSE SUM(p.payment_value) / COUNT(DISTINCT o.customer_id)
    END AS arppu 
FROM orders o
INNER JOIN payments p ON o.order_id = p.order_id
WHERE o.order_status = 'delivered';

 

4. 고과금 결제 비율 분석: payments 테이블
동일한 결제수단에서 이루어진 다른 결제의 평균 금액보다 높은 결제들 중, 해당 결제가 해당 결제수단의 총 결제 금액 대비 20% 이상을 차지하는 주문 조회
payment_ratio는 결제 금액이 총 결제 금액에서 차지하는 비율을 소수점 둘째 자리까지 계산

 

-- WITH() 함수: 쿼리의 일부를 이름으로 지정하여 임시 테이블처럼 사용할 수 있는 기능
WITH payment_summary AS (
    SELECT 
        payment_type,
        AVG(payment_value) AS avg_payment_value,
        SUM(payment_value) AS total_payment_value
    FROM payments
    GROUP BY payment_type
)
SELECT 
    p.order_id,
    p.payment_type,
    p.payment_value,
    ROUND(p.payment_value / ps.total_payment_value * 100, 2) AS payment_ratio
FROM payments p
JOIN payment_summary ps ON p.payment_type = ps.payment_type
WHERE p.payment_value > ps.avg_payment_value -- 조건1 체크!
AND p.payment_value / ps.total_payment_value >= 0.2; -- 조건2 체크!

 

5. 월별 주문 추세 분석: orders 테이블
orders 테이블에서 월별 주문 건수 계산 (단. 주문이 없는 달도 0건으로 포함), 지난달 대비 주문 건수 계산
결과는 년-월 순서대로 정렬, 증감율은 소수점 둘째 자리까지 반올림

• 재귀적 CTE: WITH RECURSIVE

WITH RECURSIVE 테이블명 (컬럼명) AS (
    SELECT 초기값 
    UNION ALL
    SELECT 반복할 쿼리 -- 이전값에 대해 쿼리 적용
    WHERE 반복 조건 -- FALSE가 나오면 반복종료!! where, limit 등 사용하시면됩니다
)
SELECT * FROM 테이블명;

-- 1단계: 월별 주문 건수 계산 및 누락월 확인
SELECT 
    DATE_FORMAT(order_purchase_timestamp, '%Y-%m') AS months,
    COUNT(order_id) AS cnt_orders
FROM orders
GROUP BY months
ORDER BY months;

-- 2단계: 누락월 생성***
WITH RECURSIVE all_months AS (
    SELECT DATE_FORMAT(MIN(order_purchase_timestamp), '%Y-%m') AS months -- MIN값: 2016-10
    FROM orders -- 초기값
    UNION ALL
    SELECT DATE_FORMAT(DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH), '%Y-%m') -- 1달 간격으로 01일 추가(형식을 맞추기 위해)
    FROM all_months -- 반복할 쿼리
    WHERE DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH) <= (
        SELECT DATE_FORMAT(MAX(order_purchase_timestamp), '%Y-%m-01') FROM orders -- MAX값: 2018-08
    ) -- 반복 조건
)
SELECT months FROM all_months ORDER BY months; -- 누락월 채워진 것 확인!

-- 3단계: 월별 주문 건수 합계
WITH RECURSIVE all_months AS (
    SELECT DATE_FORMAT(MIN(order_purchase_timestamp), '%Y-%m') AS months
    FROM orders
    UNION ALL
    SELECT DATE_FORMAT(DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH), '%Y-%m')
    FROM all_months
    WHERE DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH) <= (
        SELECT DATE_FORMAT(MAX(order_purchase_timestamp), '%Y-%m-01') FROM orders
    )
)
SELECT 
    m.months,
    COALESCE(COUNT(o.order_id), 0) AS cnt_orders -- COUNT(o.order_id)가 null이면 0 출력
FROM all_months m
LEFT JOIN orders o ON DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m') = m.months
GROUP BY 1
ORDER BY 1;

-- 4단계: 지난달 주문량 계산
WITH RECURSIVE all_months AS (
    SELECT DATE_FORMAT(MIN(order_purchase_timestamp), '%Y-%m') AS months
    FROM orders
    UNION ALL
    SELECT DATE_FORMAT(DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH), '%Y-%m')
    FROM all_months
    WHERE DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH) <= (
        SELECT DATE_FORMAT(MAX(order_purchase_timestamp), '%Y-%m-01') FROM orders
    )
),
monthly_orders AS ( -- 맨 앞에 WITH가 있으므로 이번에는 WITH를 생략해야함
    SELECT 
        m.months,
        COALESCE(COUNT(o.order_id), 0) AS cur_orders
    FROM all_months m
    LEFT JOIN orders o ON DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m') = m.months
    GROUP BY m.months
)
SELECT 
    months,
    cur_orders,
    LAG(cur_orders) OVER (ORDER BY months) AS prev_orders
FROM monthly_orders;

-- *최종* 5단계: 월별 주문 증감율 계산 
WITH RECURSIVE all_months AS (
    SELECT DATE_FORMAT(MIN(order_purchase_timestamp), '%Y-%m') AS months
    FROM orders
    UNION ALL
    SELECT DATE_FORMAT(DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH), '%Y-%m')
    FROM all_months
    WHERE DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH) <= (
        SELECT DATE_FORMAT(MAX(order_purchase_timestamp), '%Y-%m-01') FROM orders
    )
),
monthly_orders AS (
    SELECT 
        m.months,
        COALESCE(COUNT(o.order_id), 0) AS cur_orders
    FROM all_months m
    LEFT JOIN orders o ON DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m') = m.months
    GROUP BY m.months
)
SELECT 
    months,
    cur_orders,
    prev_orders,
    ROUND(
        CASE 
            WHEN prev_orders IS NULL THEN NULL
            WHEN prev_orders = 0 THEN cur_orders * 100
            ELSE (cur_orders - prev_orders) / prev_orders * 100
        END, 
        2
    ) AS growth_rate
FROM (
    SELECT 
        months,
        cur_orders,
        LAG(cur_orders) OVER (ORDER BY months) AS prev_orders
    FROM monthly_orders
) sub
ORDER BY 1;

 

6. 결제 이상치 탐지
각 결제 방식별 결제 금액의 평균 +- 표준편차(standard deviation)를 기준으로 이상치 'YES /NO ' 탐지

 

-- STD, STEDDEV: 표본에 대한 표준편차를 구하는 함수
-- STEDDEV_POP: 모집단에 대한 표준편차를 구하는 함수
-- 이상치: 정규분포의 범위 내에 해당하지 않는 수치
SELECT
	p.order_id,
	p.payment_type,
	p.payment_value,
	CASE -- 이상치 구하기
		WHEN p.payment_value < ps.avg_payment - 3 * ps.stddev_payment
		OR p.payment_value > ps.avg_payment + 3 * ps.stddev_payment THEN 'Yes'
		ELSE 'No'
	END AS is_outlier
FROM payments p
JOIN (
	SELECT 
		payment_type,
		AVG(payment_value) AS avg_payment,
		STDDEV(payment_value) AS stddev_payment -- 표준편차 구하기
	FROM payments
	GROUP BY 1) AS ps 
ON p.payment_type = ps.payment_type
ORDER BY 3 desc;