如何在Snowflake中用LAST_VALUE实现分组层级结构的父ID匹配？

问题描述 #

需要在带分组的排序层级结构中确定子节点的父节点，尝试使用LAST_VALUE函数但无法添加动态条件，无法在分区内选取当前行之前层级为当前层级减1的最后一个值。

示例输入数据 #

期望输出 #

解决方案 #

可以通过窗口函数结合条件判断实现，核心逻辑是在同一分组内按ID顺序遍历，筛选出当前行之前层级等于当前层级减1的最近记录ID作为父节点。以下是兼容多数SQL数据库（如PostgreSQL、BigQuery、Snowflake等）的实现代码：

SELECT
    "Group",
    ID,
    Level,
    CASE 
        WHEN Level = 1 THEN NULL
        ELSE LAST_VALUE(CASE WHEN Level = curr_level - 1 THEN ID END IGNORE NULLS) 
             OVER (PARTITION BY "Group" ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    END AS "Parent ID"
FROM (
    SELECT 
        *,
        Level AS curr_level
    FROM your_table
) t
ORDER BY "Group", ID;

逻辑说明 #

1. 分区与排序：按Group分区，确保父节点查找范围限定在同一分组内；按ID排序，保证层级结构的顺序与数据排列一致。
2. 条件筛选：用CASE语句标记出层级等于当前层级减1的记录ID，其余情况设为NULL。
3. 取最近匹配值：通过LAST_VALUE(...) IGNORE NULLS获取当前行之前最后一个非NULL的匹配ID，即最近的父节点。
4. 根节点处理：层级为1的节点直接返回NULL，因为没有父节点。

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