Python-självstudie: Skapa datafunktioner med T-SQL

Gäller för: SQL Server 2017 (14.x) och senare versioner Azure SQL Managed Instance

I del tre i den här självstudieserien i fem delar får du lära dig hur du skapar funktioner från rådata med hjälp av en Transact-SQL funktion. Sedan anropar du funktionen från en SQL-lagrad procedur för att skapa en tabell som innehåller funktionsvärdena.

Processen med funktionsutveckling, att skapa funktioner från rådata, kan vara ett viktigt steg i avancerad analysmodellering.

I den här artikeln ska du:

I del ett installerade du förhandskraven och återställde exempeldatabasen.

I del två utforskade du exempeldata och genererade några diagram.

I del fyra läser du in modulerna och anropar nödvändiga funktioner för att skapa och träna modellen med hjälp av en lagrad SQL Server-procedur.

I del fem får du lära dig hur du operationaliserar de modeller som du har tränat och sparat i del fyra.

Definiera funktionen

Avståndsvärdena som rapporteras i de ursprungliga data baseras på det rapporterade mätaravståndet och representerar inte nödvändigtvis geografiskt avstånd eller avstånd som färdas. Därför måste du beräkna det direkta avståndet mellan upphämtnings- och avlämningspunkterna med hjälp av koordinaterna som är tillgängliga i nyc taxi-källdatauppsättningen. Du kan göra detta med hjälp av Haversine-formeln i en anpassad Transact-SQL funktion.

Du använder en anpassad T-SQL-funktion, fnCalculateDistance, för att beräkna avståndet med hjälp av Haversine-formeln och använda en andra anpassad T-SQL-funktion, fnEngineerFeatures, för att skapa en tabell som innehåller alla funktioner.

Beräkna reseavstånd med fnCalculateDistance

Funktionen fnCalculateDistance ingår i exempeldatabasen. Det tar en minut att granska koden:

1. I Management Studio expanderar du Programmability, expanderar Functions och sedan Scalar-värderade funktioner.

2. Högerklicka på fnCalculateDistance och välj Ändra för att öppna skriptet Transact-SQL i ett nytt frågefönster.

   Den bör se ut ungefär så här:

   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
   

Anteckningar:

• Funktionen är en skalärvärdesfunktion som returnerar ett enda datavärde av en fördefinierad typ.
• Funktionen tar latitud- och longitudvärden som indata som hämtas från platser för upphämtning och avlämning av resor. Haversine-formeln konverterar platser till radianer och använder dessa värden för att beräkna det direkta avståndet i miles mellan dessa två platser.

Spara funktionerna med fnEngineerFeatures

Om du vill lägga till det beräknade värdet i en tabell som kan användas för att träna modellen använder du den anpassade T-SQL-funktionen fnEngineerFeatures. Den här funktionen är en tabellvärdesfunktion som tar flera kolumner som indata och matar ut en tabell med flera funktionskolumner. Syftet med den här funktionen är att skapa en funktionsuppsättning som ska användas för att skapa en modell. Funktionen fnEngineerFeatures anropar den tidigare skapade T-SQL-funktionen, fnCalculateDistance, för att få det direkta avståndet mellan upphämtnings- och avlämningsplatser.

Det tar en minut att granska koden:

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

För att kontrollera att den här funktionen fungerar kan du använda den för att beräkna det geografiska avståndet för de resor där det uppmätta avståndet var 0, men upphämtnings- och avlämningsplatserna var olika.

    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

Som du ser motsvarar avståndet som rapporteras av mätaren inte alltid det geografiska avståndet. Det är därför funktionsutveckling är viktigt.

I nästa del får du lära dig hur du använder dessa datafunktioner för att skapa och träna en maskininlärningsmodell med Python.

Nästa steg

I den här artikeln kommer du att: