如何在Databricks SQL中用CTE实现递归循环（支持多轮执行）

Databricks SQL支持递归CTE（WITH RECURSIVE），可以直接替代你手动定义20个CTE的重复工作，核心思路是通过锚点查询初始化第一轮数据，再通过递归查询自动迭代生成后续轮次，同时控制迭代次数不超过20次。

替代方案代码示例 #

WITH RECURSIVE recursive_loop AS (
    -- 锚点成员：初始化第一轮数据（对应你原来的H）
    SELECT 
        -- 替换为你原来H中的字段，新增迭代次数字段标记第一轮
        col1, col2, ...,
        1 AS iteration
    FROM your_initial_data_source -- 替换为你原来H的数据源逻辑
    WHERE -- 保留你原来H中的过滤条件
    
    UNION ALL
    
    -- 递归成员：基于上一轮结果生成下一轮数据（对应H1→H2→…→H20的逻辑）
    SELECT 
        -- 替换为你原来Hn基于Hn-1的字段计算逻辑
        new_col1, new_col2, ...,
        rl.iteration + 1 AS iteration
    FROM recursive_loop rl
    -- 替换为你原来Hn与Hn-1的关联/计算逻辑（比如JOIN或字段转换）
    WHERE rl.iteration < 20 -- 控制迭代总次数，按需调整数字
)
-- 自动合并所有迭代轮次的结果（对应你原来的H_ALL）
SELECT * FROM recursive_loop

关键逻辑说明 #

• 锚点成员：对应你最初的H，生成第一轮初始数据，用iteration字段标记轮次。
• 递归成员：每一轮自动基于上一轮的结果生成新数据，iteration自增1，通过iteration < 20限制最多迭代20次（最终包含1到20轮的所有数据）。
• 结果合并：递归CTE会自动聚合所有迭代轮次的数据，无需手动写20次UNION ALL。

适配原有代码的注意点 #

1. 将锚点成员里的字段、数据源、过滤条件替换为你原来H中的逻辑；
2. 将递归成员里的字段计算、关联逻辑替换为你原来H1基于H、H2基于H1的重复逻辑；
3. 修改iteration < 20中的数字即可调整迭代次数（比如要跑30次就改成30）。

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