PostgreSQL按连续日期partition by分组合并为时间段的实现方案

实现思路 #

这是典型的连续值分组（岛屿问题），核心逻辑是利用连续日期与有序行号的差值固定的特性划分连续组：

• 按员工岗位分组，对每个岗位的日期从小到大排序生成行号
• 日期减去对应行号的天数，结果相同的即为同一连续时间段
• 最后按岗位和分组标识聚合，即可得到每个时间段的起止日期和天数

实现代码 #

假设业务表名为employee_special_dates，可直接执行以下SQL：

SELECT 
    EmployeePosition,
    MIN(DeviationDays) AS DateStart,
    MAX(DeviationDays) AS DateEnd,
    COUNT(*) AS CountDays
FROM (
    SELECT 
        EmployeePosition,
        DeviationDays,
        -- 生成连续组标识：连续日期的该值相同
        DeviationDays - ROW_NUMBER() OVER (PARTITION BY EmployeePosition ORDER BY DeviationDays) AS date_group
    FROM employee_special_dates
) t
GROUP BY EmployeePosition, date_group
ORDER BY EmployeePosition, DateStart;

结果验证 #

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

+----------------+------------+------------+-----------+
|employeeposition|datestart   |dateend     |countdays  |
+----------------+------------+------------+-----------+
|1925            |2021-09-06  |2021-09-06  |1          |
|1925            |2021-09-08  |2021-09-09  |2          |
|1925            |2021-09-21  |2021-09-23  |3          |
|1925            |2021-10-07  |2021-10-08  |2          |
+----------------+------------+------------+-----------+

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