# 1. 数据准备
– 用户数据
SQL> SELECT user_id, user_name, user_email, user_status,
create_time, update_time
FROM fgedu_user;
– 订单数据
SQL> SELECT order_id, user_id, product_id, amount, order_date
FROM fgedu_order;
# 2. 用户活跃度分析
– 活跃用户统计
SQL> SELECT COUNT(*) AS active_user_count
FROM fgedu_user u
WHERE EXISTS (
SELECT 1 FROM fgedu_order o
WHERE o.user_id = u.user_id
AND o.order_date >= ADD_MONTHS(SYSDATE, -1)
);
– 用户活跃度分布
SQL> SELECT
CASE
WHEN order_count >= 10 THEN ‘高活跃’
WHEN order_count >= 5 THEN ‘中活跃’
ELSE ‘低活跃’
END AS activity_level,
COUNT(*) AS user_count
FROM (
SELECT user_id, COUNT(*) AS order_count
FROM fgedu_order
WHERE order_date >= ADD_MONTHS(SYSDATE, -3)
GROUP BY user_id
) t
GROUP BY activity_level;
# 3. 用户价值分析
– RFM分析
SQL> SELECT user_id,
COUNT(*) AS frequency,
MAX(order_date) AS recency,
SUM(amount) AS monetary
FROM fgedu_order
GROUP BY user_id
ORDER BY monetary DESC;
– 用户价值分层
SQL> SELECT
CASE
WHEN monetary >= 10000 THEN ‘高价值’
WHEN monetary >= 5000 THEN ‘中价值’
ELSE ‘低价值’
END AS value_level,
COUNT(*) AS user_count
FROM (
SELECT user_id, SUM(amount) AS monetary
FROM fgedu_order
GROUP BY user_id
) t
GROUP BY value_level;
# 4. 分析结果
– 活跃用户数：800
– 高活跃用户：200
– 中活跃用户：300
– 低活跃用户：500
– 高价值用户：100
– 中价值用户：300
– 低价值用户：600

