Python-zelfstudie: Gegevensfuncties maken met T-SQL

Van toepassing op: SQL Server 2017 (14.x) en latere versies van Azure SQL Managed Instance

In deel drie van deze vijfdelige reeks zelfstudies leert u hoe u functies maakt op basis van onbewerkte gegevens met behulp van een Transact-SQL-functie. Vervolgens roept u die functie aan vanuit een opGESLAGEN SQL-procedure om een tabel te maken die de functiewaarden bevat.

Het proces van functie-engineering, het maken van functies op basis van de onbewerkte gegevens, kan een kritieke stap zijn in geavanceerde analysemodellering.

In dit artikel gaat u het volgende doen:

In deel 1 hebt u de vereisten geïnstalleerd en de voorbeelddatabase hersteld.

In deel twee hebt u de voorbeeldgegevens verkend en enkele plots gegenereerd.

In deel vier laadt u de modules en roept u de benodigde functies aan om het model te maken en te trainen met behulp van een opgeslagen SQL Server-procedure.

In deel vijf leert u hoe u de modellen kunt operationeel maken die u hebt getraind en opgeslagen in deel vier.

De functie definiëren

De afstandswaarden die in de oorspronkelijke gegevens zijn gerapporteerd, zijn gebaseerd op de gerapporteerde meterafstand en vertegenwoordigen niet noodzakelijkerwijs geografische afstand of reisafstand. Daarom moet u de directe afstand tussen de ophaal- en afleverpunten berekenen met behulp van de coördinaten die beschikbaar zijn in de brongegevensset NYC Taxi. U kunt dit doen met behulp van de Haversine-formule in een aangepaste Transact-SQL functie.

U gebruikt één aangepaste T-SQL-functie, fnCalculateDistance, om de afstand te berekenen met behulp van de Haversine-formule en een tweede aangepaste T-SQL-functie, fnEngineerFeatures, te gebruiken om een tabel te maken met alle functies.

Reisafstand berekenen met fnCalculateDistance

De functie fnCalculateDistance is opgenomen in de voorbeelddatabase. Neem even de tijd om de code te controleren:

1. Vouw in Management Studio Programmability uit, vouw vervolgens Functies en daarna Scalaire-waarden functies uit.

2. Klik met de rechtermuisknop op fnCalculateDistance en selecteer Wijzigen om het Transact-SQL script te openen in een nieuw queryvenster.

   Het ziet er ongeveer als volgt uit:

   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
   

Notes:

• De functie is een scalar-gewaardeerde functie die één gegevenswaarde van een vooraf gedefinieerd type retourneert.
• De functie neemt waarden voor breedtegraad en lengtegraad op als invoer, verkregen van de ophaal- en afgiftelocaties van de rit. De Haversine-formule converteert locaties naar radialen en gebruikt deze waarden om de directe afstand tussen deze twee locaties te berekenen.

De functies opslaan met fnEngineerFeatures

Als u de berekende waarde wilt toevoegen aan een tabel die kan worden gebruikt voor het trainen van het model, gebruikt u de aangepaste T-SQL-functie fnEngineerFeatures. Deze functie is een tabelwaardefunctie die meerdere kolommen als invoer gebruikt en een tabel met meerdere functiekolommen uitvoert. Het doel van deze functie is het maken van een functieset voor gebruik bij het bouwen van een model. De functie fnEngineerFeatures roept de eerder gemaakte T-SQL-functie fnCalculateDistance aan om de directe afstand tussen ophaal- en afgiftelocaties op te halen.

Neem even de tijd om de code te controleren:

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

Als u wilt controleren of deze functie werkt, kunt u deze gebruiken om de geografische afstand voor die reizen te berekenen waarbij de afstand naar gebruik 0 was, maar de ophaal- en afleverlocaties verschillend 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

Zoals u kunt zien, komt de door de meter gerapporteerde afstand niet altijd overeen met geografische afstand. Daarom is functie-engineering belangrijk.

In het volgende deel leert u hoe u deze gegevensfuncties gebruikt om een machine learning-model te maken en te trainen met behulp van Python.

Volgende stappen

In dit artikel gaat u als volgt te werk: