DuckDB 表格函式與 Lateral Join 應用

DuckDB 提供了強大的表格函式和 LATERAL JOIN 功能，可以有效地處理時間序列資料和巢狀資料。range 和 generate_series 函式可以生成數值或日期範圍，方便進行資料填充和同比分析。配合 FILTER 子句，可以精確計算特定條件下的聚合值。LATERAL JOIN 則允許子查詢參考外部查詢的欄位，實作更靈活的資料處理邏輯。例如，可以根據索引從 JSON 陣列中提取特定元素，或者根據時間戳從價格表中查詢最近的有效價格。此外，DuckDB 支援直接查詢外部檔案，例如 CSV、JSON 和 Parquet，並可自動推斷檔案型別和結構，簡化資料探索和分析流程。對於多個結構不同的檔案，DuckDB 提供了 union_by_name 選項，可以根據欄位名稱合併資料，確保資料整合的正確性。

4.9 使用表格函式

在前面的範例中，我們已經廣泛使用了 read_csv*、read_parquet 等函式。其他擴充套件功能，例如空間擴充套件，可以新增到讀取外部資源並產生關係資料的表格函式列表中。

range(start, stop) 和 generate_series(start, stop) 是兩個非常有用的表格函式。兩個函式都建立了一個在 start 和 stop 之間的值列表。start 引數是包含的。對於 range 函式，stop 引數是排除的，而對於 generate_series 則是包含的。兩個函式都提供了過載，帶有額外的第三個引數 step，定義了步長，預設為 1。也有隻接受 stop 引數並預設 start 為 0 的變體。

雖然被用作普通函式，但它們提供了有用的結構，但在像表格一樣查詢時更為強大。

使用 generate_series 函式生成日期範圍

如果你需要一個介於 1 和 5 之間的數字列表，而不想硬編碼，可以使用 SELECT generate_series(1, 5);。數字很有用，但這些函式也適用於時間資料。使用時間資料時，請注意，你需要指定 start 和 end 引數，因為兩者都沒有合理的預設值。

讓我們將其付諸實踐。我們的範例資料中的讀數在 2020 年中旬結束。如果報告是針對整年，則會提前結束，如下所示：

SELECT strftime(day, '%Y-%m') AS month, avg(kwh)
FROM v_power_per_day 
WHERE year(day) = 2020
GROUP BY ALL 
ORDER BY month;

結果如下： ┌─────────┬────────────────────┐ │ month │ avg(kwh) │ │ varchar │ double │ ├─────────┼────────────────────┤ │ 2020-01 │ 222.13169014084497 │ │ 2020-02 │ 133.52356321839076 │ │ 2020-03 │ 207.86670454545438 │ │ 2020-04 │ 309.7838888888888 │ │ 2020-05 │ 349.5753763440861 │ │ 2020-06 │ 337.80820512820515 │ └─────────┴────────────────────┘

如果你需要建立一個圖表，你可能會發現自己需要思考如何表示未來的月份。這裡有一種使用 range() 函式來涵蓋整年並用 0 表示缺失值的方法。

WITH full_year AS (
  SELECT generate_series AS day
  FROM generate_series(
    '2020-01-01'::date, 
    '2020-12-31'::date, 
    INTERVAL '1 day'
  )
)
SELECT 
  strftime(full_year.day, '%Y-%m') AS month,
  avg(kWh) FILTER (kWh IS NOT NULL) AS actual
FROM full_year
LEFT OUTER JOIN v_power_per_day per_day
  ON per_day.day = full_year.day
GROUP BY ALL 
ORDER BY month;

程式碼解析：

1. 建立一個名為 full_year 的 CTE，使用 generate_series 生成從 ‘2020-01-01’ 到 ‘2020-12-31’ 的日期序列，間隔為一天。
2. 將 full_year CTE 與 v_power_per_day 表進行左外連線，以確保包含所有日期，即使某些日期在 v_power_per_day 中沒有對應的記錄。
3. 使用 FILTER 子句計算每月的平均 kWh，並排除空值。
4. 按月份分組並排序結果。

結果如下： ┌─────────┬────────────────────┐ │ month │ actual │ │ varchar │ double │ ├─────────┼────────────────────┤ │ 2020-01 │ 222.13169014084508 │ │ 2020-02 │ 133.52356321839076 │ │ … │ … │ │ 2020-06 │ 337.80820512820515 │ │ 2020-07 │ │ │ … │ │ └─────────┴────────────────────┘

