Examples - Amazon Redshift
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

本文属于机器翻译版本。若本译文内容与英语原文存在差异,则一律以英文原文为准。

Examples

以下部分说明使用 CREATE MODEL 命令生成的 SQL 函数为不同场景创建模型和运行一些推理查询的端到端示例。此示例中使用的完整 SQL 脚本可在 Customer activity file (客户活动文件) 中找到。

以下查询通过创建 customer_activity 表并使用示例数据集提取数据来准备训练数据。

DROP TABLE IF EXISTS customer_activity; CREATE TABLE customer_activity ( state varchar(2), account_length int, area_code int, phone varchar(8), intl_plan varchar(3), vMail_plan varchar(3), vMail_message int, day_mins float, day_calls int, day_charge float, total_charge float, eve_mins float, eve_calls int, eve_charge float, night_mins float, night_calls int, night_charge float, intl_mins float, intl_calls int, intl_charge float, cust_serv_calls int, churn varchar(6), record_date date); COPY customer_activity FROM 's3://redshift-downloads/redshift-ml/customer_activity/' REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::XXXXXXXXXXXX:role/Redshift-ML' DELIMITER ',' IGNOREHEADER 1;

以下查询创建训练模型。

CREATE MODEL customer_churn_auto_model FROM (SELECT state, account_length, area_code, total_charge/account_length AS average_daily_spend, cust_serv_calls/account_length AS average_daily_cases, churn FROM customer_activity WHERE record_date < '2020-01-01' ) TARGET churn FUNCTION ml_fn_customer_churn_auto IAM_ROLE 'arn:aws:iam::XXXXXXXXXXXX:role/Redshift-ML' SETTINGS ( S3_BUCKET 'your-bucket' );

导出训练数据后,CREATE MODEL 命令将完成。训练将在后台继续。要检查训练的状态,请使用 STV_ML_MODEL_INFO

select schema_name, model_name, model_state from stv_ml_model_info; schema_name | model_name | model_state -------------+---------------------------+-------------------------------------- public | customer_churn_auto_model | Train Model On SageMaker In Progress (1 row)

在 model_state 变为 后Model is Ready,函数 ml_fn_customer_churn_auto 变为可用。以下示例将来自上一个 CREATE MODEL 示例的推理函数用于其他用户案例,其中 Amazon Redshift 预测来自不同状态的客户之间的流失器和非流失率比例,因为 2020-01-01。

WITH inferred AS (SELECT state, ml_fn_customer_churn_auto( state, account_length, area_code, total_charge/account_length, cust_serv_calls/account_length )::varchar(6) AS active FROM customer_activity WHERE record_date > '2020-01-01' ) SELECT state, SUM(CASE WHEN active = 'True.' THEN 1 ELSE 0 END) AS churners, SUM(CASE WHEN active = 'False.' THEN 1 ELSE 0 END) AS nonchurners, COUNT(*) AS total_per_state FROM inferred GROUP BY state ORDER BY state;