1. 오늘 학습 키워드
SQL, Window Function
2. 학습한 내용
윈도우 함수(Window Function)
핵심 기능은 집계 하면서도 개별 행을 그대로 유지할 수 있게 해줌
GROUP BY = 행을 줄임 vs 윈도우 함수 = 행을 줄이지 않고도 집계 적용 가능
over () : 집계 함수에 윈도우(범위)를 지정해주는 기능
over()는 모든 행이 개별적으로 존재, 집계 범위(PARTITION BY)와 정렬 기준(ORDER BY)을 지정 가능
OVER() 안에는 윈도우 프레임 문법이 들어가야 함, 컬럼명을 넣는 건 SQL 문법상 허용 불가
개인 과제 정리
1번 문제
예상 배송일보다 실제 배송을 늦게 받은 고객들 중에서, 가장 많은 주문을 한 고객의 ID와 총 주문 수를 조회하세요.
결과 컬럼: customer_id, total_orders
작성한 답
SELECT customer_id,
COUNT(*) AS total_orders
FROM marketer_orders
WHERE order_delivered_customer_date > order_estimated_delivery_date
GROUP BY customer_id
ORDER BY total_orders DESC
LIMIT 1;
2번 문제
marketer_payments 테이블에서 각 결제 방식(payment_type)별 결제 금액의 합계와 해당 결제 방식이 전체 결제 금액에서 차지하는 비율을 계산하세요.
결과 컬럼: payment_type, total_payment_value, payment_percentage
작성한 답
select payment_type ,
sum(payment_value) as total_payment_value,
ROUND(SUM(payment_value) /
(SELECT SUM(payment_value) FROM marketer_payments) * 100, 2) as paymaent_percentage
from marketer_payments
group by 1
3번 문제
배송 완료된 주문의 총 고객수, 총 주문건수, 총 결제금액, 평균 결제금액을 계산하세요.
작성한 답
select count(distinct a.customer_id) as cnt_users,
count(distinct a.order_id) as cnt_orders,
sum(b.payment_value) as sum_payment,
avg(b.payment_value) as arpu
from marketer_orders a join marketer_payments b on a.order_id=b.order_id
where order_status = 'delivered'
4번 문제
4-1. 각 결제 수단별 평균 결제 금액을 구하는 쿼리를 작성하세요.(1개 쿼리 작성)
출력: payment_type, avg_payment
결제 수단별 평균 => 집계함수 필요
작성한 답
select payment_type,
avg(payment_value) as avg_payment
from marketer_payments
group by 1;
4-2. 각 결제 수단별 평균 결제 금액보다 높은 주문만 조회하세요.(4개 쿼리 작성)
출력: order_id, payment_type, payment_value
필터링 조건에서 사용될 평균금액을 구하기 위해 서브쿼리 사용해야함
작성한 답
select order_id,
payment_type,
payment_value
from marketer_payments
where payment_type='credit_card'
and payment_value > (
select avg(payment_value)
from marketer_payments
where payment_type='credit_card');
SELECT order_id,
payment_type,
payment_value
FROM marketer_payments
WHERE payment_type = 'boleto'
AND payment_value > (
SELECT AVG(payment_value)
FROM marketer_payments
WHERE payment_type = 'boleto');
SELECT order_id,
payment_type,
payment_value
FROM marketer_payments
WHERE payment_type = 'voucher'
AND payment_value > (
SELECT AVG(payment_value)
FROM marketer_payments
WHERE payment_type = 'voucher');
SELECT order_id,
payment_type,
payment_value
FROM marketer_payments
WHERE payment_type = 'debit_card'
AND payment_value > (
SELECT AVG(payment_value)
FROM marketer_payments
WHERE payment_type = 'debit_card');
생각해본 통합형 쿼리
SELECT
mp.order_id,
mp.payment_type,
mp.payment_value
FROM marketer_payments mp
JOIN (
SELECT
payment_type,
AVG(payment_value) AS avg_payment
FROM marketer_payments
GROUP BY payment_type
) avg_table
ON mp.payment_type = avg_table.payment_type
WHERE mp.payment_value > avg_table.avg_payment;
5번 문제
5-1. 필수
marketer_orders 테이블에서 월별 주문 건수 기준으로 전체 주문 건수 대비 각 그룹(월별)의 비율(%)을 계산하고, 소수점 둘째 자리까지 ROUND() 처리하여 출력합니다.
출력 항목 : 출력: , payment_type, cnt_order, order_ratio_percent
소숫점 2자리에서 반올림 할것(ROUND)
집계를 위해 집계 함수 사용
order_ratio_percent = 각 그룹(월별)의 총 주문건수 / 충총 주문건수 * 100
작성한 답
select DATE_FORMAT(STR_TO_DATE(order_purchase_timestamp, '%Y-%m-%d %H:%i:%s'), '%Y-%m') AS month,
count(distinct order_id) as cnt_order,
round(count(distinct order_id)/(select count(distinct order_id) from marketer_orders)*100,2) as order_ratio_percent
from marketer_orders
group by 1;
5-2. 선택
marketer_payments 테이블에서 결제수단별 주문 건수 기준으로 전체 주문 건수 대비 각 그룹(결제수단)의 비율(%)을 계산하고, 소수점 둘째 자리까지 ROUND() 처리하여 출력합니다.
작성한 답
SELECT
payment_type,
COUNT(DISTINCT order_id) AS cnt_orders,
ROUND(COUNT(DISTINCT order_id) /
(SELECT COUNT(DISTINCT order_id) FROM marketer_payments) * 100, 2) AS order_ratio_percent
FROM marketer_payments
GROUP BY 1
order by cnt_orders desc;
5-3. 선택
marketer_orders 와marketer_payments 테이블에서 월별, 결제수단별 주문 건수 기준으로 전체 주문 건수 대비 각 그룹(월별, 결제수단)의 비율(%)을 계산하고, 소수점 둘째 자리까지 ROUND() 처리하여 출력합니다.
작성한 답
SELECT
DATE_FORMAT(STR_TO_DATE(o.order_purchase_timestamp, '%Y-%m-%d %H:%i:%s'), '%Y-%m') AS month,
p.payment_type,
SUM(p.payment_value) AS total_payment_value,
ROUND(
SUM(p.payment_value) /
(SELECT SUM(p2.payment_value)
FROM marketer_orders o2
JOIN marketer_payments p2 ON o2.order_id = p2.order_id) * 100, 2) AS payment_ratio_percent
FROM marketer_orders o
JOIN marketer_payments p ON o.order_id = p.order_id
GROUP BY month, p.payment_type
ORDER BY month, p.payment_type;
6번 문제
marketer_payments 테이블에서 각 결제수단(payment_type) 별 평균 결제금액보다 1.5배 이상 결제된 주문을 찾아, is_outlier 컬럼을 Yes/No로 표시하세요.
출력항목 : order_id , payment_type , payment_value , is_outlier
서브쿼리 사용 : 결재수단별 평균 결재금액 * 1.5 값을 구하기 위해 필요
이상치(outlier) 여부를 판단해서 Yes/No 판단을 하기 위해 CASE WHEN 구문 사용
payment_value 내림차순 정렬
select mp.order_id,
mp.payment_type,
mp.payment_value,
case when mp.payment_value >= outlier_payment then 'Yes'
when mp.payment_value < outlier_payment then 'No' end is_outlier
from marketer_payments mp join
(
select payment_type,
avg(payment_value)*1.5 outlier_payment
from marketer_payments
group by payment_type) avg_table
ON mp.payment_type = avg_table.payment_type
order by mp.payment_value desc;
3. 학습하며 겪었던 문제점
SQL에서는 GROUP BY를 쓰면, 그룹 기준 컬럼 외의 컬럼은 반드시 집계함수로 감싸야 한다는 사실을 제대로 인지하지 못한채로 초반에 서브쿼리를 작성해서 오류가 많이 났다.
그래서 결과가 "각 결제 방식에서 한 개의 랜덤한 row"만 나오고, 나머지 데이터는 사라졌다. 결제 수단마다 대표값만 남을 가능성을 고려하지 못했다.
4. 오늘의 인사이트
- 기본 쿼리가 탄탄해야 함
- 이해하지 못한 채로 답만 내려고 하지 말 것
- 컬럼명 보면서 문제 푸는 것은 최악!
- distinct 하려고 group by 쓰지 말 것
- group by 집계열 외에 다른 것 select 못함
- substr()은 중간에서 잘라올 때, left는 왼쪽에서 잘라올 때 ex) select left(order_purchase_timestamp, 7) as month
5. 내일 학습 할 일 정리
- 피그마 강의 완강
- SQL 복습
