DuckDB資料操作語言查詢技巧

– 12 分鐘閱讀

DuckDB 作為一款高效能的嵌入式分析型資料函式庫，其資料操作語言（DML）功能十分強大。除了基本的增刪改查操作外，DuckDB 還提供了一些進階技巧，可以幫助開發者更有效地管理和分析資料。例如，使用範圍函式產生檔案列表，雖然受限於表格函式的實作，但仍有其應用價值。在處理重複資料時，ON CONFLICT DO UPDATE 子句提供了一種優雅的合併資料方式，可以根據特定條件更新現有資料，避免資料冗餘。DELETE 陳述式則可以幫助我們快速清理資料中的異常值，提高資料品質。SELECT 陳述式作為資料函式庫查詢的核心，結合 WHERE、GROUP BY 等子句和聚合函式，可以實作複雜的資料篩選、分組和統計分析。此外，文章還介紹了 VALUES 子句和 JOIN 子句的用法，前者可以方便地生成虛擬表格，後者則可以將多個表格關聯起來，進行更深入的資料分析。

3.4 資料操作語言查詢

在進行資料操作語言（Data Manipulation Language, DML）查詢時，瞭解檢視（view）的定義至關重要。檢視是一種封裝邏輯的絕佳方式，例如將日期截斷至天，並匯總當天的讀取總值，如同我們的範例所示。

3.4.1 使用範圍函式產生檔案列表

若要查詢2020年的資料，基本上與前述查詢相同，只是URL引數不同。我們可以利用範圍函式（range function）產生一個檔案名稱列表，使其像內聯表格一樣運作，像這樣：

SELECT *
FROM (
  SELECT 'https://' || years.range || '.csv' AS v
  FROM range(2019,2021) years
) urls, read_csv_auto(urls.v);

然而，這個查詢理論上正確，卻因DuckDB中對所謂的表格函式（table functions）的實作限制而無法運作。目前為止，它們只接受常數引數。此外，read_csv或read_parquet透過檢視輸入引數和讀取給設定檔案來瞭解其結構，因此存在一個先有雞還是先有蛋的問題。

3.4.2 合併資料

通常，你會遇到包含重複或已存在於資料函式庫中的資料集。雖然可以忽略衝突，如第3.4.1節所示，但當你的任務只是精煉和清理新資料時，有時你會想要將新資料合併到現有資料中。為此，DuckDB提供了ON CONFLICT DO UPDATE子句，在其他資料函式庫中稱為MERGE INTO。在我們的範例中，我們可能有多個來自不同儀表的讀取值，用於相同的系統，並希望計算平均讀取值。這裡，我們使用DO UPDATE而不是什麼都不做。

清單3.8：衝突時計算新值

INSERT INTO readings(system_id, read_on, power)
VALUES (10, '2023-06-05 13:00:00', 4000);

INSERT INTO readings(system_id, read_on, power)
VALUES (10, '2023-06-05 13:00:00', 3000)
ON CONFLICT(system_id, read_on) DO UPDATE
SET power = CASE
  WHEN power = 0 THEN excluded.power
  ELSE (power + excluded.power) / 2 END;

#### 內容解密：

  1. 第一個INSERT陳述式：向readings表中插入一條新記錄，系統ID為10，讀取時間為'2023-06-05 13:00:00’，功率為4000。
  2. 第二個INSERT陳述式：嘗試插入另一條記錄，具有相同的系統ID和讀取時間，但功率為3000。這會導致衝突，因為system_id和read_on的組合已經存在。
  3. ON CONFLICT(system_id, read_on) DO UPDATE：指定當發生衝突時執行的動作。這裡的衝突是根據system_id和read_on的組合鍵。
  4. SET power = CASE：更新power欄位的值，根據現有的power值和新插入的值（透過excluded.power參照）進行計算。如果現有的power為0，則將其設定為新值；否則，計算兩個值的平均值。

3.4.3 DELETE陳述式

在我們使用的資料來源中存在一些異常值。我們匯入了一堆積在不同分鐘測量的讀取值，我們不想要它們在我們的資料集中。處理它們最簡單的方法是應用DELETE陳述式並將它們刪除。

DELETE FROM readings
WHERE date_part('minute', read_on) NOT IN (0,15,30,45);

