Postgres滞后/铅功能过滤器

Question

有人可以帮助我解决我查询的最后一步。

我有此表

    CREATE TABLE rent(id integer,start_date date, end_date date,objekt_id integer,person_id integer); 
INSERT INTO rent VALUES

(1,  '2011-10-01','2015-10-31',5156,18268),
(2,  '2015-11-01','2018-04-30',5156,18268),
(3,  '2018-05-01','2021-03-31',5156,18269),
(4,  '2021-04-01','2021-05-15',5156,null),
(5,  '2021-05-16','2100-01-01',5156,18270),
(6,  '2021-03-14','2021-05-15',5160,18270),
(7,  '2021-05-16','2100-01-01',5160,18271);

“下一个人_id。

有了这个查询，我几乎解决了我的问题，但是我仍然需要一件事才能改变。

with tbl as (
SELECT rent.*,
row_number() over (PARTITION BY objekt_id) as row_id
    FROM rent
    ORDER BY id)
SELECT r.id,
       r.start_date,
       r.end_date,
       r.objekt_id,
       r.person_id,
       lag(person_id) over (PARTITION BY objekt_id, person_id IS NOT NULL AND objekt_id IS NOT NULL ORDER BY id) as last_person,
       lead(person_id) over (PARTITION BY objekt_id, person_id IS NOT NULL AND objekt_id IS NOT NULL ORDER BY id) as next_person
    FROM tbl r
order by 1;

最后一个或下一个person_id始终必须null或另一个person_id

。如果Person_ID为空，我也想见下一个和下一个人。

现在输出：

id  start_date  end_date    objekt_id   person_id   last_person next_person
  1 2011-10-01  2015-10-31  5156        18268                    18268
  2 2015-11-01  2018-04-30  5156        18268       18268        18269
  3 2018-05-01  2021-03-31  5156        18269       18268        18270
  4 2021-04-01  2021-05-15  5156            
  5 2021-05-16  2100-01-01  5156        18270       18269   
  6 2021-03-14  2021-05-15  5160        18270                    18271
  7 2021-05-16  2100-01-01  5160        18271       18270

希望输出：

id  start_date  end_date    objekt_id   person_id   last_person next_person
  1 2011-10-01  2015-10-31  5156        18268                    18269
  2 2015-11-01  2018-04-30  5156        18268                    18269
  3 2018-05-01  2021-03-31  5156        18269       18268        18270
  4 2021-04-01  2021-05-15  5156                    18269        18270
  5 2021-05-16  2100-01-01  5156        18270       18269   
  6 2021-03-14  2021-05-15  5160        18270                    18271
  7 2021-05-16  2100-01-01  5160        18271       18270   

查询的目标是选择一个特定的日期，并确定该对象是否用于出租，然后显示谁是租金，谁是最后一个，并且有一个人租用的人

Answer 1

您可以尝试使用相关的提价来通过您的逻辑条件来实现它。

with tbl as (
SELECT rent.*,
row_number() over (PARTITION BY objekt_id) as row_id
    FROM rent
    ORDER BY id)
SELECT r.id,
       r.start_date,
       r.end_date,
       r.objekt_id,
       r.person_id,
       ( SELECT t1.person_id
         FROM tbl t1
         WHERE t1.objekt_id = r.objekt_id
         AND t1.id < r.id
         AND (t1.person_id <> r.person_id OR r.person_id  IS NULL)
         AND t1.person_id IS NOT NULL
         ORDER BY t1.id desc
         LIMIT 1) last_person,
       (SELECT t1.person_id
         FROM tbl t1
         WHERE t1.objekt_id = r.objekt_id
         AND t1.id > r.id
         AND (t1.person_id <> r.person_id OR r.person_id  IS NULL)
         AND t1.person_id IS NOT NULL
         ORDER BY t1.id  
         LIMIT 1) next_person
    FROM tbl r
order by 1;

Answer 2

窗口函数是可能的，但是我正在努力解决一个简洁的答案，因为PostgreSQL没有忽略nulls 。

现在，这是一个笨拙的答案...

with
  tbl as
(
  -- From your question, but fixed by moving the `ORDER BY` into the window function
  SELECT
    rent.*,
    row_number() over (PARTITION BY objekt_id ORDER BY start_date) as row_id
  FROM
    rent
),
  lag_lead AS
(
  -- do a naive lag and lead, not yet trying to account for nulls
  -- if the result is the same as the current row, replace with NULL
  -- (thus only identifying lag/lead values where the's a change)
  SELECT
    *,
    NULLIF(LAG( person_id) over (PARTITION BY objekt_id ORDER BY start_date), person_id)   AS last_person,
    NULLIF(LEAD(person_id) over (PARTITION BY objekt_id ORDER BY start_date), person_id)   AS next_person
  FROM
    tbl
),
  identify_partitions AS
(
  -- create groups of rows where the results should be the same
  SELECT
    *,
    COUNT(new_last_person) OVER (PARTITION BY objekt_id ORDER BY start_date  ASC)  AS last_person_partition,
    COUNT(new_next_person) OVER (PARTITION BY objekt_id ORDER BY start_date DESC)  AS next_person_partition
  FROM
    lag_lead
)
SELECT
  *,
  MAX(new_last_person) OVER (PARTITION BY objekt_id, last_person_partition)   AS real_last_person,
  MAX(new_next_person) OVER (PARTITION BY objekt_id, next_person_partition)   AS real_next_person
FROM
  identify_partitions
ORDER BY
  1;

在该示例中，我稍微修改了您的数据，以说明从x到null到null和返回x的Person_ID过渡的行为。