如何去除LISTAGG聚合结果中的重复值？附SQL代码问题咨询

这个问题我碰到过好多次了——本质上是因为当你在同一个分组里对两个独立的列做LISTAGG时，如果这两列的非空行数不一致，数据库会先做隐式的笛卡尔积再聚合，导致其中一列的内容被重复填充来匹配另一列的行数。比如两个source对应一个filter时，filter会被重复两次，反之亦然。

要让source和filter的数量完全匹配，核心思路是先把每个分组内的source和filter按顺序一一配对，再进行聚合。具体可以通过窗口函数给每个分组内的source、filter分别编号，再通过全连接关联对应编号的记录，最后聚合。

完整解决方案代码 #

WITH source_data AS (
    -- 给每个分组内的source按顺序编号
    SELECT rule, DATASET, type, TARGET, source,
           ROW_NUMBER() OVER(PARTITION BY rule, DATASET, type, TARGET ORDER BY source) AS rn
    FROM demo_table
    WHERE source IS NOT NULL  -- 过滤空值，避免无效编号
),
filter_data AS (
    -- 给每个分组内的filter按顺序编号
    SELECT rule, DATASET, type, TARGET, filter,
           ROW_NUMBER() OVER(PARTITION BY rule, DATASET, type, TARGET ORDER BY filter) AS rn
    FROM demo_table
    WHERE filter IS NOT NULL  -- 过滤空值，避免无效编号
)
SELECT 
    COALESCE(s.rule, f.rule) AS rule,
    COALESCE(s.DATASET, f.DATASET) AS DATASET,
    COALESCE(s.type, f.type) AS type,
    COALESCE(s.TARGET, f.TARGET) AS TARGET,
    -- 聚合source，缺值补空字符串
    LISTAGG(COALESCE(s.source, ''), ';') WITHIN GROUP (ORDER BY COALESCE(s.rn, f.rn)) AS source,
    -- 聚合filter，缺值补空字符串
    LISTAGG(COALESCE(f.filter, ''), ';') WITHIN GROUP (ORDER BY COALESCE(s.rn, f.rn)) AS filter
FROM source_data s
-- 全连接保证两边的记录都被保留
FULL JOIN filter_data f
    ON s.rule = f.rule
    AND s.DATASET = f.DATASET
    AND s.type = f.type
    AND s.TARGET = f.TARGET
    AND s.rn = f.rn
GROUP BY COALESCE(s.rule, f.rule), COALESCE(s.DATASET, f.DATASET), COALESCE(s.type, f.type), COALESCE(s.TARGET, f.TARGET);

逻辑解释 #

1. 编号阶段：通过ROW_NUMBER()窗口函数，给每个(rule, DATASET, type, TARGET)分组内的source和filter分别按顺序编号，确保每个分组内的source和filter都有自己的序号。
2. 关联阶段：用FULL JOIN按分组键和序号关联source和filter记录，这样序号相同的source和filter会一一配对；如果某一边数量更多，另一边缺失的位置会用NULL填充。
3. 聚合阶段：用COALESCE把NULL转成空字符串，再用LISTAGG聚合，这样最终的source和filter列表长度完全一致——缺值的位置会用空字符串占位，保证数量匹配。

举个例子：

• 若分组内有2个source、1个filter，最终source是source1;source2，filter是filter1;
• 若分组内有1个source、2个filter，最终source是source1;，filter是filter1;filter2

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