sp_rxPredictsp_rxPredict

適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本 - 僅限 Windows適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later - Windows only

產生指定輸入的預測值,包含以二進位格式儲存在 SQL Server 資料庫中的機器學習模型。Generates a predicted value for a given input consisting of a machine learning model stored in a binary format in a SQL Server database.

以近乎即時的方式提供 R 和 Python 機器學習模型的評分。Provides scoring on R and Python machine learning models in near real-time. sp_rxPredict是在 RevoScaleR 和 MicrosoftML 中提供作為 R 函式包裝函式的預存程式 rxPredict ,以及revoscalepyMicrosoftML中的rx_predict Python 函數。 sp_rxPredict is a stored procedure provided as a wrapper for the rxPredict R function in RevoScaleR and MicrosoftML, and the rx_predict Python function in revoscalepy and microsoftml. 它是以 c + + 撰寫,專門針對計分作業進行優化。It is written in C++ and is optimized specifically for scoring operations.

雖然模型必須使用 R 或 Python 來建立,但在目標資料庫引擎實例上序列化並儲存為二進位格式後,即使未安裝 R 或 Python 整合,也可以從該資料庫引擎實例取用。Although the model must be created using R or Python, once it is serialized and stored in a binary format on a target database engine instance, it can be consumed from that database engine instance even when R or Python integration is not installed. 如需詳細資訊,請參閱 使用 sp_rxPredict 的即時評分For more information, see Real-time scoring with sp_rxPredict.

語法Syntax

sp_rxPredict  ( @model, @input )

引數Arguments

modelmodel

支援格式的預先定型模型。A pretrained model in a supported format.

inputinput

有效的 SQL 查詢A valid SQL query

傳回值Return values

會傳回分數資料行,以及輸入資料來源中的任何傳遞資料行。A score column is returned, as well as any pass-through columns from the input data source. 如果演算法支援產生這類值,則可以傳回其他分數資料行,例如信賴區間。Additional score columns, such as confidence interval, can be returned if the algorithm supports generation of such values.

備註Remarks

若要允許使用預存程式,必須在實例上啟用 SQLCLR。To enable use of the stored procedure, SQLCLR must be enabled on the instance.

注意

啟用此選項有安全性含意。There are security implications to enabling this option. 如果無法在您的伺服器上啟用 SQLCLR,請使用替代的執行,例如 TRANSACT-SQL PREDICT 函數。Use an alternative implementation, such as the Transact-SQL PREDICT function, if SQLCLR cannot be enabled on your server.

使用者需要 EXECUTE 資料庫的許可權。The user needs EXECUTE permission on the database.

支援的演算法Supported algorithms

若要建立和定型模型,請使用其中一個支援的 R 或 Python 演算法(由 SQL Server Machine Learning Services (r 或 python) SQL Server 2016 R ServicesSQL Server Machine Learning Server (獨立) (R 或 Python) ,或 SQL Server 2016 R Server (獨立) To create and train model, use one of the supported algorithms for R or Python, provided by SQL Server Machine Learning Services (R or Python), SQL Server 2016 R Services, SQL Server Machine Learning Server (Standalone) (R or Python), or SQL Server 2016 R Server (Standalone).

R: RevoScaleR 模型R: RevoScaleR models

R: MicrosoftML 模型R: MicrosoftML models

R: MicrosoftML 提供的轉換R: Transformations supplied by MicrosoftML

Python: revoscalepy 模型Python: revoscalepy models

Python: microsoftml 模型Python: microsoftml models

Python: microsoftml 提供的轉換Python: Transformations supplied by microsoftml

不支援的模型類型Unsupported model types

不支援下列模型類型:The following model types are not supported:

  • 使用 rxGlm RevoScaleR 中的或演算法的模型 rxNaiveBayesModels using the rxGlm or rxNaiveBayes algorithms in RevoScaleR
  • 在 R 中 PMML 模型PMML models in R
  • 使用其他協力廠商程式庫建立的模型Models created using other third-party libraries

範例Examples

DECLARE @model = SELECT @model 
FROM model_table 
WHERE model_name = 'rxLogit trained';

EXEC sp_rxPredict @model = @model,
@inputData = N'SELECT * FROM data';

除了成為有效的 SQL 查詢以外, @ inputData 中的輸入資料還必須包含與預存模型中的資料行相容的資料行。In addition to being a valid SQL query, the input data in @inputData must include columns compatible with the columns in the stored model.

sp_rxPredict 僅支援下列 .NET 資料行類型: double、float、short、ushort、long、ulong 和 string。sp_rxPredict supports only the following .NET column types: double, float, short, ushort, long, ulong and string. 您可能需要在輸入資料中篩選出不受支援的類型,才能使用它來進行即時評分。You may need to filter out unsupported types in your input data before using it for real-time scoring.

如需相對應 SQL 類型的詳細資訊,請參閱 SQL-CLR 類型對應對應 CLR 參數資料For information about corresponding SQL types, see SQL-CLR Type Mapping or Mapping CLR Parameter Data.