透视转换oracle,用Oracle实现ASH的数据透视图

11g里面有个很有用的新特性，对数据透视图的支持。

简单而言，它可以实现宽表和窄表之间的转化。举一个例子，有一张表记录了全校所有班级所有学生的成绩(A,B,C,D,E)，现在想统计每个班级里每个分数级别对应的学生人数。当然，一个SQL就可以实现：

SELECT class, score, count(*) FROM score_table

GROUP BY class, score;

结果的格式如下：班级 分数 人数

一年一班 A 10

一年一班 B 16

一年一班 C 16

一年一班 D 16

一年一班 E 16

一年二班 A 15

一年二班 B 14

一年二班 C 15

一年二班 D 14

一年二班 E 15

不过，很多人更想要下面的格式班级 A B C D E

一年一班 10 16 16 16 16

一年二班 15 14 15 14 15

第二种格式显然简单明了多了。

用Oracle的新语法，可以实现如下

SELECT *

FROM

(

SELECT class,score,count(*) cnt

FROM score_table

GROUP BY class,score

)

pivot

(

sum(cnt) FOR score IN ('A','B','C','D','E')

)

;

一个更实用的场合是对ASH(active session history)数据的处理上。

一般而言，我们需要一段时间内Top 10的wait event，并掌握其在每个时间片(例如10 seconds)里的分布。这些信息可以通过下面的SQL进行获取:

SELECT

to_char(to_date(trunc(to_char(sample_time,'SSSSS')/10)*10,'SSSSS'),'hh24:mi:ss') start_time

, decode(ash.session_state,'ON CPU','ON CPU',ash.event) event

, count(1)/10 total

FROM

v$active_session_history ash

WHERE

sample_time > sysdate-1/24

GROUP BY trunc(to_char(sample_time,'SSSSS')/10)

, decode(ash.session_state,'ON CPU','ON CPU',ash.event)

;

同样的，我们更习惯将这个结果进行倒置。这同样可以通过pivot来实现:

SELECT * FROM

(SELECT

to_char(to_date(trunc(to_char(sample_time,'SSSSS')/10)*10,'SSSSS'),'hh24:mi:ss') start_time

, decode(ash.session_state,'ON CPU','ON CPU',ash.event) event

, count(1)/10 total

FROM

v$active_session_history ash

WHERE

sample_time > sysdate-1/24

GROUP BY trunc(to_char(sample_time,'SSSSS')/10)

, decode(ash.session_state,'ON CPU','ON CPU',ash.event)

) ash

pivot (sum(total) FOR event IN ('ON CPU' AS TOP1,'PX Deq: Slave Session Stats' AS TOP2))

ORDER BY 1;

下面是一个ASH的例子，系统的工作状态已经一目了然了！TOP EVENT

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

TOP1 cell smart table scan

TOP2 ASM file metadata operation

TOP3 control file sequential read

TOP4 ON CPU

TOP5 enq: XL - fault extent map

TOP6 DFS lock handle

TOP7 cell single block physical read

TOP8 reliable message

TOP9 read by other session

TOP10 latch: shared pool

TIME TOP1 TOP2 TOP3 TOP4 TOP5 TOP6 TOP7 TOP8 TOP9 TOP10

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

00:30:50 1

00:31:30

00:31:40

00:31:50

00:32:10

00:32:30

00:32:50 1

00:34:10

00:34:30 1

00:34:40

00:35:10 1

00:35:40 1

00:37:50 16 34 5 9 2 1 14 3 9

00:38:00 296 95 7 9 3 6 1 6

00:38:10 478 133 15 4 17 7 1

00:38:20 543 71 21 6 8 7 5

00:38:30 531 81 13 2 14 9 1 2

00:38:40 600 30 17 4 2 1 2

00:38:50 592 36 16 4 5 10 1

00:39:00 609 20 12 6 10 4

00:39:10 620 14 13 4 2 10

00:39:20 628 5 10 4 10 1

00:39:30 248 7 4 4 4