將反正規結果進行反反正規化，將資料欄位(Column)轉置為資料行(Row)，可提供後續運算之用。
先前文章PIVOT(Row to Column)介紹將正規化資料(Row)轉置成反正化格式(Column)，實務上常應用於報表呈現外，也應用於資料表設計上，考量資料庫執行效率或寫入效率性時，原本應正規化設計改採反正規化設計模式，將許多相依資料置於同一資料列（Row）上，可減少資料表連結(Table Join)使用以提升效能，但為運算或其他原因需將原本同筆資料的多個資料欄位（Column）資料值轉換成多筆資料列(Row)的方式，即UNPIVOT(Column to Row)，以下將說明如何透過SQL指令轉置：
資料庫資料表格型式(反正規化)
|
希望呈現結果(反反正規化)
| ||||||||||||||||||||||||||||||||||||||||||||||||
表格：訂單日期轉置資料表（四筆資料，三個轉置欄位）
註：2009-08-02張先生並任無應收帳款，當轉置後結果需將此筆資料剔除。
|
表格：應收帳款（A/R）彙總表（八筆資料）
|
Column To Row重點在於轉換欄位的欄位名稱及資料內容(值)的重新配置，欄位名稱將成為其屬性值（Attribute），欄位中的資料值則為特徵值（Feature），在此將Orders_Pivot資料表中"2009-08-01"、"2009-08-02"及"2009-08-03"等3個欄位（Column）中的應收帳款（AR）值，分別轉入新表格3筆資料列（Row）的AR欄位中，轉置概念如下圖所示：
 |
