Postgres lag/lead Function filter

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

2 answers

  • answered 2022-05-04 11:28 D-Shih

    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

  • answered 2022-05-04 11:42 MatBailie

    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

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum