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函數所傳回的值是浮點數，代表司機收到小費 (任何金額) 的機率。
批次評分 (預測清單)
較常見的案例是在批次模式中產生多個觀察的預測。 在此步驟中，讓我們來看看批次評分的運作方式。
先取得要使用的一小組輸入資料。 此查詢會建立「前 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 延伸模組。
意見反應
即將登場：在 2024 年，我們將逐步淘汰 GitHub 問題作為內容的意見反應機制，並將它取代為新的意見反應系統。 如需詳細資訊，請參閱：https://aka.ms/ContentUserFeedback。
提交並檢視相關的意見反應