如何从满足特定条件的行中获取连续日期区间？

问题描述 #

我有一张存储员工职位状态的users_position表，包含两种user_position类型。规则是：同一user_id的下一条记录如果date_position是当前记录的次日，且user_position未变化，则视为连续日期区间；同一用户单日不会有不同职位。需要将连续的相同职位记录合并为一条区间记录，包含起始和结束日期。

解决方案 #

使用窗口函数标记连续区间，再通过聚合得到最终结果：

WITH position_groups AS (
    SELECT
        user_id,
        user_position,
        date_position,
        -- 标记连续区间：当前记录与上一条不满足连续条件时，生成新分组
        SUM(CASE
            WHEN LAG(date_position) OVER (PARTITION BY user_id, user_position ORDER BY date_position) + INTERVAL '1 day' = date_position
            THEN 0
            ELSE 1
        END) OVER (PARTITION BY user_id, user_position ORDER BY date_position) AS group_id
    FROM users_position
)
SELECT
    user_id,
    user_position,
    TO_CHAR(MIN(date_position), 'DD.MM.YYYY') AS position_start,
    TO_CHAR(MAX(date_position), 'DD.MM.YYYY') AS position_end
FROM position_groups
GROUP BY user_id, user_position, group_id
ORDER BY user_id, position_start;

逻辑说明 #

1. 标记连续区间：
   • 用LAG(date_position)窗口函数，获取同一用户、同一职位的上一条记录的日期
   • 判断当前记录的日期是否是上一条的次日，若不是则标记为新分组，通过SUM累加生成每个连续区间的唯一group_id
2. 聚合生成区间：
   • 按user_id、user_position和group_id分组，取每组的最小日期作为区间起始，最大日期作为区间结束
   • 用TO_CHAR将日期格式化为期望的DD.MM.YYYY样式

执行后将得到与期望完全匹配的结果。

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