Créer des caractéristiques de données à l’aide de T-SQLCreate Data Features using T-SQL

S’APPLIQUE À : ouiSQL Server nonAzure SQL Database nonAzure Synapse Analytics (SQL DW) nonParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Après l’exploration de données, vous avez recueilli des insights grâce aux données et vous êtes prêt à passer à l’ingénierie des caractéristiques.After data exploration, you have collected some insights from the data, and are ready to move on to feature engineering. Ce processus de création de caractéristiques à partir des données brutes peut être une étape essentielle pour la modélisation de l’analytique avancée.This process of creating features from the raw data can be a critical step in advanced analytics modeling.

Cet article fait partie d’un tutoriel, Analytique Python en base de données pour développeur SQL.This article is part of a tutorial, In-database Python analytics for SQL developers.

Lors de cette étape, vous allez découvrir comment créer des caractéristiques à partir de données brutes en utilisant une fonction Transact-SQLTransact-SQL .In this step, you'll learn how to create features from raw data by using a Transact-SQLTransact-SQL function. Ensuite, vous appellerez cette fonction à partir d’une procédure stockée pour créer une table qui contient les valeurs des caractéristiques.You'll then call that function from a stored procedure to create a table that contains the feature values.

Définir la fonctionDefine the Function

Les valeurs de distance indiquées dans les données d’origine sont basées sur la distance signalée au compteur, et ne représente pas nécessairement la distance géographique ou la distance parcourue.The distance values reported in the original data are based on the reported meter distance, and don't necessarily represent geographical distance or distance traveled. Ainsi, vous devrez calculer la distance directe entre les lieux de prise en charge et de dépose, en utilisant les coordonnées disponibles dans le dataset source « NYC Taxi ».Therefore, you'll need to calculate the direct distance between the pick-up and drop-off points, by using the coordinates available in the source NYC Taxi dataset. Vous pouvez pour cela utiliser la formule de Haversine dans une fonction Transact-SQLTransact-SQL personnalisée.You can do this by using the Haversine formula in a custom Transact-SQLTransact-SQL function.

Vous allez utiliser une fonction T-SQL personnalisée, fnCalculateDistance, pour calculer la distance à l’aide de la formule de Haversine, et utiliser une seconde fonction T-SQL personnalisée, fnEngineerFeatures, pour créer une table contenant toutes les caractéristiques.You'll use one custom T-SQL function, fnCalculateDistance, to compute the distance using the Haversine formula, and use a second custom T-SQL function, fnEngineerFeatures, to create a table containing all the features.

Calcul de la distance des trajets à l’aide de fnCalculateDistanceCalculate trip distance using fnCalculateDistance

  1. La fonction fnCalculateDistance doit avoir été téléchargée et inscrite auprès de SQL ServerSQL Server dans le cadre de la préparation de cette procédure pas à pas.The function fnCalculateDistance should have been downloaded and registered with SQL ServerSQL Server as part of the preparation for this walkthrough. Prenez le temps de passer le code en revue.Take a minute to review the code.

    Dans Management StudioManagement Studio, développez Programmabilité, Fonctions puis Fonctions scalaires.In Management StudioManagement Studio, expand Programmability, expand Functions and then Scalar-valued functions. Cliquez avec le bouton droit sur fnCalculateDistance, puis sélectionnez Modifier pour ouvrir le script Transact-SQLTransact-SQL dans une nouvelle fenêtre de requête.Right-click fnCalculateDistance, and select Modify to open the Transact-SQLTransact-SQL script in a new query window.

    CREATE FUNCTION [dbo].[fnCalculateDistance] (@Lat1 float, @Long1 float, @Lat2 float, @Long2 float)
    -- User-defined function that calculates the direct distance between two geographical coordinates
    RETURNS float
    AS
    BEGIN
      DECLARE @distance decimal(28, 10)
      -- Convert to radians
      SET @Lat1 = @Lat1 / 57.2958
      SET @Long1 = @Long1 / 57.2958
      SET @Lat2 = @Lat2 / 57.2958
      SET @Long2 = @Long2 / 57.2958
      -- Calculate distance
      SET @distance = (SIN(@Lat1) * SIN(@Lat2)) + (COS(@Lat1) * COS(@Lat2) * COS(@Long2 - @Long1))
      --Convert to miles
      IF @distance <> 0
      BEGIN
        SET @distance = 3958.75 * ATAN(SQRT(1 - POWER(@distance, 2)) / @distance);
      END
      RETURN @distance
    END
    GO
    

