天天看點
傳回

oracle 11g 新特性Pivot 和 Unpivot

piovt

如您所知，關系表是表格化的，即，它們以列-值對的形式出現。假設一個表名為 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 <PivotSet><item><column name = "STATE_CODE">CT</co
               lumn><column name = "COUNT(STATE_CODE)">165</colum
               n></item><item><column name = "STATE_CODE">NY</col
               umn><column name = "COUNT(STATE_CODE)">33048</colu
               mn></item></PivotSet>

             2 <PivotSet><item><column name = "STATE_CODE">CT</co
               lumn><column name = "COUNT(STATE_CODE)">179</colum
               n></item><item><column name = "STATE_CODE">NY</col
               umn><column name = "COUNT(STATE_CODE)">33151</colu
               mn></item></PivotSet>

… 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 操作需要搜尋的值域。

oracle-11g oracle 11g pivot
上一篇: oracle 11g acfs,Oracle 11g 新特性 – ACFS 說明
下一篇: Oracle 11g 新特性 -- RMAN Data Recovery Advisor(DRA)

繼續閱讀