【SQL Server 2017 による In-Database R 分析 チュートリアル】Lesson 4: T-SQLを使用したデータの特徴抽出

#### チュートリアルのはじめから
[SQL開発者のための In-Database R 分析](http://qiita.com/qio9o9/items/4f020bb93dc07567e556)

#### 前のステップ
[Lesson 3: データの探索と可視化](http://qiita.com/qio9o9/items/b0913b659cc256dffa85)

#### 次のステップ
[Lesson 5: T-SQLを使用したモデルのトレーニングと保存](http://qiita.com/qio9o9/items/5ddf586094fa5751a8ec)

# Lesson 4: T-SQLを使用したデータの特徴抽出

このステップでは、Transact-SQL関数を使用して生データから特徴抽出を行う方法を学習します。

## 特徴抽出について

データの探索後、データからいくつかの洞察を収集し特徴エンジニアリングに移ります。生データから特徴抽出を行うプロセスは、高度な分析モデリングの重要なステップです。

元データに記録されたメーター距離値は地理的距離または移動距離を表すものになっていない場合があるため、このデータセットで利用可能な座標を使用して乗車位置と降車位置の間の直接距離を計算します。これを行うためにカスタムTransact-SQL関数で[Haversine式](https://en.wikipedia.org/wiki/Haversine_formula)を使用します。

T-SQL関数`fnCalculateDistance`はHaversine式を使用して距離を計算し、T-SQL関数`fnEngineerFeatures`はすべての特徴を含むデータセットを作成します。

## 関数fnCalculateDistanceを使用して移動距離を計算する

T-SQL関数`fnCalculateDistance`は[Lesson 2: PowerShellを使用したSQL Serverへのデータインポート](http://qiita.com/qio9o9/items/c16ec818afd80fb7f232)を通じてSQL Serverに定義されています。

1. Management Studioのオブジェクトエクスプローラで、[プログラミング]、[関数]、[スカラー値関数]の順に展開します。

2. `fnCalculateDistance`を右クリックし、[変更] を選択して新しいクエリウィンドウでTransact-SQLスクリプトを開きます。

    ```SQL:T-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式は、位置をラジアンに変換し、これらの値を使用して、2つの場所の間の直接距離を計算します。


## 関数fnEngineerFeaturesを使用して特徴値を保存する

T-SQL関数`fnEngineerFeatures`は[Lesson 2: PowerShellを使用したSQL Serverへのデータインポート](http://qiita.com/qio9o9/items/c16ec818afd80fb7f232)を通じてSQL Serverに定義されています。

`fnEngineerFeatures`は複数の列を入力として使用し複数の特徴値列を返すテーブル値関数で、複数の列を入力に複数の特徴値の列を含むデータセットを出力します。`fnEngineerFeatures`の目的は、モデル構築に使用する特徴値列（説明変数）を作成することです。`fnEngineerFeatures`は乗車位置と降車位置の間の直線距離を得るために`fnCalculateDistance`を呼び出します。

1. Management Studioのオブジェクトエクスプローラで、[プログラミング]、[関数]、[テーブル値関数]の順に展開します。

2. `fnEngineerFeatures`を右クリックし、[変更] を選択して新しいクエリウィンドウでTransact-SQLスクリプトを開きます。

    ```SQL:T-SQL
    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
    ```

3. 動作確認として、乗車位置と降車位置の場所が異なる運転にもかかわらずメーター距離値が0に設定された記録に対して地理的距離を計算してみます。

    ```SQL:T-SQL
    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
    ```
    
    ![sqldev-r-step4-1-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/90bfa316-130a-9514-e071-de520266a179.png)
    
    この通りメーターによって報告された距離は、必ずしも地理的距離を示すものとして記録されているとは限りません。こうした前処理が特徴エンジニアリングが重要な理由です。

次のステップでは、これらの機能を使用して、Rを使用して機械学習モデルを作成し、トレーニングする方法を学習します。

## リンク

#### 次のステップ

[Lesson 5: T-SQLを使用したモデルのトレーニングと保存](http://qiita.com/qio9o9/items/5ddf586094fa5751a8ec)

#### 前のステップ

[Lesson 3: データの探索と可視化](http://qiita.com/qio9o9/items/b0913b659cc256dffa85)

#### チュートリアルのはじめから

[SQL開発者のための In-Database R 分析](http://qiita.com/qio9o9/items/4f020bb93dc07567e556)

#### 出典

[Lesson 4: Create data features using T-SQL](https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/sqldev-create-data-features-using-t-sql)

#### 関連項目

[Machine Learning Services with R](https://docs.microsoft.com/en-us/sql/advanced-analytics/r/sql-server-r-services)