Python-Tutorial: Erstellen von Datenfeatures mit T-SQL

Gilt für: SQL Server 2017 (14.x) und höher Azure SQL Managed Instance

In Teil drei dieser fünfteiligen Tutorialreihe erfahren Sie, wie Sie mithilfe einer Transact-SQL-Funktion Features aus Rohdaten erstellen. Anschließend rufen Sie diese Funktion aus einer gespeicherten SQL-Prozedur auf, um eine Tabelle zu erstellen, die die Funktionswerte enthält.

Der Vorgang des Feature Engineerings, der Erstellung von Features aus Rohdaten, kann ein entscheidender Schritt in der Modellierung der erweiterten Analyse sein.

In diesem Artikel führen Sie Folgendes durch:

• Ändern einer benutzerdefinierten Funktion zum Berechnen eine Fahrtstrecke
• Speichern der Features mithilfe einer weiteren benutzerdefinierten Funktion

In Teil 1 haben Sie die Voraussetzungen installiert und die Beispieldatenbank wiederhergestellt.

In Teil zwei haben Sie die Beispieldaten untersucht und einige Plots generiert.

In Teil vier laden Sie die Module und rufen die erforderlichen Funktionen auf, um das Modell mithilfe einer gespeicherten SQL Server-Prozedur zu erstellen und zu trainieren.

In Teil fünf erfahren Sie, wie Sie die Modelle operationalisieren können, die Sie in Teil vier trainiert und gespeichert haben.

Definieren der Funktion

Die in den ursprünglichen Daten angezeigten Entfernungswerte basieren auf der vom Taxameter angezeigten Entfernung und stellen in der Regel keine geografische Distanz oder zurückgelegte Entfernung dar. Aus diesem Grund müssen Sie die direkte Entfernung zwischen den Abhol- und den Zielorten berechnen, indem Sie die verfügbaren Koordinaten in im Quell-Dataset NYC Taxi verwenden. Sie erreichen dies, indem Sie die Haversine-Formel in einer benutzerdefinierten Transact-SQL-Funktion verwenden.

Verwenden Sie eine benutzerdefinierte T-SQL-Funktion, fnCalculateDistance, um die Entfernung mithilfe der Haversine-Formel zu berechnen, und verwenden Sie eine zweite benutzerdefinierte T-SQL-Funktion, fnEngineerFeatures, um eine Tabelle mit allen Funktionen zu erstellen.

Berechnen der Fahrstrecke mithilfe von fnCalculateDistance

Die Funktion fnCalculateDistance ist in der Beispieldatenbank enthalten. Nehmen Sie sich etwas Zeit, um den Code zu überprüfen:

1. Erweitern Sie in Management StudioProgrammierbarkeit, erweitern Sie Funktionen und anschließend Skalarwertfunktionen.

2. Klicken Sie mit der rechten Maustaste auf fnCalculateDistance, und wählen Sie Ändern aus, um das Transact-SQL-Skript in einem neuen Abfragefenster zu öffnen.

   Der Bericht könnte beispielsweise wie folgt aussehen:

   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
   

Hinweise:

• Die Funktion ist eine Skalarwertfunktion, die einen einzelnen Datenwert eines vordefinierten Typs zurückgibt.
• Die Funktion verwendet Längen- und Breitengrade als Eingaben, die sie aus den Abhol- und Zielorten der Fahrten bezieht. Die Haversine-Formel wandelt Orte ins Bogenmaß um und verwendet diese Werte, um die direkte Entfernung zwischen zwei Orten in Meilen zu berechnen.

Speichern der Features mithilfe von fnEngineerFeatures

Verwenden Sie die benutzerdefinierte T-SQL-Funktion fnEngineerFeatures, um die berechneten Werte einer Tabelle hinzuzufügen, die zum Trainieren des Modells verwendet werden kann. Diese Funktion ist eine Tabellenwertfunktion, die mehrere Spalten als Eingaben annimmt und eine Tabelle mit mehreren Funktionsspalten ausgibt. Der Zweck dieser Funktion ist die Erstellung einer Funktionsgruppe zum Verwenden beim Erstellen eines Modells. Die Funktion fnEngineerFeatures ruft die zuvor erstellte T-SQL-Funktion fnCalculateDistanceauf, um die direkte Entfernung zwischen den Abhol- und Zielorten abzurufen.

Nehmen Sie sich etwas Zeit, um den Code zu überprüfen:

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

Um sicherzustellen, dass diese Funktion funktioniert, können Sie sie zum Berechnen der geografischen Distanz für diese Fahrten verwenden, bei denen die gemessene Distanz 0 war, aber die Abhol- und Zielorte unterschiedlich waren.

    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

Wie Sie sehen können, entspricht die vom Taxameter angezeigte Entfernung nicht immer der geografischen Distanz. Daher ist die Featureentwicklung wichtig.

Im nächsten Teil erfahren Sie, wie Sie diese Datenfeatures zum Erstellen und Trainieren eines Machine Learning-Modells mit Python verwenden.

Nächste Schritte

In diesem Artikel führen Sie folgende Schritte aus:

• Ändern einer benutzerdefinierten Funktion zum Berechnen einer Fahrtstrecke
• Speichern der Features mithilfe einer weiteren benutzerdefinierten Funktion

Python-Tutorial: Trainieren und Speichern eines Python-Modells mit T-SQL