Amazon Redshift は、パッチ 198 以降、新しい Python UDF の作成をサポートしなくなります。既存の 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 を使用して、1 日の任意の時間にトロント市の自転車シェアサービスを利用する人の数を予測するリグレッションモデルを構築します。モデルの入力には、祝日と気象条件が含まれます。この問題には数値的な結果が必要なため、リグレッションモデルを使用します。

CREATE MODEL コマンドを使用して、トレーニングデータをエクスポートし、モデルをトレーニングし、Amazon Redshift で SQL 関数として使用できるようにします。トレーニングデータをテーブルまたは SELECT ステートメントとして指定するには、CREATE MODEL オペレーションを使用します。

## ユースケースの例
<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. 3 つのパブリックデータセットを Amazon Redshift に読み込むには、3 つのテーブルを作成する必要があります。データセットは[トロントバイクライダーシップデータ](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` という名前の 2 つの新しいビューを作成することによってテーブルを単純化します。

   ```
   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 年 1 月 1 日の各トリップ時間に対するトリップ数の誤差率を計算します。このクエリは、誤差率が最も低い時間から誤差率が最も高い時間にトリップ時間を並べ替えます。

   ```
   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 機械学習を使用するためのコスト](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)