hivesql练习_员工在职人数问题

现有用户表（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

 

解答：

with tt as
(
  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 flag
    from emp
  ) t1
  group BY dt
) -- 统计用户表入职日期和离职日期，对应的在职人数
SELECT m mth -- 月
, cast(sum(emp_count)/max(month_days) as decimal(16, 2)) ps  -- 截止到月末累计情况汇总/当月的天数 = 每天平均在职人数
FROM
(
  SELECT month(dt) m  -- 日期，对应的月份
  , emp_count  -- 截止到当天的累计情况
  , count(1) over(partition by month(dt)) month_days  -- 每个月的天数
  FROM(
    select cal.dt  -- 日期
    , sum(nvl(flag, 0)) over(order by cal.dt) emp_count -- 截止到当天的累计情况
    FROM (select dt from cal where dt >= '2020-01-01' and dt <= '2020-04-01') cal --  日期维度表
    left join tt -- 用户表入职日期和离职日期，对应的在职人数
    on cal.dt = tt.dt
  ) t2
) t3
group by m;