Amazon Redshift 將不再支援從修補程式 198 開始建立新的 Python UDFs。現有 Python UDF 將繼續正常運作至 2026 年 6 月 30 日。如需詳細資訊，請參閱[部落格文章](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)。

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

# 教學課程：建置迴歸模型
<a name="tutorial_regression"></a>

在本教學中，您會使用 Amazon Redshift ML 建立機器學習迴歸模型，並在模型上執行預測查詢。迴歸模型允許您預測數值結果，例如房屋的價格，或有多少人將使用城市的自行車租賃服務。您可以在 Amazon Redshift 中使用 CREATE MODEL 命令搭配訓練資料。然後，Amazon Redshift ML 會編譯模型，將經過訓練的模型匯入 Redshift，並準備 SQL 預測函數。您可以在 Amazon Redshift 中的 SQL 查詢中使用預測函數。

在本教學課程中，您將使用 Amazon Redshift ML 建置迴歸模型，以預測在一天中的任何特定時間使用多倫多市自行車共享服務的人數。模型的輸入包括假日和天氣條件。您將使用迴歸模型，因為您想要此問題的數值結果。

您可以使用 CREATE MODEL 命令來匯出訓練資料、訓練模型、匯入模型，以及讓模型可在 Amazon Redshift 中作為 SQL 函數。使用 CREATE MODEL 操作，將訓練資料指定為資料表或 SELECT 陳述式。

## 使用案例範例
<a name="tutorial_regression_tasks"></a>

您可以使用 Amazon Redshift ML 解決其他迴歸問題，例如預測客戶的終身價值。您也可以使用 Redshift ML 來預測最有利的價格和產生的產品收入。

**工作**
+ 先決條件
+ 步驟 1：將資料從 Amazon S3 載入到 Amazon Redshift
+ 步驟 2：建立機器學習模型
+ 步驟 3：驗證模型

## 先決條件
<a name="tutorial_regression_prereqs"></a>

