SQL层级向下填充：实体表层级补全需求实现

现有Table_Entity表，每行对应一个实体，通过id排序呈现隐含层级关系，需转换为Table_Entity_OUTPUT表，转换规则为上级层级的非空值向下填充，直至遇到同层级的新非空值或更高层级的新非空值。

实现SQL #

WITH entity_groups AS (
    SELECT 
        id,
        Level_1,
        Level_2,
        Level_3,
        Level_4,
        -- 生成Level_1分组标识：每出现非空Level_1则分组+1
        SUM(CASE WHEN Level_1 IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY id) AS grp1,
        -- 生成Level_2分组标识：在Level_1分组内，每出现非空Level_2则分组+1
        SUM(CASE WHEN Level_2 IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY grp1 ORDER BY id) AS grp2,
        -- 生成Level_3分组标识：在Level_1+Level_2分组内，每出现非空Level_3则分组+1
        SUM(CASE WHEN Level_3 IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY grp1, grp2 ORDER BY id) AS grp3
    FROM Table_Entity
)
SELECT 
    id,
    -- 填充Level_1：取当前Level_1分组内的最后非空值
    MAX(Level_1) OVER (PARTITION BY grp1 ORDER BY id) AS Level_1,
    -- 填充Level_2：取当前Level_1+Level_2分组内的最后非空值
    MAX(Level_2) OVER (PARTITION BY grp1, grp2 ORDER BY id) AS Level_2,
    -- 填充Level_3：取当前Level_1+Level_2+Level_3分组内的最后非空值
    MAX(Level_3) OVER (PARTITION BY grp1, grp2, grp3 ORDER BY id) AS Level_3,
    -- 填充Level_4：继承当前Level_1+Level_2+Level_3分组内的最后非空值
    MAX(Level_4) OVER (PARTITION BY grp1, grp2, grp3 ORDER BY id) AS Level_4
FROM entity_groups
ORDER BY id;

逻辑说明 #

1. 分组标识生成：
   • grp1：按id顺序累计非空Level_1的出现次数，确保同一个Level_1下的所有行属于同一分组，当出现新的Level_1值时，分组自动重置。
   • grp2：在每个grp1内部累计非空Level_2的出现次数，实现Level_2在所属Level_1范围内的分组重置。
   • grp3：同理，在grp1+grp2的组合分组内累计非空Level_3的出现次数，为Level_3和Level_4的填充提供分组边界。
2. 层级填充：
   利用MAX() OVER()窗口函数，在对应分组内取当前行及之前的最后一个非空值，既实现了向下填充，又能在遇到同层级或更高层级的新非空值时，通过分组变化自动终止旧值的填充，完全匹配需求规则。

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