01. 가설 검정 (Hypothesis Testing)

# 1. 데이터 추출
query = """
SELECT
    u.gender,
    SUM(oi.sale_price) as total_purchase
FROM `your-project-id.retail_analytics_us.src_users` u
JOIN `your-project-id.retail_analytics_us.src_orders` o ON u.user_id = o.user_id
JOIN `your-project-id.retail_analytics_us.src_order_items` oi ON o.order_id = oi.order_id
WHERE o.status NOT IN ('Cancelled', 'Returned')
GROUP BY u.user_id, u.gender
"""
df = client.query(query).to_dataframe()
 
# 2. 그룹 분리
group_m = df[df['gender'] == 'M']['total_purchase']
group_f = df[df['gender'] == 'F']['total_purchase']
 
# 3. T-test 수행 (등분산 가정 X -> Welch's t-test)
t_stat, p_val = stats.ttest_ind(group_m, group_f, equal_var=False)
 
print(f"남성 평균: ${group_m.mean():.2f}")
print(f"여성 평균: ${group_f.mean():.2f}")
print(f"T-statistic: {t_stat:.4f}")
print(f"P-value: {p_val:.4f}")
 
if p_val < 0.05:
    print("✅ 차이가 통계적으로 유의미합니다.")
else:
    print("❌ 차이가 유의미하지 않습니다.")

# 1. 데이터 병합 및 집계
full_data = users.merge(orders, on='user_id')\
                 .merge(items, on='order_id')
 
# 구매 이력 필터링 및 집계
user_spend = full_data[~full_data['status'].isin(['Cancelled', 'Returned'])]\
                      .groupby(['user_id', 'gender'])['sale_price']\
                      .sum().reset_index()
 
# 2. 그룹 분리
group_m = user_spend[user_spend['gender'] == 'M']['sale_price']
group_f = user_spend[user_spend['gender'] == 'F']['sale_price']
 
# 3. T-test
t_stat, p_val = stats.ttest_ind(group_m, group_f, equal_var=False)
 
print(f"남성 평균: ${group_m.mean():.2f}")
print(f"여성 평균: ${group_f.mean():.2f}")
print(f"P-value: {p_val:.4f}")

# 1. 데이터 추출
query = """
SELECT
    u.country,
    SUM(oi.sale_price) as total_purchase
FROM `your-project-id.retail_analytics_us.src_users` u
JOIN `your-project-id.retail_analytics_us.src_orders` o ON u.user_id = o.user_id
JOIN `your-project-id.retail_analytics_us.src_order_items` oi ON o.order_id = oi.order_id
WHERE o.status NOT IN ('Cancelled', 'Returned')
AND u.country IN ('United States', 'China', 'Japan')  -- 3개국 비교
GROUP BY u.user_id, u.country
"""
df_anova = client.query(query).to_dataframe()
 
# 2. 그룹별 데이터 준비
groups = [df_anova[df_anova['country'] == c]['total_purchase'] for c in df_anova['country'].unique()]
 
# 3. ANOVA 수행
f_stat, p_val = stats.f_oneway(*groups)
 
print(f"F-statistic: {f_stat:.4f}")
print(f"P-value: {p_val:.4f}")

# 국가별 데이터 준비 (상위 3개국만)
top_countries = ['United States', 'China', 'Japan']
user_spend_country = full_data[
    (full_data['country'].isin(top_countries)) & 
    (~full_data['status'].isin(['Cancelled', 'Returned']))
].groupby(['user_id', 'country'])['sale_price'].sum().reset_index()
 
# 리스트로 변환
groups = [group['sale_price'].values for name, group in user_spend_country.groupby('country')]
 
# ANOVA
f_stat, p_val = stats.f_oneway(*groups)
 
print(f"P-value: {p_val:.4f}")

from statsmodels.stats.multicomp import pairwise_tukeyhsd
 
# df_anova는 위에서 만든 데이터프레임
tukey = pairwise_tukeyhsd(endog=df_anova['total_purchase'],
                          groups=df_anova['country'],
                          alpha=0.05)
print(tukey)