Postgres 14中忽略同rental_start行的LAG/LEAD函数实现需求

我在Postgres 14数据库中有一张object_history表，需要在SELECT语句中添加last_contract_id和next_contract_id两列，显示每行对应的前后合同ID。核心要求是：如果上下行的rental_start日期相同，则跳过该行直到日期变化。例如ID为6的行（contract_id=807）的last_contract_id应为NULL而非806，因为直到ID为3的行rental_start才发生变化。我尝试了以下语句，但无法跳过rental_start相同的行：

lag(contract_id) over (partition by objekt_id order by id) as last_contract_id, 
lead(contract_id) over (partition by objekt_id order by id) as next_contract_id 

表结构 #

CREATE TABLE object_history (
    objekt_id int4 NOT NULL,
    id serial NOT NULL,
    rental_start date NULL,
    rental_end date NULL,
    contract_id int4 NULL
);

INSERT INTO public.object_history
(objekt_id, id, rental_start, rental_end, contract_id)
VALUES(77920, 6, '2023-06-01', '2100-01-01', 807);
INSERT INTO public.object_history
(objekt_id, id, rental_start, rental_end, contract_id)
VALUES(77920, 5, '2023-06-01', '2100-01-01', 806);
INSERT INTO public.object_history
(objekt_id, id, rental_start, rental_end, contract_id)
VALUES(77920, 4, '2023-06-01', '2100-01-01', 803);
INSERT INTO public.object_history
(objekt_id, id, rental_start, rental_end, contract_id)
VALUES(77920, 3, '2023-05-01', '2023-05-31', NULL);
INSERT INTO public.object_history
(objekt_id, id, rental_start, rental_end, contract_id)
VALUES(77920, 2, '2022-01-01', '2023-04-30', 802);
INSERT INTO public.object_history
(objekt_id, id, rental_start, rental_end, contract_id)
VALUES(77920, 1, '2017-11-01', '2021-12-31', NULL);

原查询结果 #

| objekt_id | id  | rental_start | rental_end   | contract_id |
| --------- | --- | ------------ | ------------ | ----------- |
| 77920     | 6   | 2023-06-01   | 2100-01-01   | 807         |
| 77920     | 5   | 2023-06-01   | 2100-01-01   | 806         |
| 77920     | 4   | 2023-06-01   | 2100-01-01   | 803         |
| 77920     | 3   | 2023-05-01   | 2023-05-31   |             |
| 77920     | 2   | 2022-01-01   | 2023-04-30   | 802         |
| 77920     | 1   | 2017-11-01   | 2021-12-31   |             |

要实现跳过相同rental_start行的需求，需先将相同日期的行归为同一组，再基于组计算前后合同ID。以下是可行的SQL语句：

WITH grouped AS (
    -- 按rental_start分组，为每个组分配唯一ID
    SELECT 
        *,
        sum(CASE WHEN rental_start = lag(rental_start) OVER (PARTITION BY objekt_id ORDER BY rental_start ASC, id ASC) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY objekt_id ORDER BY rental_start ASC, id ASC) AS group_id
    FROM object_history
),
group_contracts AS (
    -- 获取每个组的最新合同ID（同组内取id最大的行的contract_id）
    SELECT 
        *,
        first_value(contract_id) OVER (PARTITION BY objekt_id, group_id ORDER BY rental_start DESC, id DESC) AS group_contract
    FROM grouped
),
group_lag_lead AS (
    -- 基于组计算前后合同ID
    SELECT 
        *,
        lag(group_contract) OVER (PARTITION BY objekt_id ORDER BY group_id) AS last_contract_id,
        lead(group_contract) OVER (PARTITION BY objekt_id ORDER BY group_id) AS next_contract_id
    FROM group_contracts
)
-- 输出最终结果
SELECT 
    objekt_id,
    id,
    rental_start,
    rental_end,
    contract_id,
    last_contract_id,
    next_contract_id
FROM group_lag_lead
ORDER BY id DESC;

查询结果 #

| objekt_id | id  | rental_start | rental_end   | contract_id | last_contract_id | next_contract_id |
| --------- | --- | ------------ | ------------ | ----------- | ---------------- | ---------------- |
| 77920     | 6   | 2023-06-01   | 2100-01-01   | 807         | NULL             | NULL             |
| 77920     | 5   | 2023-06-01   | 2100-01-01   | 806         | NULL             | NULL             |
| 77920     | 4   | 2023-06-01   | 2100-01-01   | 803         | NULL             | NULL             |
| 77920     | 3   | 2023-05-01   | 2023-05-31   | NULL        | 802              | 807              |
| 77920     | 2   | 2022-01-01   | 2023-04-30   | 802         | NULL             | NULL             |
| 77920     | 1   | 2017-11-01   | 2021-12-31   | NULL        | NULL             | 802              |

逻辑说明 #

1. 分组标记：通过sum和lag函数，为每个rental_start相同的行分配同一个group_id，实现按日期分组。
2. 组内合同提取：用first_value获取每个组内最新的合同ID（按id降序取最大的行）。
3. 组间前后计算：基于group_id使用lag和lead，跨组获取前后合同ID，自动跳过同组行。

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