所有工人的日期范围重叠问题

Question

我正在尝试计算日期范围（岛屿），其中所有工人都一起工作。换句话说，如果有一个工人在某个日期不在，则该日期应从结果中排除。以下是样本数据：

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是整个团队。

我知道如何找到重叠的日期范围，但我不知道如何同时应用于所有工人的重叠日期范围。

- Livius

Answer 1

假设工人之间没有重叠，您可以使用一种计数技巧来解决这个问题。统计每个日期上工作的工人数量，那么所有工人都在工作的日期就是您想要的日期。

实际上，您并不需要每一个日期。假设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，因此不要使用绝对最大日期作为该值。 这里提供了一个db<>fiddle。

- Gordon Linoff

---

太棒了！谢谢你。我看到你将日期的数量相加并与工人的数量进行比较。真的很简单。这是一个修改过的Firebird 3解决方案的链接：https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=fa83e359e1b1134a9b92884a77b0303e - Livius

@Livius...我忘了db<>fiddle支持Firebird数据库。我本应该一开始就用那个的。干杯！ - Gordon Linoff

也许我甚至会将其具体化，并创建一个{日期，工人数量}结构的累加表（在FB中没有具体化视图）。然后，我会在FB的WORK_DAYS表上放置一个AFTER UPDATE OR INSERT OR DELETE的SQL触发器，以便该新表始终自动更新。或者也许更好的是{日期，雇佣的工人数量，正在工作的工人数量}。当然，如果粒度从天逐渐细化到小时或秒，这种方法的效率会越来越低。但对于仅仅按天计算的情况应该还可以。 - Arioch 'The

我对这个解决方案感到非常惊讶。我不确定在分组中 SUM(sum(inc)) over(order by dte) 是如何工作的。你能否请解释一下？ - George Joseph