SQL面试题——部门人员在职情况统计分析

部门人员在职情况统计分析

现有一张员工在职所在部门信息表，包含员工ID、所属部门、开始日期、结束日期，请查询出如下内容

2024年1月31日A部门在职员工数；
2024年1月份A部门员工最多时有多少员工；
2024年1月份A部门平均有多少员工；

employee_id	department	start_date	end_date
1	A	2023-12-20	2024-01-22
2	A	2024-01-02	2024-01-11
2	B	2024-01-11	2024-01-25
2	A	2023-01-25	9999-01-01
3	A	2023-12-20	9999-01-01
4	A	2024-02-02	9999-01-01
5	A	2023-06-20	2023-12-22

分析

由于我们这是一个拉链表，是一个左闭右开的区间，也就是说我们是去不到end_date的，所以这个边界问题要考虑清楚

2024年1月31日A部门在职员工数

现在要计算的是某一天的人数，我们的数据是一个人在这个部门的待的起始时间和截止时间，那我们只需要开始时间小于等于这一天，截止时间大于这一天

select count(1) as a_depart_num
from emp_dep_info
where department = 'A'and start_date <= '2024-01-31'and end_date > '2024-01-31';

2024年1月份A部门员工最多时有多少员工

由于员工数一直在变化，每天都有员工入职离职，所以我们需要算出每天都有多少人，然后人数最多的那一天就是员工最多的时候

为了方便计算，我们假设我们有一张时间维度表，然后按照我们之前计算某一天的在职人数的逻辑，计算每一天的人数

selecta.date,count(b.employee_id)
from 	dim.dim_date a
inner joinemp_dep_info b
onb.department = 'A'and a.date>='2024-01-01' and a.date<='024-01-31'and start_date <= a.dateand end_date > a.date
group bya.date

如果这里我没有这样的一个时间维度表呢，我们可以用下面的方式计算

-- 截止到当天每一天的人数
selectevent_time,sum(status) over(order by event_time ) as cnt
from(
-- 1入职，-1离职
select employee_id, department, start_date as event_time, 1 status 
from emp_dep_info
where department = 'A'and start_date <= '2024-01-31'
union all
-- 1入职，-1离职
select employee_id, department, end_date as event_time, -1 status 
from emp_dep_info
where department = 'A'and end_date <= '2024-01-31'
order by event_time
)

然后再去按照event_time去分组