Remarques :Notes:

  • La fonction est une fonction scalaire qui retourne une valeur de données unique d’un type prédéfini.The function is a scalar-valued function, returning a single data value of a predefined type.
  • Elle accepte des valeurs de latitude et de longitude comme entrées, obtenues à partir des lieux de prise en charge et de dépose des passagers.It takes latitude and longitude values as inputs, obtained from trip pick-up and drop-off locations. La formule de Haversine convertit les emplacements en radians et utilise ces valeurs pour calculer la distance directe en miles entre ces deux emplacements.The Haversine formula converts locations to radians and uses those values to compute the direct distance in miles between those two locations.

Pour ajouter la valeur calculée à une table qui peut être utilisée pour l’apprentissage du modèle, vous allez utiliser une autre fonction, fnEngineerFeatures.To add the computed value to a table that can be used for training the model, you'll use another function, fnEngineerFeatures.

Enregistrer les caractéristiques à l’aide de fnEngineerFeaturesSave the features using fnEngineerFeatures

  1. Prenez une minute pour examiner le code de la fonction T-SQL personnalisée, fnEngineerFeatures, qui doit avoir été créé pour vous dans le cadre de la préparation de cette procédure pas à pas.Take a minute to review the code for the custom T-SQL function, fnEngineerFeatures, which should have been created for you as part of the preparation for this walkthrough.

    Il s’agit d’une fonction table qui prend plusieurs colonnes comme entrées et retourne une table avec plusieurs colonnes de caractéristiques.This function is a table-valued function that takes multiple columns as inputs, and outputs a table with multiple feature columns. Le rôle de cette fonction consiste à créer un ensemble de caractéristiques servant à générer un modèle.The purpose of this function is to create a feature set for use in building a model. La fonction fnEngineerFeatures appelle la fonction T-SQL créée précédemment, fnCalculateDistance, pour obtenir la distance directe entre les lieux de prise en charge et de dépose des passagers.The function fnEngineerFeatures calls the previously created T-SQL function, fnCalculateDistance, to get the direct distance between pickup and dropoff locations.

    CREATE FUNCTION [dbo].[fnEngineerFeatures] (
    @passenger_count int = 0,
    @trip_distance float = 0,
    @trip_time_in_secs int = 0,
    @pickup_latitude float = 0,
    @pickup_longitude float = 0,
    @dropoff_latitude float = 0,
    @dropoff_longitude float = 0)
    RETURNS TABLE
    AS
      RETURN
      (
      -- Add the SELECT statement with parameter references here
      SELECT
        @passenger_count AS passenger_count,
        @trip_distance AS trip_distance,
        @trip_time_in_secs AS trip_time_in_secs,
        [dbo].[fnCalculateDistance](@pickup_latitude, @pickup_longitude, @dropoff_latitude, @dropoff_longitude) AS direct_distance
      )
    GO
    
  2. Pour vérifier que cette fonction fonctionne, vous pouvez l’utiliser pour calculer la distance géographique pour les trajets où la distance au compteur était égale à 0, mais où les lieux de prise en charge et de dépose étaient différents.To verify that this function works, you can use it to calculate the geographical distance for those trips where the metered distance was 0 but the pick-up and drop-off locations were different.

        SELECT tipped, fare_amount, passenger_count,(trip_time_in_secs/60) as TripMinutes,
        trip_distance, pickup_datetime, dropoff_datetime,
        dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) AS direct_distance
        FROM nyctaxi_sample
        WHERE pickup_longitude != dropoff_longitude and pickup_latitude != dropoff_latitude and trip_distance = 0
        ORDER BY trip_time_in_secs DESC
    

    Comme vous pouvez le voir, la distance signalée par le compteur ne correspond pas toujours à la distance géographique.As you can see, the distance reported by the meter doesn't always correspond to geographical distance. C’est pourquoi l’ingénierie des caractéristiques est importante.This is why feature engineering is important.

À l’étape suivante, vous découvrirez comment utiliser ces caractéristiques de données pour créer et entraîner un modèle Machine Learning avec Python.In the next step, you'll learn how to use these data features to create and train a machine learning model using Python.

Étape suivanteNext step

Entraîner et enregistrer un modèle Python à l’aide de T-SQLTrain and save a Python model using T-SQL

Étape précédentePrevious step

Explorer et visualiser les donnéesExplore and visualize the data