#### 內容解密：

  1. DELETE FROM readings：從readings表中刪除記錄。
  2. WHERE date_part('minute', read_on) NOT IN (0,15,30,45)：篩選出要刪除的行。date_part('minute', read_on)提取read_on時間戳的分鐘部分。只有當分鐘部分不是0、15、30或45時，才會刪除該行。

3.4.4 SELECT陳述式

本文重點介紹SELECT陳述式和查詢已匯入的資料。該陳述式從資料函式庫中檢索資料作為行，或者以巢狀方式使用時，建立暫時的關係。這些關係可以再次被查詢或用於插入資料。

清單3.9：清理已匯入的資料

清單3.10：SELECT陳述式的結構

SELECT select_list
FROM tables
WHERE condition
GROUP BY groups
HAVING group_filter
ORDER BY order_expr
LIMIT n;

#### 內容解密：

  1. SELECT select_list：指定要檢索的欄位或表示式。
  2. FROM tables：指定資料來源，可以是一個或多個表格。
  3. WHERE condition：根據條件篩選行。
  4. GROUP BY groups：根據指定的欄位或表示式分組結果。
  5. HAVING group_filter：對分組結果應用篩選條件。
  6. ORDER BY order_expr：對結果進行排序。
  7. LIMIT n：限制傳回的行數。

我們認為以下子句對於理解最為重要：

  • FROM與JOIN結合使用
  • WHERE
  • GROUP BY

它們定義了查詢的來源、過濾讀取和寫入查詢，並最終重塑它們。它們在許多情境中被使用，不僅僅是查詢資料。

SELECT和FROM子句

每個標準SQL陳述式如果要讀取資料，都以SELECT子句開始。SELECT子句定義了最終將作為行傳回的欄位或表示式。如果你想從陳述式的來源表格中取得所有內容，可以使用*.

#### 內容解密：

  1. SELECT *：選擇所有欄位。
  2. FROM prices：指定資料來源為prices表格。
  3. LIMIT 2：限制結果傳回的行數為2行。
SELECT *
FROM prices
LIMIT 2;

這個查詢將傳回prices表格中的前兩行。沒有ORDER BY子句時，順序實際上是未定義的，可能在你的例項中有所不同。結果如下所示：

┌───────┬──────────────┬────────────┬─────────────┐
│ id    │ value        │ valid_from │ valid_until │
│ int32 │ decimal(5,2) │ date       │ date        │
├───────┼──────────────┼────────────┼─────────────┤
│ 1     │ 11.59        │ 2018-12-01 │ 2019-01-01  │
│ 10    │ 11.47        │ 2019-01-01 │ 2019-02-01  │
└───────┴──────────────┴────────────┴─────────────┘

執行 SQL 查詢

WHERE 子句

WHERE 子句允許透過新增條件至查詢中來過濾資料。這些條件由一個或多個表示式組成。使用 SELECT、DELETE 或 UPDATE 陳述式選取的資料必須符合這些條件，才能包含在操作中。這使得你能夠只選取感興趣的資料子集。

在我們的範例中，可以用一個適當的條件取代任意的 LIMIT，以包含特定年份（2020）的價格，方法是新增以下 WHERE 子句：

FROM prices
WHERE valid_from BETWEEN '2020-01-01' AND '2020-12-31';

根據我們的範例資料，查詢將傳回 11 行： ┌───────┬──────────────┬────────────┬─────────────┐ │ id │ value │ valid_from │ valid_until │ │ int32 │ decimal(5,2) │ date │ date │ ├───────┼──────────────┼────────────┼─────────────┤ │ 15 │ 8.60 │ 2020-11-01 │ 2023-01-01 │ │ 17 │ 8.64 │ 2020-10-01 │ 2020-11-01 │ │ · │ · │ · │ · │ │ · │ · │ · │ · │ │ · │ · │ · │ · │ │ 25 │ 9.72 │ 2020-02-01 │ 2020-03-01 │ │ 26 │ 9.87 │ 2020-01-01 │ 2020-02-01 │ ├───────┴──────────────┴────────────┴─────────────┤ │ 11 rows (4 shown) 4 columns │ └─────────────────────────────────────────────────┘

