Tutoriel : Création de modèles de régression

Vous devez créer trois tables pour charger les trois jeux de données publics dans Amazon Redshift. Les jeux de données sont Bike Share Toronto Ridership Data, Données climatiques historiques et Données historiques des jours fériés. Exécutez la requête suivante dans l'éditeur de requête Amazon Redshift pour créer des tables nommées ridership, weather et 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));

La requête suivante charge les données d'exemple dans les tables que vous avez créées à l'étape précédente.

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;

La requête suivante effectue des transformations sur les jeux de données ridership et weather pour éliminer les biais ou les anomalies. La suppression des biais et des anomalies améliore la précision du modèle. La requête simplifie les tables en créant deux nouvelles vues appelées ridership_view et 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;

La requête suivante crée une table qui combine tous les attributs d'entrée pertinents de ridership_view et weather_view dans la table 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);

La requête suivante divise vos données en un jeu d'entraînement et un jeu de validation en désignant 80 % du jeu de données pour l'entraînement et 20 % pour la validation. Le jeu d'entraînement est l'entrée du modèle ML utilisée pour identifier le meilleur algorithme possible pour le modèle. Une fois le modèle créé, vous utilisez le jeu de validation pour valider la précision du modèle.

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

La requête suivante crée un modèle de régression pour prédire la valeur trip_count pour n'importe quelles date et heure d'entrée. Dans l'exemple suivant, remplacez amzn-s3-demo-bucket par votre propre compartiment 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
    );

Utilisez la requête suivante pour générer des aspects du modèle et recherchez la métrique d'écart quadratique moyen dans la sortie. L'écart quadratique moyen est une métrique de précision standard des problèmes de régression.

show model predict_rental_count;

Exécutez les requêtes de prédiction suivantes par rapport aux données de validation pour comparer le nombre prédit de trajets au nombre réel de trajets.

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;

La requête suivante calcule l'écart quadratique moyen et l'écart-type en fonction de vos données de validation. Vous utilisez l'écart quadratique moyen et l'écart-type pour mesurer la distance entre la cible numérique prédite et la réponse numérique réelle. Un bon modèle présente un faible score pour les deux métriques. La requête suivante renvoie la valeur des deux métriques.

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

La requête suivante calcule le pourcentage d'erreur dans le nombre de trajets pour chaque heure de trajet le 01/01/2017. La requête ordonne les heures de trajet de l'heure avec le pourcentage d'erreur le plus bas jusqu'à l'heure avec le pourcentage d'erreur le plus haut.

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;