# 示例：创建决策树模型
BEGIN
DBMS_DATA_MINING.CREATE_MODEL(
model_name => ‘CUSTOMER_CHURN_MODEL’,
mining_function => DBMS_DATA_MINING.CLASSIFICATION,
data_table_name => ‘CUSTOMERS_TRAIN’,
case_id_column_name => ‘CUSTOMER_ID’,
target_column_name => ‘CHURN’,
settings_table_name => ‘SETTINGS_TABLE’
);
END;
/

# 示例：使用模型进行预测
SELECT
customer_id,
PREDICTION(CUSTOMER_CHURN_MODEL USING *) AS predicted_churn,
PREDICTION_PROBABILITY(CUSTOMER_CHURN_MODEL USING *) AS churn_probability
FROM
CUSTOMERS_TEST;

CUSTOMER_ID PREDICTED_CHURN CHURN_PROBABILITY
———– ————— —————-
1 YES 0.85
2 NO 0.92
3 YES 0.78

# 示例：使用OML4Py训练模型
import oml
import pandas as pd
from sklearn.ensemble import RandomForestClassifier

# 连接到Oracle数据库
oml.connect(user=’username’, password=’password’, dsn=’localhost:1521/orcl’)

# 加载数据
train_df = oml.DataFrame(‘CUSTOMERS_TRAIN’)

# 训练模型
rf = RandomForestClassifier(n_estimators=100, random_state=42)
model = oml.sklearn_estimator(rf)
model.fit(train_df[“age”, “income”, “tenure”], train_df[“churn”])

# 保存模型
model.save(“CUSTOMER_CHURN_MODEL_PY”)

# 预测
test_df = oml.DataFrame(‘CUSTOMERS_TEST’)
predictions = model.predict(test_df[“age”, “income”, “tenure”])
print(predictions)

0 NO
1 YES
2 NO
dtype: object

# 示例：部署为SQL函数
CREATE OR REPLACE FUNCTION predict_churn(
p_age NUMBER,
p_income NUMBER,
p_tenure NUMBER
) RETURN VARCHAR2
IS
v_prediction VARCHAR2(10);
BEGIN
SELECT PREDICTION(CUSTOMER_CHURN_MODEL USING
p_age AS age,
p_income AS income,
p_tenure AS tenure
) INTO v_prediction
FROM DUAL;
RETURN v_prediction;
END;
/

# 使用函数
SELECT
customer_id,
predict_churn(age, income, tenure) AS predicted_churn
FROM
customers;

CUSTOMER_ID PREDICTED_CHURN
———– —————
1 NO
2 YES
3 NO