sql - 按日期划分的在职员工总数

Question

我过去曾写过查询，按日期提供计数(雇用、终止等...)，如下所示:

SELECT per.date_start AS "Date",
  COUNT(peo.EMPLOYEE_NUMBER) AS "Hires"

FROM hr.per_all_people_f peo,
  hr.per_periods_of_service per

WHERE per.date_start BETWEEN peo.effective_start_date AND peo.EFFECTIVE_END_DATE

AND per.date_start BETWEEN :PerStart AND :PerEnd

AND per.person_id = peo.person_id

GROUP BY per.date_start

我现在希望按日期创建活跃员工的计数，但是我不确定如何确定查询的日期，因为我使用范围来确定活跃员工:

SELECT COUNT(peo.EMPLOYEE_NUMBER) AS "CT"

FROM hr.per_all_people_f peo

WHERE peo.current_employee_flag = 'Y'

and TRUNC(sysdate) BETWEEN peo.effective_start_date AND peo.EFFECTIVE_END_DATE

Answer 1

这是一个简单的入门方法。这适用于数据中的所有有效日期和结束日期:

select thedate,
       SUM(num) over (order by thedate) as numActives
from ((select effective_start_date as thedate, 1 as num from hr.per_periods_of_service) union all
      (select effective_end_date as thedate, -1 as num from hr.per_periods_of_service)
     ) dates

它的工作原理是为每个起点添加一个人，为每个终点减去一个人(通过num)并进行累积和。这可能有重复的日期，因此您还可以进行聚合来消除这些重复:

select thedate, max(numActives)
from (select thedate,
             SUM(num) over (order by thedate) as numActives
      from ((select effective_start_date as thedate, 1 as num from hr.per_periods_of_service) union all
            (select effective_end_date as thedate, -1 as num from hr.per_periods_of_service)
           ) dates
     ) t
group by thedate;

如果您确实想要所有日期，那么最好从日历表开始，并对原始查询使用简单的变体:

select c.thedate, count(*) as NumActives
from calendar c left outer join
     hr.per_periods_of_service pos
     on c.thedate between pos.effective_start_date and pos.effective_end_date
group by c.thedate;