Snowflake非递减累计序列gaps and islands分组规则实现问题

针对你提出的非递减累计求和序列的gaps and islands分组需求，Snowflake中可以用递归CTE的方案实现，完美适配你描述的分组规则，避免整除取组号、普通累积窗口逻辑带来的偏差问题。

实现逻辑 #

利用非递减序列的有序特性，逐行判断当前值与所在组leading值的差值：

1. 第一行默认作为第一个分组的leading值，组号为1
2. 后续每行如果和当前组leading值的差值≤阈值（示例为7），则归属当前组
3. 差值超过阈值时，将当前值作为新分组的leading值，组号+1

完整实现代码 #

-- 构造示例测试数据，实际使用时替换为你的业务表即可
WITH input_data AS (
    SELECT value::INT AS num
    FROM TABLE(SPLIT_TO_TABLE('0 0 3 4 5 6 6 7 7 8 11 12 18 22', ' '))
),
-- 给序列添加行号，确保排序逻辑稳定，避免相同值排序混乱
ranked_data AS (
    SELECT num, ROW_NUMBER() OVER(ORDER BY num) AS rn
    FROM input_data
),
-- 递归CTE完成分组标记
recursive_groups AS (
    -- 锚点：取第一行作为第一个分组的起点
    SELECT 
        rn, 
        num, 
        num AS leading_val, 
        1 AS group_id
    FROM ranked_data
    WHERE rn = 1
    UNION ALL
    -- 递归逐行判断分组归属
    SELECT 
        r.rn,
        r.num,
        CASE WHEN r.num - rg.leading_val <= 7 THEN rg.leading_val ELSE r.num END AS leading_val,
        CASE WHEN r.num - rg.leading_val <= 7 THEN rg.group_id ELSE rg.group_id + 1 END AS group_id
    FROM ranked_data r
    INNER JOIN recursive_groups rg ON r.rn = rg.rn + 1
)
-- 聚合输出分组结果
SELECT 
    group_id,
    LISTAGG(num, ' ') WITHIN GROUP(ORDER BY rn) AS group_values
FROM recursive_groups
GROUP BY group_id
ORDER BY group_id;

运行结果 #

注意事项 #

如果你的序列长度超过1000，需要提前调整Snowflake会话的递归迭代次数限制，执行以下语句修改：
ALTER SESSION SET RECURSIVE_CTE_MAX_ITERATIONS = <你的序列最大长度>;

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