在系統設計時，為減少資料庫中資料重複性以及考慮一致性問題，將進行資料庫正規化（Normalization），正規化會將一筆資料中的多個屬性特徵值，改以資料列（Row）方式儲存，但直接以此種格式產生報表，通常不符合報表使用習慣。如左下表格是根據訂單資料表中的單價及數量，所產生的應收帳款（Accounts Receivable；A/R）彙總資料表，但一般常以訂單日期、客戶別等兩種維度產生相關報表，後續將以訂單日期此維度為主要範例，說明如何將原本以資料列形式的資料轉換成以欄位方式呈現的報表型態。
本文介紹如何透過一般基本語法達成資料轉置，藉由CASE運算式、彙總函數與NULL值的特性達成資料轉置的目的，另外《資料轉置(PIVOT)》將介紹MSSQL2005及ORALCE 11g所提供的PIVOT指令也可達成相同目的。以下將說明如何透過SQL指令做轉置（Row To Column）功能。
資料庫資料表格型式
|
客戶希望呈現結果
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
表格：應收帳款（A/R）彙總表
|
â
|
表格：訂單日期轉置資料表
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
表格：客戶姓名轉置資料表
|
此範例將訂單資料表分別以「客戶姓名」及「訂單日期」計算出應收帳款（A/R）加總。以下僅以「訂單日期」為例說明，由訂單日期轉置資料表中，除了客戶姓名（Cust_Name）外，其他欄位是由原本訂單日期（Order_Date）欄位中的資料獨特值（Distinct）轉置產生而成的新欄位，即2009-08-01、2009-08-02及2009-08-03等，而新欄位的資料值是該新欄位所對應的銷售數量（Qty），如以第一筆2009-08-01李先生的訂單為例，會將其訂單數量轉置至2009-08-01欄位所存的資料值，如上表灰底的部分。透過使用CASE運算式將前述三個銷售日期分為轉換指定為新的欄位名稱，而對應的銷售數量為新欄位之資料值，其SQL語法如下：
SELECT Cust_Name
, Order_Date
, AR
, CASE WHEN Order_Date ='2009-08-01' THEN AR END
"2009-08-01"
, CASE WHEN Order_Date ='2009-08-02' THEN AR END
"2009-08-02"
, CASE WHEN Order_Date ='2009-08-03' THEN AR END
"2009-08-03"
FROM
(
--每天、每個客戶合計
SELECT FORMAT(Order_Date, 'yyyy-MM-dd') Order_Date
, Cust_Name
, SUM(Qty * Price ) AS AR
FROM Orders
GROUP BY FORMAT(Order_Date, 'yyyy-MM-dd')
, Cust_Name
) A
ORDER BY Cust_Name
|
Cust_Name
|
Order_Date
|
AR
|
2009-08-01
|
2009-08-02
|
2009-08-03
|
|
1
|
李先生
|
2009-08-01
|
9980
|
9980
|
||
2
|
李先生
|
2009-08-02
|
13900
|
13900
|
||
3
|
李先生
|
2009-08-03
|
13140
|
13140
|
||
4
|
張先生
|
2009-08-01
|
5040
|
5040
|
||
5
|
張先生
|
2009-08-03
|
20340
|
20340
|
||
6
|
曹先生
|
2009-08-02
|
8200
|
8200
|
||
7
|
曹先生
|
2009-08-03
|
7540
|
7540
|
||
8
|
陳先生
|
2009-08-01
|
16200
|
16200
|
由前述結果，以李先生在2009-08-01、02及03分別訂購9980、13900及13140共三筆資料列。
透過前述之CASE WHEN的作法，將三筆訂單日期及應收帳款分別以訂單日期轉置成對應的三個欄位及其資料值，而在未對應到的日期欄位上，則為NULL。轉置語法以2009-08-01為例，語法如下：
CASE WHEN Order_Date ='2009-08-01' THEN AR
|
當訂單日期為2009-08-01時，值設定為應收帳款，若不符合（ELSE）則為NULL，因ELSE是非必項目，範例中將省略，CASE運算式將自動視為NULL。
對於此運算式所產生新的欄位，欄位名稱將採用訂單日期2009-08-01，一般資料庫特性是不允許以數值為首的欄位名稱，但可以用雙引號（"）包夾欄位名稱來指定。
前述SQL語法及結果只是展示轉置概念，同一客戶的訂單資料仍分散於多筆並未整合，以李先生為例，在2009-08-01的三筆資料分別為9980、NULL及NULL，利用一般彙總函數（Aggregate Function）運算時會將NULL值剔除的特性，即可將資料合併，在下列SQL語法中將採用MAX指令，當然也可以採用其他的彙總函數，如MIN、SUM等，其結果也相同。函數特性及說明請參考各資料庫線上說明文件。SQL語法及結果如下：
SELECT Cust_Name
--, Order_Date
--, AR
, MAX(CASE WHEN Order_Date ='2009-08-01' THEN AR END)
"2009-08-01"
, MAX(CASE WHEN Order_Date ='2009-08-02' THEN AR END)
"2009-08-02"
, MAX(CASE WHEN Order_Date ='2009-08-03' THEN AR END)
"2009-08-03"
FROM
(
|
--每天、每個客戶合計
SELECT FORMAT(Order_Date, 'yyyy-MM-dd') Order_Date
, CUST_NAME
, SUM(Qty * Price) AR
FROM Orders
WHERE Order_Date BETWEEN '2009-08-01'
AND CAST('2009-08-03' AS datetime) +1
GROUP BY FORMAT(Order_Date, 'yyyy-MM-dd')
, CUST_NAME
UNION ALL
--每天全部客戶合計
SELECT FORMAT(Order_Date, 'yyyy-MM-dd') Order_Date
, NULL CUST_NAME
, SUM(Qty * Price) AR
FROM Orders
WHERE Order_Date BETWEEN '2009-08-01'
AND CAST('2009-08-03' AS datetime) +1
GROUP BY FORMAT(Order_Date, 'yyyy-MM-dd')
|
) A
GROUP BY Cust_Name
ORDER BY CASE WHEN Cust_Name IS NULL THEN 1 ELSE 0 END
, Cust_Name
|
註: 藍色部分可直接用GROUPING SETS完成，後續文章再行探討。
Cust_Name
|
2009-08-01
|
2009-08-02
|
2009-08-03
|
|
1
|
李先生
|
9980
|
13900
|
13140
|
2
|
張先生
|
5040
|
20340
|
|
3
|
曹先生
|
8200
|
7540
|
|
4
|
陳先生
|
16200
|
||
5
|
31220
|
22100
|
41020
|
另一種常見的SQL寫法如下，為了測試效能，將建立300萬筆測試資料，但在大量資料量時其在效能較前者為差一些(以MSSQL2015、ORACLE11g版本)，可自行以運算邏輯進行計算次數之推論亦可得到相同結果。
SELECT
Cust_Name
, SUM(
CASE WHEN FORMAT(Order_Date, 'yyyy-MM-dd')='2009-08-01' THEN
Qty* Price END
) "2009-08-01"
, SUM(
CASE WHEN FORMAT(Order_Date, 'yyyy-MM-dd') ='2009-08-02' THEN
Qty* Price
END
) "2009-08-02"
, SUM(
CASE WHEN FORMAT(Order_Date, 'yyyy-MM-dd') ='2009-08-03' THEN
Qty* Price
END
) "2009-08-03"
FROM
Orders
WHERE
1=1
AND Order_Date BETWEEN '2009-08-01' AND CAST('2009-08-03' AS datetime) +1
GROUP BY
Cust_Name
|
Oracle語法如下，與SQL
SERVER主要差異在於型態轉換函數的不同，SQL SERVER採用FORMAT()函數，而ORACLE採用TO_CHAR()函數，其他部分則相同不再贅述。
同樣的，若採用其他資料庫如Microsoft Access、IBM DB2及Terdata等做法皆類似，建議讀者必須了解轉置作法的概念，這也對於使用PIVOT()函數的應用能有更深入的了解。
SELECT
Cust_Name
, MAX(CASE WHEN
Order_Date ='2009-08-01' THEN AR END)
"2009-08-01"
, MAX(CASE WHEN
Order_Date ='2009-08-02' THEN AR END)
"2009-08-02"
, MAX(CASE WHEN
Order_Date ='2009-08-03' THEN AR END)
"2009-08-03"
FROM
(
SELECT
TO_CHAR(Order_Date, 'YYYY-MM-DD')Order_Date
, CUST_NAME
, SUM(Qty
* Price) AR
FROM Orders
WHERE
Order_Date BETWEEN DATE'2009-08-01'
AND DATE'2009-08-03' +1
GROUP BY
TO_CHAR(Order_Date, 'YYYY-MM-DD')
, CUST_NAME
UNION ALL
SELECT
TO_CHAR(Order_Date, 'YYYY-MM-DD')
Order_Date
,NULL
CUST_NAME
, SUM(Qty
* Price) AR
FROM Orders
WHERE
Order_Date BETWEEN DATE'2009-08-01'
AND
TO_DATE('2009-08-03', 'YYYY-MM-DD') +1
GROUP BY
Order_Date
) A
GROUP BY
Cust_Name
ORDER BY
Cust_Name NULLS LAST
|
沒有留言:
張貼留言