Question 275446 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to use three different columns in a join to get only two columns in Postgres?

I have two tables one mentioning the start and end time of an event and corresponding value and other having the value recorded at midnight daily.

CREATE TABLE table1 (
    id integer,
    date_strt timestamp,
    date_end timestamp,
    strt_unit integer,
    end_unit integer
);

INSERT INTO table1 (id, date_strt, date_end,strt_unit,end_unit)
VALUES
    (1, '2023-10-27 12:00:00','2023-10-31 12:00:00', 5,72),
    (2, '2023-10-30 12:15:00','2023-11-02 00:00:00', 78,90);
    
    
CREATE TABLE table2 (
    id integer,
    dates timestamp,
    unit integer
);

INSERT INTO table2 (id, dates, unit)
VALUES
    (1, '2023-10-28 00:00:00', 55),
    (1, '2023-10-29 00:00:00', 60),
    (1, '2023-10-30 00:00:00', 65),
    (1, '2023-10-31 00:00:00', 70),
    (2, '2023-10-30 00:00:00', 75),
    (2, '2023-10-31 00:00:00', 80),
    (2, '2023-11-01 00:00:00', 85),
    (2, '2023-11-02 00:00:00', 90);

I want to obtain data set like this where I obtain difference in data of each day at midnight 00:00:00 hours starting from the start date to end date mentioned in table 1 by using the date available at table 2.

id start_time          start_value    end_time            end_value
1, '2023-10-27 12:00:00', 5,      '2023-10-28 00:00:00', 55
1, '2023-10-29 00:00:00', 60,     '2023-10-29 00:00:00', 60
1, '2023-10-29 00:00:00', 60,     '2023-10-30 00:00:00', 65
1, '2023-10-30 00:00:00', 65,     '2023-10-31 00:00:00', 70
1, '2023-10-31 00:00:00', 70,     '2023-10-31 12:00:00', 72
2, '2023-10-30 12:15:00', 78,     '2023-10-31 00:00:00', 80    
2, '2023-10-31 00:00:00', 80,     '2023-11-01 00:00:00', 85
2, '2023-11-01 00:00:00', 85,     '2023-11-02 00:00:00', 90

I planned to generate a date series between the start and end dates using 1 day as interval but could not generate the appropriate dates and hence can’t further use lead or lag in it.
I have added the fiddle link.

Tags:

2

Answers


  1. 0

    Here’s how to do it with the window functions LAG() and LEAD() :

    with cte as (
  select t1.*, CASE WHEN start_time=date_strt THEN start_time ELSE date(start_time) END as start_time,
               lead(date(start_time), 1, date_end) over (partition by id order by start_time) as end_time
  from table1 t1
  cross join generate_series
        ( date_strt, date_end, '1 day'::interval) start_time
),
cte2 as (
  select c.id, c.start_time, c.strt_unit, 
         c.end_time, case when c.end_time = date_end then end_unit else unit end as end_value
  from cte c
  inner join table2 t on c.id = t.id and DATE(t.dates) = DATE(c.end_time)
)
select id, start_time,
       lag(end_value, 1, strt_unit) over (partition by id order by start_time) as start_value,
       end_time, end_value
from cte2;

    Result :

    id  start_time          start_value end_time            end_value
1   2023-10-27 12:00:00 5           2023-10-28 00:00:00 55
1   2023-10-28 00:00:00 55          2023-10-29 00:00:00 60
1   2023-10-29 00:00:00 60          2023-10-30 00:00:00 65
1   2023-10-30 00:00:00 65          2023-10-31 00:00:00 70
1   2023-10-31 00:00:00 70          2023-10-31 12:00:00 72
2   2023-10-30 12:15:00 78          2023-10-31 00:00:00 80
2   2023-10-31 00:00:00 80          2023-11-01 00:00:00 85
2   2023-11-01 00:00:00 85          2023-11-02 00:00:00 90

    Explanations :

    • First CTE used to generate ranges between two dates using generate_series

    • The second CTE was used to link data with the second table in order to obtain the end_value for each range.

    • The third CTE is used to obtain the start_value for each range.

    Demo here

    Login or Signup to reply.
  2. 0

    You can join table2 with the previous table2 and table1 and the next table2 and table1, making sure that there is nothing in between (this is why we join with the *_no_between tables and filter by the *_no_between tables being nulls) and then decide what’s the proper value via case–when.

    SELECT table2.id,
       CASE
           WHEN NOT (table2_previous.dates IS NULL) AND
                (table2_previous.dates > table1_previous.date_end)
           THEN table2_previous.dates
           WHEN NOT (table1_previous.date_end IS NULL)
           THEN table1_previous.date_end
           ELSE table2.dates
       END AS start_time,
       CASE
           WHEN NOT (table2_previous.dates IS NULL) AND
                (table2_previous.dates > table1_previous.date_end)
           THEN table2_previous.unit
           WHEN NOT (table1_previous.date_end IS NULL)
           THEN table1_previous.end_unit
           ELSE table2.unit
       END AS start_value,
       CASE
           WHEN NOT (table2_next.dates IS NULL) AND
                (table2_next.dates < table1_next.date_strt)
           THEN table2_next.dates
           WHEN NOT (table1_next.date_strt IS NULL)
           THEN table1_next.date_strt
           ELSE table2.dates
       END AS end_time,
       CASE
           WHEN NOT (table2_next.dates IS NULL) AND
                (table2_next.dates < table1_next.date_strt)
           THEN table2_next.unit
           WHEN NOT (table1_next.date_strt IS NULL)
           THEN table1_next.strt_unit
           ELSE table2.unit
       END AS end_value
FROM table2
LEFT JOIN table2 as table2_previous
ON table2.id = table2_previous.id AND table2.dates > table2_previous.dates
LEFT JOIN table2 as table2_previous_no_between
ON table2.id = table2_previous_no_between.id and table2_previous.dates < table2_previous_no_between.dates AND table2_previous_no_between.dates < table2.dates
LEFT JOIN table1 as table1_previous
ON table2.id = table1_previous.id AND table1_previous.date_end < table2.dates
LEFT JOIN table1 as table1_previous_no_between
ON table2.id = table1_previous_no_between.id AND table1_previous.date_end < table1_previous_no_between.date_end AND table1_previous_no_between.date_end < table2.dates
LEFT JOIN table2 as table2_next
ON table2.id = table2_next.id AND table2.dates < table2_next.dates
LEFT JOIN table2 as table2_next_no_between
ON table2.id = table2_next_no_between.id and table2_next.dates > table2_next_no_between.dates AND table2_next_no_between.dates > table2.dates
LEFT JOIN table1 as table1_next
ON table2.id = table1_next.id AND table1_next.date_strt > table2.dates
LEFT JOIN table1 as table1_next_no_between
ON table2.id = table1_next_no_between.id AND table1_next.date_end > table1_next_no_between.date_end AND table1_next_no_between.date_end > table2.dates
WHERE (table2_previous_no_between.id IS NULL) AND
      (table2_next_no_between.id IS NULL) AND
      (table1_previous_no_between.id IS NULL) AND
      (table1_next_no_between.id IS NULL)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search