返回文章列表
玄貓（BlackCat）

DuckDB資料透視與ASOFJOIN進階應用

本文探討 DuckDB 中的 PIVOT 陳述式和 ASOF JOIN 的應用，示範如何使用 PIVOT 陳述式將資料從列轉換為欄，以及如何使用 ASOF JOIN 處理時間序列資料，並結合視窗函式計算累積收益。此外，文章也介紹了 DuckDB 中的表格函式，以及如何取得可用表格函式列表。

資料函式庫 SQL
DuckDB PIVOT ASOF JOIN 資料分析 SQL 表格函式

DuckDB 提供了 PIVOT 陳述式和 ASOF JOIN 等進階功能，能有效地處理資料透視和時間序列資料。PIVOT 陳述式可以根據指定的欄位值將資料從列轉換為欄，方便進行資料比較和分析。ASOF JOIN 則能根據不等式條件連線表格，特別適用於時間戳記不完全匹配的時間序列資料。結合視窗函式，ASOF JOIN 可以進一步計算累積收益等指標。此外，DuckDB 也支援表格函式，可以傳回多行資料，並在 SQL 陳述式中像表格一樣使用，方便處理外部資源和進行更複雜的資料分析。

4.7 PIVOT陳述式

您可以擁有多個聚合函式在一個查詢中，並且每個都可以單獨過濾。這可能幫助您回答這樣的任務：我想獲得每個系統和每年的能源生產報告，並且年份應該是列！按系統聚合生產很容易，按年份聚合生產也很容易。按兩個鍵分組也不難——像這樣的陳述式：

SELECT 
    system_id, 
    year(day), 
    sum(kWh) 
FROM 
    v_power_per_day 
GROUP BY 
    ALL 
ORDER BY 
    system_id;

結果如下： ┌───────────┬─────────────┬────────────────────┐ │ system_id │ year(“day”) │ sum(kWh) │ │ int32 │ int64 │ double │ ├───────────┼─────────────┼────────────────────┤ │ 10 │ 2019 │ 1549.280000000001 │ │ 10 │ 2020 │ 677.1900000000003 │ │ 34 │ 2019 │ 205742.59999999992 │ │ 34 │ 2020 │ 101033.75000000001 │ │ 1200 │ 2019 │ 62012.109999999986 │ │ 1200 │ 2020 │ 30709.329999999998 │ └───────────┴─────────────┴────────────────────┘

雖然我們按系統和年份對資料進行了分組，但每年的資料出現在行中，而不是列中。我們希望有三行，每行有兩列，分別是2019年和2020年的值，就像您在電子試算表程式中找到的前面的資料一樣。這種重新組織表的過程稱為資料透視（pivoting），DuckDB提供了幾種實作方法，其中之一是使用多個過濾後的聚合函式。

使用多個過濾後的聚合函式進行資料透視

SELECT 
    system_id,
    sum(kWh) FILTER (WHERE year(day) = 2019) AS 'kWh in 2019',
    sum(kWh) FILTER (WHERE year(day) = 2020) AS 'kWh in 2020'
FROM 
    v_power_per_day
GROUP BY 
    system_id;

內容解密：

  • 使用FILTER子句對每個聚合函式進行條件篩選，將不同年份的資料分別匯總到不同的列中。
  • 結果是每個系統對應的2019年和2020年的總kWh值，方便進行比較。

結果如下： ┌───────────┬────────────────────┬────────────────────┐ │ system_id │ kWh in 2019 │ kWh in 2020 │ │ int32 │ double │ double │ ├───────────┼────────────────────┼────────────────────┤ │ 10 │ 1549.280000000001 │ 677.1900000000003 │ │ 34 │ 205742.59999999992 │ 101033.75000000001 │ │ 1200 │ 62012.109999999986 │ 30709.329999999998 │ └───────────┴────────────────────┴────────────────────┘

使用DuckDB的PIVOT陳述式

DuckDB提供了PIVOT陳述式，可以動態地對錶進行資料透視。

PIVOT (
    FROM v_power_per_day
)
ON year(day)
USING sum(kWh);

內容解密：

  • PIVOT陳述式動態地將year(day)的不同值轉換為列，並計算每個列的sum(kWh)。
  • 這種方法避免了手動指定每一年份，使查詢更加靈活和動態。

這種方法使得資料可以根據實際資料動態調整列，更加靈活。

進階資料彙總與分析

