如何从Type 1维度表提取员工在职的月份与年份？

需求背景 #

现有Type 1员工维度表，需结合通用日历表提取每个员工的在职对应年月。规则：若员工雇佣日期与重新雇佣日期不同，则默认其在重新雇佣日期的前一个月离职（如emp_id=4视为2024年11月后离职，2024年12月复职）。

原始表结构与数据 #

EmployeeData表 #

CREATE TABLE EmployeeData 
(
    emp_id INT PRIMARY KEY,
    hiredate DATE,
    rehiredate DATE,
    termination_date DATE
);

INSERT INTO EmployeeData (emp_id, hiredate, rehiredate, termination_date)
VALUES 
  (1, '2024-12-01', '2025-09-01', NULL),
  (2, '2024-11-01', '2024-11-01', NULL),
  (3, '2024-12-06', '2024-12-06', '2025-01-10'),
  (4, '2024-11-06', '2024-12-06', '2025-01-12');

EmployeeMonthlyData（示例参考表） #

CREATE TABLE EmployeeMonthlyData (
    emp_id INT,
    hiredate DATE,
    month VARCHAR(20),
    year INT,
    termination_date DATE
);

INSERT INTO EmployeeMonthlyData (emp_id, hiredate, month, year, termination_date)
VALUES 
    (1, '2024-12-01', 'January', 2025, NULL),
    (1, '2025-01-09', 'February', 2025, NULL),
    (2, '2024-11-01', 'November', 2024, NULL),
    (2, '2024-11-01', 'December', 2024, NULL),
    (2, '2024-11-01', 'January', 2025, NULL),
    (3, '2024-12-06', 'December', 2024, '2025-01-10'),
    (3, '2024-12-06', 'January', 2025, '2025-01-10'),
    (4, '2024-11-06', 'November', 2024, '2025-01-12'),
    (4, '2024-12-06', 'December', 2024, '2025-01-12');

解决方案 #

1. 创建通用日历表 #

先构建包含连续年月的日历表，覆盖业务所需日期范围：

CREATE TABLE Calendar (
    year INT,
    month INT,
    month_name VARCHAR(20),
    first_day DATE,
    last_day DATE,
    PRIMARY KEY (year, month)
);

-- 插入2024-2025年的月度数据
INSERT INTO Calendar (year, month, month_name, first_day, last_day)
VALUES
(2024, 11, 'November', '2024-11-01', '2024-11-30'),
(2024, 12, 'December', '2024-12-01', '2024-12-31'),
(2025, 1, 'January', '2025-01-01', '2025-01-31'),
(2025, 2, 'February', '2025-02-01', '2025-02-28'),
(2025, 9, 'September', '2025-09-01', '2025-09-30');

2. 生成员工在职时间区间 #

通过CTE拆分首次雇佣和复职的时间区间，处理离职规则：

WITH EmployeePeriods AS (
    -- 首次雇佣区间
    SELECT
        emp_id,
        hiredate AS start_date,
        CASE
            WHEN rehiredate IS NOT NULL AND rehiredate != hiredate THEN LAST_DAY(DATE_SUB(rehiredate, INTERVAL 1 MONTH))
            ELSE COALESCE(termination_date, CURDATE())
        END AS end_date
    FROM EmployeeData
    UNION ALL
    -- 复职区间（仅当复职日期与雇佣日期不同时）
    SELECT
        emp_id,
        rehiredate AS start_date,
        COALESCE(termination_date, CURDATE()) AS end_date
    FROM EmployeeData
    WHERE rehiredate IS NOT NULL AND rehiredate != hiredate
)

3. 关联日历表提取在职年月 #

将时间区间与日历表关联，筛选出有重叠的年月：

WITH EmployeePeriods AS (
    -- 首次雇佣区间
    SELECT
        emp_id,
        hiredate AS start_date,
        CASE
            WHEN rehiredate IS NOT NULL AND rehiredate != hiredate THEN LAST_DAY(DATE_SUB(rehiredate, INTERVAL 1 MONTH))
            ELSE COALESCE(termination_date, CURDATE())
        END AS end_date
    FROM EmployeeData
    UNION ALL
    -- 复职区间（仅当复职日期与雇佣日期不同时）
    SELECT
        emp_id,
        rehiredate AS start_date,
        COALESCE(termination_date, CURDATE()) AS end_date
    FROM EmployeeData
    WHERE rehiredate IS NOT NULL AND rehiredate != hiredate
)
SELECT
    ep.emp_id,
    c.year,
    c.month_name AS month,
    -- 取对应区间的生效雇佣日期
    CASE 
        WHEN ep.start_date >= c.first_day THEN ep.start_date
        ELSE (SELECT hiredate FROM EmployeeData WHERE emp_id = ep.emp_id)
    END AS effective_hire_date,
    ep.end_date AS termination_date
FROM EmployeePeriods ep
JOIN Calendar c
    ON c.first_day <= ep.end_date AND c.last_day >= ep.start_date
ORDER BY ep.emp_id, c.year, c.month;

查询结果 #

内容的提问来源于stack exchange，提问作者Kofisam