Oracle 列数据聚合方法汇总

成功IT人 2017-01-15

网上流传众多Oracle列数据聚合方法,现将各方法整理汇总,以做备忘。

wm_concat

该方法来自wmsys下的wm_concat函数,属于Oracle内部函数,返回值类型varchar2,最大字符数4000。随着版本的变更返回值类型可能会有改动,项目中使用时候最好在新的用户下创建一个函数。

使用方法:

select deptno,wm_concat(ename) from emp group by deptno;

排序方法(未必仅此一种写法):

select *

from (select wm_concat(ename) over(partition by deptno order by empno) val,

row_number() over(partition by deptno order by empno desc) rn,

a.*

from emp a)

where rn = 1;

如果仅是简单聚合数据,可以使用该函数,

优点:效率高。

缺点:

(1)、返回最大字符数4000;

(2)、行数据默认以逗号分隔,可以修改函数更改,但是函数一旦创建不能随意自定义分隔符;

(3)、排序实现复杂且效率低;

(4)、内部聚合混乱。比如:

select wm_concat(col1) col3,wm_concat(col2) col4 from tab;

返回的col3和col4里的聚合数据未必是一一对应的。

zh_concat

该函数是在wm_concat基础上修改返回值类型得到,可以返回clob类型数据,内部实现同wm_concat。优缺点同wm_concat。

listagg

11g新增函数,返回值varchar2,同样受4000字符数限制。但是可以排序,可以指定分隔符。

使用方法:

select deptno,listagg(ename,',') within group(order by empno) from emp group by deptno

优点:

(1)、可排序

(2)、可自定义分隔符

缺点:

(1)、仅11g之后版本可用

(2)、返回最大字符数4000

xmlagg

该方法通过将数据聚合成xml结构,再转换成varchar2或者clob类型。

使用方法:

select deptno,xmlagg(xmlparse(content ename||',' wellformed) order by empno).getstringVal() from emp group by deptno;

select deptno,xmlagg(xmlparse(content ename||',' wellformed) order by empno).getclobval() from emp group by deptno;

优点:

(1)、可排序

(2)、可返回clob类型容纳大数据量数据

(3)、可自定义分隔符

(4)、10g可用

缺点:

(1)、在不排序的情况下效率比wm_concat、zh_concat差

(2)、在排序情况下效率比listagg差

(3)、最终数据在后面或者前面会多一个分隔符,需要再做处理

sys_connect_by_path

借助connect by实现数据聚合。

实现方法:

select deptno, res

from (select rn, level, deptno, sys_connect_by_path(ename, ',') res,

connect_by_isleaf il

from (select row_number() over(partition by deptno order by empno) rn,a.*

from emp a)

start with rn = 1

connect by deptno = prior deptno

and prior rn = rn - 1)

where il = 1

该方法实现复杂,效率低下,这里不再讨论。

总结

不同场景下使用不同方法(最佳选择):

  

10g

11g以上

排序(varchar2)

xmlagg

listagg

排序(clob)

xmlagg

xmlagg

不排序(varchar2)

wm_concat

wm_concat

不排序(clob)

zh_concat

zh_concat

相关推荐