你知道吗？SQL也能做机器学习！

是的，你没听错。我们可以用 SQL 来运行机器学习模型！当我听到这个消息时，我很好奇SQL 要如何在机器学习模型中使用，以及我们要如何使用 SQL 生成预测。

你可能会问，我们要用标准的 SQL 查询来做机器学习吗？没错，答案是YES！

多亏有Google 的BigQuery ML，只需要用标准 SQL 查询，我们就可以创建机器学习模型。

等等……那什么是BigQuery ML呢？

Google BigQuery是一个无服务、经济高效、且高度可扩展的数据仓库系统， BigQuery ML 让你只需用标准 SQL 查询，就可以在BigQuery中创建和执行机器学习模型。BigQuery ML 让数据科学家、机器学习工程师、和数据工程师可以直接用 SQL 快速构建和分析机器学习模型。如果你想了解更多数据分析相关内容，可以阅读以下这些文章：
手把手教你用Python创建SQL数据库！
六条鲜为人知的SQL技巧，帮你每月省下100小时！
数据分析新工具MindsDB–用SQL预测用户流失
DS数据科学家和DA数据分析师：要学习什么不同内容？

BigQuery ML 支持哪些类型的模型？

• 回归模型

o 线性回归、二元逻辑回归、多类逻辑回归

• 聚类

o K-means 聚类

• 用于创建如推荐系统的矩阵分解
• 用于预测的时间序列模型
• Boosted Tree 模型 -> XGBoost （分类和回归）
• 深度神经网络 (DNN) -> 分类和回归

更多可用模型参见官方文档：

https://cloud.google.com/bigquery-ml/docs/introduction

BigQuery在幕后进行什么工作？

• 利用BigQuery 的处理能力构建模型
• 自动调整学习率
• 自动将数据拆分为训练集和测试集
• L1/L2 正则化
• 训练/测试拆分的 3 种策略：随机、顺序、自定义
• 设置学习率

BigQuery ML创建模型的步骤

现在我们已经初步了解了BigQuery ML，是时候看看我们要如何加载和创建 ML ，来对数据集进行预测了。

接下来，我将向你展示我们如何使用著名的 Titanic 数据集来训练模型、模型评估、和测试模型。我将构建一个简单的基线模型来进行演示。

将数据加载到BigQuery 中：

登录到 GCP ，找到Big Query平台处。

现在，创建一个新的数据库：

接下来，我们在数据集上创建一个表，选择上传并选择 test.csv 文件，设定文件格式为 CSV ，然后为你的表格命名。

然后，单击 Create Table 按钮，你会看到你的表格布局，你还可以预览你的表格：

创建机器学习模型

现在可以编写一些 SQL 查询了，在编辑器中编写这个查询：

CREATE OR REPLACE MODEL
`qwiklabs-gcp-04-905d44b8a3bb.titanic.credit_classification_model`
OPTIONS
(
model_type='logistic_reg',
labels = ['Pclass']
)
AS
SELECT
* EXCEPT(Survived)
FROM
`qwiklabs-gcp-04-905d44b8a3bb.titanic.data`

点击 Run 按钮，并等待它训练和处理模型。处理完成后，你会在侧边栏中看到一个名为 model 的新文件夹，单击model文件夹，你可以看到有关模型的所有信息，包括损失（Loss）、持续时间（Duration）、F1 分数、ROC、AUC、学习率、混淆矩阵等等。

评估机器学习模型的 SQL 查询

我们将通过roc_auc分数比较我们的模型，单击查询编辑器并输入此查询：

SELECT
roc_auc,
CASE
WHEN roc_auc > .8 THEN 'good'
WHEN roc_auc > .7 THEN 'fair'
WHEN roc_auc > .6 THEN 'not great'
ELSE
'poor'
END
AS model_quality
FROM
ML.EVALUATE(MODEL `qwiklabs-gcp-04-905d44b8a3bb.titanic.credit_classification_model`,
(
SELECT
* EXCEPT(Survived)
FROM
`qwiklabs-gcp-04-905d44b8a3bb.titanic.data` ) )

你可以看到，我们的roc_auc得分为 0.9，这非常好；但是，在这里，我使用的是一种非常简单的方法来训练模型，因此，我们的模型可能存在偏差和过度拟合。

模型预测

评估完模型后，我们可以进行预测了，输入这个查询到查询编辑器中。

SELECT
*
FROM
ml.PREDICT(MODEL `qwiklabs-gcp-04-905d44b8a3bb.titanic.credit_classification_model`,
(
SELECT
* EXCEPT(Survived)
FROM
`qwiklabs-gcp-04-905d44b8a3bb.titanic.data` ) );

我们可以通过 google sheet 和 Data Studio 看到我们的预测结果，让我们在 Sheets 上探索它。

BigQuery ML备忘单

下面，我列出了在BigQuery ML上常用的查询语句，你可以用它做查询参考。

回顾一下构建 ML 模型需要做的事情：

• ETL 到BigQuery
• 创建模型
• 模型评估
• 模型预测

BigQuery ML的优势

• 无需使用 Python 编写代码/使用 Java 构建 ML 解决方案，可以直接用 SQL在BigQuery中训练和访问模型
• BigQuery提高了速度和模型运行时间，因为我们不需要从任何其他源数据加载数据，这些数据已经存储在BigQuery表格中
• 正如我们之前讨论过的， BigQuery ML 是无服务器的，因此，随着数据量的增加，我们无需担心需要去维护服务器，也无需在制作 ML 解决方案之前设置环境。

以上就是本文的全部内容，你可以下载Google的BigQuery ML，并创建自己的机器学习模型。感谢你的阅读！我们下一篇文章见。你还可以订阅我们的YouTube频道，观看大量数据科学相关公开课：https://www.youtube.com/channel/UCa8NLpvi70mHVsW4J_x9OeQ；在LinkedIn上关注我们，扩展你的人际网络！https://www.linkedin.com/company/dataapplab/

原文作者：Himanshu Tripathi
翻译作者：Jiawei Tong
美工编辑：过儿
校对审稿：Jiawei Tong
原文链接：https://pub.towardsai.net/machine-learning-with-sql-30e942c75240