PIVOT 敘述的應用

在資料分析中，PIVOT 是一種強大的工具，能夠將資料從列轉換為欄，使得資料呈現更加直觀。DuckDB 提供了簡化的 PIVOT 語法，能夠動態地根據資料產生欄位。

簡化 PIVOT 語法

PIVOT v_power_per_day
ON year("day")
USING sum(kWh);

這個查詢會將 v_power_per_day 檢視中的資料根據 day 欄位的年份進行 PIVOT，並計算每個系統在每年中的總發電量。

PIVOT 結果

┌───────────┬────────────────────┬────────────────────┐
│ system_id │ 2019 │ 2020 │
│ int32 │ double │ double │
├───────────┼────────────────────┼────────────────────┤
│ 10 │ 1549.280000000001 │ 677.1900000000003 │
│ 34 │ 205742.59999999992 │ 101033.75000000001 │
│ 1200 │ 62012.109999999986 │ 30709.329999999998 │
└───────────┴────────────────────┴────────────────────┘

內容解密：

  1. PIVOT v_power_per_day 指定了要進行 PIVOT 的資料來源。
  2. ON year("day") 表示根據 day 欄位的年份進行 PIVOT。
  3. USING sum(kWh) 指定了在交叉點計算的聚合函式，這裡是計算總發電量。

多重聚合計算

DuckDB 的 PIVOT 語法還支援多重聚合計算。

PIVOT v_power_per_day
ON year(day)
USING round(sum(kWh)) AS total, max(kWh) AS best_day;

這個查詢不僅計算了每年的總發電量，還找出每年中最高的單日發電量。

結果

┌───────────┬────────────┬───────────────┬────────────┬───────────────┐
│ system_id │ 2019_total │ 2019_best_day │ 2020_total │ 2020_best_day │
│ int32 │ double │ double │ double │ double │
├───────────┼────────────┼───────────────┼────────────┼───────────────┤
│ 10 │ 1549.0 │ 7.47 │ 677.0 │ 6.97 │
│ 34 │ 205743.0 │ 915.4 │ 101034.0 │ 960.03 │
│ 1200 │ 62012.0 │ 337.29 │ 30709.0 │ 343.43 │
└───────────┴────────────┴───────────────┴────────────┴───────────────┘

內容解密：

  1. USING round(sum(kWh)) AS total, max(kWh) AS best_day 同時計算了總發電量和最高單日發電量。
  2. 結果中包含了每年的總發電量和最佳單日發電量，使得資料更加豐富。

ASOF JOIN 的應用

在處理具有時間戳記的資料時，ASOF JOIN 能夠根據最近的時間戳記進行資料匹配。

範例查詢

WITH prices AS (
    SELECT range AS valid_at,
           random()*10 AS price
    FROM range(
        '2023-01-01 01:00:00'::timestamp,
        '2023-01-01 02:00:00'::timestamp, INTERVAL '15 minutes')
),
sales AS (
    SELECT range AS sold_at,
           random()*10 AS num
    FROM range(
        '2023-01-01 01:00:00'::timestamp,
        '2023-01-01 02:00:00'::timestamp, INTERVAL '5 minutes')
)
SELECT sold_at, valid_at AS 'with_price_at', round(num * price,2) as price
FROM sales
JOIN prices ON prices.valid_at = sales.sold_at;

ASOF JOIN 的優勢

ASOF JOIN 能夠解決時間戳記不完全匹配的問題，提供更準確的資料分析結果。

使用 ASOF JOIN 處理時間序列資料

在處理時間序列資料時，內部連線（INNER JOIN）可能會導致資料不完整或不正確的結果。圖 4.5 展示了內部連線的結果，清楚地表明銷售資料相當不理想。

內部連線的限制

┌─────────────────────┬─────────────────────┬────────┐
│ sold_at │ with_price_at │ price │
│ timestamp │ timestamp │ double │
├─────────────────────┼─────────────────────┼────────┤
│ 2023-01-01 01:00:00 │ 2023-01-01 01:00:00 │ 21.17 │
│ 2023-01-01 01:15:00 │ 2023-01-01 01:15:00 │ 12.97 │
│ 2023-01-01 01:30:00 │ 2023-01-01 01:30:00 │ 44.61 │
│ 2023-01-01 01:45:00 │ 2023-01-01 01:45:00 │ 9.45 │
└─────────────────────┴─────────────────────┴────────┘

