DuckDB進階資料彙總與分析技巧

– 10 分鐘閱讀

DuckDB 作為一款高效能的分析型資料函式庫，提供豐富的 SQL 功能和易於使用的介面，讓資料分析工作更加便捷。本文將會探討 DuckDB 的進階資料彙總與分析技巧，從資料匯入時的預處理和清理開始，逐步講解如何使用視窗函式、子查詢、公用表運算式等方法進行更複雜的資料分析。過程中，我們將會使用實際案例來說明如何運用 time_bucket 函式處理時間序列資料、使用 CASE 陳述式來處理異常值和空值，並使用 SUMMARIZE 命令快速獲得資料摘要資訊。此外，我們也將會探討如何在 DuckDB 中有效地使用子查詢，包括標量子查詢、相關子查詢，以及如何將子查詢重寫成等效的 JOIN 操作，並介紹 EXISTS、IN、ANY 等運算子在子查詢中的應用。最後，還會介紹群組集合、ROLLUP 和 CUBE 等進階聚合功能，讓讀者能夠更有效率地分析和匯總資料，從而提取更有價值的資訊。

進階資料彙總與分析

本章旨在探討如何利用分析型資料函式庫（如DuckDB）產生報告，這些報告若以命令式程式語言撰寫，需要耗費大量程式碼。雖然我們會在第三章的基礎上繼續深入，但很快就會超越簡單的SELECT xyz FROM abc查詢。投入時間學習現代SQL不會白費，因為這裡介紹的概念可以在任何支援DuckDB的環境中使用，從而豐富你的應用程式。

本章涵蓋以下主題：

  • 在資料匯入過程中進行準備、清理和彙總
  • 使用視窗函式在不同資料分割槽上建立新的彙總
  • 瞭解不同型別的子查詢
  • 使用公用表運算式（Common Table Expressions）
  • 對任何彙總套用篩選條件

4.1 資料匯入時的預彙總

讓我們繼續我們的範例情境。在3.4.1節中，我們使用了某光伏電網的資料，該資料雖然有一些一致性問題，但仍然適合我們的架構和想法。我們的目標是以15分鐘為間隔儲存測量資料。如果你檢視在3.2.1節下載的其他資料集，你會注意到有些資料的間隔不是15分鐘。

使用 tail 命令快速檢視檔案內容

使用tail命令可以傳回檔案的最後n行（head命令同樣有效）。在2020_10.csv上使用該命令，顯示該檔案包含1分鐘間隔的測量資料：

> duckdb -s ".maxwidth 40" -s "FROM read_csv_auto('2020_10.csv') LIMIT 3"
┌────────┬─────────────────────┬───┬───────────────┬────────────────┐
│ SiteID │ Date-Time           │ ... │ module_temp_3 │ poa_irradiance │
│ int64  │ timestamp           │     │ double        │ double         │
├────────┼─────────────────────┼───┼───────────────┼────────────────┤
│ 10     │ 2020-01-23 11:20:00 │ ... │ 14.971        │ 748.36         │
│ 10     │ 2020-01-23 11:21:00 │ ... │ 14.921        │ 638.23         │
│ 10     │ 2020-01-23 11:22:00 │ ... │ 14.895        │ 467.67         │
├────────┴─────────────────────┴───┴───────────────┴────────────────┤
│ 3 rows 16 columns (4 shown)                                      │
└───────────────────────────────────────────────────────────────────┘

當然，2020_1200.csv有不同的間隔——這次是5分鐘——但整體結構也看起來不同：

> duckdb -s ".maxwidth 40" -s "FROM read_csv_auto('2020_1200.csv') LIMIT 3"
┌────────┬─────────────────────┬───┬──────────────────┬──────────────┐
│ SiteID │ Date-Time           │ ... │ ac_power_metered │ power_factor │
│ int64  │ timestamp           │     │ int64            │ double       │
├────────┼─────────────────────┼───┼──────────────────┼──────────────┤
│ 1200   │ 2020-01-01 00:00:00 │ ... │ 20               │ 0.029        │
│ 1200   │ 2020-01-01 00:05:00 │ ... │ 20               │ 0.029        │
│ 1200   │ 2020-01-01 00:10:00 │ ... │ 20               │ 0.029        │
├────────┴─────────────────────┴───┴──────────────────┴──────────────┤
│ 3 rows 6 columns (4 shown)                                       │
└────────────────────────────────────────────────────────────────────┘

