Tutoriel Python : Créer des caractéristiques de données à l’aide de T-SQL
S’applique à : 
SQL Server 2017 (14.x) et versions ultérieures Azure SQL Managed Instance
Dans cette troisième des cinq parties du tutoriel, vous allez découvrir comment créer des caractéristiques à partir de données brutes en utilisant une fonction Transact-SQL. Ensuite, vous appellerez cette fonction à partir d’une procédure stockée SQL pour créer une table qui contient les valeurs des caractéristiques.
Ce processus de caractérisation à partir des données brutes peut être une étape essentielle pour la modélisation de l’analytique avancée.
Dans cet article, vous allez :
- Modifier une fonction personnalisée pour calculer la distance du trajet
- Enregistrer les fonctionnalités à l’aide d’une autre fonction personnalisée
Dans la première partie, vous avez installé les prérequis et restauré l’exemple de base de données.
Dans la deuxième partie, vous avez étudié les exemples de données et généré des tracés.
Dans la quatrième partie, vous chargez les modules et appelez les fonctions nécessaires pour créer et entraîner le modèle à l’aide d’une procédure stockée SQL Server.
Dans la cinquième partie, vous apprendrez à rendre opérationnels les modèles que vous avez formés et enregistrés dans la quatrième partie.
Définir la fonction
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. 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 ». Vous pouvez pour cela utiliser la formule de Haversine dans une fonction Transact-SQL personnalisée.
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.
Calcul de la distance des trajets à l’aide de fnCalculateDistance
La fonction fnCalculateDistance est incluse dans l’exemple de base de données. Prenez le temps de passer le code en revue :
Dans Management Studio, développez Programmabilité, Fonctions puis Fonctions scalaires.
Cliquez avec le bouton droit sur fnCalculateDistance, puis sélectionnez Modifier pour ouvrir le script Transact-SQL dans une nouvelle fenêtre de requête.
Il doit se présenter comme suit :
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 :
- La fonction est une fonction scalaire qui retourne une valeur de données unique d’un type prédéfini.
- La fonction accepte des valeurs de latitude et de longitude comme entrées, obtenues à partir des adresses de départ et d’arrivée des passagers. La formule de Haversine convertit les emplacements en radians et utilise ces valeurs pour calculer la distance directe en miles entre ces deux emplacements.
Enregistrer les caractéristiques à l’aide de fnEngineerFeatures
Pour ajouter la valeur calculée à une table qui peut être utilisée pour l’apprentissage du modèle, vous allez utiliser la fonction T-SQL personnalisée, fnEngineerFeatures. Il s’agit d’une fonction table qui prend plusieurs colonnes comme entrées et retourne une table avec plusieurs colonnes de caractéristiques. Le rôle de cette fonction consiste à créer un ensemble de caractéristiques servant à générer un modèle. 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.
Prenez le temps de passer le code en revue :
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 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.
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. C’est pourquoi l’ingénierie des caractéristiques est importante.
Dans la partie 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.
Étapes suivantes
Dans cet article, vous découvrirez comment :
- Modifier une fonction personnalisée pour calculer la distance du trajet
- Enregistrer les fonctionnalités à l’aide d’une autre fonction personnalisée