R-zelfstudie: Gegevensfuncties maken

Van toepassing op: SQL Server 2016 (13.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.

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 gecontroleerd 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.

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

Over functie-engineering

Na verschillende gegevensverkenningsrondes hebt u enkele inzichten uit de gegevens verzameld en bent u klaar om door te gaan met functie-engineering. Dit proces voor het maken van zinvolle functies op basis van de onbewerkte gegevens is een kritieke stap bij het maken van analytische modellen.

In deze gegevensset zijn de afstandswaarden gebaseerd op de gerapporteerde meterafstand en vertegenwoordigen ze niet noodzakelijkerwijs geografische afstand of de werkelijke 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.

Het algehele proces is als volgt:

• De T-SQL-functie maken waarmee de berekeningen worden uitgevoerd

• De functie aanroepen om de functiegegevens te genereren

• De functiegegevens opslaan in een tabel

Reisafstand berekenen met fnCalculateDistance

De functie fnCalculateDistance moet zijn gedownload en geregistreerd bij SQL Server als onderdeel van de voorbereiding voor deze zelfstudie. 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.

   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
   

   • De functie is een scalar-gewaardeerde functie die één gegevenswaarde van een vooraf gedefinieerd type retourneert.

   • De waarden voor breedtegraad en lengtegraad worden als invoer gebruikt, 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 genereren met fnEngineerFeatures

Als u de berekende waarden wilt toevoegen aan een tabel die kan worden gebruikt voor het trainen van het model, gebruikt u een andere functie, fnEngineerFeatures. Met de nieuwe functie wordt de eerder gemaakte T-SQL-functie fnCalculateDistance aangeroepen om de directe afstand tussen ophaal- en afgiftelocaties op te halen.

1. Neem een minuut de tijd om de code voor de aangepaste T-SQL-functie fnEngineerFeatures te controleren. Deze moet voor u zijn gemaakt als onderdeel van de voorbereiding voor deze procedure.

   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
   

   • Deze tabelwaardefunctie die meerdere kolommen als invoer gebruikt en een tabel met meerdere functiekolommen uitvoert.

   • Het doel van deze functie is het maken van nieuwe functies voor het bouwen van een model.

2. Als u wilt controleren of deze functie werkt, gebruikt u deze om de geografische afstand te berekenen voor die reizen waarbij de meterafstand 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 zo belangrijk. U kunt deze verbeterde gegevensfuncties gebruiken om een machine learning-model te trainen met R.

Volgende stappen

In dit artikel gaat u als volgt te werk: