Hive 刷题——员工在职人数问题

需求描述

现有用户表（emp）如下。

id (员工id)	en_dt (入职日期)	le_dt (离职日期)
1001	2020-01-02	null
1002	2020-01-02	2020-03-05
1003	2020-02-02	2020-02-15
1004	2020-02-12	2020-03-08

日历表(cal) 如下：

dt
(日期)

2020-01-01

2020-01-02

2020-01-03

2020-01-04

统计2020年每个月实际在职员工数量(只统计2020-03-31之前)，如果1个月在职天数只有1天，数量计算方式：1/当月天数。

如果一个月只有一天的话，只算30分之1个人

期望结果如下：

mnt <int> (月份)	ps <decimal(16,2)> (在职人数)
1	1.94
2	3.62
3	2.23

解题思路

这个题目和之前同时在线人数有一部分是相通的，可以参考那个需求先求出每天的在职人数 

select c.dt, sum(nvl(flag, 0)) over (order by c.dt) emp_cnt
from cal c
         left join
     (select dt, sum(flag) flag
      from (select en_dt dt, 1 flag
            from emp
            union all
            select nvl(date_add(le_dt, 1), '2020-04-01') dt, -1
            from emp) t
      group by dt) u;

在后就是按照月份进行聚合求出在职人数和总天数就行了

参考SQL

select mnt, cast(sum(emp_cnt) / max(cnt) as decimal(10, 2)) ps
from (select month(dt) mnt, emp_cnt, sum(1) over (partition by month(dt)) cnt
      from (select c.dt, sum(nvl(flag, 0)) over (order by c.dt) emp_cnt
            from cal c
                     left join
                 (select dt, sum(flag) flag
                  from (select en_dt dt, 1 flag
                        from emp
                        union all
                        select nvl(date_add(le_dt, 1), '2020-04-01') dt, -1
                        from emp) t
                  group by dt) t1 on c.dt = t1.dt) t) t
group by mnt;