튜토리얼: 회귀 모델 구축

세 개의 퍼블릭 데이터 세트를 Amazon Redshift로 로드하려면 테이블 세 개를 생성해야 합니다. 데이터 세트는 Toronto Bike Ridership Data, historical weather data, historical holidays data입니다. 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));

다음 쿼리는 샘플 데이터를 이전 단계에서 생성한 테이블로 로드합니다.

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;

다음 쿼리는 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;

다음 쿼리는 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);

다음 쿼리는 데이터 세트의 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
    );

다음 쿼리는 임의의 입력 날짜 및 시간에 대해 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
    );

다음 쿼리를 사용하여 모델의 측면을 출력하고 출력에서 평균 제곱 오차 메트릭을 찾습니다. 평균 제곱 오차는 회귀 문제에서 흔히 사용하는 정확도 메트릭입니다.

show model predict_rental_count;

검증 데이터에 대해 다음 예측 쿼리를 실행하여 예측된 이동 횟수를 실제 이동 횟수와 비교합니다.

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;

다음 쿼리는 검증 데이터를 기반으로 평균 제곱 오차 및 평균 제곱근 오차를 계산합니다. 평균 제곱 오차와 평균 제곱근 평균 제곱 오차를 사용하여 예상 수치 목표 값과 실제 수치 답변 간의 거리를 측정합니다. 좋은 모델은 두 메트릭 모두에서 점수가 낮습니다. 다음 쿼리는 두 메트릭의 값을 반환합니다.

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

다음 쿼리는 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;