如何在AWS Redshift中将逗号分隔字符串拆分为多行？

问题场景 #

现有如下结构的表：

需要将每行的order_id按逗号拆分，生成对应原id的多行数据，最终结果如下：

由于Redshift不支持PostgreSQL的string_to_array、unnest等函数，需用Redshift兼容的方法实现。

解决方案1：递归CTE #

递归CTE是Redshift中处理字符串拆分的常用方法，无需依赖额外表：

WITH recursive_split AS (
    -- 初始行：提取第一个拆分项与剩余字符串
    SELECT
        id,
        CASE WHEN CHARINDEX(',', order_id) > 0 THEN LEFT(order_id, CHARINDEX(',', order_id)-1) ELSE order_id END AS split_order_id,
        CASE WHEN CHARINDEX(',', order_id) > 0 THEN RIGHT(order_id, LEN(order_id)-CHARINDEX(',', order_id)) ELSE '' END AS remaining_order_ids
    FROM your_table_name

    UNION ALL

    -- 递归迭代：处理剩余字符串直到为空
    SELECT
        id,
        CASE WHEN CHARINDEX(',', remaining_order_ids) > 0 THEN LEFT(remaining_order_ids, CHARINDEX(',', remaining_order_ids)-1) ELSE remaining_order_ids END AS split_order_id,
        CASE WHEN CHARINDEX(',', remaining_order_ids) > 0 THEN RIGHT(remaining_order_ids, LEN(remaining_order_ids)-CHARINDEX(',', remaining_order_ids)) ELSE '' END AS remaining_order_ids
    FROM recursive_split
    WHERE remaining_order_ids != ''
)
-- 输出最终拆分结果
SELECT id, split_order_id AS order_id
FROM recursive_split
ORDER BY id, order_id;

代码说明 #

1. 初始CTE部分：提取每行的第一个order_id，同时计算剩余未拆分的字符串；
2. 递归部分：重复拆分剩余字符串，直到剩余内容为空；
3. 最终查询：筛选拆分结果并排序。

解决方案2：数字表关联（大场景更高效） #

如果集群中有现成数字表（或临时生成），这种方法在处理大量数据时性能更优：

先创建临时数字表（若没有现成表）：

CREATE TEMP TABLE numbers AS
SELECT ROW_NUMBER() OVER () AS n
FROM SVV_TABLES LIMIT 100; -- LIMIT值需大于字符串最大拆分数量

再执行拆分逻辑：

SELECT
    t.id,
    TRIM(SPLIT_PART(t.order_id, ',', n.n)) AS order_id
FROM your_table_name t
JOIN numbers n ON n.n <= REGEXP_COUNT(t.order_id, ',') + 1
ORDER BY t.id, order_id;

代码说明 #

1. 数字表：生成连续数字序列，数量需覆盖所有行中order_id的最大元素个数；
2. SPLIT_PART：Redshift支持的函数，按逗号拆分字符串并取第n个部分；
3. JOIN条件：通过REGEXP_COUNT计算每行逗号数量，确定需关联的数字个数，避免无效匹配。

内容的提问来源于stack exchange，提问作者Gerardo Sáncheź Villaseñor