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

文章
05/04/2023

適用於：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 函數所傳回的值是浮點數，代表司機收到小費 (任何金額) 的機率。

批次評分 (預測清單)

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

先取得要使用的一小組輸入資料。此查詢會建立「前 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

在 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

在變數中提供查詢文字並當作參數傳遞至預存程序：

-- 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 資料類型，或是驗證資料類型和資料長度。

建立預存程序 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

請以手動方式提供值來試試看。

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

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

結果指出，從這前 10 趟車程獲得小費的機率偏低 (零)，因為全部都是單一乘客且距離相對較短的車程。

結論

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

後續步驟

在本文章中，您將：

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

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

分享方式：

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

基本計分

批次評分 (預測清單)

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

結論

後續步驟

意見反映

更多資源