문제 : orders 테이블에서 월별(년-월) 주문 건수를 계산하되, 주문이 없는 달도 0건으로 포함하고, 지난달 대비 주문 건수 증감율(growth_rate)을 계산하세요. 결과는 년-월(month) 순서대로 정렬하며, 증감율은 소수점 둘째 자리까지 반올림 해주세요.
결과 컬럼: month, cnt_orders, growth_rate
풀이 과정 및 피드백 :
월별 날짜 테이블이기때문에 증감율을 구하기 위해 `lag() OVER()` 윈도우 함수를 사용하여 전월 값 컬럼을 추가로 생성하고, 증감률을 계산하고자 함.
테이블을 확인해보니 중간중간 빠진 월이 존재. 문제 조건에서 주문이 없는 달은 0건으로 집계 해야하기 때문에 먼저 `누락 월`의 `행`을 추가하는 게 필요 (행을 생성해야함) → `재귀적 CTE (=WITH RECURSIVE) & `LEFT JOIN` & NULL 값 처리 진행
✔️ 재귀적 CTE 란 ? - 테이블을 기반으로 재귀적으로 반복해주는 구문임. - 행을 만들때 주로 사용 ( EX. 누락 월 생성)
-- 재귀적 CTE 기본 형태1: 새롭게 생성할 경우 --
WITH RECURSIVE 테이블명 (컬럼명) AS (-- 반복할 테이블 기재
SELECT 초기값 -- 반복을 시작할 첫번째 값
UNION ALL
SELECT 반복 생성할 쿼리
WHERE 반복 조건(종료 조건) -- FALSE가 나오면 반복종료!! where, limit 등 사용하시면됩니다
)
SELECT *
FROM 테이블명;
-- 재귀적 CTE 기본 형태2 :기존 테이블에서 행 추가 할경우 --
WITH RECURSIVE 테이블명 (컬럼명) AS (-- 반복할 테이블 기재
SELECT 초기값 -- 반복을 시작할 첫번째 값
FROM 테이블명 -- 추가할 테이블명
UNION ALL
SELECT 반복 생성할 쿼리
FROM 테이블명 -- 동일한 반복문 테이블명
WHERE 반복 조건(종료 조건) -- FALSE가 나오면 반복종료!! where, limit 등 사용하시면됩니다
)
SELECT *
FROM 테이블명;
-- 예제1 : 컬럼NUM 를 생성하고 1,2,3 값을 저장
WITH RECURSIVE numbers (num) AS (
SELECT 1 -- 초기값은 1
UNION ALL
SELECT num + 1 -- 반복 쿼리: 이전 값에 1을 더함
FROM numbers
WHERE num < 3 -- 반복 조건: num이 3 미만 -> num이 3 되면 반복문 종료!!
)
SELECT *
FROM numbers;
-- 예제2 : n 컬럼을 추가하고 1~100까지 값을 저장해줘
WITH RECURSIVE cte (n) AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM cte
LIMIT 100 -- 100행까지 만들면 종료
)
SELECT *
FROM cte;
정답 쿼리
WITH RECURSIVE all_months AS ( -- 재귀적 cte : 누락 월 생성
SELECT DATE_FORMAT(MIN(order_purchase_timestamp), '%Y-%m-01') AS months -- 생성을 시작할 초기 값(=가장 오래된,첫번째 날)
FROM orders
UNION ALL
SELECT DATE_ADD(months, INTERVAL 1 MONTH) -- 생성 조건(위에 선언한 변수 사용 가능) : 초기값 +1달 값 생성
FROM all_months -- 재귀문 테이블명
WHERE DATE_ADD(months, INTERVAL 1 MONTH) <= (SELECT DATE_FORMAT(MAX(order_purchase_timestamp), '%Y-%m-01') FROM orders) -- 종료 조건 : 가장 최근일 (= 마지막 날짜 값)보다 작거나 같으면 종료.-> 넘어가면 미래의 날짜가 추가생성되는 개념
),monthly_orders AS (-- 재귀문이랑 SELF JOIN(LEFT) 하여 누락월 삽입
SELECT
m.months,
COALESCE(COUNT(o.order_id), 0) AS cur_orders -- 생성한 월의 다른 행은 전부 NULL -> 0으로 대치
FROM all_months m
LEFT JOIN orders o ON DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m-01') = m.months
GROUP BY m.months
)
SELECT
months,
cur_orders,
prev_orders,
ROUND( -- 증감률 구하기 : 0일때 제외해야하니까 :(현재값) - ( 과거값 + 1) / (과거값 + 1)
CASE
WHEN prev_orders IS NULL THEN NULL -- 지난달 값이 없을때
WHEN prev_orders = 0 THEN cur_orders * 100 -- 지난달 값이 0일때, 비율이니까
ELSE (cur_orders / prev_orders-1) * 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;
내 쿼리
WITH RECURSIVE cte AS( -- 재귀cte 누락월 채우기
SELECT date_format(min(order_purchase_timestamp),'%Y-%m-01') AS new_month
FROM orders o
UNION ALL
SELECT date_add(new_month,INTERVAL 1 MONTH)
FROM cte
WHERE date_add(new_month,INTERVAL 1 MONTH) <= (SELECT date_format(max(order_purchase_timestamp),'%Y-%m-01') FROM orders o )
),prev AS (
SELECT c.new_month
,COALESCE (count(DISTINCT order_id),0) AS cnts -- 집계안된값은 0으로 연결
,lag(count(DISTINCT order_id),1) OVER (ORDER BY c.new_month) AS prev_cnts
FROM cte c
LEFT JOIN orders o ON c.new_month=date_format(o.order_purchase_timestamp,'%Y-%m-01')
GROUP BY c.new_month
)
SELECT date_format(new_month,'%Y-%d') AS 'month'
,cnts
,prev_cnts
,CASE WHEN prev_cnts=0 THEN cnts*100 -- 분모가 0일때
WHEN prev_cnts IS NULL THEN NULL -- 분모가 null 일때
ELSE (cnts/prev_cnts-1)*100.0
END AS growth_rate
FROM prev;
📂문제 6.결제 이상치 탐지 (이상치 계산)
문제: 각 결제 방식(payment_type)별 결제 금액의 평균 ± 3 표준편차(standard deviation)를 기준으로 이상치를 'YES' OR 'NO 탐지하세요. 결제 금액이 이 범위를 벗어나면 이상치로 간주합니다. 결제 금액이 큰 순으로 정렬해주세요.
결과 컬럼: order_id, payment_type, payment_value, is_outlier
1. 정규 분포 : 정규분포는 대칭적이고 종 모양의 분포로, 데이터가 평균(μ)을 중심으로 양쪽이 일정한 패턴을 보이는 확률 분포.
2. 이상치 : 평균을 기준으로 3배 표준편차를 벗어난 값은 99.7% 이외의 0.3%에 해당 → 통계적으로 매우 드문 값(이상치)
* 이상치 비율: 0.3% (전체 데이터 중 극단값)
▷ 표준 편차 함수 (통계 함수)
함수
계산 대상
분산 계산 분모
용도
STD
표본
n−1
표본(모집단의 일부 데이터)에 대한 표준편차를 구할 때 사용합니다!
STDDEV
표본
n−1
(STD와 동일!)
STDDEV_POP
모집단
n
데이터 전체(모집단)에 대한 표준편차를 구할 때 사용합니다.
정답쿼리 &내쿼리
WITH payment_std AS (
SELECT order_id
,payment_type
,payment_value
,avg(payment_value) OVER (PARTITION BY payment_type) AS avg_per_type
,stddev(payment_value) OVER (PARTITION BY payment_type) AS std_per_type
FROM payments p
)
SELECT order_id
,payment_type
,payment_value
,CASE WHEN payment_value < avg_per_type-3*std_per_type OR payment_value > avg_per_type+3*std_per_type THEN 'yes'
ELSE 'no'
END AS is_outlier
FROM payment_std
select 절 case 문 이상치 구할때 절대값 함수로 더 간단하게 표현 가능 → 증명해보니 가능하긴 한데, 직관적으로 와닿진 않음 더 연습이 필요함
CASE WHEN ABS(payment_value - avg_per_type) > 3 * std_per_type THEN 'yes'
ELSE 'no'
END AS is_outlier
