行列转换之字符串拼接（二）、LISTAGG函数

字符串拼接（String Aggregation Techniques）是数据处理时经常需要用到一个技术，比如需要按时间顺序拼装一个快递的运输记录，或者将流程中各个环节的处理人拼装为一个字符串。

Oracle中有多种方法来实现这个功能，这里罗列几种，详细用法可以参考下面的文章：
- WM_CONCAT函数
- LISTAGG函数
- 自定义聚合函数

本文介绍第二种：LISTAGG函数，这是Oracle11gR2开始正式推出的字符串聚合函数，功能非常强大。

0.测试样例及基本用法

从all_objects视图中取4个表记录和3个视图记录作为测试数据：

SQL> CREATE TABLE T_STRAGG AS
  2    select OBJECT_TYPE,CREATED,OBJECT_NAME from ALL_OBJECTS WHERE OBJECT_TYPE='TABLE' AND rownum<5
  3    UNION ALL
  4    select OBJECT_TYPE,CREATED,OBJECT_NAME from ALL_OBJECTS WHERE OBJECT_TYPE='VIEW' AND rownum<4;
Table created

SQL> select OBJECT_TYPE,TO_CHAR(CREATED,'YYYY-MM-DD HH24:MI:SS') CREATED,OBJECT_NAME from T_STRAGG;
OBJECT_TYPE         CREATED             OBJECT_NAME
------------------- ------------------- ------------------------------
TABLE               2013-10-09 18:23:43 DUAL
TABLE               2013-10-09 18:23:44 SYSTEM_PRIVILEGE_MAP
TABLE               2013-10-09 18:23:45 TABLE_PRIVILEGE_MAP
TABLE               2013-10-09 18:23:47 STMT_AUDIT_OPTION_MAP
VIEW                2013-10-09 18:23:53 ALL_XML_SCHEMAS
VIEW                2013-10-09 18:23:56 ALL_XML_SCHEMAS2
VIEW                2013-10-09 18:23:54 V_$ADVISOR_CURRENT_SQLPLAN

现在想要根据OBJECT_TYPE分组，将OBJECT_NAME按时间顺序拼装成类似物流信息的字符串：

2013-10-09 18:23:43@DUAL
2013-10-09 18:23:44@SYSTEM_PRIVILEGE_MAP
2013-10-09 18:23:45@TABLE_PRIVILEGE_MAP

实现方法很简单：

SQL> select object_type
  2        ,LISTAGG(TO_CHAR(CREATED,'YYYY-MM-DD HH24:MI:SS')||'@'||OBJECT_NAME,CHR(10)) WITHIN GROUP(ORDER BY CREATED) AS TXT
  3    FROM T_STRAGG
  4    GROUP BY object_type;
OBJECT_TYPE         TXT
------------------- --------------------------------------------------------------------------------
TABLE               2013-10-09 18:23:43@DUAL
                    2013-10-09 18:23:44@SYSTEM_PRIVILEGE_MAP
                    2013-10-09 18:23:45@TABLE_PRIVILEGE_MAP
                    2013-10-09 18:23:47@STMT_AUDIT_OPTION_MAP
VIEW                2013-10-09 18:23:53@ALL_XML_SCHEMAS
                    2013-10-09 18:23:54@V_$ADVISOR_CURRENT_SQLPLAN
                    2013-10-09 18:23:56@ALL_XML_SCHEMAS2

跟WM_CONCAT相比，LISTAGG有如下特征（优缺点）：

1.返回值的分隔符可以自定义

这一点大大方便了开发人员，只要有需要可以摆出各种姿势^_^

2.返回值类型是VARCHAR2

跟WM_CONCAT相反，速度是快了，但如果数据量大就报错
ORA-01489: result of string concatenation is too long
（字符串连接的结果过长）

SQL> INSERT INTO T_STRAGG select object_type,CREATED,OBJECT_NAME FROM ALL_OBJECTS where rownum<1000;
999 rows inserted

SQL> select object_type
  2        ,LISTAGG(TO_CHAR(CREATED,'YYYY-MM-DD HH24:MI:SS')||'@'||OBJECT_NAME,CHR(10)) WITHIN GROUP(ORDER BY CREATED) AS TXT
  3    FROM T_STRAGG
  4    GROUP BY object_type;

ORA-01489: result of string concatenation is too long

没办法，用回WM_CONCAT？
还是建议用第三个方法：自定义聚合函数

3.不支持DISTINCT

直接报错ORA-30482: DISTINCT option not allowed for this function
（DISTINCT 选项在此函数中禁用）

SQL> select object_type,LISTAGG(DISTINCT OBJECT_NAME) WITHIN GROUP(ORDER BY CREATED) AS TXT
  2    FROM T_STRAGG
  3    GROUP BY object_type;

ORA-30482: DISTINCT option not allowed for this function

解决方法：
1.先distinct，结果再listagg
2.自定义聚合函数来取代listagg函数

结论

1. listagg能满足的情况下，用listagg，不要用wm_concat
2. listagg无法满足的情况下，还是网上拿一个自定义聚合函数好了