SQL查詢資料操作與連線技術

– 11 分鐘閱讀

SQL 查詢在資料函式庫操作中扮演著至關重要的角色，理解不同型別的連線和資料操作技術是提升查詢效率的關鍵。外部連線，包括左外部連線、右外部連線和全外部連線，允許我們合併不同表格的資料，並保留特定表格的所有資料列。這在需要處理不完整資料或需要保留所有資訊的場景下非常有用。資料操作語言（DML）查詢則允許我們對資料函式庫中的資料進行操作，例如插入、更新和刪除等。最佳化 DML 查詢的效能至關重要，尤其是在處理大量資料時。使用 WITH 子句（CTE）可以簡化查詢邏輯，提高可讀性和可維護性，同時提升查詢效能。遞迴 CTE 更是在處理樹狀或圖狀結構資料時不可或缺的工具。DuckDB 作為一個高效能的嵌入式分析型資料函式庫，提供了一系列 SQL 擴充功能，例如 COLUMNS 表示式、BY NAME 子句、別名存取和資料取樣等，這些功能可以進一步簡化查詢邏輯，提升查詢效率，並增強 SQL 查詢的靈活性。

執行 SQL 查詢：資料操作與連線

外部連線的應用

在 SQL 查詢中，外部連線（Outer Join）是一種重要的資料合併技術，用於結合兩個或多個表格，並保留其中一個或兩個表格中的所有資料列。外部連線分為左外部連線（LEFT OUTER JOIN）、右外部連線（RIGHT OUTER JOIN）和全外部連線（FULL OUTER JOIN）。

左外部連線範例

以下是一個左外部連線的範例，使用虛擬表格 l 和 r：

SELECT *
FROM
(VALUES (1, 'a1'),
(2, 'a2'),
(3, 'a3')) l(id, nameA)
LEFT OUTER JOIN
(VALUES (1, 'b1'),
(2, 'b2'),
(4, 'b4')) r(id, nameB)
USING (id)
ORDER BY id;

結果如下：

┌───────┬─────────┬─────────┐
│ id │ nameA │ nameB │
│ int32 │ varchar │ varchar │
├───────┼─────────┼─────────┤
│ 1 │ a1 │ b1 │
│ 2 │ a2 │ b2 │
│ 3 │ a3 │ │
└───────┴─────────┴─────────┘

全外部連線範例

以下是一個全外部連線的範例，使用虛擬表格 l 和 r：

SELECT *
FROM
(VALUES (1, 'a1'),
(2, 'a2'),
(3, 'a3')) l(id, nameA)
FULL OUTER JOIN
(VALUES (1, 'b1'),
(2, 'b2'),
(4, 'b4')) r(id, nameB)
USING (id)
ORDER BY id;

結果如下：

┌───────┬─────────┬─────────┐
│ id │ nameA │ nameB │
│ int32 │ varchar │ varchar │
├───────┼─────────┼─────────┤
│ 1 │ a1 │ b1 │
│ 2 │ a2 │ b2 │
│ 3 │ a3 │ │
│ 4 │ │ b4 │
└───────┴─────────┴─────────┘

程式碼解析

在上述範例中，使用了 USING 子句來指定連線條件，因為兩個表格都有一個名為 id 的欄位。如果兩個表格的連線欄位名稱不同，則需要使用 ON 子句來指定連線條件。

連線型別的選擇

內部連線（Inner Join）會過濾掉沒有匹配資料的資料列，而外部連線則會保留所有資料列。因此，內部連線通常用於需要精確匹配的情況，而外部連線則用於需要保留所有資料列的情況。

資料操作語言查詢

資料操作語言（Data Manipulation Language，DML）查詢用於對資料函式庫中的資料進行操作，包括插入、更新和刪除等。

COPY TO 命令

COPY TO 命令用於將查詢結果複製到檔案中。以下是一個範例：

duckdb -c "COPY (SELECT * FROM 'production.csv' JOIN 'consumption.csv'
USING (ts) JOIN 'export.csv' USING (ts) JOIN 'import.csv' USING (ts) )
TO '/dev/stdout' (HEADER)"

這個命令將四個 CSV 檔案根據分享欄位 ts 進行內部連線，並將結果複製到標準輸出中。

程式碼解析

在上述範例中，使用了 COPY TO 命令來將查詢結果複製到檔案中。查詢中使用了內部連線來合併四個 CSV 檔案，並保留一個分享欄位 ts。

連線多個表格時的注意事項

