listagg 函数--oracle 11g release 2

原创 2012年03月23日 13:39:13

http://xpchild.blog.163.com/blog/static/10180985920108485721969/

 

listagg 函数--oracle 11g release 2  

2010-09-04 21:45:10|  分类:默认分类 |  标签:|字号 订阅

    本文描述了在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 11g Release2 安装图文教程 for windows

Oracle 11g Release2 安装图文教程 for windows

Fedora 16安装Oracle 11g Release 2

1、一般hosts不用修改 2、修改内核参数 1) 将下列内容添加到”/etc/sysctl.conf”文件末尾 fs.aio-max-nr = 1048576 fs.file-max = ...

Install and configure Oracle 11g Release 2 on Redhat 6.3 Enterprise Linux 64 step by step

本篇以初学者的角度来剖析 Oracle 11g R2 数据库在 Redhat Enterprise Linux 6.3 的安装与配置。

Installation of Oracle 11g Release 2 (11.2.0.1.0) on RedHat EL 5, (Oracle) Enteprise Linux 5 and Cen

http://ivan.kartik.sk/oracle/install_ora11gR2_elinux.html Installation of Oracle 11g Rele...

[转]Installation of Oracle 11g Release 2 on Solaris 11 Express x86 and SPARC

在 Solaris 11上安装Oracle 11g 转自:http://ivan.kartik.sk/oracle/install_ora11gR2_solaris11.html ...

Oracle-11g 新函数 LISTAGG 行转列

现在oracle数据库都在用11g的了,

Oracle 11g Release 1 (11.1) PL/SQL_了解静态和动态 SQL

本文内容 静态 SQL动态 SQL 静态 SQL 静态 SQL 是属于 PL/SQL 语言的 SQL。也就是: 除了解释执行计划的,数据操作语句(Data Manipulati...

Oracle 11g 常用函数(Functions)详解

Oracle 11g 常用函数(Functions)详解 目录 ABS. 3 ACOS. 3 ADD_MONTHS. 4 ASCII 4 ASCIISTR. 5 ASIN.. 5 AT...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)