Tutorial de Python: Creación de características de datos mediante T-SQL

Artículo
05/30/2023

Se aplica a: SQL Server 2017 (14.x) y versiones posteriores Azure SQL Managed Instance

En la parte tres de esta serie de tutoriales de cinco partes, aprenderá a crear características a partir de datos sin procesar mediante una función Transact-SQL. Después, llamará a esa función desde un procedimiento almacenado de SQL para crear una tabla que contenga los valores de las características.

El proceso de caracterización, la creación de características a partir de los datos sin procesar, puede ser un paso crítico en el modelado de análisis avanzados.

En este artículo, hará lo siguiente:

Modificará una función personalizada para calcular la distancia de la carrera
Guardará las características mediante otra función personalizada

En la parte uno, ha instalado los requisitos previos y ha restaurado la base de datos de ejemplo.

En la parte dos, ha explorado los datos de ejemplo y ha generado algunos trazados.

En la parte cuatro, cargará los módulos y llamará a las funciones necesarias para crear y entrenar el modelo mediante un procedimiento almacenado de SQL Server.

En la parte cinco, aprenderá a poner en marcha los modelos entrenados y guardados en la parte cuatro.

Definir la función

Los valores de distancia notificados en los datos originales se basan en la distancia notificada del taxímetro y no representan necesariamente la distancia geográfica o la distancia recorrida. Por tanto, debe calcular la distancia directa entre los puntos de origen y destino, usando las coordenadas disponibles en el conjunto de datos de origen NYC Taxi. Puede hacerlo mediante la fórmula Haversine en un función personalizada de Transact-SQL.

Usará una función personalizada de T-SQL, fnCalculateDistance, para calcular la distancia usando la fórmula Haversine, y una segunda función personalizada de T-SQL, fnEngineerFeatures, para crear una tabla que contiene todas las características.

Cálculo de la distancia del trayecto con fnCalculateDistance

La función fnCalculateDistance se incluye en la base de datos de ejemplo. Tómese un minuto para revisar el código:

En Management Studio, expanda Programación, expanda Funciones y, después, Funciones escalares.

Haga clic con el botón derecho en fnCalculateDistance y seleccione Modificar para abrir el script de Transact-SQL en una nueva ventana de consulta.

Debe tener el siguiente aspecto:

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

Notas:

La función es una función escalar y devuelve un único valor de datos de un tipo predefinido.
La función toma los valores de latitud y longitud como entradas, obtenidos de las ubicaciones de origen y destino de los viajes. La fórmula Haversine convierte ubicaciones en radianes y usa esos valores para calcular la distancia directa en millas entre las dos ubicaciones.

Guardado de las características mediante fnEngineerFeatures

Para agregar el valor calculado a una tabla y poder usarlo para entrenar el modelo, deberá usar la función T-SQL personalizada, fnEngineerFeatures. Esta función es una función con valores de tabla que toma varias columnas como entradas y genera una tabla con varias columnas de características. El propósito de esta función es crear un conjunto de características para usar en la creación de un modelo. La función fnEngineerFeatures llama a la función de T-SQL creada anteriormente, fnCalculateDistance, para obtener la distancia directa entre las ubicaciones de origen y destino.

Tómese un minuto para revisar el código:

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

Para comprobar que esta función funciona, puede usarla para calcular la distancia geográfica de los viajes en los que la distancia medida era 0 pero las ubicaciones de origen y destino eran diferentes.

    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

Como puede ver, la distancia notificada por el taxímetro no siempre se corresponde con la distancia geográfica. Por eso es importante la ingeniería de características.

En la parte siguiente, aprenderá a usar estas características de datos para crear y entrenar un modelo de aprendizaje automático con Python.

Pasos siguientes

En este artículo:

Se modificó una función personalizada para calcular la distancia de la carrera
Se guardaron las características mediante otra función personalizada

Tutorial de Python: Entrenar y guardar un modelo de Python mediante T-SQL