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

文章
05/04/2023

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

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

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

在本文中，您將：

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

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

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

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

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

定義函數

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

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

使用 fnCalculateDistance 計算車程距離

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

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

以滑鼠右鍵按一下 [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 建立和定型機器學習模型。

後續步驟

在本文章中，您將：

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

Python 教學課程：使用 T-SQL 定型及儲存 Python 模型

分享方式：

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

定義函數

使用 fnCalculateDistance 計算車程距離

使用 fnEngineerFeatures 儲存特徵

後續步驟

意見反映

更多資源