請記住，這些是來自同一批次的資料檔案。即使是來自相同來源的資料，在不同檔案之間也可能存在不一致性。資料分析通常就是要處理這些問題。

使用 time_bucket() 處理日期和時間

讓我們使用DuckDB提供的許多函式之一來處理日期、時間和時間戳——在本例中是time_bucket()。time_bucket()將時間戳截斷到給定的間隔，並根據可選的偏移量對齊它們，從而建立一個時間桶。時間桶是一種強大的機制，用於彙總感測器讀數等資料。結合GROUP BY和avg作為彙總函式，我們可以根據需求準備並最終匯入資料。

使用 time_bucket() 建立15分鐘的時間桶並計算平均功率
SELECT 
    time_bucket('15 minutes', "Date-Time") AS "Time Bucket",
    avg(ac_power_metered) AS "Average Power"
FROM 
    read_csv_auto('2020_1200.csv')
GROUP BY 
    time_bucket('15 minutes', "Date-Time")
ORDER BY 
    "Time Bucket";

內容解密：

  1. time_bucket('15 minutes', "Date-Time")：將Date-Time欄位截斷到15分鐘的間隔，建立時間桶。
  2. avg(ac_power_metered)：計算每個時間桶內的平均功率。
  3. GROUP BY "Time Bucket"：根據建立的時間桶對結果進行分組。
  4. ORDER BY "Time Bucket"：按時間桶排序結果，以便於閱讀。

透過這種方式，我們可以有效地彙總和分析來自不同來源和格式的資料，為進一步的分析和報告奠定基礎。

4. 進階資料彙總與分析

4.1 資料清理與轉換

在進行資料分析時，經常需要對資料進行清理和轉換。DuckDB 提供了強大的功能來實作這一點。

使用 CASE 陳述式處理異常值

在處理資料時，經常會遇到異常值或缺失值。可以使用 CASE 陳述式來處理這些情況。

INSERT INTO readings(system_id, read_on, power)
SELECT 
  any_value(SiteId),
  time_bucket(INTERVAL '15 Minutes', CAST("Date-Time" AS timestamp)) AS read_on,
  avg(
    CASE 
      WHEN ac_power < 0 OR ac_power IS NULL THEN 0 
      ELSE ac_power 
    END
  )
FROM 
  read_csv_auto(
    'https://developer.nrel.gov/api/pvdaq/v3/data_file?api_key=DEMO_KEY&system_id=10&year=2019'
  )
GROUP BY read_on
ORDER BY read_on;

程式碼解析

此查詢使用 CASE 陳述式將小於零或為 NULL 的 ac_power 值轉換為零，然後計算平均值。

  1. any_value(SiteId)：由於 CSV 檔案中的每一行 SiteId 都相同，因此可以使用 any_value 函式選擇任意一個值。
  2. time_bucket(INTERVAL '15 Minutes', CAST("Date-Time" AS timestamp))：將時間戳記截斷到最近的 15 分鐘。
  3. avg(CASE WHEN ac_power < 0 OR ac_power IS NULL THEN 0 ELSE ac_power END)：計算每個時間桶中的 ac_power 平均值，如果值小於零或為 NULL，則視為零。

4.2 資料摘要

在深入分析資料之前，瞭解資料的特性是非常重要的。DuckDB 的 SUMMARIZE 命令可以快速提供資料的相關資訊。

使用 SUMMARIZE 命令

SUMMARIZE readings;

執行此命令後，您將獲得類別似以下的結果：

┌─────────────┬───────────────┬─────────────────────┬───┬─────────┬───────┐
│ column_name │ column_type │ max │ ... │ q75 │ count │
│ varchar │ varchar │ varchar │ │ varchar │ int64 │
├─────────────┼───────────────┼─────────────────────┼───┼─────────┼───────┤
│ system_id │ INTEGER │ 1200 │ ... │ 1200 │ 151879│
│ read_on │ TIMESTAMP │ 2020-06-26 11:00:00 │ ... │ │ 151879│
│ power │ DECIMAL(10,3) │ 133900.000 │ ... │ 5125 │ 151879│
└─────────────┴───────────────┴─────────────────────┴───┴─────────┴───────┘

