应用 SQL 转换

在输入数据集上运行 SQLite 查询以转换数据

类别： 数据转换/操作

模块概述

本文介绍如何使用机器学习 Studio (经典) 中的 "应用 SQL 转换模块来指定对输入数据集或数据集的 SQL 查询。

当你需要以复杂的方式修改数据，或保存数据以便在其他环境中使用时，SQL 非常方便。 例如，使用 "应用 SQL 转换" 模块，您可以：

• 创建结果表，并将数据集保存在便携式数据库中。

• 对数据类型执行自定义转换，或创建聚合。

• 执行 SQL 查询语句，筛选或更改数据，并以数据表的形式返回查询结果。

什么是 SQLite？

SQLite 是 C 编程库中所包含的公共域关系数据库管理系统。 对于 Web 浏览器中的本地存储，SQLite 常常被选作嵌入式数据库。

SQLite 最初是在 2000 年为美国海军设计的，用于支持无服务器的事务。 它是自包含的数据库引擎，没有管理系统，因此不需要配置或管理。

如何配置应用 SQL 转换

该模块可将最多三个数据集用作输入。 引用连接到每个输入端口的数据集时，必须使用名称 t1、t2 和 t3。 表编号指示输入端口的索引。

剩余参数是使用 SQLite 语法的 SQL 查询。 此模块支持 SQLite 语法的所有标准语句。 如需了解不支持的语句列表，请参阅技术说明部分。

一般语法和用法

• 在“SQL 脚本”文本框中键入多行时，请使用分号终止每条语句。 否则，换行符会转换为空格。

  例如，以下语句是等价的：

  SELECT   
  *   
  from   
  t1;  
  

  SELECT * from t1;  
  

• 可以通过在每行的开头使用 -- 或使用 /* */ 来封闭文本来添加注释。

  例如，此语句是有效的：

  SELECT * from t1  
  /*WHERE ItemID BETWEEN 1 AND 100*/;  
  

• 如果列名称与保留关键字的名称重复，则会将语法突出显示应用于 " SQL 脚本" 文本框中的文本。 若要避免混淆，应将列名用方括号括起来， (遵循 ANSI SQL 约定) (的 SQL 约定) 或反撇号或双引号。

  例如，在血糖捐赠数据集的以下查询中， Time 是有效的列名称，但也是保留关键字。

  SELECT Recency, Frequency, Monetary, Time, Class  
  FROM t1  
  WHERE Time between 3 and 20;  
  

  如果按原样运行查询，则查询可能返回正确的结果，但根据数据集，它可能会返回错误。 下面是如何避免此问题的一些示例：

  -- Transact-SQL  
  SELECT [Recency], [Frequency], [Monetary], [Time], [Class]  
  FROM t1  
  WHERE [Time] between 3 and 20;  
  -- ANSI SQL  
  SELECT "Recency", "Frequency", "Monetary", "Time", "Class"  
  FROM t1  
  WHERE `Time` between 3 and 20;  
  

  注意

  语法突出显示仍保留在关键字上，即使在引号中括在引号中。

• SQLite 不 区分大小写，但有几个命令具有区分大小写的区分大小写 (GLOB 与 GLOB) 的不同含义。

SELECT 语句

