将Python逗号分隔列匹配逻辑转换为Redshift SQL实现

核心逻辑转换说明 #

你的需求是基于逗号分隔的City Names列，判断是否包含目标列表的所有元素并赋值，用Redshift SQL可通过CASE语句配合字符串/数组函数实现，以下是具体方案：

方案1：基础字符串匹配（适合无空格/特殊字符的城市名称） #

直接用POSITION函数检查每个目标城市是否存在于City Names中，逻辑清晰易读：

SELECT
    "City Names",
    CASE
        -- 匹配list1所有元素（示例：New York, London, Tokyo），赋值3
        WHEN POSITION('New York' IN "City Names") > 0
             AND POSITION('London' IN "City Names") > 0
             AND POSITION('Tokyo' IN "City Names") > 0
        THEN 3
        -- 匹配list2所有元素（示例：Paris, Berlin） 或 list3所有元素（示例：Sydney, Melbourne），赋值2
        WHEN (POSITION('Paris' IN "City Names") > 0 AND POSITION('Berlin' IN "City Names") > 0)
             OR (POSITION('Sydney' IN "City Names") > 0 AND POSITION('Melbourne' IN "City Names") > 0)
        THEN 2
        -- 其他情况可按需赋值（比如0或NULL）
        ELSE 0
    END AS "#_of_target_cities"
FROM df1;

方案2：数组+去空格匹配（适合带空格/格式不统一的城市名称） #

如果City Names中的城市前后有空格（比如"New York, London"），先将字符串转为数组并去除每个元素的空格，再判断元素是否存在：

SELECT
    "City Names",
    CASE
        -- 检查list1所有元素是否都在处理后的城市列表中
        WHEN 'New York' IN (SELECT TRIM(UNNEST(STRING_TO_ARRAY("City Names", ','))))
             AND 'London' IN (SELECT TRIM(UNNEST(STRING_TO_ARRAY("City Names", ','))))
             AND 'Tokyo' IN (SELECT TRIM(UNNEST(STRING_TO_ARRAY("City Names", ','))))
        THEN 3
        -- 检查list2或list3的所有元素是否都存在
        WHEN ('Paris' IN (SELECT TRIM(UNNEST(STRING_TO_ARRAY("City Names", ','))))
              AND 'Berlin' IN (SELECT TRIM(UNNEST(STRING_TO_ARRAY("City Names", ','))))
             )
             OR
             ('Sydney' IN (SELECT TRIM(UNNEST(STRING_TO_ARRAY("City Names", ','))))
              AND 'Melbourne' IN (SELECT TRIM(UNNEST(STRING_TO_ARRAY("City Names", ','))))
             )
        THEN 2
        ELSE 0
    END AS "#_of_target_cities"
FROM df1;

关键注意事项 #

• 优先级：CASE语句从上到下执行，先判断list1的条件，确保同时满足list1和list2时优先赋值3
• 替换目标列表：把示例中的城市名称替换成你实际的list1、list2、list3元素即可
• 特殊字符处理：如果城市名称包含逗号或其他特殊字符，建议用方案2，避免匹配错误

内容的提问来源于stack exchange，提问作者Saikrishna Suresh