Esercitazione Python: Creare funzionalità dei dati con T-SQL

Si applica a: SQL Server 2017 (14.x) e versioni successive Istanza gestita di SQL di Azure

Nella terza parte di questa serie di esercitazioni in cinque parti si apprenderà come creare funzionalità dai dati non elaborati tramite una funzione Transact-SQL. La funzione verrà quindi chiamata da una stored procedure SQL per creare una tabella contenente i valori della funzionalità.

Il processo di definizione delle funzionalità, creando funzionalità dai dati non elaborati, è un passaggio critico nella modellazione dell'analisi avanzata.

Contenuto dell'articolo:

Nella prima parte sono stati installati i prerequisiti ed è stato ripristinato il database di esempio.

Nella seconda parte sono stati esaminati i dati di esempio e sono stati generati alcuni tracciati.

Nella quarta parte verranno caricati i moduli e verranno chiamate le funzioni necessarie per la creazione e il training del modello usando una stored procedure di SQL Server.

Nella quinta parte si apprenderà come rendere operativi i modelli sottoposti a training e salvati nella quarta parte.

Definire la funzione

I valori di distanza inclusi nei dati originali si basano sulla distanza registrata dal tassametro e non rappresentano necessariamente la distanza geografica o la distanza percorsa. Pertanto è necessario calcolare la distanza diretta tra i punti di inizio e fine della corsa, usando le coordinate disponibili nel set di dati di origine NYC Taxi. È possibile farlo usando la formula dell'emisenoverso in una funzione Transact-SQL personalizzata.

Si userà una funzione T-SQL personalizzata, fnCalculateDistance, per calcolare la distanza con la formula dell'emisenoverso e una seconda funzione T-SQL personalizzata, fnEngineerFeatures, per creare una tabella contenente tutte le funzionalità.

Calcolare la distanza della corsa usando fnCalculateDistance

La funzione fnCalculateDistance è inclusa nel database di esempio. Dedicare un attimo di tempo a esaminare il codice:

1. In Management Studio espandere Programmabilità, Funzioni e quindi Funzioni a valori scalari.

2. Fare clic con il pulsante destro del mouse su fnCalculateDistance e selezionare Modifica per aprire lo script Transact-SQL in una nuova finestra di query.

   L'output dovrebbe essere simile al seguente:

   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
   

Note:

• La funzione è una funzione con valori scalari che restituisce un singolo valore di dati di un tipo predefinito.
• La funzione accetta come input valori di latitudine e longitudine, ottenuti dalle località di inizio e fine della corsa. La formula dell'emisenoverso converte le posizioni in radianti e usa tali valori per calcolare la distanza diretta in miglia tra queste due posizioni.

Salvare le caratteristiche usando fnEngineerFeatures

Per aggiungere il valore calcolato a una tabella da usare per il training del modello si userà la funzione T-SQL personalizzata fnEngineerFeatures. È una funzione con valori di tabella che accetta come input più colonne e restituisce una tabella con più colonne di funzionalità. Lo scopo della funzione è la creazione di un set di funzionalità da usare per la compilazione di un modello. La funzione fnEngineerFeatures chiama la funzione T-SQL fnCalculateDistancecreata in precedenza per ottenere la distanza diretta tra i punti di inizio e fine della corsa.

Dedicare un attimo di tempo a esaminare il codice:

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

Per verificare che la funzione operi in modo corretto è possibile usarla per calcolare la distanza geografica per i viaggi in cui la distanza sul tassametro era pari a 0 ma i punti di inizio e fine corsa erano diversi.

    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

Come si può notare, la distanza indicata dal tassametro non corrisponde sempre alla distanza geografica. È per questo che la progettazione delle caratteristiche è importante.

Nella parte successiva si apprenderà come usare queste funzionalità dei dati per la creazione e il training di un modello di Machine Learning con Python.

Passaggi successivi

In questo articolo si apprenderà come: