统计跨周末的病假及假期时长需求与实现问题

核心思路 #

用间隙和岛屿逻辑识别连续请假周期（包含中间的周末），将无请假类型标记的周末行关联到对应员工的最近请假组，最终按组聚合得到所需指标。

字段定义确认 #

基于问题描述，默认字段含义：

• Name: 员工姓名
• Date: 日期（格式如YYYY-MM-DD）
• Workday: 整数标记，1=工作日，0=周末
• Calendarday: 可直接用Date字段替代（若重复可忽略）
• Leave: 请假类型（如「年假」「病假」），NULL对应周末日期

分步SQL实现 #

1. 标记有效请假行的周期组ID

给所有非周末的请假行生成组标识，同一连续周期内的行将拥有相同组ID：

WITH leave_groups AS (
    SELECT 
        Name,
        Date,
        Workday,
        Leave,
        -- 同一员工同类型请假下，连续日期的组ID一致
        DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY Name, Leave ORDER BY Date), Date) AS group_id
    FROM your_table
    WHERE Leave IS NOT NULL
),

2. 关联周末行到对应请假组

将周末行匹配到最近的、同员工的请假组，确保周末被纳入对应周期：

weekend_matched AS (
    SELECT 
        t.Name,
        t.Date,
        t.Workday,
        t.Leave,
        lg.group_id
    FROM your_table t
    LEFT JOIN leave_groups lg 
        ON t.Name = lg.Name 
        AND lg.Date = (
            SELECT MAX(Date) 
            FROM leave_groups 
            WHERE Name = t.Name AND Date <= t.Date
        )
    WHERE t.Leave IS NULL
),

3. 合并数据并聚合统计

合并标记好组ID的请假行与周末行，按组计算周期指标：

all_combined AS (
    SELECT * FROM leave_groups
    UNION ALL
    SELECT * FROM weekend_matched
)
SELECT 
    Name,
    Leave,
    MIN(Date) AS PeriodStartDate,
    MAX(Date) AS PeriodEndDate,
    SUM(Workday) AS Workdays, -- 仅统计工作日总数
    DATEDIFF(DAY, MIN(Date), MAX(Date)) + 1 AS Weekdays -- 含周末的日历日总数
FROM all_combined
WHERE Leave IS NOT NULL -- 过滤无对应请假组的孤立周末
GROUP BY Name, Leave, group_id
ORDER BY Name, PeriodStartDate;

关键细节说明 #

• 间隙和岛屿的核心是DATEADD(DAY, -ROW_NUMBER(), Date)：同一连续周期的日期减去对应行号后，得到的基准日期相同，以此区分不同请假周期。
• 若Workday不是标记值，可替换为SUM(CASE WHEN DATEPART(WEEKDAY, Date) IN (2,3,4,5,6) THEN 1 ELSE 0 END)统计工作日（不同数据库的星期编号有差异，需自行调整）。

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