基于时间戳差值生成动态行数的SQL表实现求助

修正后的PL/pgSQL函数 #

以下是满足需求的完整函数实现，核心利用PostgreSQL的generate_series生成连续小时时间序列，再按需关联原表聚合speed数据：

CREATE OR REPLACE FUNCTION get_hourly_time_series(
    t1 timestamp without time zone,
    t2 timestamp without time zone
)
RETURNS TABLE(time timestamp, speed integer) 
LANGUAGE plpgsql
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
    -- 生成从t1到t2的每小时整点序列，关联原表聚合speed（以小时内平均值为例，可按需修改）
    RETURN QUERY
    SELECT 
        hourly.hour_time AS time,
        COALESCE(AVG(yt.speed)::integer, 0) AS speed  -- 处理无数据的小时，避免返回NULL
    FROM 
        generate_series(
            DATE_TRUNC('hour', t1),  -- 将t1截断到整点
            DATE_TRUNC('hour', t2),  -- 将t2截断到整点
            '1 hour'::interval
        ) AS hourly(hour_time)
    LEFT JOIN 
        your_table yt ON DATE_TRUNC('hour', yt.timestamp) = hourly.hour_time
    GROUP BY 
        hourly.hour_time
    ORDER BY 
        hourly.hour_time;
END;
$BODY$;

关键说明 #

• 生成时间序列：generate_series是PostgreSQL内置序列生成函数，通过将输入的t1、t2截断到整点，以1小时为步长生成连续时间点，自动覆盖所有需要的小时分段（含起始和结束时间）。
• 关联原表数据：用LEFT JOIN确保无数据的小时也能生成对应行；AVG(yt.speed)::integer是对小时内speed取平均值并转整数，可替换为MAX、MIN等其他聚合函数。
• 边界处理：DATE_TRUNC('hour', t1)确保无论输入时间是否为整点，都从对应整点开始生成序列；若需保留原时间的分钟/秒，可去掉DATE_TRUNC直接使用t1和t2。

使用示例 #

输入t1 = '2022-04-04 12:00'、t2 = '2022-04-04 14:00'，调用函数：

SELECT * FROM get_hourly_time_series('2022-04-04 12:00'::timestamp, '2022-04-04 14:00'::timestamp);

返回结果示例：

原伪代码修正点 #

1. 补全完整函数定义（CREATE OR REPLACE FUNCTION），原伪代码缺少函数名和开头定义部分。
2. 替换错误的GROUP BY (HOUR FROM TIMESTMAP)语法，改用PostgreSQL标准的DATE_TRUNC('hour', ...)按小时分组。
3. 移除多余的rownumber参数，generate_series会根据t1和t2的时间差自动生成对应行数。
4. 将RETURN QUERY与具体查询语句关联，确保函数能正确返回结果集。

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