如何在Snowflake的CONNECT BY中获取上一级PRIOR值？

Snowflake的CONNECT BY语法不支持在SELECT列表中使用PRIOR关键字引用父行字段，要实现子件单位用量乘以父件累计用量的需求，除了自定义子查询，还有两种更高效的方法：

1. 使用递归CTE（推荐） #

递归CTE是处理层级数据更灵活可靠的方式，能直接追踪父级的累计用量，逻辑清晰且不易出错：

WITH recursive bom_hierarchy AS (
    -- 顶层BOM节点初始化
    SELECT 
        1 AS level,
        parent,
        child,
        quantity_per AS total_quantity
    FROM bom
    WHERE type = 'BOM'

    UNION ALL

    -- 递归遍历子件并计算累计用量
    SELECT 
        bh.level + 1 AS level,
        b.parent,
        b.child,
        b.quantity_per * bh.total_quantity AS total_quantity
    FROM bom b
    JOIN bom_hierarchy bh ON b.parent = bh.child
)
SELECT level, parent, child, total_quantity
FROM bom_hierarchy
ORDER BY level, parent, child;

2. 结合CONNECT BY与窗口函数（局限性较强） #

如果坚持使用CONNECT BY语法，可以尝试用LAG()窗口函数，但需注意排序规则必须保证父行严格在子行之前，否则可能取到错误的父级数据：

SELECT 
    level,
    parent,
    child,
    quantity_per * LAG(quantity_per) OVER (ORDER BY level, parent, child) AS total_quantity
FROM bom
START WITH type = 'BOM'
CONNECT BY parent = PRIOR child
ORDER BY level, parent, child;

这种方法仅适用于层级结构简单、同层级节点排序不会干扰父子关联的场景，稳定性远不如递归CTE。

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