SQL 滞后于上一个非重复值

发布于02月01日

有没有办法修改一个Lag函数，使其不仅返回前一行，而且返回到前一个不同的值？

What is currently happening:

 Job Date      Previous Job Date   
-----------    ---------------- 
06/10/2013             -
06/10/2013         06/10/2013
06/10/2013         06/10/2013
07/16/2014         06/10/2013
07/16/2014         07/16/2014
06/07/2015         07/16/2014                   
06/07/2015         06/07/2015
06/07/2015         06/07/2015

What I want:个

 Job Date      Previous Job Date   
-----------    ---------------- 
06/10/2013             -
06/10/2013             -
06/10/2013             -
07/16/2014         06/10/2013
07/16/2014         06/10/2013
06/07/2015         07/16/2014                   
06/07/2015         07/16/2014 
06/07/2015         07/16/2014

现在我用的是

Lag(a1."Job Date", 1) over (partition by a1."Employee Number" Order By a1."Employee Number")

但这将返回先前的ROWS记录，而不是先前的DISTINCT或非匹配记录.这是否可以通过LAG或其他函数来实现？

推荐答案

这是一个巧妙的把戏.如果使用介于"1之前"之间的范围，则窗口从前一个不同的值开始.假设您有一些要分区的键，但如果不是这样，则只需删除PARTITION BY子句.

菲德尔:https://dbfiddle.uk/3tPzIiDN

create table some_test_data
  (
    id integer,
    job_date date
  );

insert into some_test_data values
  (1, date '2023-06-01'),
  (1, date '2023-06-01'),
  (1, date '2023-06-01'),
  (1, date '2023-07-01'),
  (1, date '2023-07-01'),
  (1, date '2023-07-16'),
  (1, date '2023-07-16'),
  (1, date '2023-07-16');

select s.*,
       max(job_date) over (
         partition by id
         order by job_date
         range between unbounded preceding and 1 preceding)
  from some_test_data s;

或者，具有相关子查询的等效查询

select t1.*,
       ( select max(t2.job_date)
           from some_test_data t2
          where t1.id = t2.id
            and t1.job_date > t2.job_date
       ) as last_distinct_job_date
  from some_test_data t1;