為完成此教學課程，您必須完成 Amazon Redshift ML 的[管理設定](https://docs.aws.amazon.com/redshift/latest/dg/admin-setup.html)。

## 步驟 1：將資料從 Amazon S3 載入到 Amazon Redshift
<a name="tutorial_regression_step_load"></a>

使用 [Amazon Redshift 查詢編輯器 v2](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-v2-using.html) 來執行下列查詢。

1. 您必須建立三個資料表，才能將三個公開資料集載入 Amazon Redshift。資料集是[多倫多自行車乘客資料](https://open.toronto.ca/dataset/bike-share-toronto-ridership-data/)、[歷史氣象資料](https://climate.weather.gc.ca/historical_data/search_historic_data_e.html)和[歷史假期資料](https://github.com/uWaterloo/Datasets/blob/master/Holidays/holidays.csv)。在 Amazon Redshift 查詢編輯器中執行下列查詢，以建立名為 `ridership`、`weather` 和 `holiday` 的資料表。

   ```
   CREATE TABLE IF NOT EXISTS ridership (
       trip_id INT,
       trip_duration_seconds INT,
       trip_start_time timestamp,
       trip_stop_time timestamp,
       from_station_name VARCHAR(50),
       to_station_name VARCHAR(50),
       from_station_id SMALLINT,
       to_station_id SMALLINT,
       user_type VARCHAR(20)
   );
   
   CREATE TABLE IF NOT EXISTS weather (
       longitude_x DECIMAL(5, 2),
       latitude_y DECIMAL(5, 2),
       station_name VARCHAR(20),
       climate_id BIGINT,
       datetime_utc TIMESTAMP,
       weather_year SMALLINT,
       weather_month SMALLINT,
       weather_day SMALLINT,
       time_utc VARCHAR(5),
       temp_c DECIMAL(5, 2),
       temp_flag VARCHAR(1),
       dew_point_temp_c DECIMAL(5, 2),
       dew_point_temp_flag VARCHAR(1),
       rel_hum SMALLINT,
       rel_hum_flag VARCHAR(1),
       precip_amount_mm DECIMAL(5, 2),
       precip_amount_flag VARCHAR(1),
       wind_dir_10s_deg VARCHAR(10),
       wind_dir_flag VARCHAR(1),
       wind_spd_kmh VARCHAR(10),
       wind_spd_flag VARCHAR(1),
       visibility_km VARCHAR(10),
       visibility_flag VARCHAR(1),
       stn_press_kpa DECIMAL(5, 2),
       stn_press_flag VARCHAR(1),
       hmdx SMALLINT,
       hmdx_flag VARCHAR(1),
       wind_chill VARCHAR(10),
       wind_chill_flag VARCHAR(1),
       weather VARCHAR(10)
   );
   
   CREATE TABLE IF NOT EXISTS holiday (holiday_date DATE, description VARCHAR(100));
   ```

1. 下列查詢會將範例資料載入您在上一個步驟中建立的資料表。

   ```
   COPY ridership
   FROM
       's3://redshift-ml-bikesharing-data/bike-sharing-data/ridership/' 
       IAM_ROLE default 
       FORMAT CSV 
       IGNOREHEADER 1 
       DATEFORMAT 'auto' 
       TIMEFORMAT 'auto' 
       REGION 'us-west-2' 
       gzip;
   
   COPY weather
   FROM
       's3://redshift-ml-bikesharing-data/bike-sharing-data/weather/' 
       IAM_ROLE default 
       FORMAT csv 
       IGNOREHEADER 1 
       DATEFORMAT 'auto' 
       TIMEFORMAT 'auto' 
       REGION 'us-west-2' 
       gzip;
   
   COPY holiday
   FROM
       's3://redshift-ml-bikesharing-data/bike-sharing-data/holiday/' 
       IAM_ROLE default 
       FORMAT csv 
       IGNOREHEADER 1 
       DATEFORMAT 'auto' 
       TIMEFORMAT 'auto' 
       REGION 'us-west-2' 
       gzip;
   ```

1. 下列查詢會對 `ridership` 和 `weather` 資料集執行轉換，以移除偏差或異常。移除偏差和異常可改善模型準確度。該查詢會透過建立兩個名為 `ridership_view` 和 `weather_view` 的新檢視簡化資料表。

   ```
   CREATE
   OR REPLACE VIEW ridership_view AS
   SELECT
       trip_time,
       trip_count,
       TO_CHAR(trip_time, 'hh24') :: INT trip_hour,
       TO_CHAR(trip_time, 'dd') :: INT trip_day,
       TO_CHAR(trip_time, 'mm') :: INT trip_month,
       TO_CHAR(trip_time, 'yy') :: INT trip_year,
       TO_CHAR(trip_time, 'q') :: INT trip_quarter,
       TO_CHAR(trip_time, 'w') :: INT trip_month_week,
       TO_CHAR(trip_time, 'd') :: INT trip_week_day
   FROM
       (
           SELECT
               CASE
                   WHEN TRUNC(r.trip_start_time) < '2017-07-01' :: DATE THEN CONVERT_TIMEZONE(
                       'US/Eastern',
                       DATE_TRUNC('hour', r.trip_start_time)
                   )
                   ELSE DATE_TRUNC('hour', r.trip_start_time)
               END trip_time,
               COUNT(1) trip_count
           FROM
               ridership r
           WHERE
               r.trip_duration_seconds BETWEEN 60
               AND 60 * 60 * 24
           GROUP BY
               1
       );
   
   CREATE
   OR REPLACE VIEW weather_view AS
   SELECT
       CONVERT_TIMEZONE(
           'US/Eastern',
           DATE_TRUNC('hour', datetime_utc)
       ) daytime,
       ROUND(AVG(temp_c)) temp_c,
       ROUND(AVG(precip_amount_mm)) precip_amount_mm
   FROM
       weather
   GROUP BY
       1;
   ```

1. 下面的查詢會建立一個資料表，該資料表會將 `ridership_view` 和 `weather_view` 中的所有相關輸入屬性結合到 `trip_data` 資料表中。

   ```
   CREATE TABLE trip_data AS
   SELECT
       r.trip_time,
       r.trip_count,
       r.trip_hour,
       r.trip_day,
       r.trip_month,
       r.trip_year,
       r.trip_quarter,
       r.trip_month_week,
       r.trip_week_day,
       w.temp_c,
       w.precip_amount_mm,CASE
           WHEN h.holiday_date IS NOT NULL THEN 1
           WHEN TO_CHAR(r.trip_time, 'D') :: INT IN (1, 7) THEN 1
           ELSE 0
       END is_holiday,
       ROW_NUMBER() OVER (
           ORDER BY
               RANDOM()
       ) serial_number
   FROM
       ridership_view r
       JOIN weather_view w ON (r.trip_time = w.daytime)
       LEFT OUTER JOIN holiday h ON (TRUNC(r.trip_time) = h.holiday_date);
   ```

### 檢視範例資料 (選擇性)
<a name="tutorial_regression_view_data"></a>

下列查詢會顯示資料表中的項目。您可以執行此操作，以確保資料表已正確製作。

```
SELECT * 
FROM trip_data 
LIMIT 5;
```

下列為上一個操作的輸出範例。

```
+---------------------+------------+-----------+----------+------------+-----------+--------------+-----------------+---------------+--------+------------------+------------+---------------+
|      trip_time      | trip_count | trip_hour | trip_day | trip_month | trip_year | trip_quarter | trip_month_week | trip_week_day | temp_c | precip_amount_mm | is_holiday | serial_number |
+---------------------+------------+-----------+----------+------------+-----------+--------------+-----------------+---------------+--------+------------------+------------+---------------+
| 2017-03-21 22:00:00 |         47 |        22 |       21 |          3 |        17 |            1 |               3 |             3 |      1 |                0 |          0 |             1 |
| 2018-05-04 01:00:00 |         19 |         1 |        4 |          5 |        18 |            2 |               1 |             6 |     12 |                0 |          0 |             3 |
| 2018-01-11 10:00:00 |         93 |        10 |       11 |          1 |        18 |            1 |               2 |             5 |      9 |                0 |          0 |             5 |
| 2017-10-28 04:00:00 |         20 |         4 |       28 |         10 |        17 |            4 |               4 |             7 |     11 |                0 |          1 |             7 |
| 2017-12-31 21:00:00 |         11 |        21 |       31 |         12 |        17 |            4 |               5 |             1 |    -15 |                0 |          1 |             9 |
+---------------------+------------+-----------+----------+------------+-----------+--------------+-----------------+---------------+--------+------------------+------------+---------------+
```

### 顯示屬性之間的關聯 (選擇性)
<a name="tutorial_regression_show_correlation"></a>

確定相關性有助於測量屬性之間的關聯強度。關聯層級可協助您判斷哪些因素會影響您的目標輸出。在此教學課程中，目標輸出為 `trip_count`。

下列查詢會建立或取代 `sp_correlation` 程序。您可以使用名為 `sp_correlation` 的預存程序來顯示 Amazon Redshift 中資料表內屬性之間的關聯性。

```
CREATE OR REPLACE PROCEDURE sp_correlation(source_schema_name in varchar(255), source_table_name in varchar(255), target_column_name in varchar(255), output_temp_table_name inout varchar(255)) AS $$
DECLARE
  v_sql varchar(max);
  v_generated_sql varchar(max);
  v_source_schema_name varchar(255)=lower(source_schema_name);
  v_source_table_name varchar(255)=lower(source_table_name);
  v_target_column_name varchar(255)=lower(target_column_name);
BEGIN
  EXECUTE 'DROP TABLE IF EXISTS ' || output_temp_table_name;
  v_sql = '
SELECT
  ''CREATE temp table '|| output_temp_table_name||' AS SELECT ''|| outer_calculation||
  '' FROM (SELECT COUNT(1) number_of_items, SUM('||v_target_column_name||') sum_target, SUM(POW('||v_target_column_name||',2)) sum_square_target, POW(SUM('||v_target_column_name||'),2) square_sum_target,''||
  inner_calculation||
  '' FROM (SELECT ''||
  column_name||
  '' FROM '||v_source_table_name||'))''
FROM
  (
  SELECT
    DISTINCT
    LISTAGG(outer_calculation,'','') OVER () outer_calculation
    ,LISTAGG(inner_calculation,'','') OVER () inner_calculation
    ,LISTAGG(column_name,'','') OVER () column_name
  FROM
    (
    SELECT
      CASE WHEN atttypid=16 THEN ''DECODE(''||column_name||'',true,1,0)'' ELSE column_name END column_name
      ,atttypid
      ,''CAST(DECODE(number_of_items * sum_square_''||rn||'' - square_sum_''||rn||'',0,null,(number_of_items*sum_target_''||rn||'' - sum_target * sum_''||rn||
        '')/SQRT((number_of_items * sum_square_target - square_sum_target) * (number_of_items * sum_square_''||rn||
        '' - square_sum_''||rn||''))) AS numeric(5,2)) ''||column_name outer_calculation
      ,''sum(''||column_name||'') sum_''||rn||'',''||
            ''SUM(trip_count*''||column_name||'') sum_target_''||rn||'',''||
            ''SUM(POW(''||column_name||'',2)) sum_square_''||rn||'',''||
            ''POW(SUM(''||column_name||''),2) square_sum_''||rn inner_calculation
    FROM
      (
      SELECT
        row_number() OVER (order by a.attnum) rn
        ,a.attname::VARCHAR column_name
        ,a.atttypid
      FROM pg_namespace AS n
        INNER JOIN pg_class AS c ON n.oid = c.relnamespace
        INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
      WHERE a.attnum > 0
        AND n.nspname = '''||v_source_schema_name||'''
        AND c.relname = '''||v_source_table_name||'''
        AND a.atttypid IN (16,20,21,23,700,701,1700)
      )
    )
)';
  EXECUTE v_sql INTO v_generated_sql;
  EXECUTE  v_generated_sql;
END;
$$ LANGUAGE plpgsql;
```

下面的查詢顯示目標列、`trip_count` 和我們的資料集中的其他數字屬性之間的相關性。

```
call sp_correlation(
    'public',
    'trip_data',
    'trip_count',
    'tmp_corr_table'
);

SELECT
    *
FROM
    tmp_corr_table;
```

下列範例顯示上一個 `sp_correlation` 操作的輸出。

```
+------------+-----------+----------+------------+-----------+--------------+-----------------+---------------+--------+------------------+------------+---------------+
| trip_count | trip_hour | trip_day | trip_month | trip_year | trip_quarter | trip_month_week | trip_week_day | temp_c | precip_amount_mm | is_holiday | serial_number |
+------------+-----------+----------+------------+-----------+--------------+-----------------+---------------+--------+------------------+------------+---------------+
|          1 |      0.32 |     0.01 |       0.18 |      0.12 |         0.18 |               0 |          0.02 |   0.53 |            -0.07 |      -0.13 |             0 |
+------------+-----------+----------+------------+-----------+--------------+-----------------+---------------+--------+------------------+------------+---------------+
```

## 步驟 2：建立機器學習模型
<a name="tutorial_regression_create_model"></a>

1. 下列查詢會將您的資料分割為訓練集和驗證集，並指定 80% 的資料集用於訓練，而 20% 的資料集用於驗證。訓練集是 ML 模型的輸入，用於識別模型的最佳演算法。建立模型之後，您可以使用驗證集來驗證模型的準確度。

   ```
   CREATE TABLE training_data AS
   SELECT
       trip_count,
       trip_hour,
       trip_day,
       trip_month,
       trip_year,
       trip_quarter,
       trip_month_week,
       trip_week_day,
       temp_c,
       precip_amount_mm,
       is_holiday
   FROM
       trip_data
   WHERE
       serial_number > (
           SELECT
               COUNT(1) * 0.2
           FROM
               trip_data
       );
   
   CREATE TABLE validation_data AS
   SELECT
       trip_count,
       trip_hour,
       trip_day,
       trip_month,
       trip_year,
       trip_quarter,
       trip_month_week,
       trip_week_day,
       temp_c,
       precip_amount_mm,
       is_holiday,
       trip_time
   FROM
       trip_data
   WHERE
       serial_number <= (
           SELECT
               COUNT(1) * 0.2
           FROM
               trip_data
       );
   ```

1. 下列查詢會建立迴歸模型，以預測任何輸入日期和時間的 `trip_count` 值。在下列範例中，請將 amzn-s3-demo-bucket 取代為您的 S3 儲存貯體。

   ```
   CREATE MODEL predict_rental_count
   FROM
       training_data TARGET trip_count FUNCTION predict_rental_count 
       IAM_ROLE default 
       PROBLEM_TYPE regression 
       OBJECTIVE 'mse' 
       SETTINGS (
           s3_bucket 'amzn-s3-demo-bucket',
           s3_garbage_collect off,
           max_runtime 5000
       );
   ```

## 步驟 3：驗證模型
<a name="tutorial_regression_step_validate"></a>

1. 使用下列查詢來輸出模型的各個層面，並在輸出中尋找均方誤差指標。均方誤差是迴歸問題的典型準確指標。

   ```
   show model predict_rental_count;
   ```

1. 針對驗證資料執行下列預測查詢，以比較預測的行程計數與實際的行程計數。

   ```
   SELECT
       trip_time,
       actual_count,
       predicted_count,
       (actual_count - predicted_count) difference
   FROM
       (
           SELECT
               trip_time,
               trip_count AS actual_count,
               PREDICT_RENTAL_COUNT (
                   trip_hour,
                   trip_day,
                   trip_month,
                   trip_year,
                   trip_quarter,
                   trip_month_week,
                   trip_week_day,
                   temp_c,
                   precip_amount_mm,
                   is_holiday
               ) predicted_count
           FROM
               validation_data
       )
   LIMIT
       5;
   ```

1. 下列查詢會根據驗證資料計算均方誤差和均方根誤差。您可以使用均方誤差和均方根誤差來測量預測數值目標與實際數值答案之間的距離。一個好的模型在這兩個指標中的分數都很低。下列查詢會傳回這兩個指標的值。

   ```
   SELECT
       ROUND(
           AVG(POWER((actual_count - predicted_count), 2)),
           2
       ) mse,
       ROUND(
           SQRT(AVG(POWER((actual_count - predicted_count), 2))),
           2
       ) rmse
   FROM
       (
           SELECT
               trip_time,
               trip_count AS actual_count,
               PREDICT_RENTAL_COUNT (
                   trip_hour,
                   trip_day,
                   trip_month,
                   trip_year,
                   trip_quarter,
                   trip_month_week,
                   trip_week_day,
                   temp_c,
                   precip_amount_mm,
                   is_holiday
               ) predicted_count
           FROM
               validation_data
       );
   ```

1. 下列查詢會針對 2017-01-01 的每個行程時間計算行程計數中的百分比誤差。查詢會將行程時間從百分比誤差最低的時間排序到百分比誤差最高的時間。

   ```
   SELECT
       trip_time,
       CAST(ABS(((actual_count - predicted_count) / actual_count)) * 100 AS DECIMAL (7,2)) AS pct_error
   FROM
       (
           SELECT
               trip_time,
               trip_count AS actual_count,
               PREDICT_RENTAL_COUNT (
                   trip_hour,
                   trip_day,
                   trip_month,
                   trip_year,
                   trip_quarter,
                   trip_month_week,
                   trip_week_day,
                   temp_c,
                   precip_amount_mm,
                   is_holiday
               ) predicted_count
           FROM
               validation_data
       )
   WHERE
      trip_time LIKE '2017-01-01 %%:%%:%%'
   ORDER BY
      2 ASC;
   ```

## 相關主題
<a name="tutorial_regression_related_topics"></a>

如需 Amazon Redshift ML 的相關資訊，請參閱下列文件：
+ [使用 Amazon Redshift ML 的成本](https://docs.aws.amazon.com/redshift/latest/dg/cost.html)
+ [CREATE MODEL 操作](https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_MODEL.html)
+ [EXPLAIN\$1MODEL 函數](https://docs.aws.amazon.com/redshift/latest/dg/r_explain_model_function.html)

如需機器學習的相關資訊，請參閱下列文件：
+ [機器學習概述](https://docs.aws.amazon.com/redshift/latest/dg/machine_learning_overview.html)
+ [適用於新手和專家的機器學習](https://docs.aws.amazon.com/redshift/latest/dg/novice_expert.html)
+ [什麼是機器學習預測的公平性和模型解釋性？](https://docs.aws.amazon.com/sagemaker/latest/dg/clarify-fairness-and-explainability.html)