下面是如何使用窗口函数LAG()和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;

字符串
测试结果：

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

型
说明：

• 第一个CTE用于使用generate_series生成两个日期之间的范围
• 第二个CTE用于将数据与第二个表链接，以获得每个范围的end_value。
• 第三个CTE用于获得每个范围的start_value。

Demo here

您可以将table2与前一个table2和table1以及下一个table2和table1连接起来，确保两者之间没有任何东西（这就是为什么我们使用 *_no_between表进行连接，并通过 *_no_between表为空值进行过滤），然后通过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)

字符串