데이터 분석 트랙 42일차 25.04.11. [TIL]
- qcc 리뷰
- qcc 리뷰
Q1. 지역별 매출이 높은 매장을 조회
1. 조건 : "해당 지역 매장이 2개 이상인 경우"만 결과에 포함한다. 결과는 "지역 이름 기준 오름차순 정렬"
2. 접근(풀이) 방법
- "stores" 테이블 에서 "REGION_NAME"(지역 이름), "STORE_ID"(매장 ID, PK), "SALES"(매출)컬럼을 이용할 것임.
- 단순히 매출이 높은 지역을 확인하는 것이고 그 개수만 필요하기 때문에 "RANK()"함수나 서브쿼리문을 굳이 구할 필요는 없다.
3. <코드 구현>
select region_name, max(salse) highest_sales -- 지역이름과 최고 매출을 구한다. 최고매출은 " highest_sales"로 명명
from stores
group by region_name
having count(store_id) > 1
order by region_name -- 지역 이름 기준 오름차순 정렬
# "having count(store_id) > 1 " : 지역의 매장 개수가 2개 이상인 경우를 필터링하는 코드. COUNT()함수는 행의 개수를 정수로 반환! 따라서 "> 1" 혹은 ">=2"로 필터링 가능
※ 차후 지역명을 타겟팅하기 위해 "rank()"함수를 활용할 수 있다는 점을 기억하기(개인 연습 주제)
Q2. "상품x, y" 조합별 카운팅(중복 포함)
# 차후 [마케팅] 도메인 혹은 그 외에서 데이터 분석에 활용할 수 있으니 잘 기억하기!
1. 설명 : 상품1 구매시 상품 2를 함께 구매할 확률을 분석하고자 한다. 이를 위해 cart_products 테이블에서 서로 다른 두 제품 X, Y가 같은 주문(CART_ID)에 포함된 주문 수를 구한 뒤 제품 이름 X, Y를 기준으로 알파벳 순 오름차순 정렬
2. 접근 방법
A. "self join", "cross join", 등의 방법을 통해 "cart_products"테이블을 join한다.
B. "name", "cart_id" 컬럼을 사용하여 "서로 다른 두 제품 조합"과 "cart_id"에서 해당 조합별 count를 확인한다.
- 여기서 조합의 방식은 '(상품1, 상품2) != (상품2, 상품1)'를 의미한다. 즉, "요소의 중복 조합"을 구해야 한다.
3. <코드 구현>
방법 1. [self join]
select a.name name_x, b.name name_y, count(distinct a.cart_id) orders
from cart_products a
join cart_products b
on a.cart_id = b.cart_id
and a.name <> b.name # 조인 조건 부분 : 조인을 할 때 where의 기능을 수행한다!
# 즉, from에서 self join으로 결합한 뒤에 and로 조건을 걸어줄 수 있음.
group by 1, 2
# "name_x"(상품명x)별로 그룹핑 후 그 그룹핑 대상을 다시 "name_y"(상품명Y)별로 그룹핑한다.
# 이를 통해 self join한 통합 테이블에서 "상품X"와 "상품Y" 별로 조합 기준을 구할 수 있다.
# " on a.cart_id = b.cart_id" 부분에서 "!=" 혹은 "<>"를 사용할 시 고유한 조합 기준을 구할 수 있다.
order by 1, 2 # 제품 이름 X와 Y를 알파벳 순으로 정렬한다.
cf) "count(distinct a.cart_id) orders" 조합 기준 별로 그 수가 전체 데이터에서 몇 개 있는지 확인한다.
방법 2. [cross join]
select a.name name_x, b.name name_y, count(distinct a.cart_id) orders
from cart_products a
cross join cart_products b
where a.cart_id = b.cart_id
and a.name != b.name
group by 1, 2
order by 1, 2
# 이번 사례의 "cross join"에선 on을 사용하지 않아도 된다! [대신 where로 조건을 걸어줬음.]
# self join에서 on 부분 즉, join조건을 on을 사용해서 걸거나 결합 후 where로 걸어줘도 된다.
※ "self join"과 "cross join"을 활용하는 방법을 기억해두자.
Q3. 테이블 내 이상치 탐지!
1. 설명 : 테이블 내에서 "결제를 하지 않고 상품 주문", "결제를 하지 않은 시점에 이미 상품이 주문"을 확인 그 사용자의 수를 구한다.
2. 특이사항 : 현업에서 부서별 데이터 분류 및 저장 기준이 다른 경우가 종종 있다. 또한 데이터를 분석함에 있어 원하는 join key 즉, 컬럼이 없을 수 있다. 따라서 join을 원하는 방식으로 활용 할 수 있어야 한다.
3. 접근 방법 # 방향 : 컬럼을 전처리 후 테이블 결합한 뒤 조회를 한다.
A. 필요한 컬럼의 정보를 전처리
- "payments" (사용자 결제 정보) 테이블 : "user_id"(사용자 ID), "pay_day"(결제 날짜)
- "orders" (상품 배송 정보) 테이블 : "user_id"(사용자 ID), "order_date"(배송 날짜)
- 이상치 기준이 "배송은 되었다!" > "결제를 안했다" or "결제를 안 한 시점에 상품이 주문되었다"가 있기에 "payments" > "pay_day"의 최소치를 "ID" 별로 그룹핑한다.
# "결제를 안 한 시점에 상품이 주문되었다" ==첫 번째 결제일보다 이전에 상품을 주문한 사용자를 카운팅하기 위해 최초 결제일을 구한다.
B. "payments" 테이블과 "orders" 테이블 join
- 사용자 별로 결제 날짜가 다를 수 있고, 이상치 기준이 "배송은 되었다!" > "결제를 안했다" or "결제를 안 한 시점에 상품이 주문되었다" 이기에 "orders" 테이블에 "payments" 테이블을 join한다.
# "결제가 없는 경우"만 찾을 때 inner join만 사용하면 되지만 결제일이 "상품 주문일 이후"인 경우도 확인해야 하기에 반드시 left join을 해야한다.
C. join 이후 이상치를 찾아야 하기 때문에 "결제를 안했다" = "payments" > "user_id" 가 null인 것, 결제를 안 한 시점에 상품이 주문되었다"="payments">"min(pay_day)" 가 "orders">"order_date"보다 큰 경우를 필터링 한다.
D. 최종적으로 카운팅.
4. <코드 구현>
-- 첫 결제일을 담은 새로운 payments 테이블을 with 함수로 저장
with first_payment as (
select user_id, min(pay_date) # min을 적어줘야 한다!
from payments
group by 1
)
-- 새롭게 담은 " first_payment "를 "orders"와 결합
select count(distinct o.user_id) cnt
from orders o
left join first_payment fp # inner join을 하면 버그 악용자2를 구하기 힘듦
on o.user_id = fp.user_id
where fp.user_id is null # 주문을 넣었지만 결제는 하지 않은 사람! 즉 버그악용자1
or first_payment_date > order_date
# 첫 결제일이 주문 발송일보다 큰 경우! 즉, 주문 발송 이후에 결제가 된 경우 버그 악용자2