如何查找时序数据中的间断并生成人员状态变更记录？

需要分析数据集，找出时间列中的间隙，并生成每个人员各时间段的前后状态切换记录，规则如下：

• 若valid_from前一天无数据，old_value取0；若有数据则取对应值。
• 若valid_to后一天无数据，new_value取0。
• 同一person_id不会出现valid_to与下一个valid_from重叠的情况。

输入数据 #

预期结果 #

使用窗口函数LAG/LEAD获取前后记录的关联信息，结合UNION ALL拼接四种类型的状态切换记录，覆盖所有场景：

WITH ranked_data AS (
    SELECT
        person_id,
        current_value,
        valid_from,
        valid_to,
        -- 获取前一条记录的状态值
        LAG(current_value) OVER (PARTITION BY person_id ORDER BY valid_from) AS prev_value,
        -- 获取下一条记录的开始时间
        LEAD(valid_from) OVER (PARTITION BY person_id ORDER BY valid_from) AS next_valid_from,
        -- 记录当前行在人员分组中的序号
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY valid_from) AS rn,
        -- 人员分组的总记录数
        COUNT(*) OVER (PARTITION BY person_id) AS total_rows
    FROM your_table_name
)
-- 1. 初始状态切换：0 -> 第一个状态值
SELECT
    person_id,
    '0' AS old_value,
    current_value AS new_value,
    valid_from
FROM ranked_data
WHERE rn = 1

UNION ALL

-- 2. 正常记录间的状态切换：前一个状态 -> 当前状态
SELECT
    person_id,
    prev_value AS old_value,
    current_value AS new_value,
    valid_from
FROM ranked_data
WHERE rn > 1

UNION ALL

-- 3. 最后一条记录结束后的状态切换：当前状态 -> 0
SELECT
    person_id,
    current_value AS old_value,
    '0' AS new_value,
    (valid_to + INTERVAL '1 day')::DATE AS valid_from
FROM ranked_data
WHERE rn = total_rows

UNION ALL

-- 4. 记录间隙的状态切换：当前状态 -> 0（间隙开始）
SELECT
    person_id,
    current_value AS old_value,
    '0' AS new_value,
    (valid_to + INTERVAL '1 day')::DATE AS valid_from
FROM ranked_data
WHERE next_valid_from IS NOT NULL
  AND (valid_to + INTERVAL '1 day')::DATE < next_valid_from

UNION ALL

-- 5. 记录间隙的状态切换：0 -> 下一个状态值（间隙结束）
SELECT
    rd1.person_id,
    '0' AS old_value,
    rd2.current_value AS new_value,
    rd2.valid_from
FROM ranked_data rd1
JOIN ranked_data rd2
    ON rd1.person_id = rd2.person_id
    AND rd2.rn = rd1.rn + 1
WHERE (rd1.valid_to + INTERVAL '1 day')::DATE < rd2.valid_from

ORDER BY person_id, valid_from;

逻辑说明 #

1. ranked_data CTE：为每个人员的记录排序，同时获取前序状态、后续记录的开始时间、行号和总记录数，为后续判断提供基础数据。
2. 初始状态：每个人员的第一条记录，生成从0到初始状态的切换。
3. 正常切换：非第一条记录，生成前一个状态到当前状态的切换。
4. 结束状态：每个人员的最后一条记录，生成从当前状态到0的切换。
5. 间隙处理：当两条记录的时间不连续时，先生成当前状态到0的切换（间隙开始），再生成0到下一个状态的切换（间隙结束）。

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