클러스터링

-- 고객별 마지막 구매일과 경과 일수
SELECT
    u.id as user_id,
    u.email,
    MAX(o.created_at) as last_purchase_date,
    DATE_DIFF(CURRENT_DATE(), DATE(MAX(o.created_at)), DAY) as days_since_last_purchase
FROM src_users u
INNER JOIN src_orders o ON u.id = o.user_id
WHERE o.status = 'Complete'
GROUP BY u.id, u.email
ORDER BY days_since_last_purchase
LIMIT 50;

-- 고객별 총 주문 횟수
SELECT
    u.id as user_id,
    u.email,
    COUNT(DISTINCT o.order_id) as total_orders,
    COUNT(oi.id) as total_items
FROM src_users u
INNER JOIN src_orders o ON u.id = o.user_id
INNER JOIN src_order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'Complete'
GROUP BY u.id, u.email
ORDER BY total_orders DESC
LIMIT 100;

-- 고객별 총 지출액
SELECT
    u.id as user_id,
    u.email,
    ROUND(SUM(oi.sale_price), 2) as total_spent,
    ROUND(AVG(oi.sale_price), 2) as avg_item_price
FROM src_users u
INNER JOIN src_orders o ON u.id = o.user_id
INNER JOIN src_order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'Complete'
GROUP BY u.id, u.email
ORDER BY total_spent DESC
LIMIT 100;

-- 고객별 RFM 지표 및 점수
WITH rfm_calc AS (
    SELECT
        u.id as user_id,
        u.email,
        u.state,
        -- Recency
        DATE_DIFF(CURRENT_DATE(), DATE(MAX(o.created_at)), DAY) as recency,
        -- Frequency
        COUNT(DISTINCT o.order_id) as frequency,
        -- Monetary
        ROUND(SUM(oi.sale_price), 2) as monetary
    FROM src_users u
    INNER JOIN src_orders o ON u.id = o.user_id
    INNER JOIN src_order_items oi ON o.order_id = oi.order_id
    WHERE o.status = 'Complete'
    GROUP BY u.id, u.email, u.state
),
rfm_score AS (
    SELECT
        *,
        -- R 점수 (낮을수록 좋음 - 최근 구매)
        NTILE(5) OVER (ORDER BY recency DESC) as r_score,
        -- F 점수 (높을수록 좋음 - 자주 구매)
        NTILE(5) OVER (ORDER BY frequency ASC) as f_score,
        -- M 점수 (높을수록 좋음 - 많이 지출)
        NTILE(5) OVER (ORDER BY monetary ASC) as m_score
    FROM rfm_calc
)
SELECT
    user_id,
    email,
    state,
    recency,
    frequency,
    monetary,
    r_score,
    f_score,
    m_score,
    (r_score + f_score + m_score) as rfm_total_score,
    CASE
        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
        WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal Customers'
        WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customers'
        WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
        WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost'
        ELSE 'Others'
    END as customer_segment
FROM rfm_score
ORDER BY rfm_total_score DESC;

import pandas as pd
import numpy as np
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import KMeans
import matplotlib.pyplot as plt
import seaborn as sns
import warnings
warnings.filterwarnings('ignore')
 
# 재현 가능한 결과를 위한 시드 설정
np.random.seed(42)
 
# RFM 샘플 데이터 생성
n_customers = 500
 
rfm_df = pd.DataFrame({
    'user_id': range(1, n_customers + 1),
    'recency': np.random.exponential(60, n_customers).astype(int) + 1,  # 최근 구매 경과일
    'frequency': np.random.poisson(5, n_customers) + 1,  # 구매 횟수
    'monetary': np.random.exponential(500, n_customers) + 50  # 총 지출액
})
 
# 이상치 제한
rfm_df['recency'] = rfm_df['recency'].clip(1, 365)
rfm_df['frequency'] = rfm_df['frequency'].clip(1, 30)
rfm_df['monetary'] = rfm_df['monetary'].clip(50, 5000)
 
print(f"고객 수: {len(rfm_df)}")
print("\nRFM 데이터 요약:")
print(rfm_df[['recency', 'frequency', 'monetary']].describe().round(2))

# 클러스터링 전 스케일링 필수!
scaler = StandardScaler()
rfm_scaled = scaler.fit_transform(rfm_df[['recency', 'frequency', 'monetary']])
 
print("스케일링 후 평균:", rfm_scaled.mean(axis=0).round(4))
print("스케일링 후 표준편차:", rfm_scaled.std(axis=0).round(4))

# Elbow Method - 클러스터 수별 Inertia
inertias = []
K_range = range(2, 11)
 
for k in K_range:
    kmeans = KMeans(n_clusters=k, random_state=42, n_init=10)
    kmeans.fit(rfm_scaled)
    inertias.append(kmeans.inertia_)
 
# 시각화
plt.figure(figsize=(10, 5))
plt.plot(K_range, inertias, 'bo-', linewidth=2, markersize=8)
plt.xlabel('클러스터 수 (K)', fontsize=12)
plt.ylabel('Inertia (군집 내 제곱합)', fontsize=12)
plt.title('Elbow Method로 최적 K 찾기', fontsize=14, fontweight='bold')
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.show()
 
# 감소율 계산
print("\n클러스터 수별 Inertia 감소율:")
for i in range(1, len(inertias)):
    decrease = (inertias[i-1] - inertias[i]) / inertias[i-1] * 100
    print(f"K={i+2}: Inertia = {inertias[i]:.1f}, 감소율 {decrease:.1f}%")

from sklearn.metrics import silhouette_score, silhouette_samples
 
# Silhouette Score - 높을수록 좋음 (-1 ~ 1)
silhouette_scores = []
 
