作為資料庫應用開發人員，我們有很大的精力應付在處理各種各樣的資料型別，展現各種維度的報表上面。

行轉列、列轉行是我們經常會遇到的“詭異”需求。標準SQL沒有提供此型別操作的支援函式，早期Oracle的版本中，我們也只能透過複雜的變通手段實現。在Oracle11g中，這種情況改變了，我們可以利用pivot函式輕鬆實現行列互轉並且聚合的查詢需求。

1、環境準備

我們選擇Oracle 11g進行實驗。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

建立資料表T，進行有選擇的資料篩選。

SQL> create table t (owner varchar2(30), object_type varchar2(100), value number);

Table created

SQL> insert into t select owner, object_type, object_id from dba_objects where object_type in ('TABLE','INDEX');

10386 rows inserted

SQL> commit;

Commit complete

每個owner最多有兩種型別的type值，每個owner和object_type組合包括多條記錄。

SQL> select * from t where rownum<5;

OWNER OBJECT_TYP VALUE

----- ---------- ----------

SYS TABLE 20

SYS INDEX 46

SYS TABLE 28

SYS TABLE 15

要求以owner和object_type進行彙總，並且將TABLE和INDEX分別成列，顯示彙總值。簡而言之，就是一個owner一行記錄，每列對應一種object_type取值。

2、行轉列處理

按照抽絲剝繭的原則，我們聚合一次owner和object_type。

--聚合彙總結

SQL> select owner, object_type, sum(value) from t group by owner, object_type;

OWNER OBJECT_TYP SUM(VALUE)

-------------------- ---------- ----------

SCOTT INDEX 146392

PRITEST TABLE 172006

（篇幅原因，有省略......）

DICT INDEX 18490973

COMMON TABLE 9076700

63 rows selected

下面就是避免不了的行轉列操作了。在11g以前的版本中，我們可能需要藉助專門的複雜SQL書寫乃至儲存過程才能解決。在11g中，我們可以使用pivot函式，一次性的將聚合操作和行轉列操作完成。

select *

from t

pivot

(

sum(value) --聚合操作函式

for object_type --行轉列標準

in ('TABLE','INDEX') --行轉列列取值和順序

)

上面的SQL就是pivot函式的基本格式。看起來非常簡單，主要要定義三個部分的內容。

ü 聚合列取值。需要告訴pivot函式進行轉列的過程中，聚合操作的函式和處理物件；

ü 行轉列標準。依據那個列進行行轉列；

ü 列轉行取值。因為要將資料行取值轉成列，我們需要告訴Oracle那些取值成列，並且這些取值成列的過程中，列順序是如何的；

下面我們看執行結果。

SQL> select *

2 from t

3 pivot

4 (

5 sum(value)

6 for object_type

7 in ('TABLE','INDEX')

8 )

9 ;

OWNER 'TABLE' 'INDEX'

-------------------- ---------- ----------

MDSYS 8003137 7378327

MELODY 299266

RMAN 8292775 18094518

OUTLN 1351 1805

（篇幅原因，有省略......）

COMMON 9076700 17161092

34 rows selected

處理成功，我們檢視一下執行計劃。

SQL> explain plan for select *

2 from t

3 pivot

4 (

5 sum(value)

6 for object_type

7 in ('TABLE','INDEX')

8 );

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3924414983

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 34 | 612 | 13 (8)| 00:00:01 |

| 1 | HASH GROUP BY PIVOT| | 34 | 612 | 13 (8)| 00:00:01 |

| 2 | TABLE ACCESS FULL | T | 10386 | 182K| 12 (0)| 00:00:01 |

----------------------------------------------------------------------------

9 rows selected

從執行計劃上看，Oracle為了pivot函式引入了一個專門操作pivot。執行計劃中的hast group by pivot操作就是將資料轉移到專門的處理區（猜想是PGA）進行流程化的處理。

下面是使用autotrace處理過的情況。

SQL> set autotrace traceonly;

SQL> select *

2 from t

3 pivot

4 (

5 sum(value)

6 for object_type

7 in ('TABLE','INDEX')

8 );

已選擇34行。

執行計劃

----------------------------------------------------------

Plan hash value: 3924414983

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 34 | 612 | 13 (8)| 00:00:01 |

| 1 | HASH GROUP BY PIVOT| | 34 | 612 | 13 (8)| 00:00:01 |

| 2 | TABLE ACCESS FULL | T | 10386 | 182K| 12 (0)| 00:00:01 |

----------------------------------------------------------------------------

統計資訊

----------------------------------------------------------

196 recursive calls

0 db block gets

65 consistent gets

42 physical reads

0 redo size

1503 bytes sent via SQL*Net to client

441 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

34 rows processed

注意在統計資訊中，引用了3的sorts操作。

應該說，pivot函式是我們可以找到比較好的行轉列的操作方式了。在Oracle端，是要消耗一定的排序操作。當資料集合特別大的時候，我們可以猜測會有一些效能的退化（特別是發生sorts(disk)）。Pivot作為標準Oracle函式，Oracle也會不斷進行最佳化處理。所以，效能一般是可以接受的。

3、進一步的需求

在結果集合中，我們看到了結果標題如下：

OWNER 'TABLE' 'INDEX'

-------------------- ---------- ----------

MDSYS 8003137 7378327

MELODY 299266

Oracle是使用兩個列值作為標題。那麼我們結果如何引用這個列

名稱呢？

SQL> select owner, 'TABLE','INDEX' from (

2 select *

3 from t

4 pivot

5 (

6 sum(value)

7 for object_type

8 in ('TABLE','INDEX')

9 )) where rownum<3;

OWNER 'TABLE' 'INDEX'

-------------------- ------- -------

APEX_030200 TABLE INDEX

APPQOSSYS TABLE INDEX

顯然，Oracle將其識別為了字串。應該怎麼做呢？我們需要使用雙引號。之所以Oracle會識別錯誤，就因為’’的常量識別優先順序高於列名稱優先順序。我們需要讓Oracle忽略這個特點，就使用雙引號。

SQL> select owner, 'TABLE' PRI_TYP,"'TABLE'" PRI_AMT,'INDEX' SEC_TYP,"'INDEX'" SEC_AMT from (

2 select *

3 from t

4 pivot

5 (

6 sum(value)

7 for object_type

8 in ('TABLE','INDEX')

9 )) where rownum<3;

OWNER PRI_TYP PRI_AMT SEC_TYP SEC_AMT

-------------------- ------- ---------- ------- ----------

APEX_030200 TABLE 25757431 INDEX 67809456

APPQOSSYS TABLE 25887 INDEX

實現成功！

4、結論

在現實工作中，我們遇到的需求千奇百怪。在選擇工具的時候，要有選擇類庫、預定義提供的方法函式，不要輕易的選擇自定義方法。因為對現成的類庫和產品來說，它在實現這個方法的時候傾注了很大心血和能力在其中，其效率一定是比我們自己寫的要強很多。這也就要求我們需要不斷關注新特性，新方法和改進，不斷的學習和進步。

使用Oracle 11g函式Pivot實現資料聚合行轉列

相關文章