如何计算一家公司在没有人员变动的日子里最长的时间？

Question

给出一个包含EmpID、FirstName、LastName、StartDate和EndDate列的employees表。我想使用对Oracle的查询来计算一家公司在没有人员变动的日子里最长的时间。

以下是我的疑问：

select MAX(endDate-startDate)

from
(select endDate 
from employees
where endDate is not null)

union all 

(select startDate 
from employees)

但是我得到了一个错误:ORA-00904：“STARTDATE”：无效标识符

如何修复此错误？我的疑问是这个问题的正确答案吗？

谢谢

Answer 1

您不会在子查询中返回startDate。将startDate添加到内部查询。

select MAX(endDate-startDate) from 
(select startDate, endDate from employees where endDate is not null) 
union all
(select startDate from employees)

编辑：

您也可以尝试这样做：

select MAX(endDate-startDate) from employees where endDate is not null

然而，我认为您的查询并不是您要寻找的，因为它只列出了不再在公司工作的最长期限的员工。

Answer 2

在一个简单化的观点中，您可能希望将所有开始日期(当人数增加时)和所有结束日期(当其减少时)放在一起，将它们组合起来，按增加的顺序排列它们，度量连续日期之间的差异，并取最大值。

“组合在一起”是一个UNION ALL，可以用解析函数lag()来度量“连续”日期之间的差异。

一个复杂的问题是:一名员工可能在同一天开始工作，另一名员工则被解雇，因此员工人数不会改变。更普遍的情况是，在任何给定的日期上都可能有开始和结束，当有相同数量的开始和结束时，您需要排除这些日期。因此，解决方案的第一部分更复杂:您需要按日期分组，并比较开始计数和结束计数。

像这样的东西可能有用(没有测试！)：

with d ( dt, flag ) as (
       select start_date, 's' from employees union all
       select end_date  , 'e' from employees
     ),
     prep ( int ) as 
       select   dt - lag(dt) over (order by dt)
       from     d
       group by dt
       having   count(case flag when 's' then 1 end) != 
                                              count(case flag when 'e' then 1 end)
     )
select max(int) as max_interval
from   prep
;

编辑- Gordon在他的解决方案中有一个很好的观点:也许没有改变人数的最长时期是当前时期(以“现在”结尾)。因此，我们需要像他一样将SYSDATE添加到UNION ALL中。它可以添加任何一个标志(例如's‘是特定的)。

Answer 3

我认为你问题的答案是这样的：

select max(span)
from (select (lead(dte) over (order by dte) - dte) as span
      from (select startDate as dte from employees union all
            select endDate as dte from employees union all
            select trunc(sysdate) from dual
           ) d
     ) d;

当员工开始或停止时，会发生人头数的变化(大概)。因此，您需要两个这样的相邻日期之间的最大间隔。