ASOF JOIN 的介紹

ASOF JOIN（發音為 “as of” JOIN）是一種特殊的連線方式，它根據不等式條件進行連線，選擇一個「足夠好」的數值來填補連線欄位不完全相等的間隙。與內部連線不同，ASOF JOIN 能夠更有效地處理時間序列資料中的時間戳記不完全匹配的問題。

使用 ASOF JOIN 的範例

WITH prices AS (
    SELECT range AS valid_at,
           random()*10 AS price
    FROM range(
        '2023-01-01 01:00:00'::timestamp,
        '2023-01-01 02:00:00'::timestamp, INTERVAL '15 minutes')
),
sales AS (
    SELECT range AS sold_at,
           random()*10 AS num
    FROM range(
        '2023-01-01 01:00:00'::timestamp,
        '2023-01-01 02:00:00'::timestamp, INTERVAL '5 minutes')
)
SELECT sold_at, valid_at AS 'with_price_at', round(num * price,2) as price
FROM sales
ASOF JOIN prices
ON prices.valid_at <= sales.sold_at;

結果分析

此查詢使用 ASOF JOIN 將 sales 表與 prices 表連線，條件是 prices 表中的 valid_at 時間戳記小於或等於 sales 表中的 sold_at 時間戳記。結果如下：

┌─────────────────────┬─────────────────────┬────────┐
│ sold_at │ with_price_at │ price │
│ timestamp │ timestamp │ double │
├─────────────────────┼─────────────────────┼────────┤
│ 2023-01-01 01:00:00 │ 2023-01-01 01:00:00 │ 1.59 │
│ 2023-01-01 01:05:00 │ 2023-01-01 01:00:00 │ 3.56 │
│ 2023-01-01 01:10:00 │ 2023-01-01 01:00:00 │ 2.71 │
│ ... │ ... │ ... │
└─────────────────────┴─────────────────────┴────────┘

圖表說明

此圖示展示了 ASOF JOIN 的工作原理：將四個具有時間戳記的專案 p 與十二個具有時間戳記的專案 v 連線，條件是 p 的時間戳記小於或等於 v 的時間戳記。

ASOF JOIN 的應用場景

ASOF JOIN 特別適用於處理時間序列資料，如股票報價、價格或物聯網感測器資料。在我們的範例中，它用於將變化的銷售價格與系統讀數連線，以計算任何給定時間點的價格。

使用 ASOF JOIN 和視窗函式計算累積收益

SELECT power.day,
       power.kWh,
       prices.value as 'ct/kWh',
       round(sum(prices.value * power.kWh)
             OVER (ORDER BY power.day ASC) / 100, 2)
       AS 'Accumulated earnings in EUR'
FROM v_power_per_day power
ASOF JOIN prices
ON prices.valid_from <= power.day
WHERE system_id = 34
ORDER BY day;

結果展示

結果顯示了日期、產生的千瓦時數（kWh）、當天的價格（以歐分/千瓦時表示）以及累積收益。

┌────────────┬────────┬──────────────┬─────────────────────────────┐
│ day │ kWh │ ct/kWh │ Accumulated earnings in EUR │
│ date │ double │ decimal(5,2) │ double │
├────────────┼────────┼──────────────┼─────────────────────────────┤
│ 2019-01-01 │ 471.4 │ 11.47 │ 54.07 │
│ 2019-01-02 │ 458.58 │ 11.47 │ 106.67 │
│ ... │ ... │ ... │ ... │
└────────────┴────────┴──────────────┴─────────────────────────────┘

使用表格函式

大多數 SQL 函式接受引數並傳回單一值。然而，表格函式（Table Functions）則能夠傳回多行資料，因此可以在任何可以使用表格的地方出現。DuckDB 支援多種表格函式，能夠滿足多種使用場景。

取得可用表格函式列表

SELECT DISTINCT ON(function_name) function_name
FROM duckdb_functions()
WHERE function_type = 'table'
ORDER BY function_name;

此查詢使用 duckdb_functions() 表格函式檢索 DuckDB 安裝中所有可用的表格函式。

結果與應用

透過此查詢，使用者可以獲得當前 DuckDB 環境中所有可用的表格函式列表，從而更好地利用這些功能進行資料處理和分析。表格函式在處理外部資源（如檔案或 URL）並將其轉換為標準 SQL 陳述式中的關係表時尤其有用。