一、题目

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

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

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

3. 2024年1月份A部门平均有多少员工；

二、思路

这个题目的前两部分相对简单，第三部分有一些难度。

第一步计算2024年1月31日A部门在职员工数。需要对数据做过滤，首先是部门为A，其次员工的在职日期需要包含24年1月31号。过滤完成后在计算员工数。涉及的知识点：between、 count(distinct )。

第二步计算2024年1月份A部门员工最多时有多少员工。与第一步的差异是这次的时间区间是一个月，这一个月期间有些员工可能会离职或者转岗，但是只要1月份在A部门待过就要计算在内。所以只需要按月份筛选出在职月份包含1月份的即可。涉及知识点：between和count。

第三步计算2024年1月份A部门平均有多少员工。这一步是在第二步的基础之上计算每天的在职员工数，然后求平均值。具体过程为首先获取1月份每天在职的明细，然后统计每天的在职员工数，最后求平均值。

涉及知识点：with、greatest、least、between、lateral view、 posexplode、avg

三、解答

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

select 
   count(distinct employ_id) cnt
from t_dep_his
where department_id = 'A'
       and '2024-01-31' between start_date and end_date;

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

select 
   count(distinct employ_id) cnt
from t_dep_his
where department_id = 'A'
       and '2024-01' between date_format(start_date, 'yyyy-MM') and date_format(end_date, 'yyyy-MM');

3、2024年1月份A部门平均有多少员工；

with tab_detail as (
    -- 每天的雇员明细
    select 
      employ_id,
      start_date, -- 一月在职开始日期
      end_date, -- 一月在职结束日期
      date_add(start_date, idx) busi_date,
      val
    from (
    
           select 
                  employ_id,
                  greatest('2024-01-01', start_date) start_date, -- 一月在职开始日期
                  least('2024-01-31', end_date) end_date -- 一月在职结束日期
           from t_dep_his 
           where department_id = 'A'
                  and '2024-01' between date_format(start_date, 'yyyy-MM') and date_format(end_date, 'yyyy-MM')
    ) t11
    lateral view posexplode(split(repeat('1,', datediff(end_date, start_date)), ',')) tab as idx, val
) 

select 
    round(avg(emp_cnt), 2) cnt_avg
from (
    -- 每天的员工数
    select 
      busi_date,
      count(distinct employ_id) emp_cnt
    from tab_detail t11
    group by busi_date
) t21;

数据

# 建表
CREATE TABLE t_dep_his (
  employ_id int,
  department_id string,
  start_date string,
  end_date string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS parquet;

# 写入数据
insert into table t_dep_his 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', '2024-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');