Column To Row作法將分成以下：
1. UNION ALL
UNION ALL為最簡便、直覺但實用不高之作法，主點在於陳述轉置基本概念，原本以儲存於多個欄位的資料，透過UNION ALL指令可將把資料欄位值(Column)逐一組合以轉換為資料列(Row)。
在本範例將欄位"2009-08-01"、"2009-08-02"及"2009-08-03"共3個欄位(Column)中的應收帳款（AR）資料值轉入新表格中的AR欄位，而欄位名稱也將成為該筆資料的屬性值轉入Order_Date欄位中，再分別使用UNION ALL指令將3組查詢(Statement)合併組出3筆資料列(Row)。如下表的欄位名稱"2009-08-01"將轉入Order_Date欄位，資料值轉入應收帳款（AR）欄位中。
ORACLE與MSSQL作法相同，僅在於型態轉換函數語法上的差異，MSSQL以CAST函數將日期字串轉換為日期型態，ORACLE則直接採用DATE關鍵字。SQL語法如下所示：
SELECT Order_Date, Cust_Name, AR
FROM
(
--第1筆: 由第1個欄位(2009-08-01)，欄位名稱置於Order_Date欄位，資料值置於AR欄位
SELECT CAST('2009-08-01' AS datetime) Order_Date -- SQL SERVER
--DATE'2009-08-01' -- ORACLE
, Cust_Name
, "2009-08-01" AR
FROM Orders_Pivot
UNION ALL
--第2筆: 由第2個欄位(2009-08-02)，欄位名稱置於Order_Date欄位，資料值置於AR欄位
SELECT CAST('2009-08-02' AS datetime) Order_Date
, Cust_Name
, "2009-08-02" AR
FROM Orders_Pivot
UNION ALL
--第3筆: 由第3個欄位(2009-08-03)，欄位名稱置於Order_Date欄位，資料值置於AR欄位
SELECT CAST('2009-08-03' AS datetime) Order_Date
, Cust_Name
, "2009-08-03" AR
FROM Orders_Pivot
) A
WHERE AR IS NOT NULL --剔除AR為NULL值的資料
|
前述SQL中內層SQL可將"2009-08-01"、"2009-08-02"及"2009-08-03"等3個欄位(Column)資料轉換為3筆資料列(Row)，外層（主查詢）SQL功能則在於剔除應收帳款為NULL值的資料列，可參考《資料轉置(PIVOT-Row2Col)》文中之概念將未符合轉置條件值之欄位以NULL取代，在此亦需將未符合轉置條之資料（即NULL）剔除才符合原始資料，本方法概念將3個轉置欄位(Column)分別以3個查詢(Satement)以產生3筆資料列(Row)，反轉置重要概念在於如何將一筆資料中的N個欄位，改變成N筆料列是一項重要關鍵，以一般程式語言則可以利用FOR…LOOP，那麼SQL呢? (答案是笛卡兒積-Cartesian product)。
2. UNPIVOT指令
UNPIVOT指令為MSSQL2005及ORACLE 11g起所提供指令，為PIVOT反向指令，可將PIVOT結果進行反向轉置(Column To Row)，語法說明請參考《資料轉置(PIVOT)》。
MSSQL
|
ORACLE
|
SELECT Order_Date
, Cust_Name
, AR
FROM
(
SELECT Cust_Name
, "2009-08-01"
, "2009-08-02"
, "2009-08-03"
FROM Orders_Pivot
) D
UNPIVOT
(
AR --資料內容
FOR Order_Date --欄位名稱
IN ([2009-08-01]
, [2009-08-02]
, "2009-08-03"
)
) P
|
SELECT Order_Date
, Cust_Name
, AR
FROM
(
SELECT Cust_Name
, "2009-08-01"
, "2009-08-02"
, "2009-08-03"
FROM Orders_Pivot
) D
UNPIVOT
(
AR --資料內容
FOR Order_Date --欄位名稱
IN ("2009-08-01"
, "2009-08-02"
, "2009-08-03"
)
) P
|
MSSQL與ORACLE在UNPIVOT語法上並無差異，語法中會將3個欄位(IN-LIST中)資料內容(值)及欄位名稱(屬性)透過UNPIVOT指令分別轉入AR及Order_Date欄位中。前述SQL中，由於使用特殊欄位名稱(字首為數字以及保留字元)需採用雙引號（"）包夾，MSSQL尚可使用中括號（[ ]），但考量未來資料庫移轉（Migration）可能性，建議採用通用的雙引號（"）。
3. 應用笛卡兒積（Cartesian
product）
部分早期版本(或資料庫)未提供PIVOT函數指令者，如採用前述UNION ALL方式，對於較複雜原SQL或轉置欄位較多時，則SQL將變得極為龐大且無效率，可利用笛卡兒積（Cartesian product）產生與欄位數量相同的倍數資料列(Row)，再透過CASE運算式將各欄位中資料值逐筆轉入同一欄位（多資料列）。
利用笛卡兒積（Cartesian product）概念，本範例Orders_Pivot具3個轉置欄位(Column)，將與3筆數值序列資料集進行交叉連結（Cross Join）以擴展3倍的原始資料空間（Orders_Pivot），在此將應用實體數列資料表Tally，此資料表只包含一個欄位N，主要是儲存自1開始編碼的數值資料表，亦可使用《產生數值序列(1~N)》文章所述方式動態產生。
SQL
|
結果
|
|||||
SELECT *
FROM Tally
WHERE N<=3
|
|
當Orders_Pivot與Tally進行交叉連結將產生笛卡兒積，SQL語法及結果如下表：
SELECT
P.Cust_Name
, P."2009-08-01"
, P."2009-08-02"
, P."2009-08-03"
, I.N
FROM
Orders_Pivot P, Tally I
WHERE
1=1
AND N<=3
ORDER BY
1, 4
|
訂單日期轉置資料表（Orders_Pivot）
（4筆資料，3個轉置欄位）
|
â
|
左側兩資料集交叉連結（Cross Join）
Orders_Pivot (4筆資料)*Tally(3筆) = 12筆資料
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
數值序列資料集(Tally)
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
（3筆資料，數值1~3）
|
當Orders_Pivot與Tally進行交叉連結時，所產生的結果集是以Tally資料列（此範例為3）筆數為基準倍數成長，結果右上表格所示，李先生（灰底部分）由原本一筆資料列變成三筆相同的資料列，與一般程式語言FOR...LOOP概念類似。將同一筆資料列（Row）中的3個欄位，改以3筆資料列兩個資料欄（Column）中，分別存原本資料值（AR）及欄位屬性（Order_Date），分別說明如下：
l 應收帳款（AR）欄位：資料值
根據欄位No值分別取出三個欄位的資料值，當N為時1時，將取出"2009-08-01"欄位的資料值；為2時，則為"2009-08-02"的資料值；當3時，則應為"2009-08-03"，此三個數值將存於應收帳款（AR）欄位中。
l 訂單日期（Order_Date）：屬性
需產生訂單日期（Order_Date）資料值，如同前述，也將利用CASE運算式進行轉換及產生。
l 剔除多餘資料列：最後，以陳先生為例，因資料只有2009-08-01一筆，但仍會產生三筆，其餘欄位均為NULL值，當轉置後則需將這幾筆多餘資料剔除。
由前述說明，SQL語法撰寫如下。
SELECT
Order_Date
, Cust_Name
, AR
FROM
(
SELECT
CASE No WHEN
1 THEN '2009-08-01'
WHEN 2 THEN '2009-08-02'
WHEN 3 THEN '2009-08-03'
END Order_Date--2. 訂單欄位，第1筆為-08-01, 第2筆為-02, 第3筆為-03
, Cust_Name
, CASE No WHEN
1 THEN "2009-08-01"
WHEN 2 THEN
"2009-08-02"
WHEN 3 THEN
"2009-08-03"
END AR--1.應收帳款，第1筆為原欄位2009-08-01，第2筆為原欄位08-02
FROM Orders_Pivot P,
Tally I --進行CROSS JOIN
WHERE 1=1
AND No<=3--轉置欄位數目
) A
WHERE AR IS NOT NULL--3.剔除NULL值資料
|
將本方法的重要觀念整理如下：
l
利用笛卡兒積產生可能的資料集：
利用笛卡兒積的概念，拓展原資料空間以達成部分迴圈的功能，本範例中是利用實體數列資料表（Tally）。在些情況下，並無法建立Tally資料表，亦可使用《產生數值序列(1~N)》文章所述方法產生序列數列。
l 利用CASE運算式進行轉換：
本例利用CASE運算式決定三個欄位資料值，何者為輸出的欄位，透過笛卡兒積的整合，將三個欄位轉入三筆資料列同一欄位中。
本文針對Column To Row提出三種方法。
方法1-UNION ALL
將各個欄位的子查詢進行合併，此方法概念簡單，但轉置欄位數量稍多時，則SQL將非常龐雜。
方法2-內建UNPIVOT函數
因直接使用函數，語法上相對極為簡單易懂，但受限於資料庫版本是否支援。
方法3-應用笛卡兒積的概念
此方法對欄位稍多時，SQL行數不會隨之大幅成長，但難度稍高。
ORACLE的使用者建議可以嘗試使用PL/SQL中的Pipelined Functions，其操作概念與第三種方法雷同，對觀念釐清有所助益，因本書著重於盡量以一組SQL完成，後續文章將再說明。
沒有留言:
張貼留言