MySQL跨多条日期记录查询员工最近连续工作起止日期

现有存储员工工作现场履职时段的数据表emp_data，每条记录包含3个字段：

• employee：员工标识
• start_date：工作开始日期
• end_date：工作结束日期
  需要编写MySQL查询语句，识别每位员工无休假的最近一段连续工作时段，返回该连续时段对应的起始日期与结束日期。

连续工作判定规则：相邻两段工作记录中，前一段的结束日期加1天等于后一段的开始日期，即视为两段工作之间无休假，属于同一连续工作时段。

表结构与初始化数据 #

CREATE TABLE `emp_data` (
 `employee` varchar(25) NOT NULL,
 `start_date` date NOT NULL,
 `end_date` date NOT NULL
);
INSERT INTO `emp_data` (`employee`, `start_date`, `end_date`) VALUES 
('Brian', '2022-04-15', '2022-04-20'), 
('Brian', '2022-04-21', '2022-05-05'),
('Brian', '2022-05-25', '2022-05-31'),
('Brian', '2022-06-01', '2022-06-06'),
('Doug', '2022-04-25', '2022-04-29'),
('Doug', '2022-05-18', '2022-05-19'), 
('Doug', '2022-05-20', '2022-06-01');

原始数据明细 #

Brian | 2022-04-15 | 2022-04-20
Brian | 2022-04-21 | 2022-05-05 
Brian | 2022-05-25 | 2022-05-31 
Brian | 2022-06-01 | 2022-06-06 
Doug  | 2022-04-25 | 2022-04-29 
Doug  | 2022-05-18 | 2022-05-19
Doug  | 2022-05-20 | 2022-06-01

期望输出 #

Brian | 2022-05-25 | 2022-06-06
Doug  | 2022-05-18 | 2022-06-01

基于MySQL 8.0及以上版本支持的窗口函数实现，逻辑步骤如下：

• 按员工分组、工作开始日期升序排序，通过LAG函数获取每段工作的上一段工作结束日期
• 判定当前段和上一段是否连续，累加标记生成连续工作块的分组ID
• 按员工+连续工作块分组，计算每个块的最早开始日期、最晚结束日期
• 按每个员工的连续块结束日期倒序排序，取排名第一的最新连续块作为结果

完整查询SQL #

WITH consecutive_tag AS (
    SELECT
        employee,
        start_date,
        end_date,
        SUM(
            CASE WHEN DATE_ADD(prev_end, INTERVAL 1 DAY) = start_date THEN 0 ELSE 1 END
        ) OVER (PARTITION BY employee ORDER BY start_date) AS block_id
    FROM (
        SELECT
            employee,
            start_date,
            end_date,
            LAG(end_date, 1) OVER (PARTITION BY employee ORDER BY start_date) AS prev_end
        FROM emp_data
    ) t
),
block_agg AS (
    SELECT
        employee,
        block_id,
        MIN(start_date) AS continuous_start,
        MAX(end_date) AS continuous_end,
        ROW_NUMBER() OVER (PARTITION BY employee ORDER BY MAX(end_date) DESC) AS rn
    FROM consecutive_tag
    GROUP BY employee, block_id
)
SELECT
    employee,
    continuous_start AS start_date,
    continuous_end AS end_date
FROM block_agg
WHERE rn = 1;

执行上述SQL后返回结果和期望完全一致：

• Brian的最近连续工作段为2022-05-25至2022-06-06：2022-05-05到2022-05-25之间存在休假间隔，因此该段和更早的2022-04-15至2022-05-05段不属于同一连续块
• Doug的最近连续工作段为2022-05-18至2022-06-01：三段记录中后两段日期间隔为1天无休假，属于同一连续块，早于该块的2022-04-25至2022-04-29段存在间隔，不属于最新连续块

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