Snowflake中用前列非空值替换列NULL值的可扩展方案咨询

我在Snowflake中有一张名为temp的表，示例数据如下：

需求是将每一行中的NULL值替换为前一列的非空值，预期输出如下：

预期输出 #

我当前用逐列嵌套CTE+coalesce的方式实现，代码如下：

with week2 as(
select name,
       week1,
       coalesce(week2,week1) as week2,
       week3,
       week4

from temp),

 week3 as(
select name,
       week1,
       week2,
       coalesce(week3, week2) as week3,
       week4

from week2)

select name,
       week1,
       week2,
       week3,
       coalesce(week4, week3) as week4

from week3;

这个方法能正常运行，但扩展性很差——如果后续新增week5、week6等列，需要手动修改代码添加新的CTE或coalesce逻辑。希望能得到更通用、扩展性更好的解决方案。

方案1：UNPIVOT + LAST_VALUE + PIVOT（推荐，扩展性强） #

这种方法通过将列转成行（UNPIVOT），用窗口函数LAST_VALUE向前填充NULL，再转回列（PIVOT），不管有多少week列都能自动适配：

WITH unpivoted AS (
    SELECT 
        name,
        REGEXP_SUBSTR(week_col, '\\d+')::INT AS week_num,
        week_col,
        week_val
    FROM temp
    UNPIVOT (
        week_val FOR week_col IN (week1, week2, week3, week4)
    )
),
filled AS (
    SELECT 
        name,
        week_col,
        LAST_VALUE(week_val IGNORE NULLS) OVER (
            PARTITION BY name 
            ORDER BY week_num 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS filled_val
    FROM unpivoted
)
SELECT *
FROM filled
PIVOT (
    MAX(filled_val) FOR week_col IN (week1, week2, week3, week4)
)
ORDER BY name;

如果需要支持动态的week列（比如不确定列的数量），可以用Snowflake动态SQL，通过查询INFORMATION_SCHEMA.COLUMNS获取所有以week开头的列，再自动拼接UNPIVOT和PIVOT的列列表，完全无需手动修改代码。

方案2：数组函数处理（代码简洁，适合列数相对固定的场景） #

利用Snowflake的数组函数，将week列转成数组后递归填充NULL，再展开为列：

SELECT 
    name,
    arr[0]::INT AS week1,
    arr[1]::INT AS week2,
    arr[2]::INT AS week3,
    arr[3]::INT AS week4
FROM (
    SELECT 
        name,
        ARRAY_ACCUMULATE(
            ARRAY_CONSTRUCT(week1, week2, week3, week4),
            (acc, val) -> IFNULL(val, acc)
        ) AS arr
    FROM temp
);

这个方法代码更简洁，但新增列时需要修改数组构造和索引映射，扩展性弱于方案1，但比原逐列coalesce的方式更易维护。

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