oracle聚合函数汇总

今天在做项目，讲mysql项目转换为oracle项目的时候，发现某些sql语句不兼容

mysql语句如下

SELECT
	COLUMN1,
	group_concat(SHOUYILV ORDER BY CRETE_DATE) AS SHOUYILV
FROM
	RCONFIGDATA
WHERE
	column2 = '1'
GROUP BY
	COLUMN1
ORDER BY
	COLUMN1

  发现上面语句在oracle下不识别group_concat函数，因此需要进行转换，转换语句如下

SELECT
	COLUMN1,listagg(SHOUYILV,',') WITHIN GROUP(ORDER BY CRETE_DATE) AS SHOUYILV
FROM
	RCONFIGDATA
WHERE
	column2 = '1'
GROUP BY
	COLUMN1
ORDER BY
	COLUMN1

 

 各个函数详解

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