Samouczek języka Python: tworzenie funkcji danych przy użyciu języka T-SQL

Dotyczy: SQL Server 2017 (14.x) i nowsze wersje usługi Azure SQL Managed Instance

W trzeciej części tej pięcioczęściowej serii samouczków dowiesz się, jak tworzyć funkcje na podstawie danych pierwotnych przy użyciu funkcji Transact-SQL. Następnie wywołasz tę funkcję z procedury składowanej SQL, aby utworzyć tabelę zawierającą wartości funkcji.

Proces inżynierii cech, tworzenie funkcji na podstawie danych pierwotnych, może być krytycznym krokiem w zaawansowanym modelowaniu analiz.

W tym artykule dowiesz się:

• Modyfikowanie funkcji niestandardowej w celu obliczenia odległości podróży
• Zapisz cechy przy użyciu innej funkcji niestandardowej

W części 1 zainstalowano wymagania wstępne i przywrócono przykładową bazę danych.

W drugiej części zapoznaliśmy się z przykładowymi danymi i wygenerowaliśmy niektóre wykresy.

W czwartej części załadujesz moduły i wywołasz niezbędne funkcje, aby utworzyć i wytrenować model przy użyciu procedury składowanej programu SQL Server.

W części piątej dowiesz się, jak operacjonalizować modele, które zostały wytrenowane i zapisane w części czwartej.

Definiowanie funkcji

Wartości odległości zgłoszone w oryginalnych danych są oparte na zgłoszonej odległości miernika i nie muszą reprezentować odległości geograficznej ani odległości podróży. W związku z tym należy obliczyć bezpośrednią odległość między punktami pick-up i drop-off, używając współrzędnych dostępnych w źródłowym zestawie danych NYC Taxi. Można to zrobić przy użyciu formuły Haversine w niestandardowej funkcji Transact-SQL.

Użyjesz jednej niestandardowej funkcji języka T-SQL, fnCalculateDistance, aby obliczyć odległość przy użyciu formuły Haversine i użyć drugiej niestandardowej funkcji języka T-SQL, fnEngineerFeatures, aby utworzyć tabelę zawierającą wszystkie funkcje.

Oblicz odległość podróży przy użyciu fnCalculateDistance

Funkcja fnCalculateDistance jest zawarta w przykładowej bazie danych. Pośmiń chwilę na przejrzenie kodu:

1. W programie Management Studio rozwiń Programowalność, rozwiń Funkcje, a następnie Funkcje wartości skalarnych.

2. Kliknij prawym przyciskiem myszy pozycję fnCalculateDistance i wybierz polecenie Modyfikuj , aby otworzyć skrypt Transact-SQL w nowym oknie zapytania.

   Powinna to wyglądać następująco:

   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
   

Uwagi:

• Funkcja jest funkcją skalarną zwracającą pojedynczą wartość danych wstępnie zdefiniowanego typu.
• Funkcja przyjmuje wartości szerokości i długości geograficznej jako danymi wejściowymi, uzyskanymi z lokalizacji miejsca odbioru i miejsca wysiadania. Formuła Haversine konwertuje lokalizacje na radiany i używa tych wartości do obliczenia bezpośredniej odległości w milach między tymi dwiema lokalizacjami.

Zapisywanie funkcji przy użyciu narzędzia fnEngineerFeatures

Aby dodać obliczoną wartość do tabeli, która może służyć do trenowania modelu, użyjesz niestandardowej funkcji T-SQL fnEngineerFeatures. Ta funkcja jest funkcją z wartością tabeli, która przyjmuje wiele kolumn jako danych wejściowych i generuje tabelę z wieloma kolumnami funkcji. Celem tej funkcji jest utworzenie zestawu funkcji do użycia w tworzeniu modelu. Funkcja fnEngineerFeatures wywołuje wcześniej utworzoną funkcję T-SQL, fnCalculateDistance, aby uzyskać bezpośrednią odległość między lokalizacjami odbioru i dropoff.

Pośmiń chwilę na przejrzenie kodu:

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

Aby sprawdzić, czy ta funkcja działa, można użyć jej do obliczenia odległości geograficznej dla tych podróży, w których mierzona odległość wynosiła 0, ale lokalizacje odbioru i drop-off były inne.

    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

Jak widać, odległość zgłoszona przez miernik nie zawsze odpowiada odległości geograficznej. Dlatego inżynieria cech jest ważna.

W następnej części dowiesz się, jak używać tych funkcji danych do tworzenia i trenowania modelu uczenia maszynowego przy użyciu języka Python.

Dalsze kroki

W tym artykule dowiesz się:

• Zmodyfikowano funkcję niestandardową w celu obliczenia odległości podróży
• Zapisano funkcje przy użyciu innej funkcji niestandardowej

Samouczek dla języka Python: trenowanie i zapisywanie modelu w języku Python przy użyciu języka T-SQL