-- 查看支持的 ML 算法
SELECT * FROM sys.ml_available_algorithms;

-- 创建训练数据表
CREATE TABLE customer_features (
    customer_id INT PRIMARY KEY,
    age INT,
    income DECIMAL(10,2),
    purchase_frequency INT,
    avg_order_value DECIMAL(10,2),
    days_since_last_purchase INT,
    total_orders INT,
    avg_review_score DECIMAL(3,2),
    churn_label TINYINT
);

-- 特征工程：创建衍生特征
ALTER TABLE customer_features
ADD COLUMN customer_lifetime_value DECIMAL(10,2)
GENERATED ALWAYS AS (total_orders * avg_order_value);

-- 创建机器学习模型 - 梯度提升分类器
CREATE ML MODEL customer_churn_model
FROM customer_features
FEATURES (age, income, purchase_frequency, avg_order_value, days_since_last_purchase, total_orders, avg_review_score, customer_lifetime_value)
TARGET (churn_label)
ALGORITHM 'gradient_boosting'
OPTIONS (
    n_estimators = 100,
    max_depth = 5,
    learning_rate = 0.1,
    min_samples_split = 20,
    test_size = 0.2
);

-- 实时预测：单个用户流失概率
SELECT 
    customer_id,
    age,
    income,
    PREDICT(customer_churn_model USING 
        age = 35,
        income = 50000,
        purchase_frequency = 12,
        avg_order_value = 150.00,
        days_since_last_purchase = 15,
        total_orders = 50,
        avg_review_score = 4.5
    ) as churn_probability;

-- 批量预测：对所有未标记用户进行预测
SELECT 
    customer_id,
    PREDICT(customer_churn_model USING 
        age, income, purchase_frequency, avg_order_value, 
        days_since_last_purchase, total_orders, avg_review_score
    ) as churn_probability,
    CASE
        WHEN PREDICT(customer_churn_model USING 
            age, income, purchase_frequency, avg_order_value, 
            days_since_last_purchase, total_orders, avg_review_score) > 0.7 
        THEN '高风险'
        WHEN PREDICT(customer_churn_model USING 
            age, income, purchase_frequency, avg_order_value, 
            days_since_last_purchase, total_orders, avg_review_score) > 0.3 
        THEN '中风险'
        ELSE '低风险'
    END as risk_level
FROM customer_features
WHERE churn_label IS NULL;

-- 创建预测结果表
CREATE TABLE churn_predictions (
    customer_id INT PRIMARY KEY,
    predicted_churn_probability DECIMAL(5,4),
    risk_level VARCHAR(20),
    predicted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customer_features(customer_id)
);

-- 批量预测并存储结果
INSERT INTO churn_predictions (customer_id, predicted_churn_probability, risk_level)
SELECT 
    customer_id,
    PREDICT(customer_churn_model USING 
        age, income, purchase_frequency, avg_order_value, 
        days_since_last_purchase, total_orders, avg_review_score
    ) as prob,
    CASE
        WHEN prob > 0.7 THEN '高风险'
        WHEN prob > 0.3 THEN '中风险'
        ELSE '低风险'
    END
FROM customer_features
WHERE churn_label IS NULL;

-- 查看模型评估指标
SELECT * FROM sys.ml_model_evaluation
WHERE model_name = 'customer_churn_model';

-- 模型评估结果示例输出：
-- +------------------+-------------+-------------+------------+
-- | metric           | value       | train_score | test_score |
-- +------------------+-------------+-------------+------------+
-- | accuracy         | 0.9234      | 0.9567      | 0.9234     |
-- | precision        | 0.8912      | 0.9456      | 0.8912     |
-- | recall           | 0.8678      | 0.9234      | 0.8678     |
-- | f1_score         | 0.8793      | 0.9344      | 0.8793     |
-- | auc_roc          | 0.9456      | 0.9876      | 0.9456     |
-- +------------------+-------------+-------------+------------+

