透過反正規化與樞紐轉換資料

重塑資料結構是建立有效分析解決方案的關鍵部分。 去正規化、 樞軸化與 非核心 化是轉換技術，幫助你在正規化與聚合格式間重新組織資料——每種格式都滿足不同的分析與建模需求。

在這個單元中，你會學習如何將正規化表格攤平以加快查詢速度，將列旋轉成欄位以進行交叉表格分析，並在需要正規化廣大資料集時反向處理這個過程。

了解去正規化

正規化資料庫透過將資訊分散到多個相關資料表，減少資料冗餘。 雖然這種設計對交易系統運作良好，但對分析領域卻帶來挑戰。 查詢必須加入多個資料表，這會降低效能並增加複雜度。

去正規化透過將相關資料表扁平化為較少且更寬的表來解決這些挑戰。 你可以直接將維度資料表的資料合併到事實表，這樣就不需要在查詢時需要連接。

考慮一個銷售資料庫，裡面有訂單、顧客和產品的獨立表格。 正規化查詢需要多個連接：

-- Normalized query with multiple joins
SELECT o.order_id,
       o.order_date,
       c.customer_name,
       c.region,
       p.product_name,
       p.category,
       o.quantity,
       o.total_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

非正規化表將這些資訊儲存在一起：

-- Create a denormalized orders table
CREATE OR REPLACE TABLE sales.orders_denormalized AS
SELECT o.order_id,
       o.order_date,
       c.customer_name,
       c.region,
       p.product_name,
       p.category,
       o.quantity,
       o.total_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

對非正規化資料表的查詢會更快，因為不需要加入。 這種方法在為商業智慧工具建構星型結構和資料集市時很常見。 這樣的取捨是儲存空間增加，且在來源資料變更時需要重新整理非正規化的資料表。

將資料列轉換為資料行

樞紐操作將資料行中的唯一值轉換為獨立的資料行，將資料表格式從「長」形式轉換為「寬」形式。 這種轉換在準備跨表格報告或儀表板資料時特別有用。

此 PIVOT 子句需要一個彙總函數，因為多個來源列可能映射到樞軸結果中的同一儲存格。 你可以用 FOR and IN 子句指定哪些欄位值會變成新的欄位名稱。

以長格式儲存的季度銷售數據為例：

-- Create sample sales data
CREATE OR REPLACE TEMP VIEW quarterly_sales(year, quarter, region, revenue) AS
VALUES (2024, 1, 'East', 150000),
       (2024, 2, 'East', 175000),
       (2024, 3, 'East', 160000),
       (2024, 4, 'East', 200000),
       (2024, 1, 'West', 180000),
       (2024, 2, 'West', 165000),
       (2024, 3, 'West', 190000),
       (2024, 4, 'West', 210000),
       (2025, 1, 'East', 165000),
       (2025, 2, 'East', 185000);

-- View data in long format
SELECT * FROM quarterly_sales;

此查詢會為每個季度將收入分別列出。 若要並排比較各季度資料，請將資料樞紐化：

-- Pivot quarters into columns
SELECT year, region, Q1, Q2, Q3, Q4
FROM quarterly_sales
PIVOT (
  SUM(revenue)
  FOR quarter IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4)
);

結果以欄位顯示各地區的季度營收，使得年度比較變得簡單明瞭：

你可以在一次樞紐操作中套用多個聚合函數：

-- Pivot with multiple aggregations
SELECT year, Q1_total, Q1_avg, Q2_total, Q2_avg
FROM (SELECT year, quarter, revenue FROM quarterly_sales)
PIVOT (
  SUM(revenue) AS total,
  AVG(revenue) AS avg
  FOR quarter IN (1 AS Q1, 2 AS Q2)
);

這會產生每季營收總額與平均值的欄位。

將資料行取消樞紐為資料列

取消樞紐執行反向轉換—將資料行轉換回資料列。 當收到需要正規化以進行分析的寬格式資料，或準備資料以供預期長格式的系統使用時，請使用取消樞紐。

此 UNPIVOT 子句將指定的欄位轉換為值-名稱對。 你定義一欄來存放值，另一欄用來存放原始欄位名稱。

-- Create wide-format data
CREATE OR REPLACE TEMP VIEW regional_targets(region, jan, feb, mar, apr) AS
VALUES ('North', 50000, 55000, 52000, 58000),
       ('South', 45000, 48000, 51000, 53000),
       ('East',  60000, 62000, 65000, 68000);

-- Unpivot monthly columns into rows
SELECT *
FROM regional_targets
UNPIVOT (
  target_amount FOR month IN (jan, feb, mar, apr)
);

結果會將每個月欄位轉換成列：

預設情況下， UNPIVOT 排除空值。 要包含它們，請指定 INCLUDE NULLS：

-- Unpivot while keeping null values
SELECT *
FROM regional_targets
UNPIVOT INCLUDE NULLS (
  target_amount FOR month IN (jan, feb, mar, apr)
);

你也可以為非旋轉的欄位名稱指派自訂別名：

-- Unpivot with custom month labels
SELECT *
FROM regional_targets
UNPIVOT (
  target_amount FOR month IN (
    jan AS `January`,
    feb AS `February`,
    mar AS `March`,
    apr AS `April`
  )
);

選擇正確的變身

每種轉換都滿足特定的分析需求。 當你需要儀表板和報告的快速查詢效能時，可以使用非正規化。 當分析師需要跨類別進行並排比較時，選擇樞紐分析表。 當你需要將廣泛資料標準化以供彙整或機器學習管線時，請應用 unpivot。

這些轉換通常在資料管線中協同運作。 您可以對來源資料進行取消樞紐以標準化其格式，套用轉換，然後樞紐化結果以進行最終呈現—或將輸出反正規化為針對下游使用者最佳化的資料表。