分享方式:


R 教學課程:在 SQL 預存程序中執行預測

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

在這五部分教學課程系列的第五部分中,您將了解如何使用模型預測可能的結果,以運作您在上一個部分中定型並儲存的模型。 此模型會包裝在預存程序,可由其他應用程式直接呼叫。

本文示範執行評分的兩種方式:

  • 批次評分模式:使用 SELECT 查詢做為預存程序輸入。 此預存程序會傳回對應至輸入案例的觀察值資料表。

  • 個別計分模式︰傳遞一組個別參數值作為輸入。 此預存程序會傳回單一資料列或值。

在本文中,您將:

  • 建立和使用預存程序進行批次評分
  • 建立和使用預存程序進行單一資料列的評分

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

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

第三部分中,您已了解如何使用 Transact-SQL 函式,從未經處理的資料建立特徵。 接著,您從預存程序呼叫該函式,建立了包含特徵值的資料表。

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

基本計分

預存程序 RPredict 會說明將 PREDICT 呼叫包裝在預存程序中的基本語法。

CREATE PROCEDURE [dbo].[RPredict] (@model varchar(250), @inquery nvarchar(max))
AS 
BEGIN 

DECLARE @lmodel2 varbinary(max) = (SELECT model FROM nyc_taxi_models WHERE name = @model);  
EXEC sp_execute_external_script @language = N'R',
  @script = N' 
    mod <- unserialize(as.raw(model));
    print(summary(mod))
    OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));
    str(OutputDataSet)
    print(OutputDataSet)
    ',
  @input_data_1 = @inquery,
  @params = N'@model varbinary(max)',
  @model = @lmodel2 
  WITH RESULT SETS (("Score" float));
END
GO
  • 此 SELECT 陳述式可從資料庫取得序列化模型,並將模型儲存在 R 變數 mod 中,以使用 R 進一步處理。

  • 您可以從預存程序的第一個參數 @inquery 中指定的 Transact-SQL 查詢,取得計分的新案例。 讀取查詢資料之後,這些資料列會儲存在預設資料框架 InputDataSet中。 此資料框架會傳遞至產生分數的 PREDICT 函數。

    OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));

    因為 data.frame 可以包含單一資料列,所以您可以使用相同的程式碼進行批次或單一計分。

  • PREDICT 函數所傳回的值是浮點數,代表司機收到小費 (任何金額) 的機率。

批次評分 (預測清單)

較常見的案例是在批次模式中產生多個觀察的預測。 在此步驟中,讓我們來看看批次評分的運作方式。

  1. 先取得要使用的一小組輸入資料。 此查詢會建立「前 10 趟」車程的清單,其中包含乘客計數及進行預測所需的其他特徵。

    SELECT TOP 10 a.passenger_count AS passenger_count, a.trip_time_in_secs AS trip_time_in_secs, a.trip_distance AS trip_distance, a.dropoff_datetime AS dropoff_datetime, dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude,dropoff_longitude) AS direct_distance
    
    FROM (SELECT medallion, hack_license, pickup_datetime, passenger_count,trip_time_in_secs,trip_distance, dropoff_datetime, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude FROM nyctaxi_sample)a
    
    LEFT OUTER JOIN
    
    (SELECT medallion, hack_license, pickup_datetime FROM nyctaxi_sample TABLESAMPLE (70 percent) REPEATABLE (98052)    )b
    
    ON a.medallion=b.medallion AND a.hack_license=b.hack_license 
    AND a.pickup_datetime=b.pickup_datetime
    WHERE b.medallion IS NULL
    

    範例結果

    passenger_count   trip_time_in_secs    trip_distance  dropoff_datetime          direct_distance
    1                 283                  0.7            2013-03-27 14:54:50.000   0.5427964547
    1                 289                  0.7            2013-02-24 12:55:29.000   0.3797099614
    1                 214                  0.7            2013-06-26 13:28:10.000   0.6970098661
    
  2. 在 Management Studio 中建立名為 RPredictBatchOutput 的預存程序。

    CREATE PROCEDURE [dbo].[RPredictBatchOutput] (@model varchar(250), @inquery nvarchar(max))
    AS
    BEGIN
    DECLARE @lmodel2 varbinary(max) = (SELECT model FROM nyc_taxi_models WHERE name = @model);
    EXEC sp_execute_external_script 
      @language = N'R',
      @script = N'
        mod <- unserialize(as.raw(model));
        print(summary(mod))
        OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));
        str(OutputDataSet)
        print(OutputDataSet)
      ',
      @input_data_1 = @inquery,
      @params = N'@model varbinary(max)',
      @model = @lmodel2
      WITH RESULT SETS ((Score float));
    END
    
  3. 在變數中提供查詢文字並當作參數傳遞至預存程序:

    -- Define the input data
    DECLARE @query_string nvarchar(max)
    SET @query_string='SELECT TOP 10 a.passenger_count as passenger_count, a.trip_time_in_secs AS trip_time_in_secs, a.trip_distance AS trip_distance, a.dropoff_datetime AS dropoff_datetime, dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude,dropoff_longitude) AS direct_distance FROM  (SELECT medallion, hack_license, pickup_datetime, passenger_count,trip_time_in_secs,trip_distance, dropoff_datetime, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude FROM nyctaxi_sample  )a   LEFT OUTER JOIN (SELECT medallion, hack_license, pickup_datetime FROM nyctaxi_sample TABLESAMPLE (70 percent) REPEATABLE (98052))b ON a.medallion=b.medallion AND a.hack_license=b.hack_license AND a.pickup_datetime=b.pickup_datetime WHERE b.medallion is null'
    
    -- Call the stored procedure for scoring and pass the input data
    EXEC [dbo].[RPredictBatchOutput] @model = 'RTrainLogit_model', @inquery = @query_string;
    

