R öğreticisi: SQL saklı yordamlarında tahminleri çalıştırma

Şunlar için geçerlidir: SQL Server 2016 (13.x) ve sonraki sürümleri Azure SQL Yönetilen Örnek

Beş bölümlük bu öğretici serisinin beşinci bölümünde, önceki bölümde eğitip kaydettiğiniz modeli, olası sonuçları tahmin ederek operasyonelleştirmeyi öğreneceksiniz. Model, diğer uygulamalar tarafından doğrudan çağrılabilen bir saklı yordam içine sarmalanır.

Bu makalede puanlama gerçekleştirmenin iki yolu gösterilmektedir:

Toplu puanlama modu: SELECT sorgusunu saklı prosedüre giriş amacıyla kullanın. Saklı yordam, giriş durumlarıyla ilgili gözlemlerden oluşan bir tablo döndürür.
Tek tek puanlama modu: Giriş olarak tek bir parametre değerleri kümesi geçirin. Saklı yordam tek bir satır veya değer döndürür.

Bu makalede şunları yapacaksınız:

Toplu puanlama için saklı yordamlar oluşturma ve kullanma
Tek bir satır puanlama için saklı yordamlar oluşturma ve kullanma

Birinci bölümde önkoşulları yüklemiş ve örnek veritabanını geri yüklemişsinizdir.

İkinci bölümde, örnek verileri gözden geçirdiniz ve bazı çizimler oluşturacaksınız.

Üçüncü bölümde, bir Transact-SQL işlevi kullanarak ham verilerden özellik oluşturmayı öğrendinsiniz. Ardından bu işlevi saklı yordamdan çağırarak özellik değerlerini içeren bir tablo oluşturacaksınız.

Dördüncü bölümde, modülleri yüklemiş ve SQL Server saklı yordamını kullanarak modeli oluşturmak ve eğitmek için gerekli işlevleri çağırmıştınız.

Temel puanlama

RPredict saklı yordamı, bir PREDICT çağrıyı saklı yordamda sarmalamanın temel söz dizimini gösterir.

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 deyimi veritabanından seri hale getirilmiş modeli alır ve R kullanarak daha fazla işlem için modeli R değişkeninde mod depolar.
Puanlama için yeni durumlar, saklı yordamın ilk parametresinde @inquerybelirtilen Transact-SQL sorgusundan alınır. Sorgu verileri okundukça, satırlar varsayılan veri çerçevesine InputDataSetkaydedilir. Bu veri çerçevesi, puanları oluşturan PREDICT işlevine geçirilir.

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

Data.frame tek bir satır içerebileceğinden, toplu işlem veya tek puanlama için aynı kodu kullanabilirsiniz.
İşlev tarafından PREDICT döndürülen değer, sürücünün herhangi bir miktarda bahşiş alma olasılığını temsil eden bir float değeridir.

Toplu puanlama (tahmin listesi)

Daha yaygın bir senaryo, toplu iş modunda birden çok gözlem için tahminler oluşturmaktır. Bu adımda toplu puanlamanın nasıl çalıştığını görelim.

Çalışmak için daha küçük bir giriş verileri kümesi alarak başlayın. Bu sorgu, yolcu sayısı ve tahminde bulunmak için gereken diğer özellikleri içeren "ilk 10" yolculuk listesini oluşturur.

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

Örnek sonuçlar

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'da RPredictBatchOutput adlı bir saklı yordam oluşturun.

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

Bir değişkende sorgu metnini sağlayın ve saklı yordama parametre olarak geçirin:

-- 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;

Saklı yordam, ilk 10 seyahatin her biri için tahmini temsil eden bir dizi değerler döndürür. Ancak, en iyi seyahatler ayrıca sürücünün ipucu alma olasılığının düşük olduğu nispeten kısa bir seyahat mesafesine sahip tek yolculu yolculuklardır.

Tavsiye

Yalnızca "yes-tip" ve "no-tip" sonuçlarını döndürmek yerine, tahmin için olasılık puanını döndürebilir ve ardından 0,5 veya 0,7 gibi bir eşik değeri kullanarak puanı "ipucu verme olasılığı" veya "ipucu olasılığı düşük" olarak kategorilere ayırmak için Score sütun değerlerine WHERE yan tümcesi uygulayabilirsiniz. Bu adım saklı yordama dahil değildir, ancak uygulanması kolay olacaktır.

Birden çok girişin tek satırlı puanlaması

Bazen birden çok giriş değeri geçirmek ve bu değerlere göre tek bir tahmin almak istersiniz. Örneğin, saklı yordamı çağırmak ve bu uygulamalardan kullanıcılar tarafından yazılan veya seçilen girişleri sağlamak için bir Excel çalışma sayfası, web uygulaması veya Reporting Services raporu ayarlayabilirsiniz.

Bu bölümde, yolcu sayısı, seyahat mesafesi vb. birden çok girdi alan bir saklı yordam kullanarak tek bir tahmin oluşturmayı öğreneceksiniz. Veritabanında saklı yordam, daha önce depolanmış R modeline dayanarak bir puan oluşturur.

Saklı yordamı bir dış uygulamadan çağırırsanız, verilerin R modelinin gereksinimleriyle eşleştiğinden emin olun. Bu, giriş verilerinin bir R veri türüne dönüştürülebileceğinden emin olmak veya veri türünü ve veri uzunluğunu doğrulamayı içerebilir.

RPredictSingleRow saklı yordamı oluşturun.

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

Değerleri el ile sağlayarak deneyin.

Yeni bir Sorgu penceresi açın ve saklı yordamı çağırarak parametrelerin her biri için değerler sağlayın. Parametreler, model tarafından kullanılan özellik sütunlarını temsil eder ve gereklidir.

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

Alternatif olarak, saklı yordama yönelik parametreler için desteklenen bu kısa formu kullanın:

EXEC [dbo].[RPredictSingleRow] 'RTrainLogit_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

Sonuçlar, bu ilk 10 yolculukta bahşiş alma olasılığının düşük (sıfır) olduğunu gösteriyor, çünkü bunların hepsi nispeten kısa mesafede tek yolculuklu yolculuklardır.

Sonuç

Artık R kodunu saklı yordamlara eklemeyi öğrendiğinize göre, bu uygulamaları kendi modellerinizi derlemek için genişletebilirsiniz. Transact-SQL ile tümleştirme, R modellerini tahmin için dağıtmayı ve modeli yeniden eğitme işlemini kurumsal veri iş akışının bir parçası olarak birleştirmeyi çok daha kolay hale getirir.

Sonraki Adımlar

Bu makalede şunları yapacaksınız:

Toplu puanlama için oluşturulan ve kullanılan saklı prosedürler
Tek bir satır puanlama için oluşturulan ve kullanılan saklı yordamlar

R hakkında daha fazla bilgi için bkz. SQL Server'da R uzantısı.

Geri Bildirim

Bu sayfayı yararlı buldunuz mu?

Last updated on 2025-10-31