程式碼解析

  1. SUMMARIZE readings;：對 readings 表執行 SUMMARIZE 命令，提供資料的摘要資訊。
  2. 結果包括欄位名稱、資料型別、最大值、最小值、平均值、標準差、四分位數等統計資訊。

4.3 子查詢

子查詢是一種巢狀在另一個查詢中的查詢，可以用來計算巢狀聚合函式或作為比較的右側運算元。

使用子查詢計算巢狀聚合函式

SELECT avg(sum_per_system)
FROM (
  SELECT sum(kWh) AS sum_per_system
  FROM v_power_per_day
  GROUP BY system_id
);

程式碼解析

  1. SELECT avg(sum_per_system) FROM (...)：計算內部查詢結果的平均值。
  2. SELECT sum(kWh) AS sum_per_system FROM v_power_per_day GROUP BY system_id：內部查詢計算每個系統的總功率。

使用子查詢作為比較的右側運算元

SELECT read_on, power
FROM readings
WHERE power = (SELECT max(power) FROM readings);

程式碼解析

  1. SELECT read_on, power FROM readings WHERE power = (...)：找出功率等於最大功率的日期和功率值。
  2. SELECT max(power) FROM readings：子查詢計算 readings 表中的最大功率值。

進階聚合與資料分析中的子查詢應用

在資料分析與聚合運算中，子查詢（Subquery）扮演著至關重要的角色。子查詢是一種巢狀在主要查詢中的查詢陳述式，用於提供額外的資料篩選條件或運算結果。本章節將探討子查詢的不同型別及其在 DuckDB 中的應用。

標量子查詢與相關子查詢

首先，我們來區分兩種主要的子查詢型別：標量子查詢（Scalar Subquery）與相關子查詢（Correlated Subquery）。

標量子查詢

標量子查詢是指僅傳回單一值的子查詢。這種查詢通常用於簡單的條件判斷或運算表示式中。例如，當我們需要找出某個表格中具有最大或最小值的特定欄位時，可以使用標量子查詢。

SELECT read_on, power
FROM readings
WHERE power = (
  SELECT max(power)
  FROM readings
);

上述查詢會傳回 readings 表格中功率（power）達到最大值的所有紀錄。

相關子查詢

相關子查詢則是內部查詢與外部查詢存在關聯的查詢，通常是透過外部表格的欄位來影響內部查詢的結果。這使得相關子查詢能夠根據外部表格的不同列值傳回不同的結果。

SELECT system_id, read_on, power
FROM readings r1
WHERE power = (
  SELECT max(power)
  FROM readings r2
  WHERE r2.system_id = r1.system_id
)
ORDER BY ALL;

這個範例展示瞭如何找出每個系統（system_id）中功率達到最大值的紀錄。透過使用相關子查詢，我們能夠對每個系統進行獨立的最大值計算。

子查詢重寫為 Join

在某些情況下，子查詢可以被重寫為 Join 操作。以下範例展示瞭如何將前述的相關子查詢轉換為 Join：

SELECT r1.system_id, read_on, power
FROM readings r1
JOIN (
  SELECT r2.system_id, max(power) AS value
  FROM readings r2
  GROUP BY ALL
) AS max_power ON (
  max_power.system_id = r1.system_id AND
  max_power.value = r1.power
)
ORDER BY ALL;

雖然這種重寫方式在某些資料函式庫系統中可能有其效能上的考量，但在 DuckDB 中，由於其具備子查詢去相關最佳化器（Subquery Decorrelation Optimizer），因此開發者無需擔心手動將子查詢重寫為 Join 的問題，可以專注於查詢的可讀性和業務邏輯的實作。

子查詢作為表示式

所有不被用於 Join 的子查詢都可以視為表示式。這意味著它們可以與多種運算子結合使用，例如 IN、EXISTS、ANY 和 ALL，來進行集合比較。

EXISTS

EXISTS 用於檢查子查詢是否傳回至少一行資料。例如：