當連線多個表格時，需要注意以下幾點：

  • 如果連線欄位中存在重複值，則會產生多個匹配結果。
  • 如果使用內部連線，則會過濾掉沒有匹配資料的資料列。
  • 如果使用外部連線，則會保留所有資料列。

連線多個表格範例

以下是一個內部連線多個表格的範例：

SELECT *
FROM
(VALUES (1, 'a1'),
(2, 'a2'),
(2, 'a2'),
(3, 'a3')) l(id, nameA)
JOIN
(VALUES (1, 'b1'),
(2, 'b2'),
(3, 'b3'),
(3, 'b3')) r(id, nameB)
USING (id)
ORDER BY id;

結果如下：

┌───────┬─────────┬─────────┐
│ id │ nameA │ nameB │
│ int32 │ varchar │ varchar │
├───────┼─────────┼─────────┤
│ 1 │ a1 │ b1 │
│ 2 │ a2 │ b2 │
│ 2 │ a2 │ b2 │
│ 3 │ a3 │ b3 │
│ 3 │ a3 │ b3 │
└───────┴─────────┴─────────┘

程式碼解析

在上述範例中，使用了內部連線來合併兩個虛擬表格 l 和 r。由於連線欄位 id 中存在重複值，因此產生了多個匹配結果。

資料操作語言查詢的最佳化與遞迴CTE應用

在資料函式庫設計階段，我們需要預先考慮資料操作語言（DML）查詢的效能最佳化。其中，JOIN操作通常發生在預先定義好的欄位上，例如我們的範例中的systems表格的id欄位與readings表格的system_id欄位之間的關聯。在systems表格中，id是主鍵，確保其值的唯一性；而在readings表格中，system_id是外部索引鍵，用於參照systems表格的id。這種設計使得資料函式庫能夠快速進行JOIN操作，而不需要掃描整個表格。

使用WITH子句（CTE）最佳化查詢

WITH子句，也稱為公用表表達式（CTE），是一種暫時性的檢視，僅在特定的查詢中有效。使用CTE可以封裝查詢邏輯，使其更易於閱讀和維護。與檢視不同的是，CTE可以參考其他在同一個WITH子句中定義的CTE，從而實作遞迴查詢。

為何使用CTE？

  1. 避免在FROM子句中使用子查詢：雖然在FROM子句中使用子查詢是語法上有效的，但它可能導致查詢難以閱讀。CTE提供了一種更清晰的方式來組織查詢邏輯。

  2. 支援遞迴查詢：CTE支援遞迴查詢，這是檢視和普通子查詢所不具備的功能。透過遞迴CTE，我們可以處理樹狀或圖狀結構的資料。

簡單的CTE範例

假設我們需要找出readings表格中最大power值對應的列。我們可以使用子查詢或CTE來實作：

WITH max_power AS (
  SELECT max(power) AS v FROM readings
)
SELECT max_power.v, read_on
FROM max_power
JOIN readings ON power = max_power.v;

這個範例展示瞭如何將子查詢替換為CTE。對於簡單的查詢，兩者的效能差異不大，但CTE的可讀性更佳。

使用CTE進行複雜查詢

當我們需要計算每個系統每小時的平均發電量，並找出最大值時，就需要使用到巢狀聚合函式。由於聚合函式不能巢狀使用（如avg(max(v))是不允許的），我們需要透過CTE來實作這一點。

WITH per_hour AS (
  SELECT system_id,
         date_trunc('hour', read_on) AS read_on,
         avg(power) / 1000 AS kWh
  FROM readings
  GROUP BY ALL
)
SELECT name,
       max(kWh),
       arg_max(read_on, kWh) AS 'Read on'
FROM per_hour
JOIN systems s ON s.id = per_hour.system_id
WHERE system_id = 34
GROUP by s.name;

遞迴CTE的應用

遞迴CTE允許我們查詢具有層級或樹狀結構的資料。以下是一個範例，展示如何使用遞迴CTE來遍歷一個具有父子關係的表格：

CREATE TABLE IF NOT EXISTS src (
  id INT PRIMARY KEY,
  parent_id INT, 
  name VARCHAR(8)
);

INSERT INTO src (VALUES
  (1, null, 'root1'),
  (2, 1, 'ch1a'),
  (3, 1, 'ch2a'),
  (4, 3, 'ch3a'),
  (5, null, 'root2'),
  (6, 5, 'ch1b')
);

