Redshift中实现门店递归更新，关联全链路至最新门店

实现思路 #

利用Redshift支持的**递归CTE（WITH RECURSIVE）**遍历门店更替链路，定位每条链路的最终最新门店，再将该链路下所有门店记录关联到这个最新门店，最终为新增的store_LFL字段统一赋值为最新门店ID（示例中为717）。

具体SQL实现 #

假设你的表名为store_history，以下是完整的递归更新逻辑：

通用场景：自动识别所有链路的最新门店 #

首先通过递归CTE生成全链路门店与对应最新门店的映射关系，再批量更新原表：

WITH RECURSIVE store_linkage AS (
    -- 基础节点：所有无后继的门店（即各链路的最新门店）
    SELECT 
        store_end AS current_store,
        store_end AS latest_store
    FROM store_history
    WHERE store_end NOT IN (SELECT store_prev FROM store_history)
    UNION ALL
    -- 递归回溯：关联所有前驱门店，继承最新门店ID
    SELECT 
        sh.store_prev AS current_store,
        sl.latest_store
    FROM store_history sh
    JOIN store_linkage sl ON sh.store_end = sl.current_store
)
UPDATE store_history
SET store_LFL = sl.latest_store
FROM store_linkage sl
WHERE store_history.store_prev = sl.current_store
OR store_history.store_end = sl.current_store;

指定最新门店（示例717）的简化写法 #

若已知目标最新门店固定为717，可定向递归该链路：

WITH RECURSIVE seven_one_seven_chain AS (
    -- 起点：最新门店717自身
    SELECT 
        store_end AS store_node,
        '717' AS store_LFL
    FROM store_history
    WHERE store_end = '717'
    UNION ALL
    -- 回溯所有关联的前驱门店
    SELECT 
        sh.store_prev AS store_node,
        sosc.store_LFL
    FROM store_history sh
    JOIN seven_one_seven_chain sosc ON sh.store_end = sosc.store_node
)
UPDATE store_history
SET store_LFL = sosc.store_LFL
FROM seven_one_seven_chain sosc
WHERE store_history.store_prev = sosc.store_node
OR store_history.store_end = sosc.store_node;

注意事项 #

• 确保store_prev和store_end字段数据类型一致，避免关联错误；
• 大数据量场景下，建议为store_end字段添加索引提升递归效率；
• Redshift递归CTE默认最大层级为100，若门店链路超出此限制，需调整配置max_recursion_depth。

内容的提问来源于stack exchange，提问作者john.new.1998