進一步發展這個想法，你可以使用去年同月的資料來預測當月的生產值。這需要將 v_power_per_day 表再次連線，但要減去一年的偏移量，如下所示：

WITH full_year AS (
  SELECT generate_series AS day
  FROM generate_series(
    '2020-01-01'::date, 
    '2020-12-31'::date, 
    INTERVAL '1 day'
  )
)
SELECT 
  strftime(full_year.day, '%Y-%m') AS month,
  round(avg(present.kWh) FILTER (present.kWh IS NOT NULL), 3) AS actual,
  round(avg(past.kWh) FILTER (past.kWh IS NOT NULL), 3) AS forecast
FROM full_year
LEFT OUTER JOIN v_power_per_day present
  ON present.day = full_year.day
LEFT OUTER JOIN v_power_per_day past
  ON past.day = full_year.day - INTERVAL '1 year'
GROUP BY ALL 
ORDER BY month;

程式碼解析：

1. 再次使用 full_year CTE 生成日期序列。
2. 將 v_power_per_day 表連線兩次：一次用於當年資料（present），一次用於去年資料（past），並減去一年的偏移量。
3. 計算每月的平均實際值和預測值，並四捨五入到小數點後三位。
4. 按月份分組並排序結果。

結果如下： ┌─────────┬─────────┬──────────┐ │ month │ actual │ forecast │ │ varchar │ double │ double │ ├─────────┼─────────┼──────────┤ │ 2020-01 │ 222.132 │ 161.593 │ │ … │ … │ … │ │ 2020-06 │ 337.808 │ 351.607 │ │ 2020-07 │ │ 334.323 │ └─────────┴─────────┴──────────┘

使用 LATERAL 連線

在第 4.3 節中，我們學習了相關和無相關子查詢。清單 4.5 示範了無相關子查詢如何與外查詢連線一次。從效能角度來看，這可能是有益的，因為子查詢只需要被評估一次，然後對另一個表的每一行進行連線。

然而，有時你希望針對外查詢的每個值精確評估內查詢。這就是 LATERAL JOIN 發揮作用的地方。你可以將其視為 for 迴圈的內部塊，外查詢是控制結構。

使用 LATERAL 可以處理展開陣列、扇出資料等任務。假設你對太陽的強度感興趣，特別是它在一天中的某些時刻到達你所在地的能量，無論是過去還是未來。Open Meteo 提供了一個免費的 API，提供廣泛的天氣資料，包括所謂的全球水平輻照度（GHI）。

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title DuckDB 表格函式與 Lateral Join 應用

package "Pandas 資料處理" {
    package "資料結構" {
        component [Series
一維陣列] as series
        component [DataFrame
二維表格] as df
        component [Index
索引] as index
    }

    package "資料操作" {
        component [選取 Selection] as select
        component [篩選 Filtering] as filter
        component [分組 GroupBy] as group
        component [合併 Merge/Join] as merge
    }

    package "資料轉換" {
        component [重塑 Reshape] as reshape
        component [透視表 Pivot] as pivot
        component [聚合 Aggregation] as agg
    }
}

series --> df : 組成
index --> df : 索引
df --> select : loc/iloc
df --> filter : 布林索引
df --> group : 分組運算
group --> agg : 聚合函數
df --> merge : 合併資料
df --> reshape : melt/stack
reshape --> pivot : 重新組織

note right of df
  核心資料結構
  類似 Excel 表格
end note

@enduml

4.10 使用 LATERAL 進行進階資料彙總與分析

JSON 資料處理與 LATERAL 應用

首先，我們觀察一個來自 Open-Meteo 的 JSON 回應範例，該範例包含了全球水平輻照度（GHI）資料。該 JSON 資料儲存在檔案 ch04/ghi_past_and_future.json 中，其內容結構如下：

{
  "latitude": 50.78,
  "longitude": 6.0799994,
  "utc_offset_seconds": 7200,
  "timezone": "Europe/Berlin",
  "timezone_abbreviation": "CEST",
  "elevation": 178.0,
  "hourly_units": {
    "time": "iso8601",
    "shortwave_radiation_instant": "W/m²"
  },
  "hourly": {
    "time": [
      "2023-08-26T00:00",
      "2023-08-26T01:00",
      "2023-08-26T02:00",
      "2023-08-26T03:00",
      "2023-08-26T04:00",
      "2023-08-26T05:00"
    ],
    "shortwave_radiation_instant": [
      0.0,
      0.0,
      0.0,
      0.0,
      0.0,
      9.1
    ]
  }
}

