无需窗口函数，Snowflake中如何提取列值变化数据？

原SQL通过两次窗口函数+CTE的写法，在处理3亿条数据时会因重复分区排序、中间数据存储产生性能瓶颈，以下是几种更高效的实现方式：

方法1：用QUALIFY替代CTE，合并窗口计算 #

直接在SELECT后用QUALIFY过滤，避免中间CTE的数据落地，且相同PARTITION BY/ORDER BY的窗口函数会被Snowflake一次性计算，减少资源消耗：

SELECT
    Col1,
    Col2,
    Col3,
    COALESCE(LEAD(Col3) OVER(PARTITION BY Col1 ORDER BY Col3), '0') AS Col4
FROM Table1
QUALIFY 
    LAG(Col2) OVER(PARTITION BY Col1 ORDER BY Col3) != Col2 
    OR LAG(Col2) OVER(PARTITION BY Col1 ORDER BY Col3) IS NULL;

方法2：利用集群键加速分区排序 #

如果Table1未按Col1和Col3集群，先设置集群键，让Snowflake按该顺序存储数据，窗口函数的排序步骤可直接复用已有存储顺序，无需额外排序：

-- 仅需执行一次集群键设置
ALTER TABLE Table1 CLUSTER BY (Col1, Col3);

-- 执行查询
SELECT
    Col1,
    Col2,
    Col3,
    COALESCE(LEAD(Col3) OVER(PARTITION BY Col1 ORDER BY Col3), '0') AS Col4
FROM Table1
QUALIFY 
    LAG(Col2) OVER(PARTITION BY Col1 ORDER BY Col3) != Col2 
    OR LAG(Col2) OVER(PARTITION BY Col1 ORDER BY Col3) IS NULL;

方法3：合并连续相同Col2的行，减少处理数据量 #

将同一Col1下连续相同Col2的行归为一组，仅保留每组首行，再计算lead，大幅降低窗口函数处理的数据行数：

WITH grouped_data AS (
    SELECT
        Col1,
        Col2,
        Col3,
        -- 生成连续相同Col2的分组ID
        ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col3) 
        - ROW_NUMBER() OVER(PARTITION BY Col1, Col2 ORDER BY Col3) AS group_id
    FROM Table1
)
SELECT
    Col1,
    Col2,
    MIN(Col3) AS Col3,
    COALESCE(LEAD(MIN(Col3)) OVER(PARTITION BY Col1 ORDER BY MIN(Col3)), '0') AS Col4
FROM grouped_data
GROUP BY Col1, Col2, group_id
ORDER BY Col1, Col3;

额外优化建议 #

• 调整Warehouse规格：使用更大的计算仓库（如XL、XXL），利用多节点并行处理大数据量。
• 数据类型优化：将Col3设为时间类型而非字符串，减少排序和计算的开销。

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