SELECT * FROM VALUES (7), (11) s(v)
WHERE EXISTS (SELECT * FROM range(10) WHERE range = v);

IN

IN 運算子可用於檢查外部值是否包含在子查詢傳回的結果集中：

SELECT * FROM VALUES (7), (11) s(v)
WHERE v IN (SELECT * FROM range(10));

ANY

ANY 運算子允許你檢查外部值是否滿足與子查詢傳回的任何值之間的特定比較條件：

SELECT * FROM VALUES (7), (11) s(v)
WHERE v <= ANY (SELECT * FROM range(10));

4.4 群組集合（Grouping Sets）與進階資料分析

在前面的章節中，我們建立了一個名為 readings 的表格，用於儲存特定時間的電力生產資料。現在，我們將探討如何使用 SQLite 的進階聚合功能來分析和匯總這些資料。

使用 ALL 運算元進行比較

ALL 運算元用於比較外部值與子查詢中的所有內部值。當且僅當所有比較結果為真時，ALL 運算元才會傳回真。這個運算元可以幫助我們找出滿足特定條件的行。

.mode line
SELECT * FROM VALUES (7), (11) s(v)
WHERE v = ALL (SELECT 7);

內容解密：

  • .mode line 用於設定 SQLite 的輸出模式，使結果以行的方式顯示。
  • SELECT * FROM VALUES (7), (11) s(v) 建立了一個臨時表格 s，包含一個欄位 v，其值分別為 7 和 11。
  • WHERE v = ALL (SELECT 7) 過濾出 v 的值等於子查詢 (SELECT 7) 傳回的所有值的行。在這個例子中，由於子查詢只傳回一個值 7，因此只有當 v 等於 7 時才會被選中。

使用聚合函式檢查資料

我們可以使用聚合函式如 count、min、max 和 sum 來檢查資料的合理性。

SELECT count(*),
min(power) AS min_W, max(power) AS max_W,
round(sum(power) / 4 / 1000, 2) AS kWh
FROM readings;

內容解密：

  • count(*) 統計 readings 表格中的總行數。
  • min(power) 和 max(power) 分別找出 power 欄位中的最小值和最大值。
  • round(sum(power) / 4 / 1000, 2) 將 power 的總和轉換為千瓦時（kWh）。首先，將總和除以 4 以轉換為每小時的瓦特數（Wh），然後除以 1000 以轉換為千瓦時（kWh），並四捨五入到小數點後兩位。

群組集合（Grouping Sets）

群組集合允許我們在單一查詢中對多個群組進行聚合運算。

SELECT year(read_on) AS year,
system_id,
count(*),
round(sum(power) / 4 / 1000, 2) AS kWh
FROM readings
GROUP BY GROUPING SETS ((year, system_id), year, ())
ORDER BY year NULLS FIRST, system_id NULLS FIRST;

內容解密：

  • GROUP BY GROUPING SETS ((year, system_id), year, ()) 指定了三個群組集合：
    • (year, system_id)：按年份和系統 ID 分組。
    • year：按年份分組。
    • ()：空的分組集合，代表整體總計。
  • ORDER BY year NULLS FIRST, system_id NULLS FIRST 對結果進行排序，將 NULL 值放在最前面。

ROLLUP 和 CUBE

SQLite 也支援 ROLLUP 和 CUBE 用於簡化群組集合的定義。

-- 使用 ROLLUP
SELECT year(read_on) AS year,
system_id,
count(*),
round(sum(power) / 4 / 1000, 2) AS kWh
FROM readings
GROUP BY ROLLUP (year, system_id)
ORDER BY year NULLS FIRST, system_id NULLS FIRST;

-- 使用 CUBE
SELECT year(read_on) AS year,
system_id,
count(*),
round(sum(power) / 4 / 1000, 2) AS kWh
FROM readings
GROUP BY CUBE (year, system_id)
ORDER BY year NULLS FIRST, system_id NULLS FIRST;

內容解密：

  • ROLLUP (year, system_id) 自動產生按 (year, system_id)、year 和整體總計的分組集合。
  • CUBE (year, system_id) 自動產生所有可能的分組集合組合，包括 (year, system_id)、year、system_id 和整體總計。
玄貓 BlackCat

玄貓 BlackCat

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