如何在AWS Athena SQL中实现分组动态偏移的LAG计算？

我有一张表，包含数字列Num、floor10列（将Num向下取整为10的倍数，例如8对应0、17对应10）以及其他数据列。需要新增floor10_prev列，展示每行在同一Name分组下floor10的前一个不同取值。每个不同的floor10值对应的行数不固定，尝试使用LAG函数但未得到正确结果；用子查询先获取distinct的floor10和Name再关联主表的方法，在大数据集上执行效率极低。

示例输入表 #

期望输出表 #

错误的SQL尝试 #

SELECT
Num,
floor10,
Name,
LAG(floor10) OVER (PARTITION BY Name, Num ORDER BY Name, Num) AS floor10_prev
FROM table

高效解决方案 #

使用窗口函数组合实现，无需额外关联操作，性能更优：

WITH floor_groups AS (
    SELECT
        Num,
        floor10,
        Name,
        -- 标记当前行是否是同一Name分组内该floor10值的首次出现
        CASE 
            WHEN floor10 != LAG(floor10) OVER (PARTITION BY Name ORDER BY floor10, Num) 
            THEN 1 
            ELSE 0 
        END AS is_new_floor,
        -- 获取首次出现时对应的前一个不同floor10值
        LAG(floor10) OVER (PARTITION BY Name ORDER BY floor10, Num) AS temp_prev
    FROM your_table
)
SELECT
    Num,
    floor10,
    Name,
    -- 用LAST_VALUE填充同一floor10组内所有行的prev值，忽略空值
    LAST_VALUE(CASE WHEN is_new_floor = 1 THEN temp_prev END IGNORE NULLS) 
        OVER (PARTITION BY Name ORDER BY floor10, Num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
    AS floor10_prev
FROM floor_groups
ORDER BY Name, floor10, Num;

逻辑说明 #

1. 第一步通过LAG(floor10)判断当前行是否是同一Name分组内新的floor10值，并记录首次出现时的前一个floor10值。
2. 第二步使用LAST_VALUE结合IGNORE NULLS，将同一floor10组内所有行的floor10_prev填充为该组首次出现时的前一个值，确保同一组内所有行的floor10_prev一致。
3. 排序规则按Name、floor10、Num排列，与示例输出一致。

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