2020年6月27日 星期六

資料轉置(PIVOT)

先前文章《資料轉置(PIVOT-Row2Col)曾探討資料轉置(Row to Column)之概念,及如何使用基本指令達成資料轉置目的,MSSQL2005ORACLE 11g(含)以上的版本,提供資料轉置語法PIVOT可直接將資料列轉置為資料欄(Row to Column)的顯示方式,以下分別以銷售日期及客戶兩種轉置方式來說明PIVOT之用法(以下是MSSQLPIVOT語法)。
MSSQL
ORACLE
SELECT …
SELECT …
FROM <Table Source> AS <Tab Src Alias>
FROM <Table Source> AS <Tab Src Alias>
PIVOT
PIVOT
(
(
<agg func>(<Val col>) AS <Alias>
<agg func>(<Val col>) [AS <Alias>]

[,<agg func>(<Val col >) [AS <Alias>]]
FOR <Pivot Column>
FOR <Pivot Column>
IN (
IN (
<Column List>
<Value1> AS <Column Alias>

, <Value2> AS <Column Alias>

, …

, <ValueN> AS <Column Alias>
           )
)
) AS <Pivot Table Alias>
   ) AS < Pivot Table Alias >
WHERE
WHERE

引數
說明
MSSQL
ORACLE
Table Source
需要進行Pivot的資料來源
(資料表、子查詢或CT)


Tab Src Alias
資料來源別名
必要
選擇性
(不可搭配AS)
aggregation function
(Value column)
針對指定欄位執行彙總函數運算,如SUMCOUNTMAX…
1
指定欄位
多組
可為運算(Expr)

彙總計算別名
不可使用
選擇性
使用時則會於轉置欄位自動加上。
Pivot Column
用以轉置成欄位名稱原始資料列欄位


