2020年7月2日 星期四

資料反轉置(UNPIVOT)-應用CROSS APPLY

MSSQL2005起提供APPLY運算子,主要用於子查詢結果資料表值函式(table-valued funtion)兩者間進行連結。運算時將每一筆查詢結果傳入資料表值函式中,將子查詢結果視為左側資料表,資料表值函式則當成右側資料表()再執行結合。APPLY運算子分成以下兩種方法
1.    CROSS APPLY: 相當於使用INNER JOIN
傳回兩個資料表中相符(匹配)的記錄,即傳入資料表值函式可產生子查詢結果其他資料表之資料列(Rows)
2.    OUTER APPLY: 相當於使用LEFT OUTER JOIN
子查詢保留資料表(Preseved Table,將保有所有資料,資料表值函式可能產生或不會產生結果集的資料列(即未符合為NULL)。
前述資料表值函式(table-valued funtion)僅是MSSQL中常見用法,在此將應於資料反轉置(UNPIVOT),如同於資料反轉置(UNPIVOT)》文中案例如下所示。欲將Orders_Pivot資料表中"2009-08-01""2009-08-02""2009-08-03"3個欄位Column)中資料內容為應收帳款(AR)值,分別轉入新表格3筆資料列(RowAR欄位中,而原欄位名稱成為該筆資料的屬性值轉入Order_Date欄位中。
表格:訂單日期轉置資料表(四筆資料,三個轉置欄位)
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
反轉置(UNPIVOT)重要基本概念是轉置欄位(Column)轉換(擴增)為一筆資料列(Row)APPLY運算子應用時會將資料逐筆傳入資料表值函式特性(在此使用MSSQL2008所提供VALUES增加功能:Table Value Constructor)同筆資料中3個欄位(Column)資料透過VALUES增加功能轉換成3筆資料(Row),資料內容將存於AR欄位中,而將資料欄位名稱(屬性)存於Order_Date中。
ORACLE 12C亦開始支援ANSI語法APPLY 運算子,使用方式與MSSQL雷同,資料是由子查詢結果中逐筆傳入,本案例需將3個欄位(Column)轉換成3筆資料(Row)而目前版本未支援MSSQLVALUES增加功能,將透過UNION ALL組合查詢方式產生反轉置之3筆資料列(Row),由於資料為子查詢逐筆傳入,本身無實際資料表,查詢語法上規定需搭配FROM子句,因此將使用虛擬資料表(DAUL)替代,SQL如下所示。
MSSQL
ORACLE
SELECT Cust_Name
     , Order_Date
     , AR
FROM Orders_Pivot
     CROSS APPLY
     (VALUES ("2009-08-01", '2009-08-01')
            , ("2009-08-02", '2009-08-02')
            , ("2009-08-03", '2009-08-03')
     ) X (AR, Order_Date)
WHERE 1=1
      AND X.AR IS NOT NULL
ORDER BY Cust_Name
       , Order_Date

SELECT CUST_NAME
     , Order_Date
     , AR
FROM Orders_Pivot
     CROSS APPLY
     (
    SELECT "2009-08-01" AR          --資料內容
, '2009-08-01' Order_Date--欄位名稱
    FROM DUAL
    UNION ALL
    SELECT "2009-08-02" AR, '2009-08-02'
    FROM DUAL
    UNION ALL
    SELECT "2009-08-03" AR, '2009-08-03'
    FROM DUAL
     )  X
WHERE 1=1
    AND X.AR IS NOT NULL
ORDER BY CUST_NAME
        , Order_Date


沒有留言:

張貼留言