Étape 4: Extraction de fonctionnalités des données à l'aide de T-SQL

Après avoir exploré les données, nous rassemblerons des informations à partir des données et passerons à l'ingénierie des fonctionnalités. Le processus d'extraction de caractéristiques à partir de données brutes est une étape importante dans la modélisation analytique avancée.

Dans cette étape, vous apprendrez à utiliser les fonctions Transact-SQL pour extraire des fonctionnalités à partir de données brutes. Appelez ensuite cette fonction à partir de la procédure stockée pour créer une table contenant les valeurs de fonctionnalité.

Définition des fonctions

Les valeurs de distance en mètres enregistrées dans les données d'origine peuvent ne pas représenter la distance géographique ou la distance de déplacement, de sorte que les coordonnées disponibles dans cet ensemble de données sont utilisées pour diriger la distance entre la position d'embarquement et la position de débarquement. Calculer. Pour ce faire, utilisez l'expression Haversine (https://en.wikipedia.org/wiki/Haversine_formula) dans votre fonction Transact-SQL personnalisée.

La fonction T-SQL fnCalculateDistance utilise l'expression Haversine pour calculer la distance, et la fonction T-SQL fnEngineerFeatures crée une table contenant toutes les caractéristiques.

Calculez la distance parcourue en utilisant fnCalculateDistance

La fonction T-SQL fnCalculateDistance est définie dans SQL Server à l'étape 2: Importer des données vers SQL Server à l'aide de PowerShell (http://qiita.com/qio9o9/items/98df36982f1fbecdf5e7).

Dans l'Explorateur d'objets de Management Studio, développez Programmation> Fonctions> Fonctions de valeur scalaire.

Cliquez avec le bouton droit sur fnCalculateDistance et sélectionnez Modifier pour ouvrir le script Transact-SQL dans une nouvelle fenêtre de requête.

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

Cette fonction est une fonction de valeur scalaire qui renvoie une seule valeur de données d'un type prédéfini.
Les valeurs de latitude et de longitude obtenues à partir des positions d'embarquement et de débarquement seront utilisées comme entrée. La formule Haversine convertit les positions en radians et utilise ces valeurs pour calculer la distance directe entre les deux emplacements.

Enregistrer les valeurs des fonctionnalités à l'aide de fnEngineerFeatures

La fonction T-SQL fnEngineerFeatures est définie dans SQL Server à l'étape 2: Importer des données vers SQL Server à l'aide de PowerShell (http://qiita.com/qio9o9/items/98df36982f1fbecdf5e7).

Dans l'Explorateur d'objets de Management Studio, développez Programmation> Fonctions> Fonctions de valeur de table.
Cliquez avec le bouton droit sur fnCalculateDistance et sélectionnez Modifier pour ouvrir le script Transact-SQL dans une nouvelle fenêtre de requête.

fnEngineerFeatures est une fonction table qui prend plusieurs colonnes en entrée et renvoie plusieurs colonnes à valeur caractéristique. Le but de fnEngineerFeatures est de créer un jeu de valeurs de fonction à utiliser pour la construction de modèles. fnEngineerFeatures appelle fnCalculateDistance pour obtenir la distance linéaire entre la position d'embarquement et la position de débarquement.

```SQL:fnEngineerFeatures
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
```

Pour vérifier l'opération, calculons la distance géographique pour l'enregistrement où la valeur de distance du compteur est mise à 0 même si la position d'embarquement et la position de descente sont différentes.

    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 indiquée par le compteur n'est pas toujours enregistrée comme une indication de distance géographique. Ce prétraitement en est une caractéristique, c'est pourquoi l'ingénierie est importante.

Dans l'étape suivante, vous apprendrez à utiliser ces fonctionnalités pour créer et entraîner des modèles d'apprentissage automatique à l'aide de Python.