載入 JSON 模組並讀取資料

在 DuckDB 中，我們可以透過安裝並載入 json 模組來讀取 JSON 檔案：

INSTALL json;
LOAD json;

SELECT * FROM 'code/ch04/ghi_past_and_future.json';

使用 LATERAL JOIN 處理 JSON 資料

現在，我們希望從 JSON 資料中擷取特定時間點的 GHI 資料，例如每天的早上 8 點、下午 1 點和晚上 7 點。為此，我們可以使用 LATERAL JOIN 結合 generate_series 函式來產生索引，並根據索引從 JSON 資料中擷取對應的值。

WITH days AS (
  SELECT generate_series AS value FROM generate_series(7)
),
hours AS (
  SELECT unnest([8, 13, 18]) AS value
),
indexes AS (
  SELECT days.value * 24 + hours.value AS i
  FROM days, hours
)
SELECT 
  date_trunc('day', now()) - INTERVAL '7 days' + INTERVAL (indexes.i || ' hours') AS ts,
  ghi.v AS 'GHI in W/m^2'
FROM indexes,
LATERAL (
  SELECT hourly.shortwave_radiation_instant[i+1] AS v
  FROM 'code/ch04/ghi_past_and_future.json'
) AS ghi
ORDER BY ts;

LATERAL JOIN 的運作原理

在上述查詢中，LATERAL JOIN 用於將 indexes 表與一個子查詢進行連線，該子查詢根據 indexes 表中的索引值 i 從 JSON 資料中擷取對應的 GHI 值。LATERAL JOIN 的特點是子查詢可以參考外部查詢中的欄位，這使得我們能夠根據 indexes 表中的每一行動態地計算出對應的 GHI 值。

使用 LATERAL JOIN 處理價格資料

另一個使用 LATERAL JOIN 的場景是處理時間序列資料中的價格變動。假設我們有一個 prices 表，記錄了不同時間點的價格變動，而我們希望根據每個銷售記錄的時間點找到最近的價格。

SELECT 
  power.day, 
  power.kWh,
  prices.value AS 'EUR/kWh'
FROM v_power_per_day power,
LATERAL (
  SELECT *
  FROM prices
  WHERE prices.valid_from <= power.day
  ORDER BY valid_from DESC LIMIT 1
) AS prices
WHERE system_id = 34
ORDER BY day;

在這個查詢中，LATERAL JOIN 用於將 v_power_per_day 表與一個子查詢進行連線，該子查詢根據每個銷售記錄的時間點 power.day 從 prices 表中找到最近的有效價格。

在無資料持久化的情況下探索資料

本章節將學習如何在DuckDB中查詢資料而不將資料持久化，這是一種對於資料函式庫來說相當不尋常且違反直覺的技術，但在適當的情況下卻非常有用。例如，當我們需要將資料從一種格式轉換為另一種格式時，我們不一定需要建立中間儲存模型。

本章節也展示了DuckDB分析引擎的強大功能，即使資料並非以原生格式儲存。我們將展示如何查詢多種常見的資料格式，包括JSON、CSV和Parquet，以及其他資料函式庫，如SQLite。

本章節涵蓋以下內容：

• 將CSV檔案轉換為Parquet檔案
• 自動推斷檔案型別和資料結構
• 建立檢視以簡化巢狀JSON檔案的查詢
• 探索Parquet檔案的中繼資料
• 查詢其他資料函式庫，如SQLite

為何在不持久化資料的情況下使用資料函式庫？

在處理儲存在遠端位置的資料時，探索和分析資料而不持久化是有意義的。例如，您可能在Amazon S3中有檔案。我們尚不清楚是否要使用這些資料建立生產管道，因此不想花費大量時間定義資料模型，就像我們在第3章中所做的那樣，並將遠端資料匯入DuckDB的儲存格式。或者它可能包含出於隱私原因而不想持久化的資料。然而，我們仍然希望利用前幾章中學到的關於DuckDB和SQL的知識，以便使用它來瞭解資料的形狀和數量。根據檔案格式和儲存位置，DuckDB甚至可能不需要下載整個檔案內容。在稍後的階段，我們可能會選擇將資料匯入DuckDB。

