如何用SQL创建表格统计员工病假申请数量及起止日期

需求 #

创建按EmployeeID排序的表格，查看所有病假申请，展示病假时段的开始和结束时间。

当前使用的SQL查询 #

SELECT
    EmployeeID,
    StartDateTime, EndDateTime,
    hourtype,
    LEAD(hourtype, 1, 'other') OVER (PARTITION BY EmployeeID ORDER BY StartDateTime) AS NextHourType
FROM
    dflxs.stg_registeredhours 
WHERE
    employeeID = 29

当前查询结果 #

查询返回了EmployeeID=29的多条工时记录，包含EmployeeID、StartDateTime、EndDateTime、hourtype、NextHourType列，其中包含多条病假相关的工时记录。

预期结果 #

解决方案 #

要聚合连续的病假时段并生成预期格式，可采用间隙和孤岛分组方法，SQL语句如下：

WITH sickness_groups AS (
    SELECT
        EmployeeID,
        StartDateTime,
        EndDateTime,
        hourtype,
        -- 标记连续病假的分组：当前/前一条不是病假时，分组ID递增
        SUM(CASE WHEN hourtype = '病假' AND LAG(hourtype) OVER (PARTITION BY EmployeeID ORDER BY StartDateTime) = '病假' THEN 0 ELSE 1 END) 
            OVER (PARTITION BY EmployeeID ORDER BY StartDateTime) AS group_id
    FROM
        dflxs.stg_registeredhours
    WHERE
        hourtype = '病假' -- 仅筛选病假记录
),
grouped_sickness AS (
    SELECT
        EmployeeID,
        CAST(MIN(StartDateTime) AS DATE) AS Startdate,
        CAST(MAX(EndDateTime) AS DATE) AS Enddate,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY MIN(StartDateTime)) AS Sickness_call
    FROM
        sickness_groups
    GROUP BY
        EmployeeID, group_id
)
SELECT
    EmployeeID,
    Startdate,
    Enddate,
    Sickness_call
FROM
    grouped_sickness
ORDER BY
    EmployeeID, Startdate;

说明 #

1. 第一个CTEsickness_groups：通过LAG函数判断病假记录的连续性，生成分组ID，将连续的病假归为同一组。
2. 第二个CTEgrouped_sickness：按员工和分组聚合，取每组最早的开始日期、最晚的结束日期，并用ROW_NUMBER生成病假申请序号。
3. 最终结果按员工ID和开始日期排序，匹配预期表格格式。

注意：需将SQL中的'病假'替换为业务实际对应的hourtype值。

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