如何在Spark SQL中获取当前行的上一条不等薪资记录

现有员工薪资表如下：

需要新增prevSalary列，展示员工的上一条不同薪资记录；若当前是该员工的第一条薪资记录，或之前没有不同薪资，则显示null或0。期望结果如下：

尝试过普通LAG函数：

select *, lag(salary) over(partition by id order by startdate) as prevSalary from tablename;

但该函数仅取上一条记录的薪资，无法跳过连续相同的薪资值，不符合需求。也尝试过按id和salary分区，仍未解决问题。

注：表中包含多个员工id，示例仅展示单个id；日期记录连续无间隙。

核心思路是先给每个员工的连续相同薪资记录分组，再针对每个薪资组获取上一个不同薪资组的薪资值，最后将该值填充到当前组的所有记录中。

完整SQL语句 #

WITH salary_groups AS (
    SELECT 
        id, startdate, enddate, salary,
        SUM(CASE WHEN salary = LAG(salary) OVER (PARTITION BY id ORDER BY startdate) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY id ORDER BY startdate) AS group_id
    FROM tablename
),
group_salaries AS (
    SELECT 
        id, group_id,
        LAG(salary) OVER (PARTITION BY id ORDER BY group_id) AS prev_group_salary
    FROM salary_groups
    GROUP BY id, group_id, salary
)
SELECT 
    sg.id, sg.startdate, sg.enddate, sg.salary,
    COALESCE(gs.prev_group_salary, 0) AS prevSalary -- 不需要转0的话，直接用gs.prev_group_salary
FROM salary_groups sg
JOIN group_salaries gs ON sg.id = gs.id AND sg.group_id = gs.group_id
ORDER BY sg.id, sg.startdate DESC;

逻辑说明 #

1. 标记薪资分组：
   用SUM()结合LAG()判断，为连续相同薪资的记录分配同一个group_id——如果当前薪资和上一条相同则加0，否则加1，实现同薪资记录归为一组。
2. 获取上组薪资：
   对每个薪资组去重后，用LAG()按组的顺序取上一个组的薪资值，得到每个组对应的历史不同薪资。
3. 关联填充结果：
   将分组后的历史薪资关联回原始数据，让同组的所有记录共享同一个prevSalary值；用COALESCE可以把null转为0，不需要的话直接保留null即可。

该方案适配MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库，排序规则可根据实际需求调整。

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