チュートリアルのはじめから
前のステップ
次のステップ
Lesson 5: T-SQLを使用したモデルのトレーニングと保存
Lesson 4: T-SQLを使用したデータの特徴抽出
このステップでは、Transact-SQL関数を使用して生データから特徴抽出を行う方法を学習します。
特徴抽出について
データの探索後、データからいくつかの洞察を収集し特徴エンジニアリングに移ります。生データから特徴抽出を行うプロセスは、高度な分析モデリングの重要なステップです。
元データに記録されたメーター距離値は地理的距離または移動距離を表すものになっていない場合があるため、このデータセットで利用可能な座標を使用して乗車位置と降車位置の間の直接距離を計算します。これを行うためにカスタムTransact-SQL関数でHaversine式を使用します。
T-SQL関数fnCalculateDistance
はHaversine式を使用して距離を計算し、T-SQL関数fnEngineerFeatures
はすべての特徴を含むデータセットを作成します。
関数fnCalculateDistanceを使用して移動距離を計算する
T-SQL関数fnCalculateDistance
はLesson 2: PowerShellを使用したSQL Serverへのデータインポートを通じてSQL Serverに定義されています。
-
Management Studioのオブジェクトエクスプローラで、[プログラミング]、[関数]、[スカラー値関数]の順に展開します。
-
fnCalculateDistance
を右クリックし、[変更] を選択して新しいクエリウィンドウでTransact-SQLスクリプトを開きます。T-SQLCREATE 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式は、位置をラジアンに変換し、これらの値を使用して、2つの場所の間の直接距離を計算します。
関数fnEngineerFeaturesを使用して特徴値を保存する
T-SQL関数fnEngineerFeatures
はLesson 2: PowerShellを使用したSQL Serverへのデータインポートを通じてSQL Serverに定義されています。
fnEngineerFeatures
は複数の列を入力として使用し複数の特徴値列を返すテーブル値関数で、複数の列を入力に複数の特徴値の列を含むデータセットを出力します。fnEngineerFeatures
の目的は、モデル構築に使用する特徴値列(説明変数)を作成することです。fnEngineerFeatures
は乗車位置と降車位置の間の直線距離を得るためにfnCalculateDistance
を呼び出します。
-
Management Studioのオブジェクトエクスプローラで、[プログラミング]、[関数]、[テーブル値関数]の順に展開します。
-
fnEngineerFeatures
を右クリックし、[変更] を選択して新しいクエリウィンドウでTransact-SQLスクリプトを開きます。T-SQLCREATE 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に設定された記録に対して地理的距離を計算してみます。
T-SQLSELECT 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を使用して機械学習モデルを作成し、トレーニングする方法を学習します。
リンク
次のステップ
Lesson 5: T-SQLを使用したモデルのトレーニングと保存
前のステップ
チュートリアルのはじめから
出典
Lesson 4: Create data features using T-SQL