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;