選取您的 Cookie 偏好設定

我們使用提供自身網站和服務所需的基本 Cookie 和類似工具。我們使用效能 Cookie 收集匿名統計資料，以便了解客戶如何使用我們的網站並進行改進。基本 Cookie 無法停用，但可以按一下「自訂」或「拒絕」以拒絕效能 Cookie。

如果您同意，AWS 與經核准的第三方也會使用 Cookie 提供實用的網站功能、記住您的偏好設定，並顯示相關內容，包括相關廣告。若要接受或拒絕所有非必要 Cookie，請按一下「接受」或「拒絕」。若要進行更詳細的選擇，請按一下「自訂」。

偏好設定
聯絡我們
意見回饋
AWS Documentation
建立 AWS 帳戶
教學課程：建置迴歸模型 - Amazon Redshift
使用案例範例先決條件步驟 1：將資料從 Amazon S3 載入到 Amazon Redshift步驟 2：建立機器學習模型步驟 3：驗證模型相關主題

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

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

教學課程：建置迴歸模型

PDFRSS

在本教學中，您會使用 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 陳述式。

使用案例範例

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

工作

  • 先決條件

  • 步驟 1：將資料從 Amazon S3 載入到 Amazon Redshift

  • 步驟 2：建立機器學習模型

  • 步驟 3：驗證模型

先決條件

為完成此教學課程，您必須完成 Amazon Redshift ML 的管理設定。

步驟 1：將資料從 Amazon S3 載入到 Amazon Redshift

使用 Amazon Redshift 查詢編輯器 v2 來執行下列查詢。

  1. 您必須建立三個資料表，才能將三個公開資料集載入 Amazon Redshift。資料集是多倫多自行車乘客資料、歷史氣象資料和歷史假期資料。在 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));

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

    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;

  3. 下列查詢會對 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;

  4. 下面的查詢會建立一個資料表，該資料表會將 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);

檢視範例資料 (選擇性)

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


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 |
+---------------------+------------+-----------+----------+------------+-----------+--------------+-----------------+---------------+--------+------------------+------------+---------------+

顯示屬性之間的關聯 (選擇性)

確定相關性有助於測量屬性之間的關聯強度。關聯層級可協助您判斷哪些因素會影響您的目標輸出。在此教學課程中，目標輸出為 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：建立機器學習模型

  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
    );

  2. 下列查詢會建立迴歸模型，以預測任何輸入日期和時間的 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：驗證模型

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

    show model predict_rental_count;

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

    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;

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

    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
    );

  4. 下列查詢會針對 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;

如需 Amazon Redshift ML 的相關資訊，請參閱下列文件：

如需機器學習的相關資訊，請參閱下列文件：

需要協助？

隱私權網站條款Cookie 偏好設定
© 2025, Amazon Web Services, Inc.或其附屬公司。保留所有權利。