将Oracle递归查询迁移至Redshift：替换CONNECT BY等语句求助

没问题，我来帮你把这段Oracle的递归拆分逻辑改成Redshift支持的写法，用递归CTE（WITH RECURSIVE）替代CONNECT BY、LEVEL和PRIOR，同时完整保留层级关系。

改写后的Redshift代码 #

WITH RECURSIVE LEVEL_COUNTING AS (
    -- 初始步骤：提取每条记录的第一个元素，层级从1开始
    SELECT
        t.*,
        REGEXP_SUBSTR(t.STR, '[^,]+', 1, 1) AS SINGLE_ELEMENT,
        1 AS element_no,
        -- 计算剩余未拆分的字符串（去掉第一个元素和后续的逗号）
        CASE
            WHEN INSTR(t.STR, ',') > 0 THEN REGEXP_REPLACE(t.STR, '^[^,]+,', '')
            ELSE ''
        END AS remaining_str
    FROM t
    WHERE t.STR IS NOT NULL AND t.STR <> '' -- 过滤空字符串/NULL，和原Oracle行为对齐
    
    UNION ALL
    
    -- 递归步骤：继续拆分剩余字符串，层级递增
    SELECT
        parent.*,
        REGEXP_SUBSTR(parent.remaining_str, '[^,]+', 1, 1) AS SINGLE_ELEMENT,
        parent.element_no + 1 AS element_no,
        CASE
            WHEN INSTR(parent.remaining_str, ',') > 0 THEN REGEXP_REPLACE(parent.remaining_str, '^[^,]+,', '')
            ELSE ''
        END AS remaining_str
    FROM LEVEL_COUNTING parent
    WHERE parent.remaining_str <> '' -- 剩余字符串为空时停止递归
)
-- 最终输出需要的字段，移除临时用的remaining_str
SELECT id, STR, SINGLE_ELEMENT, element_no
FROM LEVEL_COUNTING;

关键逻辑说明 #

1. 替代LEVEL：用element_no字段模拟原Oracle的LEVEL，初始设为1，递归时每次加1，完美保留层级计数。
2. 替代CONNECT BY和PRIOR：
   • 递归CTE的初始查询负责生成每条记录的第一个拆分元素；
   • 递归部分通过关联上一层的结果（parent），继续拆分剩余字符串，自然保证了每个id的拆分是独立的（对应原代码的id = PRIOR id）；
   • 原代码里的PRIOR DBMS_RANDOM.VALUE是为了避免CONNECT BY产生重复记录，这里通过remaining_str <> ''的终止条件，完全不会出现循环或重复。
3. 字符串拆分逻辑：用REGEXP_SUBSTR提取当前层级的元素，用REGEXP_REPLACE生成剩余待拆分的字符串，和原Oracle的拆分逻辑完全一致。

注意事项 #

• 如果你的业务允许STR为空或NULL的记录生成对应结果，可以去掉初始查询里的WHERE t.STR IS NOT NULL AND t.STR <> ''条件，此时这类记录会生成一条SINGLE_ELEMENT为NULL、element_no为1的记录。
• Redshift的正则函数语法和Oracle基本一致，这段代码可以直接运行，不需要额外调整正则表达式。

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