內容解密：

  1. WHERE 子句的作用：過濾資料，使查詢結果只包含符合特定條件的資料列。
  2. BETWEEN 關鍵字的使用：用於篩選在某個範圍內的資料，例如日期或數值範圍。
  3. 日期範圍查詢：範例中使用 valid_from BETWEEN '2020-01-01' AND '2020-12-31'，表示查詢 valid_from 日期在 2020 年內的資料。

GROUP BY 子句

GROUP BY 子句用於根據一個或多個欄位將資料分組，產生每個分組的匯總結果。透過使用聚合函式（如 count、sum、avg、min 或 max），可以計算每個分組的匯總值。

使用 GROUP BY 的範例

SELECT date_part('year', valid_from) AS year,
       min(value) AS minimum_price,
       max(value) AS maximum_price
FROM prices
WHERE date_part('year', valid_from) BETWEEN 2019 AND 2020
GROUP BY year
ORDER BY year;

查詢結果如下： ┌───────┬───────────────┬───────────────┐ │ year │ minimum_price │ maximum_price │ │ int64 │ decimal(5,2) │ decimal(5,2) │ ├───────┼───────────────┼───────────────┤ │ 2019 │ 9.97 │ 11.47 │ │ 2020 │ 8.60 │ 9.87 │ └───────┴───────────────┴───────────────┘

內容解密：

  1. date_part 函式的使用：用於從日期欄位中提取特定的部分，例如年份。
  2. 別名（Alias）的使用：使用 AS 關鍵字為欄位指定別名，使查詢結果更易讀。
  3. GROUP BY 分組邏輯：根據 year 分組，並計算每個分組中的最小值和最大值。
  4. ORDER BY 的作用：對查詢結果進行排序，使結果更具可讀性。

常見聚合函式介紹

DuckDB 提供多種聚合函式，包括但不限於：

  • list：將每個分組的值聚合成列表。
  • any_value：從非分組欄位中選擇任意值。
  • arg_max 和 arg_min：找出具有最大值或最小值的行的表示式值。
  • 統計相關函式，如 median、quantile 等。

使用聚合函式的技巧

  1. 靈活使用聚合函式：根據需求選擇合適的聚合函式，例如計算平均值、總和或極值。
  2. 結合 GROUP BY 使用：透過分組來產生更有意義的統計結果。

透過這些 SQL 功能，可以對資料進行強大的分析和處理，從而獲得有價值的洞察。

資料操作語言查詢（Data Manipulation Language Queries）

撰寫可攜式SQL時，建議使用專門的函式，因為它們更易於閱讀。

VALUES 子句

VALUES 子句用於指定固定數量的列。在插入資料時，這是一種常見的用法。然而，在 DuckDB 中，它比其他資料函式庫更具多樣性，可以用作獨立的陳述式，也可以作為 FROM 子句的一部分，具有任意數量的列和欄位。

在某些情況下，這非常方便，例如提供條件的初始資料。

以下是一個簡單的例子，定義一個具有兩個欄位的單行：

VALUES (1,2);

輸出結果如下： ┌───────┬───────┐ │ col0 │ col1 │ │ int32 │ int32 │ ├───────┼───────┤ │ 1 │ 2 │ └───────┴───────┘

內容解密：

這個查詢使用 VALUES 子句建立一個虛擬表格，包含一列兩欄的資料。輸出的表格中，col0 和 col1 是自動生成的欄位名稱，分別對應輸入的值 1 和 2。

多個列可以透過列舉多個元組來生成：

VALUES (1,2), (3,4);

輸出結果如下： ┌───────┬───────┐ │ col0 │ col1 │ │ int32 │ int32 │ ├───────┼───────┤ │ 1 │ 2 │ │ 3 │ 4 │ └───────┴───────┘

內容解密：

這個查詢建立了一個包含兩列的虛擬表格。VALUES 子句中的每個元組代表一列資料。

如果將元組包在額外的括號中，例如 VALUES ((1,2), (3,4));，則會建立一個具有兩個欄位的單行，每個欄位包含一個結構化型別： ┌────────────────────────────────┬────────────────────────────────┐ │ col0 │ col1 │ │ struct(v1 integer, v2 integer) │ struct(v1 integer, v2 integer) │ ├────────────────────────────────┼────────────────────────────────┤ │ {‘v1’: 1, ‘v2’: 2} │ {‘v1’: 3, ‘v2’: 4} │ └────────────────────────────────┴────────────────────────────────┘

內容解密：

