星期一, 12月 23, 2013

[轉載] Oracle 11g new feature Pivot 和 Unpivot

Oracle 11g new feature Pivot Unpivot:
使用行列轉換功能達成樞紐分析

使用簡單的 SQL 以試算表類型的交叉表報表顯示任何關係表中的資訊,並將交叉表中的所有資料存儲到關係表中。
下载

Pivot

如您所知,關係表是表格化的,即,它們以列-值對的形式出現。假設一個表名為 CUSTOMERS
SQL> desc customers
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 CUST_ID                                            NUMBER(10)
 CUST_NAME                                          VARCHAR2(20)
 STATE_CODE                                         VARCHAR2(2)
 TIMES_PURCHASED                                    NUMBER(3)
 
選定該表:
select cust_id, state_code, times_purchased
from customers
order by cust_id;
 
輸出結果如下:
CUST_ID STATE_CODE TIMES_PURCHASED
------- ---------- ---------------
      1 CT                       1
      2 NY                      10
      3 NJ                       2
      4 NY                       4
...  
                              

and so on ...
                            
注意資料是如何以行值的形式顯示的:針對每個客戶,該記錄顯示了客戶所在的州以及該客戶在商店購物的次數。當該客戶從商店購買更多物品時,列 times_purchased 會進行更新。 

現在,假設您希望統計一個報表,以瞭解各個州的購買頻率,即,各個州有多少客戶只購物一次、兩次、三次等等。如果使用常規 SQL,您可以執行以下語句:
select state_code, times_purchased, count(1) cnt
from customers
group by state_code, times_purchased;
輸出如下:
ST TIMES_PURCHASED        CNT
-- --------------- ----------
CT               0         90
CT               1        165
CT               2        179
CT               3        173
CT               4        173
CT               5        152
...  
                              

and so on ...
                            
這就是您所要的資訊,但是看起來不太方便。使用交叉表報表可能可以更好地顯示這些資料,這樣,您可以垂直排列資料,水準排列各個州,就像試算表一樣:
Times_purchased
             CT           NY         NJ      ...  
                              

and so on ...
 
1             0            1          0      ...
2            23          119         37      ...
3            17           45          1      ...
...  
                              

and so on ... 
                            
Oracle 資料庫 11g 推出之前,您需要針對每個值通過 decode 函數進行以上操作,並將每個不同的值編寫為一個單獨的列。但是,該方法一點也不直觀。 

慶倖的是,您現在可以使用一種很棒的新特性 PIVOT 通過一種新的操作符以交叉表格式顯示任何查詢,該操作符相應地稱為 pivot。下面是查詢的編寫方式:
select * from (
   select times_purchased, state_code
   from customers t
)
pivot 
(
   count(state_code)
   for state_code in ('NY','CT','NJ','FL','MO')
)
order by times_purchased
/
輸出如下:
. TIMES_PURCHASED       'NY'       'CT'       'NJ'       'FL'       'MO'
--------------- ---------- ---------- ---------- ---------- ----------
              0      16601         90          0          0          0
              1      33048        165          0          0          0
              2      33151        179          0          0          0
              3      32978        173          0          0          0
              4      33109        173          0          1          0
... and so on ...
這表明了 pivot 操作符的威力。state_codes 作為標題行而不是列顯示。下麵是傳統的表格化格式的圖示:
 
1 傳統的表格化顯示
在交叉表報表中,您希望將 Times Purchased 列的位置掉換到標題行,如圖 2 所示。該列變為行,就好像該列逆時針旋轉 90 度而變為標題行一樣。該象徵性的旋轉需要有一個支點 (pivot point),在本例中,該支點為 count(state_code) 運算式。
 
2 執行了 Pivot 操作的顯示
該運算式需要採用以下查詢語法:
...
pivot 
(
   count(state_code)
   for state_code in ('NY','CT','NJ','FL','MO')
)
...
第二行“for state_code ...”限制查詢物件僅為這些值。該行是必需的,因此不幸的是,您需要預先知道可能的值。該限制在 XML 格式的查詢將有所放寬,如本文後面部分所述。
注意輸出中的標題行:
. TIMES_PURCHASED       'NY'       'CT'       'NJ'       'FL'       'MO'
  --------------- ---------- ---------- ---------- ---------- ----------
列標題是來自表本身的資料:州代碼。縮寫可能已經相當清楚無需更多解釋,但是假設您希望顯示州名而非縮寫(“Connecticut”而非“CT”),那又該如何呢?如果是這樣,您需要在查詢的 FOR 子句中進行一些調整,如下所示:
select * from (
   select times_purchased as "Puchase Frequency", state_code
   from customers t
)
pivot 
(
   count(state_code)
   for state_code in ('NY' as "New York",'CT' "Connecticut",
                  'NJ' "New Jersey",'FL' "Florida",'MO' as "Missouri")
)
order by 1
/
 
