R 教學課程：建立資料特徵

  • 發行項

適用於： SQL Server 2016 (13.x) 和更新版本 Azure SQL 受控執行個體

本教學課程系列共有五個部分，在第三部分中，您將學習如何使用 Transact-SQL 函數，從未經處理的資料建立功能。 接著，您將從 SQL 預存程序呼叫該函式，以建立包含特徵值的資料表。

在本文中，您將：

  • 修改自訂函式以計算路程距離
  • 使用另一個自訂函式儲存特徵

在第一部分中，您已安裝必要條件並還原範例資料庫。

在第二部分中，您已檢閱範例資料並產生一些繪圖。

在第四部分中，您將載入模組，並呼叫所需的函式，以使用 SQL Server 預存程序來建立和定型模型。

在第五部分中，您將了解如何運作您在第四部分中定型並儲存的模型。

在第五部分中，您將了解如何運作您在第四部分中定型並儲存的模型。

關於特徵工程

瀏覽資料幾回合之後，您已從資料收集一些深入資訊，並準備好繼續進行「特徵工程」。 這個程序從未經處理資料建立有意義特徵，是建立分析模型的重要步驟。

在此資料集中，距離值是以報告的計量表距離為基礎，不一定代表地理距離或實際的行駛距離。 因此，您必須使用來源紐約市計程車資料集中可用的座標，來計算上車和下車點之間的直線距離。 您可以使用自訂 Transact-SQL 函數中的 Haversine 公式執行此作業。

您將會使用一個自訂 T-SQL 函數 fnCalculateDistance，透過 Haversine 公式來計算距離，並使用第二個自訂 T-SQL 函數 fnEngineerFeatures，建立包含所有特徵的資料表。

整體程序如下：

  • 建立執行計算的 T-SQL 函數

  • 呼叫該函數來產生特徵資料

  • 將特徵資料儲存至資料表

使用 fnCalculateDistance 計算車程距離

準備本教學課程時，應先下載函數 fnCalculateDistance，並向 SQL Server 註冊。 請花幾分鐘檢閱此程式碼。

  1. 在 Management Studio 中，依序展開 [可程式性]、[函數] 和 [純量值函式]。

  2. 在 [fnCalculateDistance] 上按一下滑鼠右鍵，然後選取 [修改]，以在新的查詢視窗中，開啟 Transact-SQL 指令碼。

    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

    • 此函數是純量值函式，會傳回預先定義類型的單一資料值。

    • 它接受緯度和經度值作為輸入，並從車程的上車和下車位置取得這些值。 Haversine 公式會將這些位置轉換成弧度，然後使用這些值來計算這兩個位置之間的直線距離。

使用 fnEngineerFeatures 產生特徵

若要將計算值加入可用於定型模型的資料表，您將使用另一個函數 fnEngineerFeatures。 這個新函數會呼叫先前建立的 T-SQL 函數 fnCalculateDistance，以取得上車與下車位置之間的直線距離。

  1. 請花幾分鐘檢閱自訂 T-SQL 函數 fnEngineerFeatures的程式碼，在進行本逐步解說的準備工作時，應該已為您建立此函數。

    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

    • 此資料表值函數接受多個資料行作為輸入，然後輸出具有多個特徵資料行的資料表。

    • 此函數的目的是建立新特徵，以便用來建置模型。

  2. 若要確認此函數運作正常，可以使用它來計算這些車程的地理距離，其中的計量付費距離為 0，但上車和下車位置不同。

        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

    如您所見，計量表回報的距離不一定會對應到地理距離。 這就是特徵工程之所以很重要的原因。 您可以使用這些已改善的資料特徵，透過 R 來定型機器學習模型。

後續步驟

在本文章中，您將：

  • 已修改自訂函式以計算路程距離
  • 已使用另一個自訂函式儲存特徵

R 教學課程：定型和儲存模型