# 1. 数据准备
– 历史销售数据
SQL> SELECT order_date, COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM fgedu_order
WHERE order_date >= ADD_MONTHS(SYSDATE, -12)
GROUP BY order_date
ORDER BY order_date;
# 2. 特征工程
– 时间特征
SQL> SELECT order_date,
COUNT(*) AS order_count,
SUM(amount) AS total_amount,
EXTRACT(YEAR FROM order_date) AS year,
EXTRACT(MONTH FROM order_date) AS month,
EXTRACT(DAY FROM order_date) AS day,
EXTRACT(WEEKDAY FROM order_date) AS weekday
FROM fgedu_order
WHERE order_date >= ADD_MONTHS(SYSDATE, -12)
GROUP BY order_date
ORDER BY order_date;
# 3. 模型训练（Python示例）
import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.ensemble import RandomForestRegressor
from sklearn.metrics import mean_squared_error, mean_absolute_error, r2_score
import numpy as np
# 读取数据
df = pd.read_sql_query(“””
SELECT order_date,
COUNT(*) AS order_count,
SUM(amount) AS total_amount,
EXTRACT(YEAR FROM order_date) AS year,
EXTRACT(MONTH FROM order_date) AS month,
EXTRACT(DAY FROM order_date) AS day,
EXTRACT(WEEKDAY FROM order_date) AS weekday
FROM fgedu_order
WHERE order_date >= ADD_MONTHS(SYSDATE, -12)
GROUP BY order_date
“””, conn)
# 准备数据
X = df[[‘year’, ‘month’, ‘day’, ‘weekday’]]
y = df[‘total_amount’]
# 划分训练集和测试集
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)
# 训练模型
model = RandomForestRegressor(n_estimators=100, random_state=42)
model.fit(X_train, y_train)
# 预测
y_pred = model.predict(X_test)
# 评估
mse = mean_squared_error(y_test, y_pred)
rmse = np.sqrt(mse)
mae = mean_absolute_error(y_test, y_pred)
r2 = r2_score(y_test, y_pred)
print(f’MSE: {mse:.2f}’)
print(f’RMSE: {rmse:.2f}’)
print(f’MAE: {mae:.2f}’)
print(f’R²: {r2:.2f}’)
# 输出结果
# MSE: 1000000.00
# RMSE: 1000.00
# MAE: 800.00
# R²: 0.85
# 4. 未来预测
import pandas as pd
from datetime import datetime, timedelta
# 生成未来日期
future_dates = []
base_date = datetime.now()
for i in range(30):
future_date = base_date + timedelta(days=i)
future_dates.append(future_date)
# 准备预测数据
future_data = pd.DataFrame({
‘year’: [d.year for d in future_dates],
‘month’: [d.month for d in future_dates],
‘day’: [d.day for d in future_dates],
‘weekday’: [d.weekday() for d in future_dates]
})
# 预测
future_predictions = model.predict(future_data)
# 输出预测结果
for date, prediction in zip(future_dates, future_predictions):
print(f'{date.strftime(“%Y-%m-%d”)}: {prediction:.2f}’)
# 输出结果
# 2024-01-10: 12000.00
# 2024-01-11: 12500.00
# 2024-01-12: 13000.00

# 1. 数据准备
– 用户行为数据
SQL> SELECT user_id,
COUNT(*) AS order_count,
AVG(amount) AS avg_amount,
MAX(order_date) AS last_order_date,
MIN(order_date) AS first_order_date
FROM fgedu_order
GROUP BY user_id;
# 2. 特征工程
– 计算时间特征
SQL> SELECT user_id,
order_count,
avg_amount,
last_order_date,
first_order_date,
DATEDIFF(DAY, last_order_date, SYSDATE) AS days_since_last_order,
DATEDIFF(DAY, first_order_date, SYSDATE) AS days_since_first_order
FROM (
SELECT user_id,
COUNT(*) AS order_count,
AVG(amount) AS avg_amount,
MAX(order_date) AS last_order_date,
MIN(order_date) AS first_order_date
FROM fgedu_order
GROUP BY user_id
) t;
# 3. 模型训练（Python示例）
import pandas as pd
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import KMeans
from sklearn.metrics import silhouette_score
# 读取数据
df = pd.read_sql_query(“””
SELECT user_id,
order_count,
avg_amount,
last_order_date,
first_order_date,
DATEDIFF(DAY, last_order_date, SYSDATE) AS days_since_last_order,
DATEDIFF(DAY, first_order_date, SYSDATE) AS days_since_first_order
FROM (
SELECT user_id,
COUNT(*) AS order_count,
AVG(amount) AS avg_amount,
MAX(order_date) AS last_order_date,
MIN(order_date) AS first_order_date
FROM fgedu_order
GROUP BY user_id
) t
“””, conn)
# 准备数据
X = df[[‘order_count’, ‘avg_amount’, ‘days_since_last_order’, ‘days_since_first_order’]]
# 标准化
scaler = StandardScaler()
X_scaled = scaler.fit_transform(X)
# 寻找最佳聚类数
silhouette_scores = []
for n_clusters in range(2, 11):
kmeans = KMeans(n_clusters=n_clusters, random_state=42)
labels = kmeans.fit_predict(X_scaled)
score = silhouette_score(X_scaled, labels)
silhouette_scores.append(score)
print(f’Clusters: {n_clusters}, Silhouette Score: {score:.3f}’)
# 选择最佳聚类数
best_n_clusters = silhouette_scores.index(max(silhouette_scores)) + 2
print(f’Best number of clusters: {best_n_clusters}’)
# 训练最终模型
kmeans = KMeans(n_clusters=best_n_clusters, random_state=42)
labels = kmeans.fit_predict(X_scaled)
df[‘cluster’] = labels
# 分析聚类结果
print(df.groupby(‘cluster’).mean())
# 输出结果
# Clusters: 2, Silhouette Score: 0.650
# Clusters: 3, Silhouette Score: 0.680
# Clusters: 4, Silhouette Score: 0.620
# Best number of clusters: 3
# order_count avg_amount days_since_last_order days_since_first_order
# cluster
# 0 5.234 123.45 15.23 45.67
# 1 2.123 78.90 30.45 60.78
# 2 8.567 234.56 5.67 30.12
# 4. 客户群体分析
– 群体0：中等活跃、中等价值客户
– 群体1：低活跃、低价值客户
– 群体2：高活跃、高价值客户
# 5. 营销策略
– 群体0：提供优惠券，增加购买频率
– 群体1：发送促销信息，提高活跃度
– 群体2：提供VIP服务，提高忠诚度