WITH RECURSIVE tree AS (
  SELECT id,
         id AS root_id,
         name AS path
  FROM src WHERE parent_id IS NULL
  UNION ALL
  SELECT src.id,
         root_id,
         list_append(tree.path, src.name) AS path
  FROM src
  JOIN tree ON (src.parent_id = tree.id)
)
SELECT path FROM tree;

這個範例展示瞭如何使用遞迴CTE來遍歷具有層級結構的資料，並構建出從根節點到葉子節點的路徑。

3.5 DuckDB的SQL擴充套件功能

DuckDB的開發者旨在使SQL更易於存取和使用。為實作此目標，他們在SQL實作中增加了一些擴充套件功能，以簡化常見任務。本文將介紹這些擴充套件功能。

3.5.1 處理SELECT陳述式

SELECT *是一把雙刃劍：一方面，它易於編寫，且結果元組很可能包含所需的資料。另一方面，選擇關係的所有列可能會導致一些問題，包括：

  • 結果元組的不穩定性，因為表定義可能會更改（新增或刪除列）。
  • 對資料函式庫伺服器或行程造成更大的記憶體壓力。
  • 雖然DuckDB是嵌入式資料函式庫，不會涉及網路流量，但SELECT *仍可能導致非嵌入式資料函式庫的網路流量增加。
  • SELECT *可能會阻止索引專用掃描（index-only scan）的發生。

雖然最好避免過多使用SELECT *，但有時它們是必要的。DuckDB透過新增兩個關鍵字：EXCLUDE和REPLACE，使SELECT *的使用更加安全。

使用EXCLUDE排除某些列

EXCLUDE子句允許從*查詢中排除一或多個列。當您有一個包含許多列的表或關係，且幾乎所有列都是必要的時候，這非常有用。

例如，如果您只想要prices表中的相關資料，可以使用以下查詢：

SELECT value, valid_from, valid_until FROM prices;

使用EXCLUDE子句，您可以簡化查詢：

SELECT * EXCLUDE (id)
FROM prices;

您可以排除任意數量的列。這樣既能保持SELECT *的靈活性，又能確保不會存取不需要的資料。

使用REPLACE重塑結果

假設您有一個檢視v_power_per_day，它計算出以分數表示的千瓦時（kWh）。有些使用者可能只想要傳回整數值。您可以使用REPLACE子句替換單個列，同時保留其他列：

SELECT * REPLACE (round(kWh)::int AS kWh)
FROM v_power_per_day;

REPLACE子句接受一或多個x AS y結構，其中x是表示式，可以參照原始選擇列表中的列，並對其應用函式和其他轉換；y是原始選擇列表中使用的名稱。

結果的結構保持不變，但kWh列現在是整數列：

┌───────────┬────────────┬───────┐
│ system_id │ day        │ kWh   │
│ int32     │ date       │ int32 │
├───────────┼────────────┼───────┤
│ 1200      │ 2019-08-29 │ 289   │
│ ·         │ ·          │ ·     │
│ ·         │ ·          │ ·     │
│ ·         │ ·          │ ·     │
│ 10        │ 2020-03-19 │ 0     │
├───────────┴────────────┴───────┤
│ 1587 rows (2 shown) 3 columns   │
└────────────────────────────────┘

動態投影和篩選列

DuckDB提供了COLUMNS表示式，可以根據正規表示式投影、篩選和聚合一或多個列。

例如，若要從prices表中選擇包含有效性資訊的列，可以使用以下查詢：

SELECT COLUMNS('valid.*') FROM prices LIMIT 3;

這將傳回所有相關的列：

┌────────────┬─────────────┐
│ valid_from │ valid_until │
│ date       │ date        │
├────────────┼─────────────┤
│ 2018-12-01 │ 2019-01-01  │
│ 2019-01-01 │ 2019-02-01  │
│ 2019-02-01 │ 2019-03-01  │
└────────────┴─────────────┘

您還可以對動態選擇的列應用任何函式，以計算多個結果列。例如，若要計算包含單詞“valid”的所有列的最大值，可以使用以下查詢：

SELECT max(COLUMNS('valid.*')) FROM prices;

這將傳回valid_from和valid_until的最大值：

┌────────────────────────┬─────────────────────────┐
│ max(prices.valid_from) │ max(prices.valid_until) │
│ date                   │ date                    │
├────────────────────────┼─────────────────────────┤
│ 2023-01-01             │ 2024-02-01              │
└────────────────────────┴─────────────────────────┘

3.5 DuckDB 專屬 SQL 擴充功能