SELECT在语句中，在标识符中不能包含空格或其他字符的列名称必须括在双引号、方括号或反撇号字符中 (") 。

例如，此查询引用 Two-Class 上 t1 的 Iris 数据集，但是一个列名称包含禁止使用的字符，因此列名用引号引起来。

SELECT class, "sepal-length" FROM t1;  

您可以添加 WHERE 子句来筛选数据集中的值。

SELECT class, "sepal-length" FROM t1 WHERE "sepal-length" >5.0;  

SQLite 语法不支持 TOP 在 transact-sql SQL 中使用的关键字。 相反，可以使用 LIMIT 关键字或 FETCH 语句。

例如，将这些查询与自行车租赁数据集进行比较。

-- unsupported in SQLite  
SELECT  TOP 100 [dteday] FROM t1 ;  
ORDER BY [dteday] DESC;  
  
-- Returns top 100   
SELECT  [dteday] FROM t1 LIMIT 100 ;  
ORDER BY [dteday] DESC;  
  
-- Returns top 100. Note that FETCH is on a new line.  
SELECT  [dteday] FROM t1 - ;  
FETCH FIRST 100 rows ONLY;  
ORDER BY [dteday] DESC;  

联接

以下示例在与 t1 对应的输入端口上使用餐馆分级数据集，在与 t2 对应的输入端口上使用餐馆功能数据集。

下面的语句将两个表联接起来，创建将指定的餐馆功能与每个餐馆的平均级别组合在一起的数据集。

SELECT DISTINCT(t2.placeid),    
t2.name, t2.city, t2.state, t2.price, t2.alcohol,  
AVG(rating)  AS 'AvgRating'   
FROM t1   
JOIN t2  
ON t1.placeID = t2.placeID  
GROUP BY t2.placeid;  

聚合函数

本部分提供了一些使用 SQLite 的常见 SQL 聚合函数的基本示例。

当前支持的聚合函数包括： AVG 、 COUNT 、 MAX 、 SUMMIN 、和 TOTAL 。

下面的查询返回包含餐馆 ID 及餐馆的平均级别的数据集。

SELECT DISTINCT placeid,  
AVG(rating) AS ‘AvgRating’,  
FROM t1  
GROUP BY placeid  

使用字符串

SQLite 支持使用双竖线运算符来串联字符串。

下面的语句通过串联两个文本列创建一个新列。

SELECT placeID, name,   
(city || '-' || state) AS 'Target Region',   
FROM t1  

COALESCE 和 CASE

COALESCE 按顺序计算多个参数，并返回第一个不计算为 NULL 的表达式的值。

例如，针对“钢退火多类”数据集进行的如下查询从假定有多个互斥的值的列的列表中返回首个非 null 标志。 如果找不到任何标志，则返回“none”字符串。

SELECT classes, family, [product-type],  
COALESCE(bt,bc,bf,[bw/me],bl, "none") AS TemperType  
FROM t1;  

CASE语句可用于测试值并基于计算结果返回新值。 SQLite 支持语句的以下语法 CASE ：

• CASE WHEN [条件] THEN [表达式] ELSE [表达式] END

• CASE [表达式] WHEN [值] THEN [表达式] ELSE [表达式] END

例如，假设您以前使用了 " 转换为指示器值 " 模块来创建包含 true-false 值的设置功能列。 下面的查询将多个功能列中的值折叠为单个多值列。

SELECT userID, [smoker-0], [smoker-1],  
CASE  
WHEN [smoker-0]= '1' THEN 'smoker'   
WHEN [smoker-1]= '1' THEN 'nonsmoker'   
ELSE 'unknown'  
END AS newLabel  
FROM t1;  

示例

有关如何在机器学习试验中使用此模块的示例，请参阅 Azure AI 库中的以下示例：

• 应用 SQL 转换：使用餐馆分级、餐厅功能和餐馆客户数据集来演示简单联接、select 语句和聚合函数。

技术说明

本部分包含实现详情、使用技巧和常见问题解答。

• 端口 1 上始终需要输入。

• 如果输入数据集包含列名，输出数据集中的列将使用输入数据集的列名。

  如果输入数据集没有列名称，则将使用以下命名约定自动创建表中的列名： T1COL1、T1COL2、T1COL3 等，其中的数字指示输入数据集中每个列的索引。

• 对于包含空格或其他特殊字符的列标识符，请在 SELECT 和 WHERE 子句中引用列时，始终将列标识符用方括号或双引号括起来。

不支持的语句

尽管 SQLite 支持很多 ANSI SQL 标准，但其并不包括商业关系数据库系统支持的许多功能。 有关详细信息，请参阅 SQL as Understood by SQLite。 另外，请在创建 SQL 语句时注意以下限制：

• SQLite 使用动态类型的值，而不是像在大多数关系数据库系统中的那样将类型分配给列。 它是一种弱类型，并允许隐式转换类型。

• 实现 LEFT OUTER JOIN，但不实现 RIGHT OUTER JOIN 或 FULL OUTER JOIN。

• 你可将 RENAME TABLE 和 ADD COLUMN 语句与 ALTER TABLE 命令结合使用，但不支持其他子句，例如 DROP COLUMN、ALTER COLUMN 和 ADD CONSTRAINT。

• 可在 SQLite 中创建“视图”，但之后视图为只读。 不可在视图中执行 DELETE、INSERT 或 UPDATE 语句。 但可创建一个触发器，尝试在视图上执行 DELETE、INSERT 或 UPDATE，并在触发器主体中执行其他操作。

除了在官方 SQLite 网站上提供不受支持的功能列表，下面的 Wiki 还提供其他不受支持的功能列表：SQLite - 不受支持的 SQL

预期输入

模块参数

Outputs

例外

有关特定于 Studio (经典) 模块的错误列表，请参阅机器学习错误代码。

有关 API 异常的列表，请参阅机器学习 REST API 错误代码。

另请参阅

操作
数据转换
A-Z 模块列表