如何在PostgreSQL中基于不规则费率数据生成小时级阶梯时间序列

问题场景 #

我们在SQL表中存储费率类数据（例如电价），表结构及示例数据如下：

表中数据的时间间隔无规律，规则为：每条记录的费率会持续生效至下一条记录的时间点，之后被新费率替代。我们需要生成小时级的时间序列，每个时间点对应正确的费率，示例输出如下：

解决方案 #

可以通过**窗口函数LEAD()**获取每条记录的下一个生效时间，再结合generate_series()生成小时级时间序列，最后关联对应费率。

假设原表名为rate_data，具体SQL语句如下：

WITH rate_with_next_date AS (
    SELECT
        date AS start_date,
        value,
        -- 获取下一条记录的时间，最后一条记录设为当前时间（可按需调整）
        LEAD(date, 1, NOW()) OVER (ORDER BY date) AS end_date
    FROM rate_data
)
SELECT
    -- 生成小时级时间点，注意对齐到小时
    generate_series(
        date_trunc('hour', start_date),
        -- 最后一个小时区间的结束处理：如果end_date不是整点，取前一个整点+1小时
        CASE
            WHEN end_date = date_trunc('hour', end_date) THEN end_date
            ELSE date_trunc('hour', end_date) + INTERVAL '1 hour'
        END,
        INTERVAL '1 hour'
    ) AS date,
    value
FROM rate_with_next_date
-- 过滤掉超过end_date的时间点
WHERE generate_series <= end_date
ORDER BY date;

语句说明 #

1. rate_with_next_date CTE：

   • 使用LEAD()窗口函数，按时间顺序获取每条记录的下一个生效时间；
   • 最后一条记录没有后续数据，用NOW()作为默认结束时间（你可以根据需求替换为固定的未来时间，比如'2030-01-01'::timestamp）。

2. 生成小时序列：

   • date_trunc('hour', start_date)将起始时间对齐到小时；
   • 对结束时间做处理：如果结束时间不是整点，将其对齐到小时后加1小时，确保覆盖到结束时间所在的小时；
   • generate_series()生成从起始小时到处理后的结束小时的所有整点时间。

3. 过滤与排序：

   • 过滤掉超过实际结束时间的时间点，避免最后一个小时的时间超出费率生效范围；
   • 按时间排序得到最终序列。

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