SQL面试题练习 —— 部门人员数据分析

题目来源：三一重工

1 题目

现有一张员工在职所在部门信息表，包含员工ID、所属部门、开始日期、结束日期。

+--------------+-------------+-------------+-------------+
| 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  |
+--------------+-------------+-------------+-------------+

请查询出如下内容

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

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

2 建表语句

CREATE TABLE IF NOT EXISTS employee_department_info (
    employee_id INT, -- 员工ID
    department STRING, -- 所属部门
    start_date STRING, -- 开始日期
    end_date STRING -- 结束日期
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','  -- 假设字段使用逗号分隔
STORED AS ORC;

insert into employee_department_info(employee_id, department, start_date,end_date) values 
(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');

3 题解

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

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

结果如下：

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

第一步：生成员工加入离开部门表，表内包含 employee_id， department，enter_or_leave，action_date。其中enter_type 1 代表进入，-1 代表离开，进入时间用start_date，离开时间用end_date。通过查询2遍员工部门表，并通过 union all 来整合到一起。

--加入部门记录
select
  employee_id,
  department,
  1 as enter_or_leave,
  start_date as action_date
from employee_department_info
where department = 'A'
union all
-- 离开部门记录
select
  employee_id,
  department,
  -1 as enter_or_leave,
  end_date as action_date
from employee_department_info
where department = 'A'

结果如下：

第二步：使用累加方式计算每次变动之后A部门的人数

with t as (
--加入部门记录
select
  employee_id,
  department,
  1 as enter_or_leave,
  start_date as action_date
from employee_department_info
where department = 'A'
union all
-- 离开部门记录
select
  employee_id,
  department,
  -1 as enter_or_leave,
  end_date as action_date
from employee_department_info
where department = 'A')
select
employee_id,
department,
enter_or_leave,
action_date,
sum(enter_or_leave) over(order by action_date) as depart_emp_cnt
from t

第三步：时间段限定在2024年1月份，对 depart_emp_cnt 求最大值。

with t as (
--加入部门记录
select
  employee_id,
  department,
  1 as enter_or_leave,
  start_date as action_date
from employee_department_info
where department = 'A'
union all
-- 离开部门记录
select
  employee_id,
  department,
  -1 as enter_or_leave,
  end_date as action_date
from employee_department_info
where department = 'A')
select
    max(depart_emp_cnt) as max_emp_cnt
from
(select
employee_id,
department,
enter_or_leave,
action_date,
sum(enter_or_leave) over(order by action_date) as depart_emp_cnt
from t) tt
where action_date >= '2024-01-01'
and action_date <= '2024-01-31';

结果如下：