2020年7月1日 星期三

資料反轉置(UNPIVOT)

將反正規結果進行反反正規化,將資料欄位(Column)轉置為資料行(Row),可提供後續運算之用。
先前文章PIVOT(Row to Column)介紹將正規化資料(Row)轉置成反正化格式(Column),實務上常應用於報表呈現外,也應用於資料表設計上,考量資料庫執行效率或寫入效率性時,原本應正規化設計改採反正規化設計模式,將許多相依資料置於同一資料列(Row)上,可減少資料表連結(Table Join)使用以提升效能,但為運算或其他原因需將原本同筆資料的多個資料欄位(Column)資料值轉換成多筆資料列(Row)的方式,即UNPIVOT(Column to Row)以下將說明如何透過SQL指令轉置:

資料庫資料表格型式(反正規化)

希望呈現結果(反反正規化)
表格:訂單日期轉置資料表(四筆資料,三個轉置欄位)
Cust_Name
2009-08-01
2009-08-02
2009-08-03
李先生
9980
13900
13140
張先生
5040
20340
曹先生
8200
7540
陳先生
16200
註:2009-08-02張先生並任無應收帳款,當轉置後結果需將此筆資料剔除。


表格:應收帳款(A/R)彙總表(八筆資料)
Order_Date
Cust_Name
AR
2009-08-01
李先生
9980
2009-08-02
李先生
13900
2009-08-03
李先生
13140
2009-08-01
張先生
5040
2009-08-03
張先生
20340
2009-08-02
曹先生
8200
2009-08-03
曹先生
7540
2009-08-01
陳先生
16200

Column To Row重點在於轉換欄位的欄位名稱資料內容()的重新配置,欄位名稱將成為其屬性值(Attribute),欄位中的資料值則為特徵值(Feature,在此Orders_Pivot資料表中"2009-08-01""2009-08-02""2009-08-03"3個欄位Column)中的應收帳款(AR)值,分別轉入新表格3筆資料列(RowAR欄位中,轉置概念如下圖所示
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)欄位中。

ORACLEMSSQL作法相同,僅在於型態轉換函數語法上的差異,MSSQLCAST函數將日期字串轉換為日期型態,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 --剔除ARNULL值的資料
前述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指令為MSSQL2005ORACLE 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
MSSQLORACLEUNPIVOT語法上並無差異,語法中會將3個欄位(IN-LIST)資料內容()及欄位名稱(屬性)UNPIVOT指令分別轉入AROrder_Date欄位中。前述SQL中,由於使用特殊欄位名稱(字首為數字以及保留字元)需採用雙引號(")包夾,MSSQL尚可使用中括號([ ]),但考量未來資料庫移轉(Migration)可能性,建議採用通用的雙引號(")。


3.   應用笛卡兒積(Cartesian product
部分早期版本(或資料庫)未提供PIVOT函數指令者,如採用前述UNION ALL方式,對於較複雜原SQL或轉置欄位較多時,則SQL將變得極為龐大且無效率,可利用笛卡兒積(Cartesian product產生與欄位數量相同的倍數資料列(Row),再透過CASE運算式將各欄位中資料值逐筆轉入同一欄位(多資料列)。

利用笛卡兒積(Cartesian product概念,本範例Orders_Pivot3個轉置欄位(Column),將與3筆數值序列資料集進行交叉連結(Cross Join)以擴展3倍的原始資料空間Orders_Pivot),在此將應用實體數列資料表Tally,此資料表只包含一個欄位N,主要是儲存自1開始編碼的數值資料表,亦可使用產生數值序列(1~N)文章所述方式動態產生。
SQL

結果
SELECT *
FROM Tally
WHERE N<=3

N
1
2
3

Orders_PivotTally進行交叉連結將產生笛卡兒積,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
Cust_Name
08-01
08-02
08-03
李先生
9980
13900
13140
張先生
5040
20340
曹先生
8200
7540
陳先生
16200
4筆資料,3個轉置欄位)







â
左側兩資料集交叉連結(Cross Join
Cust_Name
08-01
08-02
08-03
N
李先生
9980
13900
13140
1
李先生
9980
13900
13140
2
李先生
9980
13900
13140
3
張先生
5040

20340
1
張先生
5040

20340
2
張先生
5040

20340
3
曹先生

8200
7540
1
曹先生

8200
7540
2
曹先生

8200
7540
3
陳先生
16200


1
陳先生
16200


2
陳先生
16200


3
Orders_Pivot (4筆資料)*Tally(3) = 12資料
數值序列資料集(Tally)
N
1
2
3
3資料,數值1~3
Orders_PivotTally進行交叉連結時,所產生的結果集是以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完成,後續文章將再說明。

對於一般行業或系統,Column To Row的使用頻率並不高,在半導體相關產業中,生產過程中需對晶圓(Wafer)進行,依晶圓大小將抽162599點),儲存此種InlineWP QC data資料,正規化設計上需將各測試結果逐筆儲存,此種設計方法將造成資料量(筆數)大幅成長,由數千萬(或數億)筆測試記錄擴增為數億(或數拾億)筆,此類型常以反正規化方式設計,以大幅減少儲存空間(資料重複儲存)及資料筆數,但後續進行製程能力(Process Capibility)分析時,需計算各抽測試值(點)的平均值(Average)及標準差(Standard Deviation)等,則需對反正規化設計的抽測值結果進行反反正規化,以便用一組SQL計算出CaCpCpk值。

沒有留言:

張貼留言