Sdílet prostřednictvím

Kurz jazyka R: Spouštění předpovědí v uložených procedurách SQL

Platí pro: SQL Server 2016 (13.x) a novější verze Azure SQL Managed Instance

V páté části této pětidílné série kurzů se naučíte zprovoznit model, který jste natrénovali a uložili v předchozí části, pomocí modelu k predikci potenciálních výsledků. Model je zabalený do uložené procedury, kterou lze volat přímo jinými aplikacemi.

Tento článek ukazuje dva způsoby, jak provést bodování:

  • Režim dávkového bodování: Jako vstup uložené procedury použijte dotaz SELECT. Uložená procedura vrátí tabulku pozorování odpovídající vstupním případům.

  • Individuální režim bodování: Předejte sadu hodnot jednotlivých parametrů jako vstup. Uložená procedura vrátí jeden řádek nebo hodnotu.

V tomto článku:

  • Vytvoření a použití uložených procedur pro dávkové vyhodnocování
  • Vytvoření a použití uložených procedur pro bodování jednoho řádku

V první části jste nainstalovali požadavky a obnovili ukázkovou databázi.

Ve druhé části jste zkontrolovali ukázková data a vygenerovali několik grafů.

Ve třetí části jste se naučili vytvářet funkce z nezpracovaných dat pomocí funkce Transact-SQL. Potom jste tuto funkci volali z uložené procedury a vytvořili tabulku obsahující hodnoty funkcí.

Ve čtvrté části jste načetli moduly a volali potřebné funkce k vytvoření a trénování modelu pomocí uložené procedury SQL Serveru.

Základní bodování

Uložená procedura RPredict znázorňuje základní syntaxi pro zabalení PREDICT volání do uložené procedury.

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

  • Příkaz SELECT získá serializovaný model z databáze a uloží model do proměnné mod R pro další zpracování pomocí jazyka R.

  • Nové případy bodování jsou získány z Transact-SQL dotazu zadaného v @inquery, první parametr uložené procedury. Při čtení dat dotazu se řádky ukládají do výchozího datového rámce InputDataSet. Tento datový rámec se předá funkci PREDICT , která generuje skóre.

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

    Protože soubor data.frame může obsahovat jeden řádek, můžete použít stejný kód pro dávkové nebo jednoduché bodování.

  • Hodnota vrácená PREDICT funkcí je číslo s plovoucí desetinnou čárkou, které představuje pravděpodobnost, že řidič získá spropitné v libovolné výši.

Dávkové bodování (seznam předpovědí)

Častějším scénářem je generování předpovědí pro více pozorování v dávkovém režimu. V tomto kroku se podíváme, jak funguje dávkové hodnocení.

  1. Začněte tím, že pracujte s menší sadou vstupních dat. Tento dotaz vytvoří "prvních 10" výletů s počtem cestujících a dalšími funkcemi potřebnými k předpovědím.

    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

    Ukázkové výsledky

    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. Vytvořte uloženou proceduru s názvem RPredictBatchOutput v sadě Management Studio.

    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. Zadejte text dotazu do proměnné a předejte ho jako parametr uložené proceduře:

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

Uložená procedura vrátí řadu hodnot představujících předpověď pro každou z prvních 10 cest. Nejčastější jízdy jsou však také jízdy s jedním cestujícím s relativně krátkou vzdáleností, pro kterou je nepravděpodobné, že řidič dostane spropitné.

Návod

Místo vrácení pouze výsledků "ano-tip" a "no-tip", můžete také vrátit skóre pravděpodobnosti pro předpověď a pak použít klauzuli WHERE pro hodnoty sloupce Skóre k kategorizaci skóre jako "pravděpodobné tip" nebo "nepravděpodobné tip" pomocí prahové hodnoty, například 0,5 nebo 0,7. Tento krok není součástí uložené procedury, ale je snadné ho implementovat.

Jednorázové bodování více vstupů

Někdy chcete předat více vstupních hodnot a získat jednu předpověď založenou na těchto hodnotách. Můžete například nastavit excelový list, webovou aplikaci nebo sestavu služby Reporting Services tak, aby volala uloženou proceduru a poskytovala vstupy zadané nebo vybrané uživateli z těchto aplikací.

V této části se dozvíte, jak vytvořit jednotlivé předpovědi pomocí uložené procedury, která přebírá více vstupů, jako je počet cestujících, vzdálenost jízdy atd. Uložená procedura vytvoří skóre na základě dříve uloženého modelu R.

Pokud voláte uloženou proceduru z externí aplikace, ujistěte se, že data odpovídají požadavkům modelu R. To může zahrnovat zajištění toho, aby vstupní data mohla být přetypována nebo převedena na datový typ jazyka R nebo ověřovala datový typ a délku dat.

  1. Vytvořte uloženou proceduru 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. Vyzkoušejte si to tak, že hodnoty zadáte ručně.

    Otevřete nové okno dotazu a zavolejte uloženou proceduru a zadejte hodnoty pro každý z parametrů. Parametry představují sloupce funkcí používané modelem a jsou povinné.

    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

    Nebo použijte tento kratší formulář podporovaný pro parametry uložené procedury:

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

  3. Výsledky naznačují, že pravděpodobnost získání tipu je nízká (nula) na těchto 10 cestách, protože všechny jsou výlety s jedním cestujícím v relativně krátké vzdálenosti.

Závěry

Teď, když jste se naučili vkládat kód R do uložených procedur, můžete tyto postupy rozšířit o vytváření vlastních modelů. Integrace s Transact-SQL usnadňuje nasazení modelů R pro predikci a začlenění opětovného trénování modelu jako součásti pracovního postupu podnikových dat.

Další kroky

V tomto článku:

  • Vytvoření a použití uložených procedur pro dávkové vyhodnocování
  • Vytvořil a použil uložené procedury pro vyhodnocení jediného řádku

Další informace o jazyce R naleznete v tématu Rozšíření jazyka R na SQL Serveru.

  • Last updated on