分享方式:


Python 教學課程:使用 T-SQL 建立資料特徵

適用於: SQL Server 2017 (14.x) 和更新版本 Azure SQL 受控執行個體

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

特徵工程的程序 (從未經處理資料建立特徵),會是建立進階分析模型的重要步驟。

在本文中,您將:

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

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

第二部分中,您已探索範例資料並產生一些繪圖。

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

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

定義函數

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

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

使用 fnCalculateDistance 計算車程距離

函式 fnCalculateDistance 包含在範例資料庫中。 請花幾分鐘檢閱此程式碼:

  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 儲存特徵

若要將計算值加入可用於定型模型的資料表,您將使用自訂 T-SQL 函式 fnEngineerFeatures。 此函數是資料表值函式,接受多個資料行作為輸入,然後輸出具有多個特徵資料行的資料表。 此函數的目的是建立特徵集,以用於建立模型。 fnEngineerFeatures 函數會呼叫先前建立的 T-SQL 函數 fnCalculateDistance,以取得上車與下車位置之間的直線距離。

請花幾分鐘檢閱此程式碼:

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

若要確認此函數運作正常,您可以使用它來計算這些車程的地理距離,其中的計量付費距離為 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

如您所見,計量表回報的距離不一定會對應到地理距離。 這就是特徵工程之所以重要的原因。

在下一個部分中,您將了解如何使用這些資料特徵,透過 Python 建立和定型機器學習模型。

後續步驟

在本文章中,您將:

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