Oracle字符串聚合函数LISTAGG

在Oracle 19c中，LISTAGG函数是一个非常有用的字符串聚合函数，它可以将来自多个行的值连接成一个单独的字符串。这个函数特别适用于将分组内的多个值合并为一个逗号分隔（或其他分隔符）的字符串。

LISTAGG函数的基本语法如下：

LISTAGG(column, [delimiter]) WITHIN GROUP (ORDER BY order_by_clause) [OVER(PARTITION BY paration_by_clause) ]

• column 是你想要聚合的列。
• [delimiter] 是可选的，用于指定值之间的分隔符，默认为NULL。如果不指定分隔符，则所有值将直接连接在一起，没有分隔。
• WITHIN GROUP (ORDER BY order_by_clause) 是必须的，用于指定聚合时值的排序方式。
• OVER(PARTITION BY XXX) 在不使用GROUP BY语句时候，也可以使用LISTAGG函数

示例1

假设我们有一个名为employees的表，其中包含department_id和employee_name两个字段，我们想要为每个部门列出所有员工的名字，名字之间用逗号分隔。

SELECT
    department_id,
    LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS employees
FROM
    employees
GROUP BY
    department_id;

这个查询将返回每个部门的ID和该部门所有员工名字的列表，名字之间用逗号加空格分隔。

示例2

SYS@orcl> create table t_listagg(id number,nation varchar2(32),city varchar2(128),constraint pk_t_listagg_id primary key(id));

Table created.

INSERT INTO t_listagg
select 1 ID,'China' nation ,'广州' city from dual union all 
select 2 ID,'China' nation ,'深圳' city from dual union all  
select 3 ID,'China' nation ,'上海' city from dual union all  
select 4 ID,'China' nation ,'北京' city from dual union all  
select 5 ID,'USA' nation ,'New York' city from dual union all  
select 6 ID,'USA' nation ,'Boston' city from dual union all  
select 7 ID,'Japan' nation ,'Tokyo' city from dual  
COMMIT;

SYS@orcl> col city format a30
SYS@orcl> select * from t_listagg;

        ID NATION                           CITY
---------- -------------------------------- ------------------------------
         1 China                            广州
         2 China                            深圳
         3 China                            上海
         4 China                            北京
         5 USA                              New York
         6 USA                              Boston
         7 Japan                            Tokyo

SYS@orcl> col LISTAGG_CITY format a60
SYS@orcl> set linesize 200
-- 用于指定聚合时值的,以city升序方式排序
SYS@orcl> select nation,listagg(city,',') within GROUP (order by city) listagg_city  FROM t_listagg  GROUP by nation;

NATION                           LISTAGG_CITY
-------------------------------- ------------------------------------------------------------
China                            上海,北京,广州,深圳
Japan                            Tokyo
USA                              Boston,New York
-- 用于指定聚合时值的,以ID倒序方式排序
SYS@orcl> select nation,listagg(city,',') within GROUP (order by ID desc) listagg_city  FROM t_listagg  GROUP by nation;

NATION                           LISTAGG_CITY
-------------------------------- ------------------------------------------------------------
China                            北京,上海,深圳,广州
Japan                            Tokyo
USA                              Boston,New York

-- 用于指定聚合时值的,以ID倒序方式排序，以nation分组
SYS@orcl> select id,nation,city,listagg(city,',') within GROUP (order by id desc)  over (partition by nation) rank  FROM t_listagg;

        ID NATION                           CITY                           RANK
---------- -------------------------------- ------------------------------ ------------------------------------------------------------
         4 China                            北京                           北京,上海,深圳,广州
         3 China                            上海                           北京,上海,深圳,广州
         2 China                            深圳                           北京,上海,深圳,广州
         1 China                            广州                           北京,上海,深圳,广州
         7 Japan                            Tokyo                          Tokyo
         6 USA                              Boston                         Boston,New York
         5 USA                              New York                       Boston,New York

7 rows selected.

注意事项

1. 字符串长度限制：LISTAGG函数在Oracle中有字符串长度的限制。在Oracle 12c及之前的版本中，这个限制是4000字节。从Oracle 12c Release 2开始，可以通过设置ON OVERFLOW TRUNCATE子句来处理超出长度的情况，但Oracle 19c仍然默认有这个限制。如果聚合的字符串超过了这个长度，查询将失败。

2. 处理超长字符串：如果你预期聚合的字符串可能会超过4000字节的限制，你可以考虑使用XMLAGG和XMLELEMENT函数作为替代方案，因为XMLAGG不受此限制。但是，请注意，使用XMLAGG会使查询更加复杂，并且可能需要额外的处理来将XML类型的结果转换为字符串。

3. 性能：对于大型数据集，LISTAGG函数可能会影响查询性能。在可能的情况下，考虑使用索引、优化查询逻辑或考虑数据聚合的替代方法。

4. 版本兼容性：虽然LISTAGG在Oracle 11g Release 2及更高版本中可用，但某些特性（如ON OVERFLOW TRUNCATE）可能在较新的版本中才可用。始终参考你正在使用的Oracle版本的官方文档。