大多數情況下，您已經在基礎架構中擁有某種資料函式庫。DuckDB能夠使用其他幾個資料函式庫的儲存系統。最值得注意的是SQLite和Postgres整合。前者直接在SQLite儲存檔案上工作，而後者則使用Postgres客戶端-伺服器協定的二進位傳輸模式。無論哪種情況，您都沒有將資料儲存在DuckDB程式中，但仍然能夠利用DuckDB快速的查詢引擎和SQL支援。在許多情況下，對於22個TPC-H基準查詢，DuckDB在使用Postgres整合時比Postgres本身更快（在使用其自己的儲存時，所有查詢都更快）。

自動推斷檔案型別和結構

DuckDB有兩個功能使得處理檔案或檔案集變得非常容易。它可以確定正在讀取的檔案型別（也稱為自動推斷檔案型別）以及檔案中的資料結構：

• 自動推斷檔案型別：使用DuckDB，您可以像使用FROM 'flights.csv';一樣簡單地查詢支援的檔案格式內容。這個功能是開箱即用的，並且支援前幾章中提到的所有SQL子句和函式。當您發出這樣的查詢時，DuckDB首先確定您不是在查詢當前結構描述中的表或檢視。如果檔案存在於檔案系統中，DuckDB使用其副檔名來確設定檔案型別，並呼叫一個適當的函式，該函式知道如何處理該資料格式以讀取檔案。這些函式為行為（例如，在決定資料型別之前要取樣的數量）和格式（例如，日期和時間格式）提供了合理的預設值。如果這些預設值不適合您，請留意表格函式read_csv_auto、read_json_auto和read_parquet_auto。當您發出像前面的查詢時，這些函式會在內部被呼叫。它們提供了大量的引數來更改單個細節，但仍然自動派生列名稱、物件結構等。因此，您可以使用FROM read_json_auto('data*.json')並帶有適當的引數，而不是簡單地查詢FROM 'a_file.csv'或FROM 'data*.json'。

FROM read_json_auto('data*.json');

• 自動推斷結構：DuckDB自動推斷任何我們要求它處理的資料來源的結構。對於像Parquet這樣的資料格式，這比較容易，因為檔案具有DuckDB可以使用的嵌入式結構。對於其他格式，如CSV或JSON，它將從可組態數量的樣本物件中推斷結構。DuckDB還推斷CSV檔案的方言並檢測它們是否包含標題行。如果我們對所做的推斷不完全滿意，我們可以選擇覆寫所有或部分列的型別。在這種情況下，您不會使用純粹的檔名，而是使用“非自動”函式，如read_csv：

FROM read_csv(
    'flights.csv',
    auto_detect=true,
    columns={
        'FlightDate': 'DATE',
        'UniqueCarrier': 'VARCHAR',
        'OriginCityName': 'VARCHAR',
        'DestCityName': 'VARCHAR'
    }
);

內容解密：

此段落程式碼展示瞭如何使用read_csv函式讀取CSV檔案並指定特定列的資料型別。其中：

• auto_detect=true 允許DuckDB自動檢測其他未指定列的資料型別。
• columns 引數用於指定特定列的資料型別，這裡將FlightDate、UniqueCarrier、OriginCityName和DestCityName分別指定為DATE和VARCHAR型別。

DuckDB可以同時讀取不同型別（CSV、Parquet、JSON檔案等）的多個檔案，使用全域語法或提供要讀取的檔案清單。當從多個檔案讀取時，DuckDB必須合併這些檔案的結構。那是因為每個檔案可能有自己的結構，這可能與其他檔案不同。

DuckDB提供了兩種統一多個檔案結構的方法：按列位置和列名稱。預設情況下，DuckDB讀取第一個提供的檔案的結構，然後按列位置統一後續檔案中的列。只要所有檔案具有相同的結構，具有相同的名稱，在相同的列位置上，這就正確工作。否則，您可以使用read_xxx函式的union_by_name選項，它允許DuckDB透過讀取所有名稱來建構結構。

內容解密：

此段落解釋了當處理多個不同結構的檔案時，DuckDB如何統一這些結構。它提供了兩種方法：按列位置和按列名稱，並說明瞭在不同情況下如何選擇適當的方法。

• 當所有檔案具有相同的結構時，預設按列位置的方法是有效的。
• 當檔案具有不同的結構或列順序不同時，應使用union_by_name選項按列名稱統一結構。

-- 使用 union_by_name 選項
FROM read_csv('data*.csv', union_by_name=true);

內容解密：

此SQL陳述式展示瞭如何使用union_by_name=true選項讀取多個CSV檔案，並按列名稱統一它們的結構。這確保了即使列的順序不同，DuckDB也能正確地合併來自不同檔案的資料。