Oracle 12 多条件下DISTINCT LISTAGG分组拼接实现问题咨询

基础场景说明 #

• 涉及业务表结构包含ID、CONTACTCODE、CONTACTPERSON三个字段
• 表内存在多组重复的编码与角色匹配记录

核心开发需求 #

按ID维度分组聚合，输出两个统计列：

• MAIN列：筛选CONTACTPERSON = 'CLIENT'的记录，在CONTACTCODE属于('X','Y','Z')取值范围的前提下，对符合条件的去重CONTACTCODE做逗号分隔拼接；无匹配值时返回固定值0
• REF列：筛选CONTACTPERSON != 'CLIENT'的记录，在CONTACTCODE属于('X','Y','Z')取值范围的前提下，对符合条件的去重CONTACTCODE做逗号分隔拼接；无匹配值时返回固定值0

预期输出样例 #

• ID=1时：MAIN列拼接值为X,Y,Z，REF列拼接值为X,Z
• ID=2时：MAIN列值为Y，REF列值为Y
• ID=3时：MAIN列值为0，REF列值为Y

原有实现的卡点 #

此前尝试通过PARTITION BY ID, CONTACTCODE计算row_number()，筛选row_number=1的记录后调用LISTAGG完成拼接，实际运行结果与预期不符。常见的问题点包括：

• 分区去重时误将CONTACTPERSON纳入分区键，导致同ID下同编码因为角色差异被判定为不同记录，聚合时出现漏值、重复拼接
• 筛选条件与聚合逻辑未对齐，提前过滤了不符合编码范围的记录，导致后续分类筛选逻辑失效
• 未对空聚合结果做兜底处理，无匹配值时返回空串而非要求的固定值0

适配Oracle 12c的可直接落地方案 #

SELECT 
  t.ID,
  -- MAIN列：符合条件时返回去重拼接值，无匹配返回0
  NVL(
    LISTAGG(DISTINCT t.CONTACTCODE, ',') 
      WITHIN GROUP (ORDER BY t.CONTACTCODE)
      KEEP (
        DENSE_RANK FIRST 
        WHERE t.CONTACTPERSON = 'CLIENT' 
          AND t.CONTACTCODE IN ('X','Y','Z')
      ),
    '0'
  ) AS MAIN,
  -- REF列：符合条件时返回去重拼接值，无匹配返回0
  NVL(
    LISTAGG(DISTINCT t.CONTACTCODE, ',') 
      WITHIN GROUP (ORDER BY t.CONTACTCODE)
      KEEP (
        DENSE_RANK FIRST 
        WHERE t.CONTACTPERSON != 'CLIENT' 
          AND t.CONTACTCODE IN ('X','Y','Z')
      ),
    '0'
  ) AS REF
FROM 业务表 t
GROUP BY t.ID;

方案逻辑说明 #

• 用KEEP (DENSE_RANK FIRST ...)将两个列的分类筛选、编码范围筛选逻辑下推到聚合阶段，无需多层子查询嵌套
• 直接利用LISTAGG内置的DISTINCT关键字完成同分组下符合条件的编码去重，规避手动窗口函数去重时的维度错配问题
• 外层NVL做兜底处理：某分组下无符合筛选条件的记录时，LISTAGG返回空值，此时自动替换为要求的固定值0

常见兼容性调整点 #

• 若CONTACTCODE字段存在前后空格，可将代码内的t.CONTACTCODE替换为TRIM(t.CONTACTCODE)，避免值匹配失败
• 若CONTACTPERSON字段存在大小写不一致问题，可将匹配条件调整为UPPER(t.CONTACTPERSON) = 'CLIENT'、UPPER(t.CONTACTPERSON) != 'CLIENT'，保证筛选准确性
• 若拼接结果长度超出VARCHAR2默认长度限制，可在12c环境下为LISTAGG增加ON OVERFLOW TRUNCATE参数处理长文本截断

内容的提问来源于stack exchange，提问作者Thuong Duyen Truong