Oracle一个典型行列转换的几种实现方法

http://www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html
http://zhu1.blogspot.com/2008/02/oracle.html

Oracle一个典型行列转换的几种实现方法
作者：NinGoo | 【转载须以超链接形式标明文章原始出处和作者信息】

假如有如下表，其中各个i值对应的行数是不定的
SQL> select * from t;

I A D
---------- ---------- -------------------
1 b 2008-03-27 10:55:42
1 a 2008-03-27 10:55:46
1 d 2008-03-27 10:55:30
2 z 2008-03-27 10:55:55
2 t 2008-03-27 10:55:59

要获得如下结果，注意字符串需要按照D列的时间排序：
1 d,b,a
2 z,t

这是一个比较典型的行列转换，有好几种实现方法

1.自定义函数实现
create or replace function my_concat(n number)
return varchar2
is
type typ_cursor is ref cursor;
v_cursor typ_cursor;
v_temp varchar2(10);
v_result varchar2(4000):= '';
v_sql varchar2(200);
begin
v_sql := 'select a from t where i=' || n ||' order by d';
open v_cursor for v_sql;
loop
fetch v_cursor into v_temp;
exit when v_cursor%notfound;
v_result := v_result ||',' || v_temp;
end loop;
return substr(v_result,2);
end;

SQL> select i,my_concat(i) from t group by i;

I MY_CONCAT(I)
---------- --------------------
1 d,b,a
2 z,t

虽然这种方式可以实现需求，但是如果表t的数据量很大，i的值又很多的情况下，因为针对每个i值都要执行一句select，扫描和排序的次数和i的值成正比，性能会非常差。

2.使用sys_connect_by_path
select i,ltrim(max(sys_connect_by_path(a,',')),',') a
from
(
select i,a,d,min(d) over(partition by i) d_min,
(row_number() over(order by i,d))+(dense_rank() over (order by i)) numid
from t
)
start with d=d_min connect by numid-1=prior numid
group by i;

从执行计划上来看，这种方式只需要扫描两次表，比自定义函数的方法，效率要高很多，尤其是表中数据量较大的时候：
eplan1

3.使用wm_sys.wm_concat

这个函数也可以实现类似的行列转换需求，但是似乎没有办法做到直接根据另外一列排序，所以需要先通过子查询或者临时表排好序
SQL> select i,wmsys.wm_concat(a) from t group by i;

I WMSYS.WM_CONCAT(A)
---------- --------------------
1 b,a,d
2 z,t

SQL> select i,wmsys.wm_concat(a)
2 from
3 (select * from t order by i,d)
4 group by i;

I WMSYS.WM_CONCAT(A)
---------- --------------------
1 d,b,a
2 z,t

执行计划上看，只需要做一次表扫描就可以了，但是这个函数是加密过的，执行计划并不能显示函数内部的操作。

不知道大家还有没有更加高效的实现方式，欢迎指教^_^

其他一些方法，可以参考：
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15637744429336

本文网址：http://www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html

#
At 2008.03.30 21:47, yumianfeilong said:

new version ? 11g?
[Reply]

*
At 2008.03.31 10:48, NinGoo said:

我机器上的测试库是11g的，但是上面三种方式在10g也是可以的，9i似乎没有最后一个函数
[Reply]

#
At 2008.03.31 02:25, 木匠 said:

以下是 木匠 的总结.

Title: String aggregates
– A:\scripts\plsql\StringAgg.sql

Sort by performance
1) max(decode())||
2) xmlagg()
3) wmsys.wm_concat()
4) COLLECT()
5) SQL Model
6) SYS_CONNECT_BY_PATH and CONNECT BY PRIOR
7) PL/SQL StrAgg()

串行单线程条件下, COLLECT() 效率很好 , 可惜是它不支持 Parallel Execution.

问过Tom了, 希望下个Oracle版本支持 并行.
11g下, 我还没有来得及Benchmark.
[Reply]

#
At 2008.03.31 02:30, 木匠 said:

提个建议, 标题改一下;

Pivot 是 行列转换, 你可以另外写一篇文章讨论.

String Aggregation 是 字符串 分组 聚合 (直译的 不太好 凑合用吧)
[Reply]

*
At 2008.03.31 10:45, NinGoo said:

多谢木匠兄，还真没注意到Oracle弄了这么多实现方式。标题就算了吧，习惯这么叫了，其实区别不大了，相当于行转列以后又将多列合并成一个字符串了而已，本质上区别不大的
[Reply]

#
At 2008.03.31 12:11, 木匠 said:

还挺犟. -_^
[Reply]

#
At 2008.04.09 10:24, afly said:

select i,wmsys.wm_concat(a) from t group by i;
这个方式不错！！赞一个！
[Reply]


http://blue-prince.spaces.live.com/blog/cns!12D6E6CCFACF4283!1056.entry
wmsys.wm_concat这个函数可能导致RAC节点重启
应用中有个行列转换需要使用wmsys.wm_concat这个函数来做，结果发现一旦使用了这个函数，就会导致相应的节点CPU和内存消耗非常之高，接下主机无法响应，导致节点被踢出重启，甚至其他节点为了防止splitbrain而将自己也重启了。看来这个函数的使用还是需要小心。