Oracle中如何在两列维度实现去重LISTAGG并处理长度限制

需求说明 #

现有表dummy_data，数据如下：

需要按emp_id、company_id、company_version_id维度，对account_id和duplicate_account_id分别去重后聚合，得到结果：

测试表创建语句：

create table dummy_data AS
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013278 account_id,  101104723 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013278 account_id,  200999888 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013278 account_id,  203010306 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013278 account_id,  205436979 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013278 account_id,  205436980 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013293 account_id,  101104723 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013293 account_id,  200999888 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013293 account_id,  203010306 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013293 account_id,  205436979 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013293 account_id,  205436980 duplicate_account_id from dual 

解决方案 #

方法1：Oracle 12cR2及以上版本（支持LISTAGG溢出处理） #

先对每个维度下的account_id和duplicate_account_id去重，再用LISTAGG聚合，同时指定溢出处理规则避免截断：

SELECT
    emp_id,
    company_id,
    company_version_id,
    LISTAGG(DISTINCT account_id, ', ') WITHIN GROUP (ORDER BY account_id) 
        ON OVERFLOW TRUNCATE '...' WITH COUNT -- 可选：截断并标记，或用ON OVERFLOW ERROR抛出错误
        AS account_id,
    LISTAGG(DISTINCT duplicate_account_id, ', ') WITHIN GROUP (ORDER BY duplicate_account_id)
        ON OVERFLOW TRUNCATE '...' WITH COUNT
        AS duplicate_account_id
FROM dummy_data
GROUP BY emp_id, company_id, company_version_id;

• DISTINCT关键字实现去重，确保每个ID只出现一次；
• 若需完整无截断结果，可改用ON OVERFLOW ERROR，聚合结果超过长度限制时会抛出错误，而非静默截断。

方法2：兼容Oracle 11g及更早版本（用XMLAGG替代LISTAGG） #

Oracle 11g及之前的LISTAGG有4000字符长度限制，且不支持溢出处理，可使用XMLAGG生成超长字符串：

SELECT
    emp_id,
    company_id,
    company_version_id,
    RTRIM(XMLAGG(XMLELEMENT(E, account_id, ', ') ORDER BY account_id).EXTRACT('//text()').GETCLOBVAL(), ', ') 
        AS account_id,
    RTRIM(XMLAGG(XMLELEMENT(E, duplicate_account_id, ', ') ORDER BY duplicate_account_id).EXTRACT('//text()').GETCLOBVAL(), ', ') 
        AS duplicate_account_id
FROM (
    -- 先去重，避免重复ID被多次聚合
    SELECT DISTINCT
        emp_id,
        company_id,
        company_version_id,
        account_id,
        duplicate_account_id
    FROM dummy_data
) t
GROUP BY emp_id, company_id, company_version_id;

• 子查询先完成去重，确保每个维度下的ID唯一；
• XMLAGG生成的结果是CLOB类型，支持超长内容，无4000字符限制；
• RTRIM用于移除最后一个多余的分隔符。

内容的提问来源于stack exchange，提问作者DancingMonkeyOnLaughingBuffalo