在這個例子中，VALUES 子句建立了一個單行，具有兩個欄位，每個欄位是一個結構化型別，包含兩個整數值。

在 FROM 子句中使用 VALUES 子句時，可以命名產生的欄位。下面的範例定義了一個具有三個欄位的兩列，並建立了一個內聯的命名錶格：

SELECT *
FROM (VALUES
(1, 'Row 1', now()),
(2, 'Row 2', now())
) t(id, name, arbitrary_column_name);

輸出結果如下： ┌───────┬─────────┬────────────────────────────┐ │ id │ name │ arbitrary_column_name │ │ int32 │ varchar │ timestamp with time zone │ ├───────┼─────────┼────────────────────────────┤ │ 1 │ Row 1 │ 2023-06-02 13:44:30.309+02 │ │ 2 │ Row 2 │ 2023-06-02 13:44:30.309+02 │ └───────┴─────────┴────────────────────────────┘

內容解密：

這個查詢使用 VALUES 子句建立一個虛擬表格，並在 FROM 子句中命名欄位。輸出的表格具有指定的欄位名稱和對應的資料型別。

JOIN 子句

雖然在分析單一 Parquet 或 CSV 檔案時可以不使用 JOIN 子句，但這並不代表可以跳過本文：JOIN 是用於連線兩個表格或關係的基本關係運算。關係被稱為 JOIN 的左側和右側，左側是首先列出的表格。

JOIN 建立了來自兩側的匹配行對，通常根據左側表格中的某個鍵欄位與右側表格中的某個欄位相等。外部索引鍵約束並不是 JOIN 表格的必要條件。

我們更喜歡使用根據 JOIN .. USING 的 SQL 標準定義，而不是 JOIN .. ON 子句，如下面的範例所示。然而，JOIN 可以透過簡單地在 FROM 子句中列舉表格並在 WHERE 子句中比較鍵欄位來表達。

注意：

我們不使用文氏圖來解釋 JOIN，因為 JOIN 操作不是純粹的集合運算，而文氏圖更適合用於集合運算。SQL 知道集合運算，如 UNION、INTERSECT 和 EXCEPT，而 DuckDB 支援所有這些運算。JOIN 操作則根據關係代數中的笛卡兒積，或者簡單來說，它們根據將所有東西與其他所有東西連線起來，然後過濾掉不需要的東西。

最簡單的 JOIN 方式是 INNER JOIN（圖 3.3），它也是預設的 JOIN 方式。INNER JOIN 將左側的所有行與右側具有相同值的行進行匹配。

圖示：INNER JOIN 示意圖

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title DuckDB資料操作語言查詢技巧

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

此圖示展示了 INNER JOIN 的基本原理，即根據匹配條件將兩個表格的資料結合起來。

如果兩個關係都有相同名稱的欄位，可以使用 USING 子句來指定。這樣可以簡化 JOIN 操作，避免手動指定 ON 子句中的條件。

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

輸出結果如下： ┌───────┬─────────┬─────────┐ │ id │ nameA │ nameB │ │ int32 │ varchar │ varchar │ ├───────┼─────────┼─────────┤ │ 1 │ a1 │ b1 │ │ 2 │ a2 │ b2 │ └───────┴─────────┴─────────┘

內容解密：

這個查詢使用 INNER JOIN 將兩個虛擬表格根據 id 欄位進行匹配。USING 子句指定了匹配條件，即 id 欄位相等。

相對地，OUTER JOIN 則會在指定的關係側沒有匹配條目時補充 NULL 值。例如，當需要列出所有系統及其可選的供應商資訊時，可以使用 OUTER JOIN。如果某個系統沒有供應商資訊，則會在供應商欄位中顯示 NULL 值。

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);

輸出結果如下： ┌───────┬─────────┬─────────┐ │ id │ nameA │ nameB │ │ int32 │ varchar │ varchar │ ├───────┼─────────┼─────────┤ │ 1 │ a1 │ b1 │ │ 2 │ a2 │ b2 │ │ 3 │ a3 │ NULL │ └───────┴─────────┴─────────┘

內容解密：

這個查詢使用 LEFT OUTER JOIN 將左側表格的所有行與右側表格進行匹配。如果右側表格中沒有匹配的行，則會在相應的欄位中顯示 NULL 值。

玄貓 BlackCat

玄貓 BlackCat

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