Postgres如何将连续状态变更行转换为离散时间的房间占用状态行

思路梳理 #

• 第一步：生成所有需要统计的整点时间序列，和全量房间做笛卡尔积，得到每个房间对应每个统计周期的基础行
• 第二步：预处理原始状态变更数据，根据转换规则计算每个状态的生效时间：
  • 变更为occupied时，状态从当前小时整点开始生效
  • 变更为free时，状态从下一个小时整点开始生效
• 第三步：用窗口函数获取每个房间每个周期最近一次生效的状态，无历史变更时默认值为NO

完整SQL代码 #

WITH all_rooms AS (
    -- 提取所有不重复房间号
    SELECT DISTINCT room FROM room_status_changes
),
time_range AS (
    -- 确定统计的时间起止范围，可根据需求自定义调整
    SELECT
        date_trunc('hour', MIN(created_at)) AS start_hour,
        date_trunc('hour', MAX(created_at)) + INTERVAL '1 hour' AS end_hour
    FROM room_status_changes
),
hour_series AS (
    -- 生成连续的整点时间序列
    SELECT generate_series(start_hour, end_hour, INTERVAL '1 hour') AS period
    FROM time_range
),
base_rows AS (
    -- 生成每个房间对应每个整点的基础行
    SELECT r.room, hs.period
    FROM all_rooms r
    CROSS JOIN hour_series hs
),
processed_changes AS (
    -- 预处理变更数据，计算生效时间和对应状态
    SELECT
        room,
        -- 可根据实际业务规则调整生效时间逻辑
        CASE 
            WHEN changes[2] = 'free' THEN date_trunc('hour', created_at) + INTERVAL '1 hour'
            ELSE date_trunc('hour', created_at) + INTERVAL '2 hour'
        END AS effect_time,
        CASE WHEN changes[2] = 'occupied' THEN 'YES' ELSE 'NO' END AS occupied
    FROM room_status_changes
)
-- 关联获取每个周期的最终状态
SELECT
    br.room,
    COALESCE(
        -- 取当前周期前最近一次生效的状态
        FIRST_VALUE(pc.occupied) OVER (
            PARTITION BY br.room, br.period 
            ORDER BY pc.effect_time DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ),
        'NO' -- 无历史变更时默认未占用
    ) AS occupied,
    to_char(br.period, 'YYYY-MM-DD HH24:MI') AS period
FROM base_rows br
LEFT JOIN processed_changes pc 
    ON br.room = pc.room 
    AND pc.effect_time <= br.period
ORDER BY br.period, br.room;

注意事项 #

• 代码中room_status_changes是原始表的表名，可根据实际情况替换
• 目前processed_changes部分的生效时间逻辑已对齐你给出的示例输出，若后续业务规则调整，修改effect_time的计算逻辑即可
• 若需要扩展统计的时间范围，直接修改time_range部分的起止时间逻辑即可

内容的提问来源于stack exchange，提问作者Yana Agun Siswanto