-- 查看混淆矩阵
SELECT * FROM sys.ml_confusion_matrix
WHERE model_name = 'customer_churn_model';

-- 查看特征重要性
SELECT feature_name, importance_score
FROM sys.ml_feature_importance
WHERE model_name = 'customer_churn_model'
ORDER BY importance_score DESC;

-- 特征重要性示例：
-- +-----------------------------+------------------+
-- | feature_name                | importance_score |
-- +-----------------------------+------------------+
-- | days_since_last_purchase   | 0.3245          |
-- | purchase_frequency         | 0.2876          |
-- | avg_order_value            | 0.1987          |
-- | total_orders               | 0.1234          |
-- | age                        | 0.0456          |
-- | income                     | 0.0202          |
-- +-----------------------------+------------------+

-- 超参数调优
CREATE ML MODEL customer_churn_tuned
FROM customer_features
FEATURES (age, income, purchase_frequency, avg_order_value, days_since_last_purchase, total_orders)
TARGET (churn_label)
ALGORITHM 'gradient_boosting'
OPTIONS (
    n_estimators = 200,
    max_depth = 7,
    learning_rate = 0.05,
    min_samples_split = 10,
    min_samples_leaf = 5,
    subsample = 0.8,
    colsample_bytree = 0.8,
    grid_search = TRUE,    -- 启用网格搜索调优
    cv_folds = 5          -- 5折交叉验证
);

-- 案例1：电商用户流失预警系统
-- 创建用户行为特征表
CREATE TABLE user_behavior_features (
    user_id INT PRIMARY KEY,
    registration_date DATE,
    last_login_date DATE,
    total_login_days INT,
    avg_session_duration DECIMAL(10,2),
    browse_count INT,
    cart_add_count INT,
    purchase_count INT,
    wishlist_count INT,
    review_count INT,
    avg_rating_given DECIMAL(3,2),
    coupon_usage_count INT,
    referral_count INT,
    support_ticket_count INT,
    is_churned TINYINT  -- 目标变量：1=流失
);

-- 训练流失预测模型
CREATE ML MODEL churn_prediction_model
FROM user_behavior_features
FEATURES (
    DATEDIFF(NOW(), registration_date),
    DATEDIFF(NOW(), last_login_date),
    total_login_days,
    avg_session_duration,
    browse_count,
    cart_add_count,
    purchase_count,
    wishlist_count,
    review_count,
    avg_rating_given,
    coupon_usage_count,
    referral_count,
    support_ticket_count
)
TARGET (is_churned)
ALGORITHM 'random_forest'
OPTIONS (n_estimators = 150, max_depth = 10);

-- 实时预测高风险用户
SELECT 
    user_id,
    PREDICT(churn_prediction_model USING
        DATEDIFF(NOW(), registration_date) = 90,
        DATEDIFF(NOW(), last_login_date) = 15,
        total_login_days = 25,
        avg_session_duration = 1800,
        browse_count = 50,
        cart_add_count = 10,
        purchase_count = 3,
        wishlist_count = 8,
        review_count = 2,
        avg_rating_given = 4.5,
 = 5        coupon_usage_count,
        referral_count = 1,
        support_ticket_count = 0
    ) as churn_risk
FROM user_behavior_features
WHERE is_churned IS NULL
ORDER BY churn_risk DESC
LIMIT 100;

-- 案例2：商品销量预测
CREATE TABLE sales_data (
    product_id INT,
    date DATE,
    unit_price DECIMAL(10,2),
    discount_rate DECIMAL(5,4),
    advertising_budget DECIMAL(10,2),
    competitor_price DECIMAL(10,2),
    holiday_flag TINYINT,
    season VARCHAR(20),
    quantity_sold INT
);

-- 训练销量预测模型
CREATE ML MODEL sales_forecast_model
FROM sales_data
FEATURES (unit_price, discount_rate, advertising_budget, competitor_price, holiday_flag)
TARGET (quantity_sold)
ALGORITHM 'linear_regression';

