使用LISTAGG时如何去除结果首尾逗号并替换空值为'NO VALUE'

问题核心原因 #

你遇到的四类异常本质是两个代码bug导致的，单纯用LTRIM裁剪首尾字符无法彻底解决：

1. 原有正则表达式末尾多了一个|分隔符，会默认匹配空字符串，导致REGEXP_REPLACE生成大量无意义空串
2. LISTAGG拼接时会把空串作为有效元素处理，每遇到一个空串就会生成一个分隔符，最终出现连续逗号、首尾逗号、整行仅返回逗号的问题；如果所有元素都是空串，就会返回空值。

修正后完整代码 #

WITH cleaned_data AS (
    SELECT 
        s_id,
        -- 修正正则逻辑，清洗后将空串统一转为NULL
        NULLIF(
            REGEXP_REPLACE(rewrd_card_nbr, 'null|id not found|no value|no-value|blank|\'', '', 1, 0, 'i'),
            ''
        ) AS valid_result,
        MIN(TRY_TO_NUMBER(other.item_pg_nbr)) AS item_pg_nbr
    FROM table_1 S
    JOIN table_2 P
        ON P.click_stream_integration_id = S.click_stream_integration_id
    WHERE s_id IN (
        '1217423213611638899263','2988822682711638898987','8131301001481638906163',
        '8030711031105116372833','7806180295006814765443','6215684539960126690425',
        '6502689315628777977858','6959274311947723444672','1235659053876083630210',
        '5958673331149776209626','1587460374618074937485'
    )
    GROUP BY s_id, rewrd_card_nbr
)
SELECT 
    s_id,
    -- 聚合结果为空时统一返回NO VALUE
    CASE 
        WHEN LISTAGG(valid_result, ',') WITHIN GROUP (ORDER BY item_pg_nbr) IS NULL 
        THEN 'NO VALUE'
        ELSE LISTAGG(valid_result, ',') WITHIN GROUP (ORDER BY item_pg_nbr)
    END AS final_result,
    MIN(item_pg_nbr) AS item_pg_nbr
FROM cleaned_data
GROUP BY s_id
ORDER BY TRY_TO_NUMBER(item_pg_nbr);

关键修正说明 #

• 修复正则语法错误：删除原正则末尾多余的|，新增不区分大小写匹配参数，避免漏匹配大小写混写的无效值；所有匹配到的无效值替换后，用NULLIF把空串统一转为NULL。
• 从根源避免多余逗号：LISTAGG会自动忽略NULL值，不会为NULL值生成分隔符，不需要额外用LTRIM/RTRIM裁剪首尾，也不会出现中间连续逗号的问题。
• 空值处理：如果某个s_id下所有清洗后的值都是无效值，LISTAGG会返回NULL，通过CASE判断直接替换为要求的NO VALUE即可。

问题复现参考截图 #

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