在撰寫 SQL 查詢時，DuckDB 提供了一系列強大的擴充功能，使查詢更加靈活和高效。其中包括 COLUMNS 表示式、BY NAME 子句、別名存取、以及資料取樣等功能。

3.5.1 COLUMNS 表示式

當需要在 WHERE 子句中撰寫複雜的條件時，可以使用 COLUMNS 表示式來簡化查詢。例如，若要找出所有在 2020 年有效的價格記錄，可以使用以下查詢：

SELECT *
FROM prices
WHERE COLUMNS('valid.*') BETWEEN '2020-01-01' AND '2021-01-01';

在這個查詢中，COLUMNS 表示式使用了正規表示式 valid.* 來匹配欄位名稱。此外，DuckDB 也支援使用 Lambda 函式來定義匹配條件，例如：

SELECT *
FROM prices
WHERE COLUMNS(col -> col LIKE 'valid%')
BETWEEN '2020-01-01' AND '2021-01-01';

內容解密：

  1. COLUMNS 表示式：用於簡化對多個欄位的條件檢查。
  2. 正規表示式與 Lambda 函式：提供靈活的欄位匹配方式。
  3. 查詢範例：展示如何使用 COLUMNS 表示式來篩選資料。

此外，COLUMNS 表示式也可以與 EXCLUDE 或 REPLACE 條件結合使用。例如，若要計算 prices 表中除 id 之外的所有欄位的最大值，可以使用以下查詢：

SELECT max(COLUMNS(* EXCLUDE id))
FROM prices;

內容解密：

  1. COLUMNS(* EXCLUDE id)：排除了 id 欄位，對其他欄位計算最大值。
  2. 靈活性：展示瞭如何結合 COLUMNS 與其他條件來進行複雜的資料操作。

3.5.2 按名稱插入資料

在執行 INSERT INTO 操作時，DuckDB 提供了 BY NAME 子句，使得欄位對映更加直觀。例如：

INSERT INTO systems BY NAME
SELECT DISTINCT
    system_id AS id,
    system_public_name AS name
FROM 'https://oedi-data-lake.s3.amazonaws.com/pvdaq/csv/systems.csv'
ON CONFLICT DO NOTHING;

內容解密：

  1. BY NAME 子句：使插入操作根據欄位名稱進行對映，而不是依賴欄位順序。
  2. 靈活性：簡化了欄位變更時的維護工作。
  3. 範例：展示瞭如何使用 BY NAME 進行資料插入。

3.5.3 在各處存取別名

DuckDB 允許在查詢的多個子句中存取別名。例如，在以下查詢中，別名 is_not_system10 和 power_per_month 可以在 WHERE、GROUP BY 和 HAVING 子句中使用：

SELECT 
    system_id > 10 AS is_not_system10,
    date_trunc('month', read_on) AS month,
    sum(power) / 1000 / 1000 AS power_per_month
FROM readings
WHERE is_not_system10 = TRUE
GROUP BY is_not_system10, month
HAVING power_per_month > 100;

內容解密：

  1. 別名存取：展示瞭如何在不同子句中使用別名，提高了查詢的可讀性。
  2. 範例：說明瞭別名在實際查詢中的應用。

3.5.4 分組與排序

DuckDB 支援使用 GROUP BY ALL 和 ORDER BY ALL 簡化分組和排序操作。例如，建立一個檢視來計算每日的電力消耗：

CREATE OR REPLACE VIEW v_power_per_day AS
SELECT 
    system_id,
    date_trunc('day', read_on) AS day,
    round(sum(power) / 4 / 1000, 2) AS kWh
FROM readings
GROUP BY ALL;

並對該檢視進行排序：

SELECT system_id, day
FROM v_power_per_day
ORDER BY ALL;

內容解密：

  1. GROUP BY ALL：自動對非聚合欄位進行分組。
  2. ORDER BY ALL：根據查詢結果中的欄位順序進行排序。
  3. 範例：展示瞭如何使用這些功能簡化查詢。

3.5.5 資料取樣

當處理大型資料集時，DuckDB 提供了資料取樣功能。例如，從 readings 表中取樣 10% 的資料：

SELECT power
FROM readings
WHERE power <> 0
USING SAMPLE 10% (bernoulli);

內容解密：

  1. 資料取樣：提供了快速概覽大型資料集的方法。
  2. SAMPLE 子句：支援不同的取樣方法，如 bernoulli 和 system。
  3. 範例：展示瞭如何進行隨機取樣。
玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。