for k in K_range:
    kmeans = KMeans(n_clusters=k, random_state=42, n_init=10)
    labels = kmeans.fit_predict(rfm_scaled)
    score = silhouette_score(rfm_scaled, labels)
    silhouette_scores.append(score)
    print(f"K={k}: Silhouette Score = {score:.3f}")
 
# 시각화
plt.figure(figsize=(10, 5))
plt.bar(K_range, silhouette_scores, color='steelblue', edgecolor='black')
plt.xlabel('클러스터 수 (K)', fontsize=12)
plt.ylabel('Silhouette Score', fontsize=12)
plt.title('Silhouette Score로 최적 K 찾기', fontsize=14, fontweight='bold')
plt.axhline(y=max(silhouette_scores), color='red', linestyle='--', alpha=0.7)
plt.tight_layout()
plt.show()
 
print(f"\n최적 K: {K_range[np.argmax(silhouette_scores)]}")

# 비즈니스 관점에서 4개 클러스터 선택 (해석 용이성)
optimal_k = 4
kmeans = KMeans(n_clusters=optimal_k, random_state=42, n_init=10)
rfm_df['cluster'] = kmeans.fit_predict(rfm_scaled)
 
print(f"클러스터별 고객 수:")
print(rfm_df['cluster'].value_counts().sort_index())

# 클러스터별 RFM 평균
cluster_profile = rfm_df.groupby('cluster').agg(
    고객수=('user_id', 'count'),
    평균_Recency=('recency', 'mean'),
    평균_Frequency=('frequency', 'mean'),
    평균_Monetary=('monetary', 'mean'),
    총_매출=('monetary', 'sum')
).round(2)
 
# 비율 추가
cluster_profile['고객_비율'] = (cluster_profile['고객수'] / cluster_profile['고객수'].sum() * 100).round(1)
cluster_profile['매출_비율'] = (cluster_profile['총_매출'] / cluster_profile['총_매출'].sum() * 100).round(1)
 
print("=== 클러스터 프로파일 ===")
print(cluster_profile)

# 클러스터 특성에 따라 이름 부여
def name_cluster(row):
    r, f, m = row['평균_Recency'], row['평균_Frequency'], row['평균_Monetary']
 
    if r < 50 and f > 6 and m > 600:
        return 'Champions'
    elif r < 60 and f > 4:
        return 'Loyal Customers'
    elif r > 80 and f < 4:
        return 'At Risk'
    else:
        return 'Potential'
 
cluster_profile['세그먼트'] = cluster_profile.apply(name_cluster, axis=1)
print("\n=== 세그먼트 명명 결과 ===")
print(cluster_profile[['세그먼트', '고객수', '고객_비율', '매출_비율']])

from mpl_toolkits.mplot3d import Axes3D
 
fig = plt.figure(figsize=(12, 8))
ax = fig.add_subplot(111, projection='3d')
 
colors = ['#FF6B6B', '#4ECDC4', '#45B7D1', '#FFA07A']
segment_names = cluster_profile['세그먼트'].values
 
for cluster in range(optimal_k):
    mask = rfm_df['cluster'] == cluster
    ax.scatter(
        rfm_df.loc[mask, 'recency'],
        rfm_df.loc[mask, 'frequency'],
        rfm_df.loc[mask, 'monetary'],
        c=colors[cluster],
        label=f'{segment_names[cluster]} (n={mask.sum()})',
        alpha=0.6,
        s=50
    )
 
ax.set_xlabel('Recency (일)')
ax.set_ylabel('Frequency (횟수)')
ax.set_zlabel('Monetary ($)')
ax.set_title('RFM 클러스터 3D 시각화', fontsize=14, fontweight='bold')
ax.legend()
plt.tight_layout()
plt.show()

from math import pi
 
# 정규화된 값 사용
cluster_means = rfm_df.groupby('cluster')[['recency', 'frequency', 'monetary']].mean()
cluster_means_norm = (cluster_means - cluster_means.min()) / (cluster_means.max() - cluster_means.min())
 
# 레이더 차트
categories = ['Recency\n(낮을수록 좋음)', 'Frequency', 'Monetary']
N = len(categories)
 
fig, ax = plt.subplots(figsize=(10, 8), subplot_kw=dict(polar=True))
 
angles = [n / float(N) * 2 * pi for n in range(N)]
angles += angles[:1]
 
for idx, cluster in enumerate(cluster_means_norm.index):
    values = cluster_means_norm.loc[cluster].values.tolist()
    values += values[:1]
    ax.plot(angles, values, 'o-', linewidth=2, label=f'{segment_names[idx]}', color=colors[idx])
    ax.fill(angles, values, alpha=0.25, color=colors[idx])
 
ax.set_xticks(angles[:-1])
ax.set_xticklabels(categories, fontsize=12)
ax.set_title('클러스터별 RFM 프로파일', fontsize=14, fontweight='bold', pad=20)
ax.legend(loc='upper right', bbox_to_anchor=(1.3, 1.0))
plt.tight_layout()
plt.show()

# 클러스터 x RFM 지표 히트맵
plt.figure(figsize=(10, 6))
heatmap_data = cluster_means.T  # 행: RFM, 열: 클러스터
heatmap_data.columns = segment_names
 
sns.heatmap(
    heatmap_data,
    annot=True,
    fmt='.1f',
    cmap='YlOrRd',
    cbar_kws={'label': '평균값'},
    linewidths=0.5
)
plt.title('클러스터별 RFM 평균', fontsize=14, fontweight='bold')
plt.xlabel('세그먼트')
plt.ylabel('RFM 지표')
plt.tight_layout()
plt.show()