Talend多行转一行：合并同员工同原因连续日期缺勤记录

这是一个典型的间隙与孤岛（Gaps and Islands）问题，专门用来处理连续序列的分组合并需求。我会用SQL给出清晰易懂的实现方案，适配大多数主流关系型数据库（比如MySQL 8.0+、PostgreSQL、SQL Server等）。

实现思路 #

核心逻辑是给每一段连续的记录分配唯一的组ID，再按组聚合合并。具体步骤如下：

1. 分区排序：按EmployeeNr（员工编号）和Reason（缺勤原因）分区，确保只在同一员工同一原因的范围内判断日期连续性，再按startTime排序。
2. 判断连续性：用窗口函数LAG()获取当前记录的上一条记录日期，计算两者的天数差，差值为1则说明连续，否则标记为新组起点。
3. 生成组ID：通过累计求和的方式，给同一连续段的记录分配相同的组ID。
4. 聚合合并：按员工、原因和组ID分组，取该段最早的startTime和最晚的EndTime，同时保留部门和姓名（同一员工的这两个字段固定，用MIN()或MAX()均可）。

具体SQL代码 #

WITH ranked_records AS (
    SELECT
        sector,
        EmployeeNr,
        Name,
        Reason,
        startTime,
        EndTime,
        -- 提取日期部分用于判断连续性
        DATE(startTime) AS record_date,
        -- 获取同组上一条记录的日期，无则为NULL
        LAG(DATE(startTime)) OVER (PARTITION BY EmployeeNr, Reason ORDER BY startTime) AS prev_record_date,
        -- 判断是否为新组：日期差不为1则标记为新组起点
        CASE
            WHEN DATEDIFF(DATE(startTime), LAG(DATE(startTime)) OVER (PARTITION BY EmployeeNr, Reason ORDER BY startTime)) = 1 THEN 0
            ELSE 1
        END AS is_new_group
    FROM your_table_name
),
grouped_records AS (
    SELECT
        *,
        -- 累计求和生成组ID，同一连续段的组ID一致
        SUM(is_new_group) OVER (PARTITION BY EmployeeNr, Reason ORDER BY startTime) AS group_id
    FROM ranked_records
)
SELECT
    sector,
    EmployeeNr,
    Name,
    Reason,
    MIN(startTime) AS startTime,
    MAX(EndTime) AS EndTime
FROM grouped_records
GROUP BY sector, EmployeeNr, Name, Reason, group_id
ORDER BY EmployeeNr, startTime;

代码细节说明 #

• ranked_records CTE：负责标记每条记录的上一条日期，并判断是否开启新组。不同数据库的日期差函数可能略有差异，比如PostgreSQL可以用DATE_PART('day', record_date - prev_record_date)替代DATEDIFF，按需调整即可。
• grouped_records CTE：通过累计is_new_group的值生成组ID——第一次遇到is_new_group=1时组ID变为1，后续连续记录的组ID保持不变，直到下一个新组起点出现。
• 最终聚合：按组ID分组后，取该段的最早开始时间和最晚结束时间，正好符合你想要的合并结果。

验证结果 #

用你提供的样本数据运行这段代码，会得到完全匹配期望的输出：

如果你的数据库不支持CTE（比如MySQL 5.x），可以把CTE转换成嵌套子查询，逻辑完全一致。

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