如何修改LAG函数以获取上一个不同的作业日期值？

这个问题我之前也碰到过，普通的LAG()函数是按行偏移来取前一行的值，没法直接跳过重复的日期，不过我们可以通过先对不同日期分组标记，再结合窗口函数来实现你要的效果。

解决方案思路 #

核心思路是先把每个员工的不同作业日期单独分组，给每个唯一日期分配一个等级，然后针对这些唯一日期来取上一个不同的日期，最后再关联回原表，让所有重复的日期行都对应同一个“上一个不同日期”。

具体SQL示例 #

假设你的表名为your_table，以下是可直接参考的代码（适用于支持窗口函数的数据库，比如SQL Server、PostgreSQL、Oracle等）：

WITH ranked_dates AS (
    -- 给每个员工的不同作业日期分配等级，最新日期等级为1
    SELECT 
        "Employee Number",
        "Job Date",
        DENSE_RANK() OVER (PARTITION BY "Employee Number" ORDER BY "Job Date" DESC) AS date_rank
    FROM your_table
),
distinct_date_lag AS (
    -- 针对每个唯一日期，获取它的上一个不同日期
    SELECT 
        "Employee Number",
        "Job Date",
        LAG("Job Date") OVER (PARTITION BY "Employee Number" ORDER BY date_rank) AS previous_distinct_date
    FROM ranked_dates
    -- 去重，只保留每个员工的唯一日期行
    GROUP BY "Employee Number", "Job Date", date_rank
)
-- 关联回原表，让所有重复日期行都对应正确的上一个不同日期
SELECT 
    t."Job Date",
    ddl.previous_distinct_date AS "Previous Job Date"
FROM your_table t
JOIN distinct_date_lag ddl 
    ON t."Employee Number" = ddl."Employee Number" 
    AND t."Job Date" = ddl."Job Date"
ORDER BY t."Employee Number", t."Job Date" DESC;

代码解释 #

1. ranked_dates 公共表表达式：用DENSE_RANK()窗口函数，按员工编号分区，按作业日期倒序排序，给每个不同的日期分配唯一的等级值。比如最新的日期等级是1，下一个不同的日期等级是2，以此类推。
2. distinct_date_lag 公共表表达式：先通过GROUP BY去重，只保留每个员工的唯一日期行，然后用LAG()获取当前等级的上一个等级对应的日期，也就是我们需要的“上一个不同作业日期”。
3. 最终关联查询：把原表和去重后的日期映射表关联，这样原表中所有重复的作业日期行，都会对应到同一个“上一个不同日期”，完全匹配你想要的输出效果。

补充说明 #

如果你的数据库支持LAG()的IGNORE NULLS参数（比如Oracle），其实还有更简洁的写法，但上面的方案是通用型的，几乎所有支持窗口函数的数据库都能运行。

备注：内容来源于stack exchange，提问作者cgwoz