2014年3月17日 Oracle一个典型行列转换的几种实现方法 江边潮未尽，枫红一季秋 - NinGoos bloghttp:/www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html1/11首页 留言本 关于 归档Posts Comments江边潮未尽，枫红一季秋NinGoos blogSearch: Go技术 杂记 旅行 生活 工具 数据 2008中国ORACLE数据库精英工程师评选 Feedsky数据异常 Oracle一个典型行列转换的几种实现方法2008年03月30日 by NinGoo 10 Comments假如有如下表，其中各个i值对应的行数是不定的SQL select * from t;I A D - - - 1 b 2008-03-27 10:55:422014年3月17日 Oracle一个典型行列转换的几种实现方法 江边潮未尽，枫红一季秋 - NinGoos bloghttp:/www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html2/111 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) - 2014年3月17日 Oracle一个典型行列转换的几种实现方法 江边潮未尽，枫红一季秋 - NinGoos bloghttp:/www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html3/111 d,b,a 2 z,t虽然这种方式可以实现需求，但是如果表t的数据量很大，i的值又很多的情况下，因为针对每个i值都要执行一句select，扫描和排序的 次数和i的值成正比，性能会非常差。2.使用sys_connect_by_pathselect 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;从执行计划上来看，这种方式只需要扫描两次表，比自定义函数的方法，效率要高很多，尤其是表中数据量较大的时候：3.使用wm_sys.wm_concat这个函数也可以实现类似的行列转换需求，但是似乎没有办法做到直接根据另外一列排序，所以需要先通过子查询或者临时表排好序2014年3月17日 Oracle一个典型行列转换的几种实现方法 江边潮未尽，枫红一季秋 - NinGoos bloghttp:/www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html4/11SQL select i,wmsys.wm_concat(a) from t group by i;I WMSYS.WM_CONCAT(A) - 1 b,a,d 2 z,tSQL 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:1563774442933622014年3月17日 Oracle一个典型行列转换的几种实现方法 江边潮未尽，枫红一季秋 - NinGoos bloghttp:/www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html5/11您可能也喜欢：无觅关联推荐?Filed under 技术 Tagged with oracle10 条评论At 2008.03.30 21:47, yumianfeilong said:new version ? 11g?Reply At 2008.03.31 10:48, NinGoo said:我机器上的测试库是11g的，但是上面三种方式在10g也是可以的，9i似乎没有最后一个函数关于Oracle Companion CDoracle11g for windows版本发布Oracle Database Internals Newsletter October 2007Oracle11gR1 for linux x86 64位版本 可以下载了Oracle11g将于7月 11号在New York正 式发布在Redhat Entrerprise Linux 5上安装Oracle11g 截图欣赏2014年3月17日 Oracle一个典型行列转换的几种实现方法 江边潮未尽，枫红一季秋 - NinGoos bloghttp:/www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html6/11Reply At 2008.03.31 02:25, 木匠 said:以下是 木匠 的总结.Title: String aggregates A:scriptsplsqlStringAgg.sqlSort 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 是 行列转换, 你可以另外写一篇文章讨论.2014年3月17日 Oracle一个典型行列转换的几种实现方法 江边潮未尽，枫红一季秋 - NinGoos bloghttp:/www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html7/11String 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 At 2009.11.26 10:36, Constantine said:2014年3月17日 Oracle一个典型行列转换的几种实现方法 江边潮未尽，枫红一季秋 - NinGoos bloghttp:/www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html8/11字符分组聚合，汗一个！Reply At 2009.12.24 16:59, mq44944 said:第二种方法，如果表没有分析，则是两次全表扫描；如果分析了，则变成三次。 版本9208.比较奇怪。Reply At 2011.03.28 17:22, abc said:最后一种方法是错的 Reply姓名 (Required)Mail (Required, will not be published)站点2014年3月17日 Oracle一个典型行列转换的几种实现方法 江边潮未尽，枫红一季秋 - NinGoos bloghttp:/www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html9/11Say it! Preview扫描二维码关注微信 2014年3月17日 Oracle一个典型行列转换的几种实现方法 江边潮未尽，枫红一季秋 - NinGoos bloghttp:/www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html10/11NinGoo浙江 杭州加关注3月14日 13:15转发(4) | 评论(1)3月13日 23:21转发 | 评论(5)阿里巴巴大数据竞赛天猫推荐算法大挑战，告诉你什么叫big data。玩转真实数据，解决真实问题。更有百万大奖，等你挑战，赶紧报名吧！报名时间：3月10号4月20号。http:/t.cn/8FFabkF http:/t.cn/8swovtH经济受益权，听起来跟虚拟受限股差不多的意思啊招数据产品经理一枚，要求：爱生活，有想法，善沟通。看得懂报表，画得了banner，搞得定开发，抗得住需求哈哈近期文章