如何在Amazon Redshift中提取两列的公共字符串并生成新列Col3？

问题背景 #

假设Redshift中有表X，数据如下：

Col1      Col2
C,B,A     B

需要生成第三列Col3，存储Col1和Col2的公共字符串（顺序无关），期望输出：

Col1      Col2    Col3
C,B,A     B       B

解决方法 #

场景一：Col2为单个值 #

如果Col2始终是单个字符串（比如示例中的B），直接用POSITION函数判断匹配即可，写法简单高效：

SELECT 
    Col1,
    Col2,
    CASE WHEN POSITION(Col2 IN Col1) > 0 THEN Col2 ELSE '' END AS Col3
FROM X;

场景二：Col2为多值（逗号分隔） #

如果Col2也可能是逗号分隔的多个值（比如A,C），需要拆分字符串后取交集再拼接，通用SQL如下：

WITH split_col1 AS (
    -- 拆分Col1为单行值
    SELECT 
        Col1,
        Col2,
        REGEXP_SUBSTR(Col1, '[^,]+', 1, n) AS val
    FROM X
    -- 生成足够的行号用于拆分，10可根据实际最大元素数调整
    JOIN (SELECT ROW_NUMBER() OVER () AS n FROM generate_series(1, 10)) nums
    ON n <= REGEXP_COUNT(Col1, ',') + 1
),
split_col2 AS (
    -- 拆分Col2为单行值
    SELECT 
        Col2,
        REGEXP_SUBSTR(Col2, '[^,]+', 1, n) AS val
    FROM X
    JOIN (SELECT ROW_NUMBER() OVER () AS n FROM generate_series(1, 10)) nums
    ON n <= REGEXP_COUNT(Col2, ',') + 1
),
common_vals AS (
    -- 匹配两列的公共值
    SELECT 
        s1.Col1,
        s1.Col2,
        s1.val
    FROM split_col1 s1
    JOIN split_col2 s2 
        ON s1.Col2 = s2.Col2 
        AND s1.val = s2.val
)
-- 拼接公共值为逗号分隔的字符串
SELECT 
    Col1,
    Col2,
    LISTAGG(val, ',') WITHIN GROUP (ORDER BY val) AS Col3
FROM common_vals
GROUP BY Col1, Col2;

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