Schritt 4: Merkmalsextraktion von Daten mit T-SQL

Nachdem wir die Daten untersucht haben, werden wir einige Erkenntnisse aus den Daten sammeln und mit dem Feature-Engineering fortfahren. Der Prozess der Merkmalsextraktion aus Rohdaten ist ein wichtiger Schritt in der fortgeschrittenen analytischen Modellierung.

In diesem Schritt erfahren Sie, wie Sie mit Transact-SQL-Funktionen Features aus Rohdaten extrahieren. Rufen Sie dann diese Funktion aus der gespeicherten Prozedur auf, um eine Tabelle mit den Feature-Werten zu erstellen.

Funktionsdefinition

Die in den Originaldaten aufgezeichneten Werte für die Entfernungsmessung des Messgeräts stellen möglicherweise keine geografische Entfernung oder Fahrstrecke dar. Daher werden die in diesem Datensatz verfügbaren Koordinaten verwendet, um die Entfernung zwischen der Einstiegsposition und der Ausschiffungsposition zu bestimmen. Berechnen. Verwenden Sie dazu den Haversine-Ausdruck (https://en.wikipedia.org/wiki/Haversine_formula) in Ihrer benutzerdefinierten Transact-SQL-Funktion.

Die T-SQL-Funktion "fnCalculateDistance" verwendet den Haversine-Ausdruck, um die Entfernung zu berechnen, und die T-SQL-Funktion "fnEngineerFeatures" erstellt eine Tabelle mit allen Funktionen.

Berechnen Sie die zurückgelegte Strecke mit fnCalculateDistance

Die T-SQL-Funktion "fnCalculateDistance" wird in SQL Server in Schritt 2 definiert: Importieren von Daten mit PowerShell in SQL Server (http://qiita.com/qio9o9/items/98df36982f1fbecdf5e7).

Erweitern Sie im Objekt-Explorer von Management Studio die Option Programmierung> Funktionen> Skalarwertfunktionen.

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

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

--Diese Funktion ist eine Skalarwertfunktion, die einen einzelnen Datenwert eines vordefinierten Typs zurückgibt.

Die Längen- und Breitengrade, die aus den Ein- und Ausstiegspositionen erhalten werden, werden als Eingabe verwendet. Die Haversine-Formel konvertiert Positionen in Bogenmaß und berechnet anhand dieser Werte den direkten Abstand zwischen den beiden Standorten.

Speichern Sie Feature-Werte mit fnEngineerFeatures

Die T-SQL-Funktion "fnEngineerFeatures" wird in SQL Server in Schritt 2 definiert: Importieren von Daten mit PowerShell in SQL Server (http://qiita.com/qio9o9/items/98df36982f1fbecdf5e7).

Erweitern Sie im Objekt-Explorer von Management Studio die Option Programmierung> Funktionen> Tabellenwertfunktionen.
Klicken Sie mit der rechten Maustaste auf "fnCalculateDistance" und wählen Sie "Ändern", um das Transact-SQL-Skript in einem neuen Abfragefenster zu öffnen.

fnEngineerFeatures ist eine Tabellenwertfunktion, die mehrere Spalten als Eingabe verwendet und mehrere Spalten mit Feature-Wert zurückgibt. Der Zweck von "fnEngineerFeatures" besteht darin, einen Feature-Wertesatz zu erstellen, der für die Modellbildung verwendet wird. fnEngineerFeatures ruft fnCalculateDistance auf, um den linearen Abstand zwischen der Einstiegsposition und der Ausschiffungsposition zu ermitteln.

```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
```

Berechnen wir zur Überprüfung des Betriebs die geografische Entfernung für den Datensatz, bei dem der Wert für die Entfernung des Messgeräts auf 0 gesetzt ist, obwohl die Einstiegsposition und die Ausstiegsposition unterschiedlich sind.

    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, wird die vom Messgerät gemeldete Entfernung nicht immer als Hinweis auf die geografische Entfernung aufgezeichnet. Diese Vorbehandlung ist ein Merkmal dafür, weshalb Engineering wichtig ist.

Im nächsten Schritt lernen Sie, wie Sie mit diesen Funktionen maschinelle Lernmodelle mit Python erstellen und trainieren.