Postgres lag/lead Function filter-CodePudding

Can someone help me with the last step of my Query.

I have this table fiddle

    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);

With lag and lead i want two columns for last person_id and next person_id.

With this Query i almost solved my Problem but there is still one thing i need help to change.

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;

Last or Next Person_id always have to either null or from another person_id.

At the moment row 2 will give me last_person_id = 18268 since row 1 had the same person_id. If person_id is empty i also want to see last and next person.

Output now:

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

Wished Output:

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

The goal with query is to choose a specific date and to tell if the object is for rent or not and then also show who rent's it at and who was the last one and is there someone in line to rent

CodePudding user response：

You can try to use correlated-subquery to make it by your logic condition.

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;

sqlfiddle

CodePudding user response：

It is possible with window functions, but while I'm on my phone I'm struggling to work out a concise answer as PostGreSQL doesn't have IGNORE NULLS.

For now, here's a clunky answer...

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;

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=b613f88a730cfddcef4efb612b6e236c

In that example I've amended your data slightly, to demonstrate the behaviour if the person_id transitions from X to NULL and back to X.

If you require different behaviour, please comment