OracleListagg函数去重详解

一、OracleListagg函数简介

OracleListagg函数是一个集合函数，它将多行记录按指定的分隔符连接起来生成一个字符串。比如：

SELECT listagg(column_name,',') WITHIN GROUP(ORDER BY column_name)
FROM table_name;

这个命令将指定表的column_name列中的所有非空记录用逗号串联起来生成一个字符串。

二、使用OracleListagg函数去重

1、基本去重功能

OracleListagg函数默认情况下不会去重，当表中有重复记录时，生成字符串中就会包含重复的内容。我们可以使用DISTINCT关键字来去重：

SELECT listagg(DISTINCT column_name,',') WITHIN GROUP(ORDER BY column_name)
FROM table_name;

这个命令将指定表的column_name列中的所有不同的非空记录用逗号串联起来生成一个字符串。

2、去重时忽略NULL值

某些情况下，我们可能需要忽略NULL值进行去重。这时候我们可以使用NVL函数将NULL值替换为一个特殊的非空值，然后使用DISTINCT关键字进行去重：

SELECT listagg(DISTINCT NVL(column_name,' '),',') WITHIN GROUP(ORDER BY column_name)
FROM table_name;

注意这里用的是一个空格，你可以使用任何一个非NULL的值。

3、使用正则表达式去重

有时候我们需要去除那些只是大小写或特定字符串的变体。这时候可以使用正则表达式来去重：

SELECT listagg(column_name,',') WITHIN GROUP(ORDER BY column_name)
FROM (SELECT DISTINCT REGEXP_REPLACE(column_name,'[^A-Za-z0-9]+') AS column_name
     FROM table_name);

这个查询将指定表的column_name列中所有非空记录用逗号串起来生成一个字符串，但是在连接前，它会将所有的非字母、数字的字符替换为空，以去除变体。

三、 OracleListagg如何应用在实际场景中

1、将csv单元格合并为一个单元格

我们知道，在Excel中，可以使用类似于CONCATENATE函数将多个单元格的内容合并成一个，但是在Oracle数据库中并不存在类似的函数。在这种情况下，我们可以使用OracleListagg函数将多列数据合并成一列，比如：

SELECT  id, listagg(column_name,', ') WITHIN GROUP(ORDER BY column_name) "Column_Name"
FROM    table_name
GROUP BY id;

其中，id列是表中的主键或唯一标识符。

2、根据关键字合并记录

在某些场景下，我们需要根据某个关键字将多个记录合并成一个。比如，我们有一个包含多个数据点的数据表：

CREATE TABLE data (id NUMBER, data_point VARCHAR2(50));

INSERT INTO data VALUES (1, 'red');
INSERT INTO data VALUES (1, 'green');
INSERT INTO data VALUES (2, 'blue');
INSERT INTO data VALUES (2, 'green');

我们需要将所有id相同的记录合并为一行，并将其data_point列用逗号隔开。这个需求可以通过以下命令实现：

SELECT id, listagg(data_point,',') WITHIN GROUP(ORDER BY data_point) "Data Points"
FROM data
GROUP BY id;

输出将为：

ID   Data Points
--   -----------
1    green,red
2    blue,green

3、根据用户喜好合并记录

有时候用户可以自定义他们想要的结果，比如用户A更喜欢用“;”作为分隔符，他们可以使用以下的命令来实现：

SELECT id, listagg(data_point,';') WITHIN GROUP(ORDER BY data_point) "Data Points"
FROM data
GROUP BY id;