SQL中如何使用LISTAGG实现DISTINCT去重并按item_pg_nbr排序

问题场景 #

执行以下SQL时，拼接生成的item_id_txt字段存在大量重复值：

SELECT 
           s_id
          ,CASE WHEN LISTAGG(X.item_id, ',') WITHIN GROUP (ORDER BY TRY_TO_NUMBER(Z.item_pg_nbr))= '' THEN NULL
           ELSE LISTAGG (X.item_id, ',') WITHIN GROUP (ORDER BY TRY_TO_NUMBER(Z.item_pg_nbr)) 
           END AS item_id_txt
    FROM table_1 X
    JOIN table_2 Z
    ON Z.cmn_id = X.cmn_id
    WHERE s_id IN('38301','40228')
    GROUP BY s_id;

实际返回示例：

S_ID                               ITEM_ID_TXT 

38301                            618444,618444,618444,618444,618444,618444,36184
40228                            616162,616162,616162,616162,616162,616162,616162

需求为拼接结果仅保留去重后的唯一item_id，同时必须按item_pg_nbr字段的数值顺序排序，直接在LISTAGG函数中使用DISTINCT关键字无法满足排序要求。

实现方案 #

核心逻辑是提前在CTE层完成去重，同时保留每个item_id对应的排序依据，避免外层聚合时出现重复值，同时保证排序规则不变。
改写后的可直接运行SQL如下：

WITH deduped_source AS (
    SELECT
        X.s_id,
        X.item_id,
        MIN(TRY_TO_NUMBER(Z.item_pg_nbr)) AS sort_key
    FROM table_1 X
    INNER JOIN table_2 Z
        ON Z.cmn_id = X.cmn_id
    WHERE X.s_id IN ('38301','40228')
    GROUP BY X.s_id, X.item_id
)
SELECT
    s_id,
    NULLIF(LISTAGG(item_id, ',') WITHIN GROUP (ORDER BY sort_key), '') AS item_id_txt
FROM deduped_source
GROUP BY s_id;

方案说明 #

• 第一层CTE按s_id+item_id分组，从根源上去除重复的item_id，不需要在外层LISTAGG中再加DISTINCT
• 对同一个item_id关联到的多个item_pg_nbr，取最小值作为排序键，保证该item_id出现在它首次出现的页码位置，和原始排序逻辑完全对齐；如果业务要求按最后出现位置排序，将MIN替换为MAX即可
• 原SQL中判断空字符串转NULL的逻辑，用NULLIF函数实现，写法更简洁，效果完全一致

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