LISTAGG

在Oracle 11Gr2,Oracle的分析/聚合函数得到进一步的增强,本文对新增的LISTAGG函数进行一些实验和总结。

用法:
LISTAGG(measure_expr [, ‘delimiter’]) WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

listagg虽然是聚合函数,但可以提供分析功能(比如可选的OVER()子句)。使用listagg中,下列中的元素是必须的:
@需要聚合的列或者表达式
@WITH GROUP 关键词
@分组中的ORDER BY子句,当排序无关紧要时可以使用NULL代替

聚合函数功能

下面的测试数据使用11.2.0.4版本下的HR演示数据,实现对FIRST_NAME按逗号分隔进行聚合。

SQL> SELECT DEPARTMENT_ID,FIRST_NAME FROM HR.EMPLOYEES WHERE DEPARTMENT_ID <= 40;
DEPARTMENT_ID FIRST_NAME
------------- ----------------------------------------
           10 Jennifer
           20 Michael
           20 Pat
           30 Den
           30 Alexander
           30 Shelli
           30 Sigal
           30 Guy
           30 Karen
           40 Susan

已选择10行。
SQL> SELECT DEPARTMENT_ID,LISTAGG(FIRST_NAME,',') WITHIN GROUP (ORDER BY FIRST_NAME) AS LIST_FIRSTNAME
  2  FROM EMPLOYEES WHERE DEPARTMENT_ID <= 40 GROUP BY DEPARTMENT_ID;

DEPARTMENT_ID LIST_FIRSTNAME
------------- --------------------------------------------------
           10 Jennifer
           20 Michael,Pat
           30 Alexander,Den,Guy,Karen,Shelli,Sigal
           40 Susan

分析函数功能

SQL> Select DEPARTMENT_ID,
  2         employee_id,
  3         Listagg(FIRST_NAME, ',') Within Group(Order By employee_id) Over(Partition By DEPARTMENT_ID) As list_firstname
  4    From Employees
  5   Where Department_Id <= 40; 
DEPARTMENT_ID EMPLOYEE_ID LIST_FIRSTNAME 
------------- ----------- -------------------     
10         200 Jennifer            
20         201 Michael,Pat            
20         202 Michael,Pat            
30         114 Den,Alexander,Shelli,Sigal,Guy,Karen           
30         115 Den,Alexander,Shelli,Sigal,Guy,Karen            
30         116 Den,Alexander,Shelli,Sigal,Guy,Karen            
30         117 Den,Alexander,Shelli,Sigal,Guy,Karen            
30         118 Den,Alexander,Shelli,Sigal,Guy,Karen            
30         119 Den,Alexander,Shelli,Sigal,Guy,Karen            
40         203 Susan 
已选择10行。
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC +ROWS +PROJECTION'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 2095165354

------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |    10 |
|   1 |  WINDOW SORT                 |                   |    10 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |
|   3 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |
------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=2) "DEPARTMENT_ID"[NUMBER,22], "EMPLOYEE_ID"[NUMBER,22],
       "EMPLOYEES".ROWID[ROWID,10], "FIRST_NAME"[VARCHAR2,20],
       LISTAGG("FIRST_NAME",',') WITHIN GROUP ( ORDER BY "EMPLOYEE_ID") OVER (
       PARTITION BY "DEPARTMENT_ID")[4000]
   2 - "EMPLOYEES".ROWID[ROWID,10], "EMPLOYEE_ID"[NUMBER,22],
       "FIRST_NAME"[VARCHAR2,20], "DEPARTMENT_ID"[NUMBER,22]
   3 - "EMPLOYEES".ROWID[ROWID,10], "DEPARTMENT_ID"[NUMBER,22]
此条目发表在analytic function, SQL分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s