-- 预测未来销量
SELECT 
    product_id,
    PREDICT(sales_forecast_model USING
        unit_price = 99.00,
        discount_rate = 0.15,
        advertising_budget = 5000,
        competitor_price = 109.00,
        holiday_flag = 1
    ) as predicted_sales
FROM sales_data
GROUP BY product_id
ORDER BY predicted_sales DESC;

-- 查看所有模型
SELECT * FROM sys.ml_models;

-- 模型列表输出示例：
-- +---------------------------+----------------+----------------+----------------+
-- | model_name                | algorithm      | created_at     | status         |
-- +---------------------------+----------------+----------------+----------------+
-- | customer_churn_model      | gradient_boost | 2024-01-15    | ready          |
-- | sales_forecast_model      | linear_reg     | 2024-01-20    | ready          |
-- | user_clustering_model     | kmeans         | 2024-01-25    | training       |
-- +---------------------------+----------------+----------------+----------------+

-- 查看模型详细信息
DESCRIBE ML MODEL customer_churn_model;

-- 模型版本管理
ALTER ML MODEL customer_churn_model
RETRAIN FROM customer_features
FEATURES (age, income, purchase_frequency, avg_order_value, days_since_last_purchase)
TARGET (churn_label);

-- 回滚到之前的版本
ALTER ML MODEL customer_churn_model
VERSION TO 'v1';

-- 导出模型用于部署
EXPORT ML MODEL customer_churn_model
TO '/backup/models/'
FORMAT 'onnx';

-- 导入外部训练的模型
IMPORT ML MODEL customer_churn_model_v2
FROM '/backup/models/external_model.onnx';

-- 删除模型
DROP ML MODEL customer_churn_model;

-- 模型权限管理
GRANT ML MODEL 'customer_churn_model' TO 'data scientist'@'%';
REVOKE ML MODEL 'customer_churn_model' FROM 'readonly_user'@'%';

-- 最佳实践1：数据质量保证
-- 检查缺失值
SELECT 
    COUNT(*) as total_rows,
    SUM(CASE WHEN age IS NULL THEN 1 ELSE 0 END) as missing_age,
    SUM(CASE WHEN income IS NULL THEN 1 ELSE 0 END) as missing_income
FROM customer_features;

-- 处理缺失值：填充默认值
CREATE VIEW customer_features_clean AS
SELECT
    customer_id,
    COALESCE(age, AVG(age) OVER()) as age,
    COALESCE(income, MEDIAN(income) OVER()) as income
FROM customer_features;

-- 最佳实践2：特征标准化
-- 标准化数值特征
CREATE VIEW customer_features_scaled AS
SELECT
    customer_id,
    (age - AVG(age) OVER()) / STDDEV(age) OVER() as age_scaled,
    (income - AVG(income) OVER()) / STDDEV(income) OVER() as income_scaled
FROM customer_features;

-- 最佳实践3：防止过拟合
-- 使用交叉验证和早停
CREATE ML MODEL customer_churn_balanced
FROM customer_features
FEATURES (age, income, purchase_frequency, avg_order_value)
TARGET (churn_label)
ALGORITHM 'gradient_boosting'
OPTIONS (
    n_estimators = 100,
    early_stopping_rounds = 10,  -- 早停
    validation_fraction = 0.1,    -- 验证集比例
    n_iter_no_change = 5          -- 连续5次不改善则停止
);

-- 最佳实践4：模型监控
CREATE TABLE model_performance_log (
    model_name VARCHAR(100),
    evaluation_date DATE,
    accuracy DECIMAL(5,4),
    precision_score DECIMAL(5,4),
    recall DECIMAL(5,4),
    f1_score DECIMAL(5,4),
    sample_size INT
);

-- 定期记录模型性能
INSERT INTO model_performance_log
SELECT 
    'customer_churn_model',
    CURDATE(),
    accuracy,
    precision,
    recall,
    f1_score,
    sample_size
FROM sys.ml_model_evaluation
WHERE model_name = 'customer_churn_model';