套用 SQL 轉換

針對輸入資料集執行 SQLite 查詢以轉換資料

類別： 資料轉換/操作

模組概觀

本文描述如何使用機器學習 Studio (傳統) 中的「套用SQL 轉換模組，在輸入資料集或資料集上指定 SQL 查詢。

當您需要以複雜的方式修改資料，或保存要在其他環境中使用的資料時，SQL 很方便。 例如，使用「套用SQL 轉換模組，您可以：

• 建立結果的資料表，並將資料集儲存在可攜式 SQLite 資料庫中。

• 資料型別，在執行自訂轉換，或建立彙總。

• 執行 SQL 查詢陳述式來篩選或改變資料並傳回查詢結果為資料表。

什麼是 SQLite？

SQLite 是 C 程式設計程式庫中包含的一個公用網域關聯式資料庫管理系統。 SQLite 常用來做為在網頁瀏覽器中進行本機儲存時所使用的內嵌資料庫。

SQLite 原先設計在 2000 中的美國海軍，以支援無伺服器的交易。 它是一個獨立的資料庫引擎有沒有管理系統，因此不需要組態或管理。

如何設定套用 SQL 轉換

此模組最多可能需要以三個資料集做為輸入。 當您參考的資料集各連線到每個輸入連接埠時，必須使用名稱 t1、t2 和 t3。 這些數字表示輸入連接埠的索引。

其餘的參數是 SQL 查詢，會使用 SQLite 語法。 此模組支援 SQLite 語法的所有標準陳述式。 關於不支援的陳述式清單，請參閱技術提示一節。

一般語法和使用方式

• 在 [SQL 指令碼] 文字輸入框中輸入多行時，請使用分號終止每個陳述式。 否則，插入換行符號會轉換成空格。

  例如，下列陳述式是相等的：

  SELECT   
  *   
  from   
  t1;  
  

  SELECT * from t1;  
  

• 您可以使用 -- 每一行開頭的，或使用 /* */ 來封入文字，來新增批註。

  例如，此陳述式是有效的：

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

• 如果資料行名稱重複保留關鍵字的名稱，則會將語法醒目提示套用至SQL 腳本文字方塊內的文字。 為了避免混淆，您應該以方括弧括住資料行名稱， (遵循 SQL 的慣例) 或倒引號或雙引號 (ANSI 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在語句中，在識別碼中包含空格或其他字元的資料行名稱，必須用雙引號括住、方括弧或倒引號字元 (') 。

例如，此查詢會參考上 t1 Two-Class 鳶尾花資料集，但一個資料行名稱包含禁止的字元，因此資料行名稱會以引號括住。

SELECT class, "sepal-length" FROM t1;  

您可以加入 WHERE 子句來篩選資料集中的值。

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

SQLite 語法不支援在 SQL Transact-sql 中使用的 TOP 關鍵字。 相反地，您可以使用 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，和輸入連接埠對應到餐廳功能 dataset 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;  

彙總函式

本節提供一些常見 SQL 彙總函式的基本範例，使用 SQLite。

目前支援的彙總函式如下： AVG 、 COUNT 、 MAX 、 MIN 、 SUM 、 TOTAL 。

下列查詢會傳回集，其中包含餐廳識別項，以及餐廳的平均等級。

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 [條件] 然後 [expression] ELSE [expression] 結束

• 當案例 [expression] [value] 然後 [expression] ELSE [expression] 結束

例如，假設您先前已使用 [ 轉換成指標值 ] 模組來建立包含 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 標準，它不包含許多商業的關聯式資料庫系統所支援的功能。 如需詳細資訊，請參閱 SQLite 可理解的 SQL。 此外，在建立 SQL 陳述式時，請留意下列限制：

• 不過，不像大多數的 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 - Unsupported SQL

預期的輸入

模組參數

輸出

例外狀況

如需 Studio (傳統) 模組特定的錯誤清單，請參閱機器學習錯誤碼。

如需 API 例外狀況的清單，請參閱機器學習 REST API 錯誤碼。

另請參閱

操作
資料轉換
A-Z 模組清單