Postgres分割重叠日期范围的最有效方法

Question

例如，我在表A中有以下实体：

entityId, metaData, startDate, endDate
Row 1: 1, some content, 2021-01-01, 2022-01-01
Row 2: 1, some content 2, 2022-01-02, 9999-12-31

我在表B中有以下实体：

entityId, metaData, startDate, endDate
Row 1: 1, overlapping content, 2021-08-12, 2022-01-01
Row 2: 1, overlapping content 2, 2022-03-01, 2023-01-01
Row 3: 1, overlapping content 3, 2023-01-02, 9999-12-31

|-----------ROW 1 (A)-----------| |-----------ROW 2 (A)------------| 
|-------ROW 1 (B)-------|   |--ROW 2 (B)--|  |--ROW 3 (B)--|

作为输出，我想通过与来自B的行重叠来分割来自A的行(使用entityId键(：

Row 1 (A): 1, some content, 2021-01-01, 2021-08-11
Row 1 (B): 1, overlapping content, 2021-08-12, 2022-01-01
Row 2 (A): 1, some content 2, 2022-01-02, 2022-02-28
Row 2 (B): 1, overlapping content 2, 2022-03-01, 2023-01-01
Row 3 (B): 1, overlapping content 3, 2023-01-02, 9999-12-31

p.S.A/B中的行可以在不连续的日期范围内相互跟随。例如：

Row 1: 1, content, 2021-01-01 2021-12-01
Row 2: 1, content 2, 2021-12-31, 9999-12-31

Answer 1

这样的smth符合您的需求吗？

with cte as
(
select * from table_a
union all
select * from table_b
)
select
entityid,
metadata,
startdate,
coalesce(
((lead(startdate) over (order by startdate)) - INTERVAL '1 DAY') :: DATE,
enddate
)
from cte order by startdate