此預存程序會傳回一系列的值,代表前 10 趟車程的每趟車程預測。 不過,前幾趟車程也是以相對較短的車程距離進行的單趟乘客車程,司機不太可能會收到小費。

提示

除了只傳回「有小費」與「無小費」的結果,您也可以傳回預測的機率分數,然後將 WHERE 子句套用至 [分數] 資料行值,使用 0.5 或 0.7 等臨界值,將分數分類為「可能給小費」和「不可能給小費」。 雖然此步驟未包含在預存程序中,但實作起來並不難。

多個輸入的單一資料列評分

有時候您會想要傳入多個輸入值,並根據這些值取得單一預測。 例如,您可以設定 Excel 工作表、Web 應用程式或 Reporting Services 報表來呼叫預存程序,並且從這些應用程式提供使用者鍵入或選取的輸入。

在本節中,您將瞭解如何使用接受多個輸入 (例如乘客計數、路程距離等等) 的預存程序建立單一預測。 此預存程序會根據先前儲存的 R 模型建立分數。

如果您從外部應用程式呼叫預存程序,請確定資料符合 R 模型的需求。 這可能包括確保輸入資料可轉型或轉換成 R 資料類型,或是驗證資料類型和資料長度。

  1. 建立預存程序 RPredictSingleRow

    CREATE PROCEDURE [dbo].[RPredictSingleRow] @model varchar(50), @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
    AS
    BEGIN
    DECLARE @inquery nvarchar(max) = N'SELECT * FROM [dbo].[fnEngineerFeatures](@passenger_count, @trip_distance, @trip_time_in_secs,  @pickup_latitude, @pickup_longitude, @dropoff_latitude, @dropoff_longitude)';
    DECLARE @lmodel2 varbinary(max) = (SELECT model FROM nyc_taxi_models WHERE name = @model);
    EXEC sp_execute_external_script  
      @language = N'R',
      @script = N'  
        mod <- unserialize(as.raw(model));  
        print(summary(mod));  
        OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));
        str(OutputDataSet);
        print(OutputDataSet); 
        ',  
      @input_data_1 = @inquery,  
      @params = N'@model varbinary(max),@passenger_count int,@trip_distance float,@trip_time_in_secs int ,  @pickup_latitude float ,@pickup_longitude float ,@dropoff_latitude float ,@dropoff_longitude float', @model = @lmodel2, @passenger_count =@passenger_count, @trip_distance=@trip_distance, @trip_time_in_secs=@trip_time_in_secs, @pickup_latitude=@pickup_latitude, @pickup_longitude=@pickup_longitude, @dropoff_latitude=@dropoff_latitude, @dropoff_longitude=@dropoff_longitude  
      WITH RESULT SETS ((Score float));  
    END
    
  2. 請以手動方式提供值來試試看。

    開啟新的 [查詢] 視窗,然後呼叫預存程序,並針對每個參數提供值。 這些參數代表模型所使用的功能資料行,而且是必要的。

    EXEC [dbo].[RPredictSingleRow] @model = 'RTrainLogit_model',
    @passenger_count = 1,
    @trip_distance = 2.5,
    @trip_time_in_secs = 631,
    @pickup_latitude = 40.763958,
    @pickup_longitude = -73.973373,
    @dropoff_latitude =  40.782139,
    @dropoff_longitude = -73.977303
    

    或者,對於參數至預存程序使用下列這種較短的形式:

    EXEC [dbo].[RPredictSingleRow] 'RTrainLogit_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303
    
  3. 結果指出,從這前 10 趟車程獲得小費的機率偏低 (零),因為全部都是單一乘客且距離相對較短的車程。

結論

您已瞭解如何在預存程序中內嵌 R 程式碼,您可以擴充這些實務來建立自己的模型。 與 Transact-SQL 的整合可讓您更輕鬆地部署 R 模型進行預測,並納入模型重新定型作為企業資料工作流程的一部分。

後續步驟

在本文章中,您將:

  • 已建立和使用預存程序進行批次評分
  • 已建立和使用預存程序進行單一資料列的評分

如需 R 的詳細資訊,請參閱 SQL Server中的 R 延伸模組