listagg 函数--oracle 11g release 2
本文描述了在oracle 11g release 2 版本中新增的listagg函数,listagg是一个实现字符串聚合的oracle内建函数。作为一种普遍的技术,网络上也有多种实现字符串聚合的方法。本文会首先介绍listagg函数,最后会拿这些方法与listagg进行性能方面的对比。
样例数据
本文的例子将使用如下的样例数据:
DEPTNO ENAME HIREDATE
---------- ---------- ----------
10 CLARK 09/06/1981
10 KING 17/11/1981
10 MILLER 23/01/1982
20 ADAMS 12/01/1983
20 FORD 03/12/1981
20 JONES 02/04/1981
20 SCOTT 09/12/1982
20 SMITH 17/12/1980
30 ALLEN 20/02/1981
30 BLAKE 01/05/1981
30 JAMES 03/12/1981
30 MARTIN 28/09/1981
30 TURNER 08/09/1981
30 WARD 22/02/1981
字符串聚合
字符串聚合就是按照分组把多行数据串联成一行,以下面的结果集为例:
DEPTNO ENAME
--------- ----------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
按照DEPTNO字段分组,对结果集进行字符串聚合,结果如下:
DEPTNO AGGREGATED_ENAMES
--------- -------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES
可以看到,employee names基于deptno分组实现了串联,如前所述,有很多种方法实现聚合功能(文章最后提供相关链接),但是listagg更为简单,易用。
listagg 语法概述
listagg函数的语法结构如下:
LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]
listagg虽然是聚合函数,但可以提供分析功能(比如可选的OVER()子句)。使用listagg中,下列中的元素是必须的:
- 需要聚合的列或者表达式
- WITH GROUP 关键词
- 分组中的ORDER BY子句
下面将演示listagg函数使用的例子
listagg 作为聚合函数
下面以EMP表为例,按照部门分组聚合employee name,并以,为分隔符。
SQL> SELECT deptno 2 , LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees 3 FROM emp 4 GROUP BY 5 deptno;
DEPTNO EMPLOYEES ---------- ------------------------------------------------------------ 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 3 rows selected.
注:在每个聚合元素中,本例选用empolyee name字段进行排序,不过需要说明的是,在其它实现字符串聚合方法中,排序可是重量级的任务。
下面的例子中,empolyee name的聚合将按照hire date来排序。
SQL> SELECT deptno
2 , LISTAGG(ename, ',') WITHIN GROUP (ORDER BY hiredate) AS employees
3 FROM emp
4 GROUP BY
5 deptno;
DEPTNO EMPLOYEES ---------- ------------------------------------------------------------ 10 CLARK,KING,MILLER 20 SMITH,JONES,FORD,SCOTT,ADAMS 30 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 3 rows selected.
可以看到,每组中empolyee names的排序与前面的例子截然不同。
listagg作为分析函数
与许多的聚合函数类似,listagg通过加上over()子句可以实现分析功能,下面的例子将展示分析功能:
SQL> SELECT deptno 2 , ename 3 , hiredate 4 , LISTAGG(ename, ',') 5 WITHIN GROUP (ORDER BY hiredate) 6 OVER (PARTITION BY deptno) AS employees 7 FROM emp;
DEPTNO ENAME HIREDATE EMPLOYEES ---------- ---------- ----------- ------------------------------------- 10 CLARK 09/06/1981 CLARK,KING,MILLER 10 KING 17/11/1981 CLARK,KING,MILLER 10 MILLER 23/01/1982 CLARK,KING,MILLER 20 SMITH 17/12/1980 SMITH,JONES,FORD,SCOTT,ADAMS 20 JONES 02/04/1981 SMITH,JONES,FORD,SCOTT,ADAMS 20 FORD 03/12/1981 SMITH,JONES,FORD,SCOTT,ADAMS 20 SCOTT 19/04/1987 SMITH,JONES,FORD,SCOTT,ADAMS 20 ADAMS 23/05/1987 SMITH,JONES,FORD,SCOTT,ADAMS 30 ALLEN 20/02/1981 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 30 WARD 22/02/1981 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 30 BLAKE 01/05/1981 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 30 TURNER 08/09/1981 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 30 MARTIN 28/09/1981 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 30 JAMES 03/12/1981 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 14 rows selected. 切记:分析函数不会丢失结果集的每一行,而字符串的聚合却并非如此。
排序
如前所述,ORDER BY 子句是必选项,如下例所示:
SQL> SELECT deptno 2 , LISTAGG(ename, ',') WITHIN GROUP () AS employees 3 FROM emp 4 GROUP BY 5 deptno;
, LISTAGG(ename, ',') WITHIN GROUP () AS employees * ERROR at line 2: ORA-30491: missing ORDER BY clause
如果所要聚合字段的排序无关紧要,那么可以可以使用NULL代替:
SQL> SELECT deptno 2 , LISTAGG(ename, ',') WITHIN GROUP (ORDER BY NULL) AS employees 3 FROM emp 4 GROUP BY 5 deptno;
DEPTNO EMPLOYEES ---------- ------------------------------------------------------------ 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 3 rows selected.
在这个例子当中,虽然使用的是NULL来进行排序,但结果集中聚合的元素还是按字母的顺序排序的,这是因为listagg的默认排序行为。
分隔符
在字符串的聚合中,可以使用静态变量或者表达式作为分隔符,事实上,分隔符是可选项,例如:
SQL> SELECT deptno 2 , LISTAGG(ename) WITHIN GROUP (ORDER BY ename) AS employees 3 FROM emp 4 GROUP BY 5 deptno;
DEPTNO EMPLOYEES ---------- ------------------------------------------------------------ 10 CLARKKINGMILLER 20 ADAMSFORDJONESSCOTTSMITH 30 ALLENBLAKEJAMESMARTINTURNERWARD 3 rows selected.
唯一的限制是,分隔符要么是静态变量(如字母),要么是建立在分组字段上的确定性表达式,比如,不能使用ROWNUM作为分隔符,如下所示:
SQL> SELECT deptno 2 , LISTAGG(ename, '(' || ROWNUM || ')') 3 WITHIN GROUP (ORDER BY hiredate) AS employees 4 FROM emp 5 GROUP BY 6 deptno;
, LISTAGG(ename, '(' || ROWNUM || ')') * ERROR at line 2: ORA-30497: Argument should be a constant or a function of expressions in GROUP BY.
错误信息非常清楚:ROWNUM既不是静态变量,也不是建立在分组字段上的表达式,如果使用了分组字段,那就限制了表达式的类型,例如:
SQL> SELECT deptno 2 , LISTAGG(ename, '(' || MAX(deptno) || ')') 3 WITHIN GROUP (ORDER BY hiredate) AS employees 4 FROM emp 5 GROUP BY 6 deptno;
, LISTAGG(ename, '(' || MAX(deptno) || ')') * ERROR at line 2: ORA-30496: Argument should be a constant.
这个例子当中,oracle分析到分隔符试图使用分组字段,但是是一个非法的表达式,下面的例子中,使用了oracle接受的确定性表达式:
SQL> SELECT deptno 2 , LISTAGG(ename, '(' || CHR(deptno+55) || '); ') 3 WITHIN GROUP (ORDER BY hiredate) AS employees 4 FROM emp 5 GROUP BY 6 deptno;
DEPTNO EMPLOYEES ---------- ------------------------------------------------------------ 10 CLARK(A); KING(A); MILLER 20 SMITH(K); JONES(K); FORD(K); SCOTT(K); ADAMS 30 ALLEN(U); WARD(U); BLAKE(U); TURNER(U); MARTIN(U); JAMES 3 rows selected.
这里把DETPNO转化成ASCII字符作为分隔符,这个一个在分组列上的确定性表达式。
其它限制
listagg聚合的结果列大小限制在varchar2类型的最大值内(比如4000),例如:
SQL> SELECT LISTAGG(object_name) WITHIN GROUP (ORDER BY NULL) 2 FROM all_objects;
FROM all_objects * ERROR at line 2: ORA-01489: result of string concatenation is too long
这里没有clob或者更大的varchar2类型类代替,所以更大的字符串必须使用替代方案(比如COLLECTION或者用户自定义的PL/SQL函数)
性能方面
下面将比较几种常用的字符串聚合方法的性能,类比的有:
- LISTAGG (11g Release 2);
- COLLECT + PL/SQL function(10g);
- Oracle Data Cartridge - user-defined aggregate function (9i)
- MODEL SQL (10g).
这里最主要的不同是listagg是一个内建函数,所以其至少与其它方案有可比性。
建立环境
为了性能比较,下面将建立一张有2000个分组,100万行数据的表,具体如下:
SQL> CREATE TABLE t 2 AS 3 SELECT ROWNUM AS id 4 , MOD(ROWNUM,2000) AS grp 5 , DBMS_RANDOM.STRING('u',5) AS val 6 , DBMS_RANDOM.STRING('u',30) AS pad 7 FROM dual 8 CONNECT BY ROWNUM <= 1000000;
Table created.
SQL> SELECT COUNT(*) FROM t;
COUNT(*) ---------- 1000000 1 row selected.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'T');
PL/SQL procedure successfully completed.
这里将使用wall-clock和autotrace进行性能方面的类比。注:样例的数据已被缓存,准备环境如下:
SQL> set autotrace traceonly statistics SQL> set timing on SQL> set arrays 500
listagg
第一个测试的是listagg,下面将对2000个分组进行聚合,并按照value排序:
SQL> SELECT grp 2 , LISTAGG(val, ',') WITHIN GROUP (ORDER BY val) AS vals 3 FROM t 4 GROUP BY 5 grp;
2000 rows selected. Elapsed: 00:00:05.85 Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 7092 consistent gets 0 physical reads 0 redo size 6039067 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2000 rows processed
测试机上,这条语句执行了不到6秒,没有磁盘物理I/O,所有的sorting都在内存当中。
stragg/wm_concat
下面将使用广为流传的字符串聚合,Tom Kyte的定义的聚合函数STRAGG。在 oracle的10g版本中,oracle在WMSYS的用户下实现了类似功能的函数,这里直接使用这个函数来测试。注:STRAGG函数不支持字符串的排序。
SQL> SELECT grp 2 , WMSYS.WM_CONCAT(val) AS vals --<-- WM_CONCAT ~= STRAGG 3 FROM t 4 GROUP BY 5 grp;
2000 rows selected. Elapsed: 00:00:19.45 Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 7206 consistent gets 0 physical reads 0 redo size 6039067 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2000 rows processed
这个方法花费了三倍于listagg的时间(没有排序),用户自定义的函数会比这个PL/SQL函数效率更低(比如:上下文切换)
collect(without ordering)
当10g发布的时候,我就立即使用collect函数和一个“collection-to-string”PL/SQL函数来替代STRAGG。不过10g版本中的collect没有排序功能。注;To_STRING的源码可以在相关文档中查到。
SQL> SELECT grp 2 , TO_STRING( 3 CAST(COLLECT(val) AS varchar2_ntt) 4 ) AS vals 5 FROM t 6 GROUP BY 7 grp;
2000 rows selected. Elapsed: 00:00:02.90 Statistics ---------------------------------------------------------- 10 recursive calls 0 db block gets 7197 consistent gets 0 physical reads 0 redo size 6039067 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2000 rows processed
没有排序的情况下,collect/TO_STRING方法比listagg快了两倍,但是listagg花费了大量的时间在排序上,如果说排序时无关紧要的,那么可以说collect技术是最快的。
collect (with ordering)
公平起见,在collect中引入排序(11g中的一个新特性),如下;
SQL> SELECT grp 2 , TO_STRING( 3 CAST(COLLECT(val ORDER BY val) AS varchar2_ntt) 4 ) AS vals 5 FROM t 6 GROUP BY 7 grp;
2000 rows selected. Elapsed: 00:00:07.08 Statistics ---------------------------------------------------------- 10 recursive calls 0 db block gets 7197 consistent gets 0 physical reads 0 redo size 6039067 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2000 rows processed 这次,引入了排序后,collect方法确实比listagg慢多了。
model
最后一个性能比较是与使用了model子句的实现方法。下面的例子的源代码来自于Rob van Wijk's About Oracle blog 并做了些修改以适应样例数据。
SQL> SELECT grp 2 , vals 3 FROM ( 4 SELECT grp 5 , RTRIM(vals, ',') AS vals 6 , rn 7 FROM t 8 MODEL 9 PARTITION BY (grp) 10 DIMENSION BY (ROW_NUMBER() OVER (PARTITION BY grp ORDER BY val) AS rn) 11 MEASURES (CAST(val AS VARCHAR2(4000)) AS vals) 12 RULES 13 ( vals[ANY] ORDER BY rn DESC = vals[CV()] || ',' || vals[CV()+1] 14 ) 15 ) 16 WHERE rn = 1 17 ORDER BY 18 grp;
2000 rows selected. Elapsed: 00:03:28.15 Statistics ---------------------------------------------------------- 3991 recursive calls 0 db block gets 7092 consistent gets 494791 physical reads 0 redo size 6039067 bytes sent via SQL*Net to client 553 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 130 sorts (memory) 0 sorts (disk) 2000 rows processed
这个例子执行了三分钟,统计信息显示发生了大量的I/O读,递归调用和内存排序,事实上,这个糟糕的表现主要是由于在查询中,大量的对临时表空间的读和写(尽管统计信息并未显示磁盘排序)。
MODEL字符串聚合方法的执行计划如下:
-------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| -------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | SORT ORDER BY | | 1000K| 1934M| 1953M| |* 2 | VIEW | | 1000K| 1934M| | | 3 | SQL MODEL ORDERED | | 1000K| 9765K| | | 4 | WINDOW SORT | | 1000K| 9765K| 19M| | 5 | TABLE ACCESS FULL| T | 1000K| 9765K| | -------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("RN"=1) 通过SQL的监控报告(使用DBMS_SQLTUNE.REPORT_SQL_MONITOR)在SQL MODEL操作的第三步中,数据的排序使用4Gb的临时空间,在Gary Myers' Sydney Oracle Lab blog中也阐述了这个现象。
性能总结
从以上事例中可以看出,listagg函数是字符串聚合方法中效率最高的一个,并且还是一个内建的函数。 如果不需要排序的情况下,collect会更快一些,但如果是需要排序的话,listagg绝对是最快的。
相关推荐
【Oracle】LISTAGG函数的使用.pdf
* 自定义聚合函数 wmsys.wm_concat 替换办法 * 超大字符串拼接,单个字符串4000、分隔符100... * Oracle11g Release2版本引入了LISTAGG 函数,使得聚集连接字符串变得很容易。并且允许使用我们指定连接串中的字段顺序
近在学习的过程中,发现一个挺有意思的函数,它可实现对列值的拼接。下面我们来看看其具体用法。 用法: 对其作用,官方文档的解释如下: For a specified measure, LISTAGG orders data within each ...
Oracle从12C版本开始,不支持wm_concat函数,我们可以采取的办法有使用listagg函数代替wm_concat函数,或者为了减小修改程序的工作量,可以通过手工创建wm_concat函数来解决这个问题。
由于ACCESS 没有oracle的listagg函数,也没有sql server这种 for xml path 这种, 要实现分组合并需要自定义一个函数,理解了 for xml path 这个就很好理解了。
含listagg函数 (行列转换) ,Oracle-SQL-Developer-使用简要说明,oracle导入导出语句,Oracle远程登录,rownum分组排序,wm_concat列转行
oracle 10g 11g 12c 19c 21c 23c 重建wm_concat函数脚本 varchar类型clob类型报错不用listagg ORA-01790: 表达式必须具有与对应表达式相同的数据类型 ORA-00904: “WM_CONCAT“: invalid identifier 解决 owmaggrb....
WITH –-小九九算法 t_base AS (SELECT LEVEL AS lv FROM dual CONNECT BY LEVEL ), ...SELECT listagg(t_join.text, ' ') within GROUP(ORDER BY t_join.lv_b) AS m99 FROM t_join GROUP BY t_join.lv_a;
要添加到内置函数列表中的 VoltDB 存储过程,例如:像 group_concat(又名 listagg)这样的 UDF 像爆炸的 UDTF 通过 JDBC 动态执行 DDL 将多个 SQL 语句绑定到一个事务中创建表插入少量记录(使用 Insert into ...
8.7.11 Listagg 217 8.8 性能调优 218 8.8.1 执行计划 218 8.8.2 谓语 219 8.8.3 索引 220 8.9 高级话题 221 8.9.1 动态SQL 221 8.9.2 嵌套分析函数 222 8.9.3 并行 223 8.9.4 PGA大小 224 8.10 组织行为...