기록 블로그
[TIL 32] QCC / 단측 검정 할 때 들어가야하는 인자 본문
<TIL 반드시 들어가야 할 내용>
1. 어떤 문제가 있었는지
2. 내가 시도해 본 것들(자세히 쓰기)
3. 어떻게 해결했는지
4. 뭘 새롭게 알았는지(자세히 쓰기)
QCC
내가 푼 방법.
| 문제 1) 지역별로 매출이 가장 높은 매장의 매출을 조회하는 SQL 문을 작성해주세요. 단, 해당 지역에 매장이 두 개 이상인 경우만 결과에 포함해주세요. 결과는 지역 이름을 기준으로 오름차순으로 정렬해주세요. |
SELECT region_name, sales
FROM (
SELECT *,
ROW_NUMBER()over PARTITION BY REGION_NAME ORDER BY sales DESC) AS num_sales,
count(*)over(PARTITION BY region_name) AS cnt
FROM stores s
) b
WHERE num_sales=1 AND cnt>1
ORDER BY 1;지역별로 매출이 가장 높은 매장을 구별하기 위해서 ROW_NUMBER() 함수를 사용.
해당 지역에 매장이 두 개 이상인 경우를 걸러내기 위해 COUNT(*)를 사용.
집계 함수를 사용했기 때문에 그 결과값을 사용하기 위해 서브 쿼리 사용.
| 문제 2) 최근 특정 사용자들이 결제를 하지 않고 상품을 주문하거나, 결제를 하지 않은 시점에 이미 상품을 주문하는 버그가 발견되었습니다. 🐞 해당 버그를 악용한 사용자를 파악하기 위해 SQL 문을 작성해주세요. 다음 조건에 해당되는 사용자 수를 출력해주세요 :
|
SELECT count(DISTINCT p.user_id) AS cnt
FROM orders o
LEFT JOIN payments p
ON p.USER_ID = o.USER_ID
WHERE (p.user_id IS NULL OR o.ORDER_DATE < p.PAY_DATE);결제를 하지 않고 상품을 주문한 사람을 걸러주기 위해 주문 데이터는 있지만 결제 데이터가 없는 사람을 찾아야함.
때문에 orders 테이블을 기준으로 payments 테이블을 붙여줌. 이때 주문 데이터를 다 살려야 하기 때문에 LEFT JOIN 사용.
결제를 하지 않은 시점에 이미 상품을 주문한 사람은 결제 날짜 데이터가 주문 날짜보다 큼. (결제 전에 주문을 했기 때문)
| 문제 3) 데이터 분석팀은 고객이 특정 상품 X를 구매했을 때 상품 Y도 함께 구매할 확률을 분석하고자 합니다. 이를 위해, 쇼핑 카트 데이터에서 서로 다른 두 제품 X와 Y가 같은 주문(CART_ID)에 포함된 주문 수를 확인하려고 합니다.
|
SELECT name_x, name_y, orders
FROM (
SELECT cp.name AS name_x,
if(cp.name=cp2.name, NULL, cp2.name) AS name_y,
count(*) AS orders
FROM cart_products cp
LEFT JOIN cart_products cp2
ON cp.cart_id=cp2.cart_id
GROUP BY 1,2
ORDER BY 1,2
) w
WHERE name_y IS NOT NULL각 cart_id 별로 구매한 각 쌍의 경우의 수를 구해주기 위해 LEFT JOIN을 사용하고 self join을 함.
구매한 물품의 쌍으로 만들어진 테이블에서는 물품의 이름이 같은 행도 생기기 때문에 그 데이터 값을 null로 만들어주고 is not null을 사용하여 데이터를 빼줌
풀이를 보고 알게 된 것
문제 1)
좀 더 간단하게 푸는 방법.
SELECT REGION_NAME, max(sales)
FROM stores s
GROUP BY 1
HAVING count(STORE_ID)>=2
ORDER BY 1;
문제 2)
문제에서의 조건이 첫 번째 결제일보다 이전에 상품을 주문한 사용자. 즉, 첫 번째 결제일을 걸러줘야하는데 거르지 않았음.
MIN(pay_date)를 사용해서 첫 번째 결제일을 확인 하고 그 날짜가 주문 날짜보다 작으면 됨.
WITH a as(
SELECT user_id, min(pay_date) AS pay_date
FROM payments p
GROUP BY 1
)
SELECT count(DISTINCT o.user_id)
FROM orders o
LEFT JOIN a
ON o.user_id= a.user_id
WHERE (a.user_id IS NULL OR o.ORDER_DATE < a.pay_date)여기서 a(payment)의 user_id를 세게 되면 두번째 조건에서 null 값인 사람은 count가 되지 않기 때문에 orders의 user_id를 count
문제3)
좀 더 쉽게 푸는 방법.
SELECT cp.name AS name_x,
cp2.name AS name_y,
count(*) AS orders
FROM cart_products cp
LEFT JOIN cart_products cp2
ON cp.cart_id=cp2.cart_id
WHERE cp.name != cp2.name
GROUP BY 1,2
ORDER BY 1,2걍 자기 자신 하고 붙은 값만 빼주면 됨.
여기서 (a, b)와 (b,a)의 경우를 빼주기 위해서는 WHERE에 부등호를 넣어주면 됨
문자열에서 a가 b보다 작기 때문에 조건에 맞는 건 출력하고 두번째 같은 경우에는 b가 a보다 크기 때문에 걸러짐
통계 개인 과제
문제3)
- 목초에 따른 우유의 생산량을 알아보기위하여 25마리 젖소를 대상으로 3주동안 임의로 추출된 12마리 젖소에게 건조시킨 목초를 주고 13마리 젖소에게는 들판에서 말린 목초를 주었다.
- 두 종류의 모 표준편차는 같다고 가정하고 또한 우유생산량의 자료는 정규분포를 따른다고 가정한다.
- 자연적으로 말린 목초의 사육하는 젖소의 우유생산량이 인공적으로 말린 목초로 사용하는 젖소보다 많다고 할 수 있는지 유의수준 0.05 에서 검정하세요.(단측 검정)
import numpy as np
from scipy import stats
nature = [44, 44, 56, 46, 47, 38, 58, 53, 49, 35, 46, 30, 31] # 자연적으로 말린 목초
artificial = [35, 47, 55, 29, 40, 39, 32, 41, 42, 57, 51, 39] # 인공적으로 말린 목초
#nature>artificial 가정
t_stat, p_val = stats.ttest_ind(nature, artificial, alternative = "greater")
#nature<aritificial 가정
#s,v =stats.ttest_ind(nature, artificial, alternative = 'less')
print('H0 : 귀무가설은 자연적으로 말린 목초를 먹는 젖소가 우유 생산량이 적다\n'
'H1 : 대립가설은 자연적으로 말린 목초를 먹는 젖소가 우유 생산량이 많다\n'
f't-통계량 : ({t_stat:.4f})\n'
f'p-값 : ({p_val:.4f})')
if p_val > 0.05:
print('귀무가설을 기각하지 않습니다')
else :
print('귀무가설을 기각합니다')단측 검정을 할 때는 alternative 인자를 greater 혹은 less로 설정해야함.
이때 검정하기 위한 가설을 세웠을 때
대립가설의 변수가 크다고 가정하면 greater. (nature이 artificial 보다 크다고 가정)
대립가설의 변수가 작다고 가정하면 less.(nature이 artificial 보다 작다고 가정