Column List
列出Pivot Column中,將呈現於輸出資料表之資料欄位名稱的值
欄位名稱,使用雙引號(")或中括號([ ])括住欄位名稱,不可搭配別名。
資料值,使用單引號(')括住欄位名稱,但可搭配別名。
Pivot Tab Alias
樞紐資料表的別名



以訂單日期為轉置欄位
SELECT Cust_Name
        , "2009-08-01"
        , "2009-08-02"
        , "2009-08-03"
FROM
(
SELECT CONVERT(CHAR(10),Order_Date, 120) Order_Date
     --FORMAT(Order_Date, 'yyyy-MM-dd') Order_Date
     , Cust_Name
     --, Qty    --必需註解掉,否則結果會乘開
     --, Price  --必需註解掉,否則結果會乘開
     , Qty * Price AR
FROM Orders
WHERE 1=1
      AND Order_Date BETWEEN '2009-08-01' AND '2009-08-04'
)AS D
PIVOT
(
SUM(AR)
--SUM(QTY*PRICE) AS AR  --發生Incorrect syntax near '*'.  錯誤訊息
FOR Order_Date
      IN ([2009-08-01], [2009-08-02], "2009-08-03")
) AS P
ORDER BY Cust_Name COLLATE chinese_taiwan_stroke_cs_as_ks_ws --中文筆劃排序

Cust_Name
2009-08-01
2009-08-02
2009-08-03
1
李先生
9980
13900
13140
2
張先生
5040
20340
3
曹先生
8200
7540
4
陳先生
16200

MSSQLPIVOT功能有以下幾個重點:
l  彙總函數數量限制:只能指定一組彙總函數,以上述範例為SUM(AR)若欲需增加計算總數如SUM(Qty)COUNT(*)則需改用其他方式,另外,不可使用別名(Alias)

l  彙總函數運算式限制:上述範例是加總應收帳款(AR),若直接採用原計算公式即SUM(Qty*Price),則將發生錯誤,運算需在資料來源(Table Source)段先行處理,且除彙總群組、轉置欄位、彙總計算等必要欄位外,其餘欄位均不可出現(彙總運算概念)

l  轉置資料原始欄位(即參數之Pivot Column):不可在Pivot Column執行任何運算,以下語法以銷售日期為例,不可以對其做轉換,需在進行樞紐分析之資料來源做轉換。就合理性來判斷的確為正確做法,但對許多剛使用此指令的使用者會有誤解,這點並不限於MSSQL,在ORACLE也相同。
FOR CONVERT(CHAR(10),OrderDate, 120)
      IN ([2009-08-01], [2009-08-02], "2009-08-03")

l  轉置資料值(即參數之Column List):必需用中括號([ ])或雙引號(")括住欄位名稱,不管是文字、日期或數字型態均需適用,如下所示:
FOR Order_Date
  IN ([2009-08-01], [2009-08-02], "2009-08-03")

l  別名(Alias):資料來源若為子查詢,則需指別名MSSQL子查詢都需指定別名,並不限於PIVOT功能,因ORACLE對子查詢並未要求指定別名,對原本為ORACLE而轉換至MSSQL的使用者或者需做系統移轉(Migration)則需特別注意。

11g(含)以上開始提供PIVOT函數,將前述MSSQLSQL改以ORACLE語法重新撰寫如下:
SELECT *
FROM
    (
    SELECT TO_CHAR(Order_Date, 'YYYY-MM-DD') Order_Date
              , Cust_Name
              , Qty * Price AR --測試1
              --, Qty            --測試2
              --, Price          --測試3
    FROM ORDERS
    WHERE 1=1
           AND Order_Date BETWEEN date'2009-08-01' AND date'2009-08-04'
    ) D
    PIVOT
    (
    SUM(AR)  --AS AR
    --SUM(QTY*PRICE)
    FOR Order_Date
         IN ('2009-08-01' , '2009-08-02' "2019-08-02", '2009-08-03' "08-03")
    ) P
ORDER BY nlssort(Cust_Name,'NLS_SORT=TCHINESE_STROKE_M') --中文筆劃排序 

CUST_NAME
'2009-08-01'
2009-08-02
08-03
1
李先生
9980
13900
13140
2
張先生
5040
20340
3
曹先生
8200
7540
4
陳先生
16200

參數說明請直接參考MSSQL ,以下將列出ORALCEPIVOT語法中額外說明的幾項特點:
l  彙總函數ORACLE允許多組的彙總函數(MSSQL僅限一組),可使用單一欄位(如測試1)或者欄位運算(Expression, 如測試2),但兩者不可混用。

l  別名的使用:可使用,但不可搭配AS關鍵字
先前文章曾介紹資料來源(如資料表)以簡捷且有意義的別名代表,別名可使SQL命令變得比較簡短並可增加可讀性,別名可直接置於資料來源後方使用AS關鍵字明確表示,此兩種別名型式通常均可應用,但PIVOT中的資料來源部分卻發生【ORA-00933:SQL命令結束有問題】的錯誤,在前述語法中,在資料來源別名的AS關鍵字上加上刪除線,代表不可使用AS關鍵字
另外,MSSQL的彙總運算欄位是不可使用別名(Alias),但對於ORACLE則視彙總運算組數而定,一組時為選選性,但多組時為區分結果欄位則必需使用,否則將發生『ORA-00918: column ambiguously defined』錯誤。
彙總函數
ORACLE
MSSQL
1
支援
別名(可選擇)
支援

多組
支援
別名(必需)
不支援

SQL標示灰底部分是與MSSQL最大的差異,下表列出兩種資料庫的語法比較。MSSQLIn-List中的資料為欄位,In-List中的[2009-08-02]"2009-08-03"兩種格式均屬於欄位名稱型式;ORACLE則視為資料值,如In-List中的'2009-08-01'為字串資料,此處的操作如同一般SQL語法將資料值採用欄位別名的方式轉換
資料庫
SQL 命令
MSSQL
FOR OrderDate
    IN ([2009-08-01], [2009-08-02], "2009-08-03")
ORACLE
FOR OrderDate
     IN ('2009-08-01'
           , '200908-02' "2009-08-02"
           , '2009-08-03' "08-03"
         )

先前所介紹PIVOT指令應用均以一個彙總函數為範例,實務上,經常需將多種彙總資料置於同一表格中,ORACLEPIVOT指令提供多欄位(Multiple Columns)的操作,以下範例除計算每個客戶應收帳款(AR)外,並額外顯示訂單筆數,SQL語法及結果如下所示:
SELECT *
FROM
    (
    SELECT TO_CHAR(Order_Date, 'YYYY-MM-DD') OrderDate
          , Cust_Name
          , Qty
          , Price
    FROM Orders
    WHERE 1=1
           AND Order_Date BETWEEN date'2009-08-01' AND date'2009-08-04'
    )
    PIVOT
    (
       SUM(Qty * Price) AS "AR"
       , COUNT(*) AS "Qty" -- 訂單筆量(多運算欄位)
       FOR OrderDate
           IN ('2009-08-01' AS "08-01", '2009-08-02' "08-02", '2009-08-03' "08-03")
) P
ORDER BY nlssort(Cust_Name,'NLS_SORT=TCHINESE_STROKE_M') --中文筆劃排序 

CUST_NAME
08-01_AR
08-01_Qty
08-02_AR
08-02_Qty
08-03_AR
08-03_Qty
1
李先生
9980
2
13900
2
13140
2
2
張先生
5040
1
0
20340
3
3
曹先生
0
8200
2
7540
1
4
陳先生
16200
2
0
0
根據前述SQL語法及結果將重點整理如下:
l   多運算欄位:
在結果中可同時顯示應用帳款(AR)及訂單筆數(Orders
l   欄位名稱名:
在單一運欄位時,若彙總運算並未指定別名時,系統會自系統會以欄位值別名直接命名,因每一種運算將按欄位值別名產生一組欄位名稱,當多組彙總運算時則發生欄位名稱重覆的問題(ORA-00918),為避免此一問題則需針對每一組彙總運算賦予其彙總運算別名,ORACLE會自動將彙總運算別名與欄位值別名串聯,並以底線(_)符號會分隔字元,以產生唯一的欄位名稱。可由上表中08-01_AR欄位是以欄位值別名"08-01""AR"彙總運算別名組合而成。當然單一彙總運算欄位也可對彙總運算指定其彙總運算別名。

由上其實可發現,使用PIVOT進行Row To Column轉置時,此指令雖相當簡便,但有些許限制,如MSSQL不支援多運算欄位,或者自動命名規則等,可參考另一篇資料轉置(PIVOT-Row2Col)以基本函數達成的轉置需求。為比較兩種資料庫資料上方便,因此使用中文筆劃排序法,詳細說明及使用可參考其他文章所述。