Hive sql 常见面试题-员工在职人数问题

1. 现有用户表（emp）如下。

2. 日历表(cal) 如下：
   

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

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

期望结果如下：

3. 查询sql

--这道题坑比较多，首先原表的字段就不对，离职日期的字段是le_dt，这个字段即使selet * from emp也看不出来，只有要null值的列结果就不会显示，只有看报错信息里面能发现原字段的名字
--我的解题思路：在emp表对入职的日期打个标记1，离职的日期打个标记-1，然后把两张表Unionall一下，再拿cal表leftjoin这个union的表，这样就得到了一张显示每一天人员变动的表，然后开窗聚合按照日期排序，就可以得到每一天该公司的在职员工总数
--第二个比较坑的就是员工离职了，第二天才要把这个人减去，本人在解题的时候一直对不上答案，研究了好久。
WITH tmp 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(c) as decimal(16,2)) ps

from(

    SELECT

            MONTH(dt) m,

            emp_count,

            count(1) OVER(PARTITION by MONTH(dt)) c

    from(

        SELECT 

                cal.dt,

                sum(nvl(flag,0)) over(ORDER by cal.dt) emp_count

        from cal

        LEFT JOIN tmp 

        on cal.dt = tmp.dt

    )t1

)t2 GROUP by m