Puchase Frequency   New York Connecticut New Jersey    Florida   Missouri
----------------- ---------- ----------- ---------- ---------- ----------
                0      16601         90           0          0          0
                1      33048        165           0          0          0
                2      33151        179           0          0          0
                3      32978        173           0          0          0
                4      33109        173           0          1          0
...  
                              

and so on ...
                            
FOR 子句可以提供其中的值(這些值將成為列標題)的別名。

Unpivot

就像有物質就有反物質一樣,有 pivot 就應該有“unpivot”,對吧?
好了,不開玩笑,但 pivot 的反向操作確實需要。假設您有一個顯示交叉表報表的試算表,如下所示:

Purchase Frequency
New York
Connecticut
New Jersey
Florida
Missouri
0
12
11
1
0
0
1
900
14
22
98
78
2
866
78
13
3
9
...
.





現在,您希望將這些資料載入到一個名為 CUSTOMERS 的關係表中: 
SQL> desc customers
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 CUST_ID                                            NUMBER(10)
 CUST_NAME                                          VARCHAR2(20)
 STATE_CODE                                         VARCHAR2(2)
 TIMES_PURCHASED                                    NUMBER(3)
必須將試算表資料去規範化為關係格式,然後再進行存儲。當然,您可以使用 DECODE 編寫一個複雜的 SQL*:Loader SQL 腳本,以將數據載入到 CUSTOMERS 表中。或者,您可以使用 pivot 的反向操作 UNPIVOT,將列打亂變為行,這在 Oracle 資料庫 11g 中可以實現。
通過一個示例對此進行演示可能更簡單。讓我們首先使用 pivot 操作創建一個交叉表:
  1  create table cust_matrix
  2  as
  3  select * from (
  4     select times_purchased as "Puchase Frequency", state_code
  5     from customers t
  6  )
  7  pivot
  8  (
  9     count(state_code)
 10     for state_code in ('NY' as "New York",'CT' "Conn",
'NJ' "New Jersey",'FL' "Florida",
'MO' as "Missouri")
 11  )
 12* order by 1
您可以查看資料在表中的存儲方式:
SQL> select * from cust_matrix
  2  /
 
Puchase Frequency   New York       Conn New Jersey    Florida   Missouri
----------------- ---------- ---------- ---------- ---------- ----------
                1      33048        165          0          0          0
                2      33151        179          0          0          0
                3      32978        173          0          0          0
                4      33109        173          0          1          0
... and so on ...
這是資料在試算表中的存儲方式:每個州是表中的一個列(“New York”“Conn”等等)。
SQL> desc cust_matrix
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 Puchase Frequency                                  NUMBER(3)
 New York                                           NUMBER
 Conn                                               NUMBER
 New Jersey                                         NUMBER
 Florida                                            NUMBER
 Missouri                                           NUMBER
您需要將該表打亂,使行僅顯示州代碼和該州的購物人數。通過 unpivot 操作可以達到此目的,如下所示:
select *
  from cust_matrix
unpivot
(
  state_counts
    for state_code in ("New York","Conn","New Jersey","Florida","Missouri")
)
order by "Puchase Frequency", state_code
/
輸出如下:
Puchase Frequency STATE_CODE STATE_COUNTS
----------------- ---------- ------------
                1 Conn                165
                1 Florida               0
                1 Missouri              0
                1 New Jersey            0
                1 New York          33048
                2 Conn                179
                2 Florida               0
                2 Missouri              0
...  
                              

and so on ...
                            
注意每個列名如何變為 STATE_CODE 列中的一個值。Oracle 如何知道 state_code 是一個列名?它是通過查詢中的子句知道的,如下所示:
for state_code in ("New York","Conn","New Jersey","Florida","Missouri")
這裡,您指定“New York”“Conn”等值是您要對其執行 unpivot 操作的 state_code 新列的值。我們來看看部分原始資料:
Puchase Frequency   New York       Conn New Jersey    Florida   Missouri
----------------- ---------- ---------- ---------- ---------- ----------
                1      33048        165          0          0          0
當列紐約突然變為一個行中的值時,您會怎樣顯示值 33048 呢?該值應該顯示在哪一列下呢?上述查詢中 unpivot 操作符內的 for 子句上面的子句對此進行了解答。您指定了 state_counts,它就是在生成的輸出中創建的新列的名稱。 

