👲🏾 💝 🐁 SQL Server中的机器学习 🧑🏿‍🤝‍🧑🏻 😩 👨‍👩‍👧

SQL Server内部的ML Toolkit

本文介绍了创建过程的步骤，该过程使用R中包含的mtcars数据集创建简单的广义线性模型（GLM），该模型可以预测车辆配备手动变速器的可能性。第二个过程用于估计-它调用在第一个过程中创建的模型，以基于新数据输出一组预测。

术语解释

SQL是发送到数据库的结构化查询的语言。

SQL Server是Microsoft的关系数据库。

机器学习服务是SQL Server的组件，使您可以对数据执行Python和R脚本。

SQL中的触发器是一种对数据库状态变化做出反应的机制。

脚本是为狭窄且通常是周期性的任务而设计的小型程序。

R是专门为数据处理而创建的编程语言。

SQL Server

有益于机器学习的原因让我们讨论在SQL Server中使用ML的可用好处的列表。

最重要的原因之一是将SQL命令和ML代码存储在公共可见区域中的便利性，这使您可以充分利用这两种技术。

另一个重要原因是安全性，因为如果条件数据库服务器位于一个位置，并且对请求的请求来自另一个位置，则可以截获数据。如果DBMS内核既存储数据又调用代码，则可以避免此问题。

在其他方面，无论是在提供的库还是在性能方面，SQL Server都对R编程语言提供了良好的支持。根据最新的基准，数据库引擎每秒能够计算大约一百万个R预测（链接）。

检查SQL Server的运行状况

首先，让我们确保R的机器学习服务和扩展按预期工作。为此，请运行以下代码。

EXEC sp_execute_external_script @language = N'R'
, @script = N'
OutputDataSet <- data.frame(installed.packages()[,c("Package", ...;'
WITH result sets((Package NVARCHAR(255)
, Version NVARCHAR(100)...));

结果：

让我们简要分析一下SQL命令：

1.  EXEC sp_execute_external_script @language = N'R'

在这里，我们定义内核应处理R语言

2.  @script = N'OutputDataSet <- data.frame(installed...'

脚本这是一个特殊变量，可以在后续请求中进行处理，等于R命令的结果

3.  WITH result sets((Package NVARCHAR(255)..

使用递归内容显示操作脚本以表格的形式。

在SQL Server中使用ML的示例（link）

创建一个表来存储将用来训练模型的数据：

CREATE TABLE dbo.MTCars(
hp int NOT NULL,
...
wt decimal(10, 3) NOT NULL,
am int NOT NULL);

我们向其中输入数据：

INSERT INTO dbo.MTCars
EXEC sp_execute_external_script @language = N'R'
    , @script = N'MTCars <- mtcars;'
    , @input_data_1 = N''
    , @output_data_1_name = N'MTCars';

结果：

创建并训练模型：

CREATE PROCEDURE generate_GLM
... , @script = N'carsModel <- carsModel <- glm(... data = MTCarsData, ...;
        trained_model <- ...'
    , @input_data_1 = N'SELECT hp, wt, am FROM MTCars'
    , @input_data_1_name = N'MTCarsData'
    ...;

在此阶段，我将重点关注R如何在SQL Server引擎上接收数据：@ input_data_1是表的列，在代码中以变量@ input_data_1_name的形式表示。

为模型创建表：

CREATE TABLE GLM_models (
    model_name varchar(30) not null default('default model') primary key,
    model varbinary(max) not null
);

我们保存模型：

INSERT INTO GLM_models(model)
EXEC generate_GLM;

结果：

SQL Server能够将经过训练的模型加载到表中，以便以后可以快速重用它们。

我们创建一个表，用于存储分析数据：

CREATE TABLE dbo.NewMTCars(
    hp INT NOT NULL
    , wt DECIMAL (10,3) NOT NULL
    , am INT NULL)

我们填写所需范围内的随机数据：


INSERT INTO dbo.NewMTCars(hp, wt) VALUES (110, 2.634)
INSERT INTO dbo.NewMTCars(hp, wt) VALUES (72, 3.435)
INSERT INTO dbo.NewMTCars(hp, wt) VALUES (220, 5.220)
INSERT INTO dbo.NewMTCars(hp, wt) VALUES (120, 2.800)

结果：我们

预测新数据的结果：

DECLARE @glmmodel varbinary(max) = ...
    ...    , @script = N'
            --     
            '
   --    ;

结果：

predicated_am值越接近1，则汽车具有手动变速箱的机会越大。

结束语

除了该示例之外，数据处理中的机器学习还可用于将有用的信息与噪声分离，查找列之间的依存关系等等。

除了上面介绍的功能之外，您还可以应用更高级的预测方法，并设置每次出现新信息时都会触发的触发器：

CREATE TRIGGER `add_car` AFTER INSERT ON `NewMTCars `

…应用预测程序。

SQL Server中的机器学习

More articles: