如何在Lateral Flatten结果中按分组实现Rank排序？

要实现你需要的按原tree顺序生成排名的效果，核心是利用窗口函数结合Lateral Flatten自带的顺序标识列。以下是具体实现方案：

完整SQL示例 #

WITH original_data AS (
    SELECT 1 AS id, 'AB -> BC -> CD' AS tree
    UNION ALL SELECT 2, 'A -> Z'
    UNION ALL SELECT 3, 'B'
)
SELECT 
    od.id,
    od.tree,
    f.value AS flattened,
    -- 按id和tree分组，根据flatten的顺序生成排名
    ROW_NUMBER() OVER (PARTITION BY od.id, od.tree ORDER BY f.seq) AS ranker
FROM original_data od,
-- 拆分tree字符串为数组并展开
LATERAL FLATTEN(INPUT => SPLIT(od.tree, ' -> ')) f;

关键说明 #

1. 拆分与展开：用SPLIT(od.tree, ' -> ')把tree列按分隔符拆成数组，再通过LATERAL FLATTEN展开成多行。
2. 排名逻辑：ROW_NUMBER()窗口函数会在每个id+tree的分组内，根据Flatten自动生成的seq列（记录元素在原数组中的位置）排序，生成连续的排名值，完美匹配你需要的顺序。
3. 替代方案：如果不需要严格连续的排名（比如有重复值时保留相同排名），可以换成RANK()或DENSE_RANK()，但你的场景用ROW_NUMBER()最贴合需求。

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