Unpivot 可以是 pivot 的反向操作,但不要以為前者可以對後者所進行的任何操作進行反向操作。例如,在上述示例中,您對 CUSTOMERS 表使用 pivot 操作創建了一個新表 CUST_MATRIX。然後,您對 CUST_MATRIX 表使用了 unpivot,但這並沒有取回原始表 CUSTOMERS 的詳細資訊。相反,交叉表報表以便於您將資料載入到關係表中的不同方式顯示。因此 unpivot 並不是為了取消pivot 所進行的操作。在使用 pivot 創建一個表然後刪除原始表之前,您應該慎重考慮。
unpivot 的某些很有趣的用法超出了通常的強大資料操作功能範圍(如上面的示例)。Amis Technologies Oracle ACE 總監 Lucas Jellema 介紹了如何生成若干行特定資料用於測試。在此,我將對他的原始代碼稍加修改,以顯示英語字母表中的母音:
select value
from
(
    (
        select
            'a' v1,
            'e' v2,
            'i' v3,
            'o' v4,
            'u' v5
        from dual
    )
    unpivot
    (
        value
        for value_type in
            (v1,v2,v3,v4,v5)
    )
)
/
輸出如下:
V
-
a
e
i
o
u
該模型可以擴展為包含任何類型的行生成器。感謝 Lucas 為我們提供了這一巧妙招術。

XML 類型

在上述示例中,注意您指定有效的 state_codes 的方式:
for state_code in ('NY','CT','NJ','FL','MO')
該要求假設您知道 state_code 列中顯示的值。如果您不知道都有哪些值,您怎麼構建查詢呢?
pivot 操作中的另一個子句 XML 可用於解決此問題。該子句允許您以 XML 格式創建執行了 pivot 操作的輸出,在此輸出中,您可以指定一個特殊的子句 ANY 而非文字值。示例如下:
select * from (
   select times_purchased as "Purchase Frequency", state_code
   from customers t
)
                               

pivot xml
(
   count(state_code)
    
                              

for state_code in (any)
)
order by 1
/
                            
輸出恢復為 CLOB 以確保 LONGSIZE 在查詢運行之前設置為大值。

SQL> set long 99999
較之原始的 pivot 操作,該查詢有兩處明顯不同(用粗體顯示)。首先,您指定了一個子句 pivot xml 而不只是 pivot。該子句生成 XML 格式的輸出。其次,for 子句顯示 for state_code in (any) 而非長列表的 state_code 值。該 XML 標記法允許您使用 ANY 關鍵字,您不必輸入 state_code 值。輸出如下: 

Purchase Frequency STATE_CODE_XML
------------------ --------------------------------------------------
                 1 CT
                   lumn>165
                   n>
NY
                   umn>33048
                   mn>
 
                 2 CT
                   lumn>179
                   n>
NY
                   umn>33151
                   mn>
 
... and so on ...
如您所見,列 STATE_CODE_XML XMLTYPE,其中根項目是 。每個值以名稱-值元素對的形式表示。您可以使用任何 XML 分析器中的輸出生成更有用的輸出。
除了 ANY 子句外,您還可以編寫一個子查詢。假設您有一個優先州列表並希望僅選擇這些州的行。您將優先州放在一個名為 preferred_states 的新表中:
SQL> create table preferred_states
  2  (
  3     state_code varchar2(2)
  4  )
  5  /
 
Table created.
 
SQL> insert into preferred_states values ('FL')
  2> /
 
1 row created.
 
SQL> commit;
 
Commit complete.
現在 pivot 操作如下所示:
select * from (
   select times_purchased as "Puchase Frequency", state_code
   from customers t
)
pivot xml
(
   count(state_code)
   for state_code in (select state_code from preferred_states)
)
order by 1
/
for 子句中的子查詢可以是您需要的任何內容。例如,如果希望選擇所有記錄而不限於任何優先州,您可以使用以下內容作為 for 子句:
for state_code in (select distinct state_code from customers)
子查詢必須返回不同的值,否則查詢將失敗。這就是我們指定上述 DISTINCT 子句的原因。

結論

Pivot  SQL 語言增添了一個非常重要且實用的功能。您可以使用 pivot 函數針對任何關係表創建一個交叉表報表,而不必編寫包含大量 decode 函數的令人費解的、不直觀的代碼。同樣,您可以使用 unpivot 操作轉換任何交叉表報表,以常規關係表的形式對其進行存儲。Pivot 可以生成常規文本或 XML 格式的輸出。如果是 XML 格式的輸出,您不必指定 pivot 操作需要搜索的值域。
有關 pivot unpivot 操作的詳細資訊,請參考 Oracle 資料庫 11g SQL 語言參考 

沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...