Kurz Pythonu: Vytváření datových funkcí pomocí T-SQL

Platí pro: SQL Server 2017 (14.x) a novější verze Azure SQL Managed Instance

Ve třetí části této pětidílné série kurzů se naučíte vytvářet funkce z nezpracovaných dat pomocí funkce Transact-SQL. Potom tuto funkci zavoláte z uložené procedury SQL a vytvoříte tabulku obsahující hodnoty funkcí.

Proces přípravy funkcí, vytváření funkcí z nezpracovaných dat, může být kritickým krokem při modelování pokročilých analýz.

V tomto článku:

• Úprava vlastní funkce pro výpočet vzdálenosti jízdy
• Uložení funkcí pomocí jiné vlastní funkce

V první části jste nainstalovali požadavky a obnovili ukázkovou databázi.

Ve druhé části jste prozkoumali ukázková data a vygenerovali několik grafů.

Ve čtvrté části načtete moduly a zavoláte potřebné funkce k vytvoření a trénování modelu pomocí uložené procedury SQL Serveru.

V 5. části se dozvíte, jak zprovoznit modely, které jste natrénovali a uložili ve čtvrté části.

Definování funkce

Hodnoty vzdálenosti hlášené v původních datech jsou založeny na hlášené vzdálenosti měřiče a nemusí nutně představovat zeměpisnou vzdálenost nebo vzdálenost cestování. Proto budete muset vypočítat přímou vzdálenost mezi vyzvednutím a odkládacími body pomocí souřadnic dostupných ve zdrojové datové sadě taxi NYC. Můžete to provést pomocí vzorce Haversine ve vlastní funkci Transact-SQL.

K výpočtu vzdálenosti pomocí vzorce Haversine použijete jednu vlastní funkci T-SQL fnCalculateDistance a k vytvoření tabulky obsahující všechny funkce použijete druhou vlastní funkci T-SQL fnEngineerFeatures.

Výpočet vzdálenosti jízdy pomocí funkce fnCalculateDistance

Funkce fnCalculateDistance je součástí ukázkové databáze. Přečtěte si kód za minutu:

1. V aplikaci Management Studio rozbalte možnost Programovatelnost, rozbalte funkce a potom funkce vracející skalární hodnoty.

2. Klikněte pravým tlačítkem na fnCalculateDistance a výběrem možnosti Upravit otevřete skript Transact-SQL v novém okně dotazu.

   Měla by vypadat přibližně takto:

   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
   

Poznámky:

• Funkce je skalární funkce, která vrací jednu datovou hodnotu předdefinovaného typu.
• Funkce přebírá hodnoty zeměpisné šířky a délky jako vstupní hodnoty získané z míst vyzvednutí a odložení. Vzorec Haversine převede umístění na radiány a pomocí těchto hodnot vypočítá přímou vzdálenost mezi těmito dvěma umístěními.

Uložení funkcí pomocí fnEngineerFeatures

K přidání vypočítané hodnoty do tabulky, která se dá použít k trénování modelu, použijete vlastní funkci T-SQL fnEngineerFeatures. Tato funkce je funkce s hodnotou tabulky, která přebírá více sloupců jako vstupy a vypíše tabulku s více sloupci funkcí. Účelem této funkce je vytvořit sadu funkcí pro použití při vytváření modelu. Funkce fnEngineerFeatures volá dříve vytvořenou funkci T-SQL fnCalculateDistance, aby zjistila přímou vzdálenost mezi místy vyzvednutí a místa vyložení.

Přečtěte si kód za minutu:

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

Pokud chcete ověřit, že tato funkce funguje, můžete ji použít k výpočtu zeměpisné vzdálenosti pro tyto výlety, kde byla měřená vzdálenost 0, ale umístění vyzvednutí a odvozu se liší.

    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 vidíte, vzdálenost hlášená měřičem vždy neodpovídá zeměpisné vzdálenosti. To je důvod, proč je inženýrství vlastností důležité.

V další části se dozvíte, jak pomocí těchto datových funkcí vytvořit a vytrénovat model strojového učení pomocí Pythonu.

Další kroky

V tomto článku:

• Úprava vlastní funkce pro výpočet vzdálenosti jízdy
• Uložili jsme funkce pomocí jiné vlastní funkce.

Kurz Pythonu: Trénování a uložení modelu Pythonu pomocí T-SQL