如何在Snowflake SQL中实现分组内数组的行间差异对比？

需求说明 #

按Id分组后，将当前行的column1数组与下一行数组对比：

• column2：存储当前行数组中不存在于下一行数组的元素
• column3：统计column2的元素数量

初始数据集 #

期望结果 #

解决方案 #

核心思路：

1. 用窗口函数LEAD()按Id分组获取下一行的数组
2. 用ARRAY_EXCEPT()计算当前行与下一行数组的差异（当前行独有的元素）
3. 用ARRAY_SIZE()统计差异数组的元素数量
4. 处理分组内最后一行的特殊情况（无下一行时，column2为当前数组，column3留空）

完整SQL代码 #

-- 生成测试数据
WITH test_data AS (
    SELECT * FROM VALUES
        (1, ARRAY_CONSTRUCT('cat', 'dog', 'bird')),
        (1, ARRAY_CONSTRUCT('cat', 'bird')),
        (1, ARRAY_CONSTRUCT('cat', 'bear', 'tiger')),
        (1, ARRAY_CONSTRUCT('cat', 'tiger')),
        (2, ARRAY_CONSTRUCT('cat', 'tiger')),
        (2, ARRAY_CONSTRUCT('cat', 'bear', 'tiger')),
        (2, ARRAY_CONSTRUCT('cat', 'bird')),
        (3, ARRAY_CONSTRUCT('tiger')),
        (3, ARRAY_CONSTRUCT('cat', 'bird'))
    AS t(Id, column1)
),
-- 为每组添加行号，确保顺序正确（若原表有固定排序字段，替换ORDER BY后的内容）
ranked_data AS (
    SELECT 
        Id,
        column1,
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY 1) AS row_num,
        LEAD(column1) OVER (PARTITION BY Id ORDER BY row_num) AS next_row_array
    FROM test_data
)
SELECT
    Id,
    column1,
    -- 处理最后一行：无下一行时返回当前数组，否则返回差异数组
    CASE 
        WHEN next_row_array IS NOT NULL THEN ARRAY_EXCEPT(column1, next_row_array)
        ELSE column1
    END AS column2,
    -- 统计差异数组长度，最后一行留空
    CASE 
        WHEN next_row_array IS NOT NULL THEN ARRAY_SIZE(ARRAY_EXCEPT(column1, next_row_array))
        ELSE NULL
    END AS column3
FROM ranked_data
ORDER BY Id, row_num;

关键函数说明 #

• LEAD(column1) OVER (PARTITION BY Id ORDER BY row_num)：按Id分组，获取当前行的下一行column1数组
• ARRAY_EXCEPT(arr1, arr2)：返回存在于arr1但不存在于arr2的元素数组
• ARRAY_SIZE(arr)：返回数组的元素数量

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