postgresql - 在 POSTGRESQL 中获取上一行结束日期作为下一行开始日期

Question

我在这里有一张表，其中包含每个员工的hire_date和effectivity_date，每次都会更新。我想将上一行的生效日期作为下一行start_date，并且上一个日期的end_date将为负1以避免重叠。另外，我希望最后一条记录的end_date为 '2030-12-31' 作为默认值，并将Hire Date作为第一条记录的start_date。请帮我解决这个问题。

当前表：

预期输出：

标签： postgresqllaglead

Answer 1

我在您的原始表中发现了一些重复项，例如Ross并且Chandler有 2 个条目具有完全相同的数据集。如果那是复制/粘贴错误，那么我复制了您的案例：

create table testdt(name varchar, hire_dt date, eff_dt date);


insert into testdt values('Rachel','2021-03-29','2021-03-29');
insert into testdt values('Rachel','2021-03-29','2021-05-14');
insert into testdt values('Ross','2021-06-18','2021-06-18');
insert into testdt values('Monica','2021-07-21','2021-07-21');
insert into testdt values('Chandler','2021-04-12','2021-04-12');
insert into testdt values('Judy','2021-04-26','2021-04-26');
insert into testdt values('Judy','2021-04-26','2021-05-10');
insert into testdt values('Judy','2021-04-26','2021-07-01');

现在，正如您的标签所提到的那样，您需要使用LAGandLEAD函数来相应地从上一行和下一行获取列。

如果您在上testdt表上运行以下 SQL，您将获得上一个和下一个eff_dt。请注意我使用ctidas ordering 列

select *, 
    ctid, 
    lead(eff_dt) over (partition by name order by ctid) as lead_eff_dt,
    lag(eff_dt) over (partition by name order by ctid) as lag_eff_dt
from testdt tst order by ctid

结果如下

   name   |  hire_dt   |   eff_dt   | ctid  | lead_eff_dt | lag_eff_dt 
----------+------------+------------+-------+-------------+------------
 Rachel   | 2021-03-29 | 2021-03-29 | (0,1) | 2021-05-14  | 
 Rachel   | 2021-03-29 | 2021-05-14 | (0,2) |             | 2021-03-29
 Ross     | 2021-06-18 | 2021-06-18 | (0,3) |             | 
 Monica   | 2021-07-21 | 2021-07-21 | (0,4) |             | 
 Chandler | 2021-04-12 | 2021-04-12 | (0,5) |             | 
 Judy     | 2021-04-26 | 2021-04-26 | (0,6) | 2021-05-10  | 
 Judy     | 2021-04-26 | 2021-05-10 | (0,7) | 2021-07-01  | 2021-04-26
 Judy     | 2021-04-26 | 2021-07-01 | (0,8) |             | 2021-05-10

现在我们可以应用您的逻辑：对于START_DATE ，如果它是第一行（lag_eff_dt是null），那么我们使用hire_dt，否则，如果有下一行（lead_eff_dt不是null），那么我们采用以下eff_dt。如果是最后一行，我们取eff_dt.

对于END_DATE 我们只需要2021-12-31如果没有以下记录（lead_eff_dtis ）就需要休息，否则null我们从下一天休息一天eff_dt

以下查询应该做

with first_sel as(
select *, 
    ctid, 
    lead(eff_dt) over (partition by name order by ctid) as lead_eff_dt,
    lag(eff_dt) over (partition by name order by ctid) as lag_eff_dt
from testdt tst order by ctid)
select 
    name, 
    case when lag_eff_dt is null
        then hire_dt
        else
            case when lead_eff_dt is not null
                then (lead_eff_dt)::date
                else eff_dt 
        end
        end as start,
    coalesce((lead_eff_dt - INTERVAL '1 DAY')::date, '2021-12-31'::date) as end
    

from first_sel;

结果是

   name   |   start    |    end     
----------+------------+------------
 Rachel   | 2021-03-29 | 2021-05-13
 Rachel   | 2021-05-14 | 2021-12-31
 Ross     | 2021-06-18 | 2021-12-31
 Monica   | 2021-07-21 | 2021-12-31
 Chandler | 2021-04-12 | 2021-12-31
 Judy     | 2021-04-26 | 2021-05-09
 Judy     | 2021-07-01 | 2021-06-30
 Judy     | 2021-07-01 | 2021-12-31
(8 rows)