Hello各位，本篇文章给大家分享一个SQL技巧，查询公司内部每年每月每部门的入职、离职、在职人数。
1 数据预览
我们先来看下数据的字段样式。
这是比较全面的人资数据了，其中，跟我们需求相关的主要有员工编号，入职时间、离职时间、公司。其中，员工编号是便于我们统计人数，公司类似于部门。
本案例使用的数据库为ORACLE，假设表名为tb_people。
2 入职情况
入职情况其实非常好做，既然时间维度是每年每月，那么我们只要按照“入职年月”字段进行分组即可。 为了便于展示，我们筛选了2020年到2021年的数据
select t."公司",t."入职年月" ,count(t."员工编号") AS 人数
from tb_people t
where t.入职年 IN (2021,2022)
GROUP BY t."公司",t."入职年月"
ORDER BY t."公司",t."入职年月"
结果如下：
这里面有一个小问题，电商公司在2021年的1-3月没有人入职，其他公司也有相同的情况，如果我们想把所有月份都展示出来，没有入职的就显示0，这样代码怎么写呢？我们稍后进行解答。
3 离职情况
离职情况与入职情况相同，根据“离职年月”进行筛选即可。
select t."公司",t."离职年月" ,count(t."员工编号") AS 人数
from tb_people t
where 离职月份 IS NOT NULL
AND t.离职年 IN (2021,2022)
GROUP BY t."公司",t."离职年月"
ORDER BY t."公司",t."离职年月"
4 在职情况
在职情况的分析比较复杂。首先，我们先考虑一下，怎么查询当前在职的人呢？只要“离职年月”字段为空即可。那么，我们再查询某个月，比如2021年12月的是在职的人呢？应该满足两个条件： 1）该员工在2021年12月之前入职； 2）该员工至今没有离职或者离职日期在2021年12月之后。
-- 2021年12月的在职人数
select t."公司" ,count(t."员工编号") AS 人数
from tb_people t
where (t.离职年月 IS NULL OR t.离职年月> '202112')
AND t.入职年月 < '202112'
GROUP BY t."公司"
注意：对于当月入职、当月离职的情况，本次案例不纳入该月在职人数。
那么，问题来了，如何统计出每年每月的在职人数呢？手工修改SQL语句中的日期是一个办法，编写python脚本来生成每个月的SQL语句是一个办法。还有只用SQL、更为便捷的方法。
我们需要生成以一个日期维表，即每年每月的数据，如下：
该表名为DIM_DATE。 我们接下来使用表连接：
SELECT DISTINCT "YEAR"||'-'||SUBSTR("DATE", 6, 2) 年月,t.员工编号, t."公司" ,t.离职日期,t.离职年月,入职日期
FROM DIM_DATE d,tb_people t
where (t.离职日期 IS NULL OR TO_CHAR(t."离职日期",'yyyy-mm-dd')> d."DATE")
AND to_char(t."入职日期",'yyyy-mm-dd') < d."DATE"
ORDER BY t.员工编号
重点在于，我们使用from A,B 的形式来构建两张表的笛卡尔积，而不能使用join的方式，原因在于，我们需要让DIM_DATE里面的DATE字段去和每一个人作比较。因为比较的是每一日而不是每一月，故需要加上DISTINCT 来去重。
结果已经是每年每月还在职的人的数据了，后续只需再执行GROUP BY 操作即可。
上面我们还留了一个问题：如何想让没有入职、离职的显示为0？利用DIM_DATE维表做表连接，然后使用nvl(入职人数,0)即可。
到这里，我们已经完成了在职、入职、离职的统计了，后续只需把这三个结果join起来即可，作为比较的字段为年月和公司。
