sql - 所有 worker 的重叠日期范围

Question

我想弄清楚如何计算日期范围(岛屿)，其中 全部 worker 工作 一起 .换句话说，如果其中一名 worker 不在某个日期，则应从结果中排除该日期。以下示例数据:

insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(1, '2019-10-01', '2020-04-30');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(1, '2020-05-01', '2020-07-19');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(1, '2020-10-01', '9999-01-01');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(2, '2019-10-01', '2020-04-30');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(2, '2020-05-01', '2020-07-31');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(2, '2020-11-01', '9999-01-01');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(3, '2018-03-12', '2018-08-20');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(3, '2019-10-01', '2020-04-15');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(3, '2020-07-01', '2020-07-31');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(3, '2020-11-01', '9999-01-01');

我使用 Firebird 数据库，但您可以在任何数据库(如 SQL Server)中显示结果(但请不要交叉应用仅在 SQL 标准中定义的内容)。这是已经从间隙/岛屿问题中检索到的简化数据。因为我的样本中的 worker_id 是整个团队。
我知道如何找到重叠的日期范围，但我不知道如何找到同时应用于所有 worker 的重叠日期范围。

Answer 1

假设 worker 没有重叠，您可以为此使用计数技巧。计算每个日期工作的 worker 数量。那么所有 worker 的日期就是你想要的日期。
你实际上并不需要每次约会。假设 date_to包含在工作日中，您可以对数据进行逆透视并使用累积总和。
Postgres 中表达的逻辑中的以下内容(您的问题确实指定了任何数据库中的解决方案都是可以接受的，我发现 Postges 最接近于标准 SQL):

with wd as (
      select worker_id, date_from as dte, 1 as inc
      from work_days wd
      union all
      select worker_id, date_to + interval '1 day', -1 as inc
      from work_days wd
     ),
     wd_cnt as (
      select wd.dte, sum(sum(inc)) over (order by dte) as num_on_date,
             lead(wd.dte) over (order by wd.dte) as next_dte
      from wd
      group by wd.dte
     )
select dte, next_dte - interval '1 day'
from wd_cnt
where num_on_date = (select count(distinct worker_id) from work_days);

这都是标准 SQL，但日期/时间函数因数据库而异。请注意，这会为 date_to 增加一天，因此不要为此值使用绝对最大日期。
Here是一个db<> fiddle 。