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.
注意事项
-
字符串长度限制：
LISTAGG函数在Oracle中有字符串长度的限制。在Oracle 12c及之前的版本中，这个限制是4000字节。从Oracle 12c Release 2开始，可以通过设置ON OVERFLOW TRUNCATE子句来处理超出长度的情况，但Oracle 19c仍然默认有这个限制。如果聚合的字符串超过了这个长度，查询将失败。 -
处理超长字符串：如果你预期聚合的字符串可能会超过4000字节的限制，你可以考虑使用
XMLAGG和XMLELEMENT函数作为替代方案，因为XMLAGG不受此限制。但是，请注意，使用XMLAGG会使查询更加复杂，并且可能需要额外的处理来将XML类型的结果转换为字符串。 -
性能：对于大型数据集，
LISTAGG函数可能会影响查询性能。在可能的情况下，考虑使用索引、优化查询逻辑或考虑数据聚合的替代方法。 -
版本兼容性：虽然
LISTAGG在Oracle 11g Release 2及更高版本中可用，但某些特性（如ON OVERFLOW TRUNCATE）可能在较新的版本中才可用。始终参考你正在使用的Oracle版本的官方文档。