Redshift SQL提取多州名并拆分多行的最优实现方案问询

要实现从bio列提取所有美国州名并拆分为独立行，同时保留其他列数据，可以结合**递归CTE（或生成数字序列）**和REGEXP_SUBSTR函数来处理，具体方案如下：

方法1：使用递归CTE生成数字序列（兼容所有Redshift版本） #

WITH numbers AS (
    -- 生成1到10的数字序列，覆盖bio中可能出现的州名数量
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
),
state_pattern AS (
    -- 定义完整的美国州名正则匹配模式，包含所有州名（含带空格的州名）
    SELECT '(Alabama|Alaska|Arizona|Arkansas|California|Colorado|Connecticut|Delaware|Florida|Georgia|Hawaii|Idaho|Illinois|Indiana|Iowa|Kansas|Kentucky|Louisiana|Maine|Maryland|Massachusetts|Michigan|Minnesota|Mississippi|Missouri|Montana|Nebraska|Nevada|New Hampshire|New Jersey|New Mexico|New York|North Carolina|North Dakota|Ohio|Oklahoma|Oregon|Pennsylvania|Rhode Island|South Carolina|South Dakota|Tennessee|Texas|Utah|Vermont|Virginia|Washington|West Virginia|Wisconsin|Wyoming)' AS pattern
)
SELECT 
    t.userid,
    t.username,
    t.display_name,
    t.first_name,
    t.last_name,
    -- 提取第n个匹配的州名，'i'表示不区分大小写
    REGEXP_SUBSTR(t.bio, sp.pattern, 1, n, 'i') AS extracted_state
FROM mytable t
CROSS JOIN state_pattern sp
CROSS JOIN numbers num
-- 过滤掉未匹配到的结果
WHERE REGEXP_SUBSTR(t.bio, sp.pattern, 1, n, 'i') IS NOT NULL
ORDER BY t.userid, n;

方法2：使用generate_series生成数字序列（Redshift 1.0.1025+版本支持） #

如果你的Redshift版本支持generate_series，可以用更简洁的写法：

WITH state_pattern AS (
    SELECT '(Alabama|Alaska|Arizona|Arkansas|California|Colorado|Connecticut|Delaware|Florida|Georgia|Hawaii|Idaho|Illinois|Indiana|Iowa|Kansas|Kentucky|Louisiana|Maine|Maryland|Massachusetts|Michigan|Minnesota|Mississippi|Missouri|Montana|Nebraska|Nevada|New Hampshire|New Jersey|New Mexico|New York|North Carolina|North Dakota|Ohio|Oklahoma|Oregon|Pennsylvania|Rhode Island|South Carolina|South Dakota|Tennessee|Texas|Utah|Vermont|Virginia|Washington|West Virginia|Wisconsin|Wyoming)' AS pattern
)
SELECT 
    t.userid,
    t.username,
    t.display_name,
    t.first_name,
    t.last_name,
    REGEXP_SUBSTR(t.bio, sp.pattern, 1, num.n, 'i') AS extracted_state
FROM mytable t
CROSS JOIN state_pattern sp
-- 生成1到10的数字序列
CROSS JOIN (SELECT generate_series(1,10) AS n) num
WHERE REGEXP_SUBSTR(t.bio, sp.pattern, 1, num.n, 'i') IS NOT NULL
ORDER BY t.userid, num.n;

关键说明 #

• 数字序列上限：将10调整为实际业务中bio列可能出现的最大州名数量，避免不必要的计算。
• 正则模式维护：确保state_pattern中的州名完整准确，包含所有美国州名（如Rhode Island这类带空格的州名）。
• 不区分大小写：通过REGEXP_SUBSTR的'i'参数，确保匹配Maryland、maryland等不同大小写的州名。

测试结果 #

针对你的测试数据，执行后会得到如下结果：

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