数据分析SQL离职率

• 离职率
• 计算公式
• 示例SQL（HIVE）

• Dimission Rate
• 衡量企业内部人力资源流动状况 的一个重要指标
  该指标反映 企业对员工的吸引和满意情况
• 离职率过高
  通常表明 企业的员工情绪较为波动、劳资关系存在较严重的矛盾、企业凝聚力低下…
  会导致人力资源成本增加、组织的效率下降…
• 离职率不是越低越好
  在市场竞争中，保持一定的员工流动，有利于企业人才优胜劣汰，保持企业的活力和创新意识

离 职 率 = 离 职 人 数 / ( 离 职 人 数 + 期 末 在 职 人 数 ) 离职率=离职人数/(离职人数+期末在职人数) 离职率=离职人数/(离职人数+期末在职人数)

• 离职率范围：[0,1]

• 时间维度：按月、按季度、按半年、按年 统计
  月 离 职 率 = 月 离 职 人 数 / ( 月 离 职 人 数 + 月 底 在 职 人 数 ) 月离职率=月离职人数/(月离职人数+月底在职人数) 月离职率=月离职人数/(月离职人数+月底在职人数)
  年 离 职 率 = 年 离 职 人 数 / ( 年 离 职 人 数 + 年 底 在 职 人 数 ) 年离职率=年离职人数/(年离职人数+年底在职人数) 年离职率=年离职人数/(年离职人数+年底在职人数)

WITH
-- 员工表
staff as (
    SELECt 'a1' staff_id, '2019-01-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a2' staff_id, '2019-01-01' hire_date, '2020-03-01' departure_date UNIOn ALL
    SELECT 'a3' staff_id, '2019-01-01' hire_date, '2021-02-28' departure_date UNIOn ALL
    SELECT 'a6' staff_id, '2019-06-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a7' staff_id, '2019-06-01' hire_date, '2020-03-02' departure_date UNIOn ALL
    SELECT 'a8' staff_id, '2019-07-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a9' staff_id, '2019-07-01' hire_date, '2020-03-02' departure_date UNIOn ALL
    SELECT 'a10' staff_id, '2019-07-01' hire_date, '2020-03-02' departure_date UNIOn ALL
    SELECT 'a11' staff_id, '2019-07-01' hire_date, '2020-03-02' departure_date UNIOn ALL
    SELECT 'a12' staff_id, '2019-09-01' hire_date, '2020-03-02' departure_date UNIOn ALL
    SELECT 'a13' staff_id, '2020-01-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a14' staff_id, '2020-03-01' hire_date, '2020-03-31' departure_date UNIOn ALL
    SELECT 'a15' staff_id, '2020-04-11' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a18' staff_id, '2020-04-11' hire_date, '2021-02-28' departure_date UNIOn ALL
    SELECT 'a19' staff_id, '2020-04-11' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a20' staff_id, '2020-04-11' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a21' staff_id, '2020-04-11' hire_date, '2021-08-31' departure_date UNIOn ALL
    SELECT 'a22' staff_id, '2020-04-11' hire_date, '2021-08-31' departure_date UNIOn ALL
    SELECT 'a23' staff_id, '2020-11-01' hire_date, '2021-08-31' departure_date UNIOn ALL
    SELECT 'a24' staff_id, '2020-11-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a25' staff_id, '2020-11-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a26' staff_id, '2021-03-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a26' staff_id, '2021-03-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a26' staff_id, '2021-03-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a31' staff_id, '2021-06-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a31' staff_id, '2021-06-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a33' staff_id, '2021-08-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a34' staff_id, '2021-09-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a34' staff_id, '2021-09-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a34' staff_id, '2021-09-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a34' staff_id, '2021-09-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a34' staff_id, '2021-09-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a34' staff_id, '2021-09-01' hire_date, '2021-09-30' departure_date UNIOn ALL
    SELECT 'a35' staff_id, '2021-10-01' hire_date, NULL departure_date UNIOn ALL
    SELECT 'a36' staff_id, '2021-11-01' hire_date, NULL departure_date
),
-- 月维度表
t AS (
    SELECT '2019-01' ym UNIOn ALL
    SELECT '2019-02' ym UNIOn ALL
    SELECT '2019-03' ym UNIOn ALL
    SELECT '2019-04' ym UNIOn ALL
    SELECT '2019-05' ym UNIOn ALL
    SELECT '2019-06' ym UNIOn ALL
    SELECT '2019-07' ym UNIOn ALL
    SELECT '2019-08' ym UNIOn ALL
    SELECT '2019-09' ym UNIOn ALL
    SELECT '2019-10' ym UNIOn ALL
    SELECT '2019-11' ym UNIOn ALL
    SELECT '2019-12' ym UNIOn ALL
    SELECT '2020-01' ym UNIOn ALL
    SELECT '2020-02' ym UNIOn ALL
    SELECT '2020-03' ym UNIOn ALL
    SELECT '2020-04' ym UNIOn ALL
    SELECT '2020-05' ym UNIOn ALL
    SELECT '2020-06' ym UNIOn ALL
    SELECT '2020-07' ym UNIOn ALL
    SELECT '2020-08' ym UNIOn ALL
    SELECT '2020-09' ym UNIOn ALL
    SELECT '2020-10' ym UNIOn ALL
    SELECT '2020-11' ym UNIOn ALL
    SELECT '2020-12' ym UNIOn ALL
    SELECT '2021-01' ym UNIOn ALL
    SELECT '2021-02' ym UNIOn ALL
    SELECT '2021-03' ym UNIOn ALL
    SELECT '2021-04' ym UNIOn ALL
    SELECT '2021-05' ym UNIOn ALL
    SELECT '2021-06' ym UNIOn ALL
    SELECT '2021-07' ym UNIOn ALL
    SELECT '2021-08' ym UNIOn ALL
    SELECT '2021-09' ym UNIOn ALL
    SELECT '2021-10' ym UNIOn ALL
    SELECT '2021-11' ym UNIOn ALL
    SELECT '2021-12' ym
),
-- 当期入职人数
h AS (
    SELECT
        SUBSTR(hire_date,0,7) ym,
        COUNT(hire_date) c
    FROM staff
    GROUP BY SUBSTR(hire_date,0,7)
),
-- 当期离职人数
d AS (
    SELECt
        SUBSTR(departure_date,0,7) ym,
        COUNT(departure_date) c
    FROM staff
    GROUP BY SUBSTR(departure_date,0,7)
),
-- 月维度、当期入职人数、当期离职人数
staff_count_ym AS (
    SELECt
        t.ym ym,
        NVL(h.c,0) hire_count,
        NVL(d.c,0) departure_count
    FROM t
    LEFT JOIN h ON t.ym=h.ym
    LEFT JOIN d ON t.ym=d.ym
),
-- 添加：累计入职人数、累计离职人数
staff_accumulation_ym AS (
    SELECt
        ym,
        hire_count,
        departure_count,
        SUM(hire_count) OVER(
            ORDER BY ym
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) hire_accumulation,
        SUM(departure_count) OVER(
            ORDER BY ym
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) departure_accumulation
    FROM staff_count_ym
)
-- 添加：当期在职人数、离职率
SELECt
    ym,
    hire_count,
    departure_count,
    hire_accumulation,
    departure_accumulation,
    hire_accumulation-departure_accumulation AS on_job_count,
    departure_count/(departure_count+hire_accumulation-departure_accumulation) AS dimission_rate
FROM staff_accumulation_ym;