现有用户表（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 |
解答：
1 with tt as 2 ( 3 SELECT dt, sum(flag) flag 4 FROM 5 ( 6 select en_dt dt, 1 flag 7 from emp 8 union ALL 9 select nvl(date_add(le_dt, 1), '2020-04-01') dt, -1 flag 10 from emp 11 ) t1 12 group BY dt 13 ) -- 统计用户表入职日期和离职日期，对应的在职人数 14 SELECT m mth -- 月 15 , cast(sum(emp_count)/max(month_days) as decimal(16, 2)) ps -- 截止到月末累计情况汇总/当月的天数 = 每天平均在职人数 16 FROM 17 ( 18 SELECT month(dt) m -- 日期，对应的月份 19 , emp_count -- 截止到当天的累计情况 20 , count(1) over(partition by month(dt)) month_days -- 每个月的天数 21 FROM( 22 select cal.dt -- 日期 23 , sum(nvl(flag, 0)) over(order by cal.dt) emp_count -- 截止到当天的累计情况 24 FROM (select dt from cal where dt >= '2020-01-01' and dt <= '2020-04-01') cal -- 日期维度表 25 left join tt -- 用户表入职日期和离职日期，对应的在职人数 26 on cal.dt = tt.dt 27 ) t2 28 ) t3 29 group by m;